Doc

    Name:    diskread.sql

    Author:  Mark Gurry

    This script list all of the SQL statements currently stored in
    the shared pool that have disk reads > 4 seconds. The 4 seconds
    assumes 50 I/Os per second. 

#

column "Average"  format 999,999,999.99 
column "Response" format 999,999,999.99
ttitle 'List Statements in Shared Pool with the Most Disk Reads'
select  sql_text, disk_reads , 
        executions , 
        disk_reads / decode(executions, 0,1,executions) "Average",
        ‘ Estimated Response = ‘, 
        disk_reads / decode(executions, 0,1,executions) / 50 "Response"  
  from  v$sqlarea 
 where  disk_reads / decode(executions, 0,1,executions) > 200;