Re: Library Cache
Date: Mon, 29 Dec 2008 07:34:17 -0800 (PST)
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 --
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
USING p_b_date, p_e_date;
Is that correctly using the bind variables? Received on Mon Dec 29 2008 - 09:34:17 CST