Re: Library Cache

From: <mrdjmagnet_at_aol.com>
Date: Mon, 29 Dec 2008 07:34:17 -0800 (PST)
Message-ID: <3e0d392e-2c18-4ad2-b9d2-36273b652062@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? Received on Mon Dec 29 2008 - 09:34:17 CST

Original text of this message