Re: concerning hard parses
- From: Mladen Gogala <mgogala@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 7 Mar 2004 16:25:51 -0500
I forgot about that. I almost never use them.
On 03/07/2004 03:09:54 PM, Tim Gorman wrote:
> Ryan,
>
> Bind variables are always host variables ? two different terms for the same
> thing. I believe that the term "host variables" comes from PRO*Precompilers
> in particular...
>
> Mladen,
>
> SQL*Plus does have true ³bind variables²; the substitution variables
> (usually preceded by ³&²) are another matter altogether. SQL*Plus allows
> you to declare, initialize, and use bind variables as follows:
>
> SQL> variable b1 number
> SQL> exec :b1 := 100;
>
> PL/SQL procedure successfully completed.
>
> SQL> select count(*) from dba_objects where object_id = :b1;
>
> COUNT(*)
> ----------
> 1
>
> SQL> select count(*) from dba_objects where object_id = 100;
>
> COUNT(*)
> ----------
> 1
>
> As opposed to substitution variables, which you described:
>
> SQL> define V_OID = 100
> SQL> select count(*) from dba_objects where object_id = &&V_OID;
> old 1: select count(*) from dba_objects where object_id = &&V_OID
> new 1: select count(*) from dba_objects where object_id = 100
>
> COUNT(*)
> ----------
> 1
>
> Hope this helps....
>
> -Tim
>
>
> on 3/7/04 12:48 PM, Mladen Gogala at mgogala@xxxxxxxxxxxx wrote:
>
> > 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@xxxxxxxxxxxxx>
> >> To: <oracle-l@xxxxxxxxxxxxx>
> >> 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@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
> >> -----------------------------------------------------------------
> >>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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:
