Re: Library Cache

On Dec 25, 3:16 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Default cursor_sharing value was and I believe still is EXACT and the statement on the fact the same session can reuse the cached plan was based on conversations with support back in 7.0 and 7.1 days. I do not remember why I do not expect other sessions to be able to share the same SQL statement though the V$SQL_SHARED_CURSOR view will often identify why SQL cannot be shared. A whole slew of session setable database parameters can prevent sharing of SQL. Changing the cursor_sharing parameter can improve Oracle's ability to share SQL but we have ran into performance issues due to plan changes that always forced us to back the parameter change off.

I will have to re-test this when I get back to work and get caught up on end of / beginning of year special tasks.

HTH -- Mark D Powell -- Received on Fri Dec 26 2008 - 11:00:06 CST