Re: concerning hard parses

From: Tim Gorman <tim_at_sagelogix.com>
Date: Sun, 07 Mar 2004 13:09:54 -0700
Message-ID: <BC70CFA2.11164%tim@sagelogix.com>

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_at_adelphia.net 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_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
>> -----------------------------------------------------------------
>> 

----------------------------------------------------------------

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 - 14:07:49 CST