Re: Library Cache

<mrdjmagnet_at_aol.com> wrote in message
news:fa38303b-4488-4d94-932c-33e7077b6713_at_y1g2000pra.googlegroups.com... On Dec 29, 11:03 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> <mrdjmag..._at_aol.com> wrote in message
>
> news:3e0d392e-2c18-4ad2-b9d2-36273b652062_at_q30g2000prq.googlegroups.com...
> On Dec 29, 9:09 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
> > On Dec 29, 9:53 am, mrdjmag..._at_aol.com wrote:
>
> > > Hi,
>
> > > We installed this Spotlight for Oracle software. The software shows
> > > an 85% re-parse rate against the Library Cache. Here is the biggest
> > > issue we have. I do not think anything can be done, but maybe another
> > > brain can figure something out:
>
> > > We have a table with stored queries:
> > > Row 1: SELECT emp_num FROM employees
> > > Row 2: SELECT address FROM customer_address
>
> > > We have a criteria table which holds any criteria that the query MAY
> > > use:
> > > Row 1: WHERE emp_first =
> > > Row 1: AND emp_last =
> > > Row 2: WHERE customer_id =
>
> > > So, the PHP code can pass any number of parameters to the procedure,
> > > which the procedure can parse and form the query and open a cursor for
> > > the PHP code to read.
>
> > > This means that the queries may/may not be the same, and need to be
> > > re-
> > > parsed. So, we lose on that. Is there anything that can be done to
> > > tune these and make them faster? An index against every possible
> > > combination is not possible, and we have hundreds of these stored
> > > queries........
>
> > > Any smarter people have any ideas?
>
> > > Arthur
>
> > If the SQL built from the logic includes constants for the where
> > clause conditions you could change it to use bind variables in the
> > code. This would give you some reuse.
>
> > You could replace the SQL in tables with SQL housed in stored
> > procedures that determines the SQL to be submitted based on the
> > parameters passed in. These statements would all use bind variables.
> > The procedures could pass cursors back to the application.
>
> > Take a look at the value of your database parameter cursor_sharing.
> > The default is EXACT. You might be able to get some benefit from
> > changing it to SIMILAR or FORCE. SQL plan changes are possible if
> > this parameter is changed so some tuning may be required.
>
> > HTH -- Mark D Powell --
>
> Mark,
>
> How about a query done like this:
>
> v_optin_str := 'SELECT COUNT(*) FROM email_list a
> WHERE '||v_date_clause||' '||v_adid_clause||
> ' AND customer_id IS NOT NULL
> AND adid IS NOT NULL
> AND EXISTS (SELECT user_session
> FROM EMAIL_ALERTS b
> WHERE alert_type = ''OI''
> AND b.user_session =
> a.user_session)';
>
> EXECUTE IMMEDIATE v_optin_str
> INTO stats_tab(199)
> USING p_b_date, p_e_date;
>
> Is that correctly using the bind variables?
>
> Ugly, very ugly. No that isn't using bind variables. You need
> :bind_variable_name.

Yeah, we had some seriously shit programmers........time for an ALL STORED PROCEDURE fix.

Basically where ever there is dynamic SQL, it can be replaced by bind variables.

Just curious, what is the real difference between using "v_variable" and "USING :v_variable" if the values get replaced?

Sorry, I wasn't very clear. here is a simple example of what I mean: v_sqlstring:='select lastname from emp where empid=:bv_empid'; execute immediate v_sqlstring using 1234; the1234 will be the variable for the bind variable :bv_empid. (or execute immediate v_sqlstring using v_passedin_empid;

Jim Received on Mon Dec 29 2008 - 23:10:07 CST