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: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sun, 7 Mar 2004 14:48:32 -0500
Message-ID: <20040307194832.GA18194@medo.adelphia.net>


Sqplus doesn't have "bind variables". Sqlplus acts like pre-processor (say cpp) and replaces &var with the the provided substitute.

On 03/07/2004 02:39:37 PM, Ryan wrote:
> 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
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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:45:46 CST

Original text of this message

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