Script to find bad SQL's
 
Short desc Show Bad Sql Info
Os Any
Tested on 8i,9i
Remarks set DESIRED_HIT_RATIO to level of hit ration you want to see
Submitted by Ajith Pathiyil
Date Jan 2005


Description show bad sql statements with hit ratio's less then what value you wish

 

Script source code
define DESIRED_HIT_RATIO = 0.7

SELECT sql_text,
executions,
round(disk_reads / executions, 2) "Physical Reads Per Run",
disk_reads as "Physical Reads",
buffer_gets as "Buffer Gets",
round((buffer_gets - disk_reads) / buffer_gets, 2)*100 as "SQL Hit Ration"
FROM v$sqlarea
WHERE executions > 0 and
buffer_gets > 0 and
(buffer_gets - disk_reads) / buffer_gets < &&DESIRED_HIT_RATIO
ORDER BY 3 desc
/