Re: Library Cache


<mrdjmagnet@xxxxxxx> wrote in message
news:3e0d392e-2c18-4ad2-b9d2-36273b652062@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Dec 29, 9:09 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
On Dec 29, 9:53 am, mrdjmag...@xxxxxxx 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.


.

Relevant Pages

  • Re: Dynamic SQL Method 4 : Avoid Re-parsing?
    ... applications that cannot know a finite set of SQL at compile-time; ... completely dynamic SQL. ... queries) using bind variables with a single function that communicates ... cursor, open cursor, fetch, and close cursor each and every time. ...
    (comp.databases.oracle.server)
  • Re: Library Cache
    ... which the procedure can parse and form the query and open a cursor for ... the PHP code to read. ... clause conditions you could change it to use bind variables in the ... You could replace the SQL in tables with SQL housed in stored ...
    (comp.databases.oracle.server)
  • Re: Library Cache
    ... clause conditions you could change it to use bind variables in the ... You could replace the SQL in tables with SQL housed in stored ... no criteria parameter for a given query, then it is not appended to ...
    (comp.databases.oracle.server)
  • Re: Library Cache
    ... procedures that determines the SQL to be submitted based on the ... These statements would all use bind variables. ... query string to parse, whereas: ... it's HOW and WHEN that replacement occurs. ...
    (comp.databases.oracle.server)
  • Re: Library Cache
    ... procedures that determines the SQL to be submitted based on the ... These statements would all use bind variables. ... query string to parse, whereas: ... it's HOW and WHEN that replacement occurs. ...
    (comp.databases.oracle.server)