Re: Library Cache

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 24 Dec 2008 14:08:11 -0800 (PST)
Message-ID: <7125521d-6a34-4cd3-8e1e-6d51870bf329@g3g2000pre.googlegroups.com>


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 -- Received on Wed Dec 24 2008 - 16:08:11 CST

Original text of this message