Doc

Name: SQL_10SEC.sql

Author: Mahnho Han

List all SQL statements that have a response time more than 10 seconds

#

column nl newline;

set heading off

set pagesize 9999

set linesize 80

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 sys.han_table;' nl,

'explain plan into sys.han_table for ' nl,

sql_text ||';' nl,

'@$HOME/mhan/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_%'

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 sys.han_table;' nl,

'explain plan into sys.han_table for ' nl,

sql_text ||';' nl,

'@$HOME/mhan/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_%'

order by executions desc

/

spool off

spool badstmt.lis

doc

got an excessive number of disk reads

#

@diskread.sql

doc

got an excessive number of scan through the buffer cache.

#

@bufget.sql

spool off