Library Cache

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 reparsed.   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 Received on Mon Dec 29 2008 - 08:53:25 CST