Re: Library Cache

"Ind-dba" <oraclearora_at_googlemail.com> wrote in message news:cd58c96d-1deb-41d8-a0b9-e1c5ad28f924_at_g39g2000pri.googlegroups.com... On Dec 25, 3:08 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Dec 24, 3:46 pm, "gym dot scuba dot kennedy at gmail"
>
>
>
> <kenned..._at_verizon.net> wrote:
> > <mrdjmag..._at_aol.com> wrote in message
>
> >news:695797aa-6bd9-4c86-8b51-7f096343b3eb_at_s9g2000prm.googlegroups.com...
> > On Dec 24, 12:53 pm, "gym dot scuba dot kennedy at gmail"
>
> > <kenned..._at_verizon.net> wrote:
> > > <mrdjmag..._at_aol.com> wrote in message
>
> > >news:21d4d6db-6adb-4289-b5a4-76bae3d6b042_at_v5g2000prm.googlegroups.com...
>
> > > > Hi,
>
> > > > 2 questions here:
>
> > > > First, in terms of minimizing the number of parse/execute calls, it
> > > > is
> > > > suggested to use bind variables. Is a bind variable the same as a
> > > > plsql variable?
>
> > > > If I have a stored procedure with either of these in them, will it
> > > > remain in the library cache?
>
> > > > SELECT name FROM customer WHERE customer_id = v_customer_id;
> > > > SELECT name FROM customer WHERE customer_id = :cust_id;
>
> > > > Is statement #1 eligible to remain in the library cache?
>
> > > > Second, is using EXECUTE IMMEDIATE for doing a SELECT faster than a
> > > > normal coded SELECT?
>
> > > > Much Thanks!
>
> > > pl/sql will make the v_customer_id a bind variable when it compiles it
> > > (when
> > > you create the stored proc).
> > > No execute immediate will be slower and require more latching.(less
> > > scalable)
> > > Jim
>
> > So, I have this query:
>
> > SELECT customer_name FROM customer WHERE customer_id = v_cust_id AND
> > customer_department = 15;
>
> > That query will never have to be re-parsed. It has a literal, yes,
> > but the customer ID is a variable, so it can stay in the library
> > cache.....
> > yes- Hide quoted text -
>
> > - Show quoted text -
>
> When a new SQL statement is submitted to Oracle it is hard parsed
> meaning all parse steps have to be executed. When a statement is
> determined to have already been submitted it can be soft parsed which
> means some steps in the parse process can be skipped. Even when the
> statement contains a literal value Oracle should be able to soft parse
> it when it is submitted by the same session.
>
> HTH -- Mark D Powell --

> Hi Mark,

> Can you please clarify:
> "Even when the
> statement contains a literal value Oracle should be able to soft
> parse
> it when it is submitted by the same session. "

> Why do you think only same session will do a soft parse. Why cant ANY
> session will do a soft parse? .. but that may depend on CURSOR_SHARING
> parameter value.
> Am i on correct line of thinking?

> - Sachin

You sure did a good job confusing me.

How does one relate "a literal value" to "soft parse" and then to "the same session"? A statement containing a literal value alone does not make it less likely to be soft parsed. Soft parse in turn has nothing to do with being in the same session or not. Received on Fri Dec 26 2008 - 20:28:47 CST