Re: Library Cache
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 26 Dec 2008 11:30:00 -0800
Message-ID: <1230319798.787431@bubbleator.drizzle.com>
>
> 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 --
Date: Fri, 26 Dec 2008 11:30:00 -0800
Message-ID: <1230319798.787431@bubbleator.drizzle.com>
Mark D Powell wrote:
> 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 --
Julian Dyke has created to brilliant presentations on this topic that you can likely find by googling his name and the topic.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Dec 26 2008 - 13:30:00 CST