Doc


     Name:    sqlarea.sql

     Author:  Mark Gurry

     This script scans through the buffer cache and lists all statements
     that have a response time more than 10 seconds caused by disk reads or
     buffer gets (scans through the buffer cache). It also give the execution 
     paths of the statements.

#

Column nl newline;
set heading off
set pagesize 9999
set linesize 1000
set echo off 
set feedback off
set verify off
column runt format 999,999.99 newline;
spool diskread.sql

select  ' ' nl,
        'doc' nl,
        translate(sql_text, '&', ':')  nl,
'  Expected Run Time = '|| 
        DISK_Reads / decode(executions, 0, 1, executions) / 50 runt , 
        '  Executions = ' || executions nl, 
        '#' nl,
        'delete plan_table;' nl,
       'explain plan for ' nl,
       sql_text ||';' nl,
       '@/usr2/dev1/markg/plan '
from v$sqlarea
where disk_reads / decode(executions, 0, 1, executions) / 50 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
and length(sql_text) < 1000
order by executions desc
/
spool off
spool bufget.sql
select  ' ' nl,
        'doc' nl,
        translate(sql_text, '&', ':')  nl,
'  Expected Run Time = '|| 
        buffer_gets / decode(executions, 0, 1, executions) / 500 runt , 
        '  Executions = ' || executions nl, 
        '#' nl,
        'delete plan_table;' nl,
       'explain plan for ' nl,
       sql_text ||';' nl,
       '@/usr2/dev1/markg/plan '
from v$sqlarea
where buffer_gets / decode(executions, 0, 1, executions) / 500 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
and length(sql_text) < 1000
order by executions desc
/
spool off
spool badstmt.lis       
doc

    The following statements have got an excessive number of disk reads.
    This has caused the response time for the statement to exceed 10 seconds.

#
@diskread.sql
doc

    The following statements have got an excessive number of scans through the 
     buffer cache. This type of problem can cause a statement to become 
     a large CPU hog!

    This has caused the response time for the statement to exceed 10 seconds.

#   
@bufget.sql
spool off