set echo off
--
--  Name: topN_queries.sql
--
--  Description: SQL - Display Top-N Queries from v$sqlarea
--
--  Compatability: 7.3.x and above
--
--  Usage: @topN_queries
--
--

--spool c:\temp\topN_queries.lst

-- Top-N buffer gets per exec queries
set pages 100
set lines 100
prompt
prompt Top 30 buffergets per exec queries > 200000
select buffer_gets,  executions, bf_per_execs , sql_text 
from (select buffer_gets, executions, buffer_gets/decode(executions,0,1,executions) bf_per_execs, sql_text from v$sqlarea 
      order by bf_per_execs desc) 
where rownum < 31 and bf_per_execs > 200000 ;

-- Top-N physical reads per exec queries
set lines 100
prompt
prompt Top 30 physical reads per exec queries > 3000
select disk_reads,  executions,  dr_per_execs , sql_text 
from (select disk_reads, executions, disk_reads/decode(executions,0,1,executions) dr_per_execs, sql_text from v$sqlarea 
      order by dr_per_execs desc) 
where rownum < 31 and dr_per_execs > 3000 ;

-- Top-N executed queries
prompt
prompt Top 20 executed queries
select executions, buffer_gets, disk_reads, sql_text 
from (select executions, buffer_gets, disk_reads, sql_text from v$sqlarea 
      order by executions desc) 
where rownum < 21 and executions > 5000 ;

-- Top-N Queries with physical reads > 1000000
set lines 100
prompt
prompt Top-N Queries with physical reads > 1000000
select disk_reads,  executions,  dr_per_execs , sql_text 
from (select disk_reads, executions, disk_reads/decode(executions,0,1,executions) dr_per_execs, sql_text from v$sqlarea 
      order by disk_reads desc) 
where rownum < 31 and disk_reads > 1000000 ;

--spool off