Re: Library Cache

<mrdjmagnet_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 Received on Wed Dec 24 2008 - 14:46:10 CST