topsql.sql

--
--
--     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'
drop table maptemp;
create table maptemp as
select  sql_text, buffer_gets , 
        executions , 
        buffer_gets / decode(executions, 0,1,executions) "AVERAGE",
        buffer_gets / decode(executions, 0,1,executions) / 500 "RESPONSE"  
  from  v$sqlarea 
 where  buffer_gets / decode(executions, 0,1,executions) > 1000;
select * from maptemp;
drop table maptemp;