So sqlplus bind variables are really just host variables?
----- Original Message -----
From: "Tim Gorman" <tim_at_sagelogix.com>
To: <oracle-l_at_freelists.org>
Sent: Sunday, March 07, 2004 2:25 PM
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 - 13:36:33 CST
