RE: concerning hard parses
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 7 Mar 2004 15:17:41 -0600
I believe it's safe to infer the closure from SQL trace data upon
observing either of two phenomena:
- STAT #n indicates that cursor #n has been closed.
- PARSING IN CURSOR #n, if #n has been used before, indicates that the
former cursor #n has been closed.
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
Sent: Sunday, March 07, 2004 1:25 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx at ryan.gaffuri@xxxxxxx 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
