| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: concerning hard parses
I believe it's safe to infer the closure from SQL trace data upon
observing either of two phenomena:
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Sunday, March 07, 2004 1:25 PM
To: oracle-l_at_freelists.org
Subject: Re: concerning hard parses
I haven't seen your script (might have gotten stripped out by the list handler?), but the problem is likely the test bed, SQL*Plus.
SQL*Plus is (apparently) coded to conserve cursors. SQL tracing a
session
in SQL*Plus shows that, no matter how often you execute the same query,
it
is parsed each time, an indirect indication that the previously-used
cursor
was closed (note: "close cursor" operations do not show up in SQL
tracing,
so I am only inferring the closure). Note that even when you execute a
different SQL statement, the same cursor number is reused. That is,
each
"dep=0" (i.e. recursive depth zero) cursor always tends to show up with
the
same cursor number in SQL*Plus, a further indication of cursor re-use
and
conservation. SQL*Plus only seems to allocate a new cursor number for
each
new recursive depth (i.e. "dep" > 0)
In other words, it's just the way SQL*Plus is coded, nothing more
significant. SQL*Plus is not designed to reduce parsing, but it is
probably
designed to minimize cursor memory resources.
In contrast, most forms and reports tools and batch program APIs tend to
encourage the use of the HOLD_CURSOR=TRUE RELEASE_CURSOR=FALSE
philosophy,
where a new cursor is opened for each SQL statement (unless explicitly
closed), but by no means is it a requirement...
Hope this helps...
on 3/5/04 2:26 PM, ryan.gaffuri_at_cox.net at ryan.gaffuri_at_cox.net wrote:
> I ran a test with bind variables from sqlplus. I am not sure why Im
getting a
> hard parse.
>
> 1. create table myTable as select * from dba_objects;
> 2. I then ran a script to tell me how many parses my current session
has.
> 3. I then initialized a bind variable with 'test_bind.sql'
> and ran the following query from test_select.sql
>
> select object_name
> from mytable
> where object_name like :object_name
> and rownum < 2
>
> 4. I then re-initialized the bind variable to a different value and
ran it
> again. I got a hard parse. Shouldn't I get a soft parse since I'm
using bind
> variables?
>
> so that its readable. I attached the results and I attached the 3
scripts I
> used. I hope this is ok...
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun Mar 07 2004 - 15:17:11 CST
 |
 |