Doc

     Name:    bufgets.sql
 
     Author:  Mark Gurry 

     This script lists all statements that are taking longer than
     2 seconds to scan through the buffer cache. The statements
     use 500 buffer scans per second, which we have found that
     most sites run at.

#

column "Response" format 999,999,999.99
ttitle 'List Statements in Shared Pool with the Most Buffer Gets'
select  sql_text, buffer_gets , 
        executions , 
        buffer_gets / decode(executions, 0,1,executions) "Average",
        ‘ Estimated Response = ‘, 
        buffer_gets / decode(executions, 0,1,executions) / 500 "Response"  
  from  v$sqlarea 
 where  buffer_gets / decode(executions, 0,1,executions) > 1000;