Re: Library Cache

<mrdjmagnet_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 =

   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. Received on Mon Dec 29 2008 - 11:03:54 CST