Friday, July 27, 2012

script to identify inefficient sql

This script reports on the apparent efficiency of SQL statements whose information presently
resides in the shared pool

SELECT   hash_value stmtid,
           SUM (disk_reads) disk_reads,
           SUM (buffer_gets) buffer_gets,
           SUM (rows_processed) rows_processed,
           SUM (buffer_gets) / GREATEST (SUM (rows_processed), 1) rpr,
           SUM (executions) executions,
           SUM (buffer_gets) / GREATEST (SUM (executions), 1) rpe,
           SQL_TEXT
    FROM   v$sql
   WHERE   command_type IN (2, 3, 6, 7)
GROUP BY   hash_value, SQL_TEXT
ORDER BY   5 DESC

No comments:

Post a Comment