Re: Library Cache

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