Way to determine what specific SQL statements causing lib cache misses

From: Mike <mikea_730_at_yahoo.com>
Date: 20 Mar 2003 16:02:12 -0800
Message-ID: <39f5fc53.0303201602.1b36c2d4@posting.google.com>

I'm trying to determine a way to report on what SQL statements currently in the Shared Pool are causing reloads. Would the following SQL statement work by determining the ratio of reloads to executions? I know that this would only apply to the SQL statements currently in the library cache. I also realize I could use SQL_TRACE, but I'd like to use a more general (not user specific) method to start with.

SELECT loads, executions, parse_calls,
(loads/executions) * 100 "Loads to Execution Ratio", sql_text FROM v$sqlarea
WHERE executions > 5
AND (loads/executions) * 100 > 5; -- > 5%

Does anyone know a better way?

Thanks,
Mike Received on Thu Mar 20 2003 - 18:02:12 CST