Re: concerning hard parses
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 07 Mar 2004 12:25:23 -0700
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
-----------------------------------------------------------------
Other related posts:
