Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: concerning hard parses

RE: concerning hard parses

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Sun, 7 Mar 2004 15:17:41 -0600
Message-ID: <001901c40489$a52da840$8b00470a@CVMLAP02>


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
> -----------------------------------------------------------------



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
-----------------------------------------------------------------

----------------------------------------------------------------
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US