Andrew Fraser DBA

Slow SQL report

Lists slow SQL in library cache for tuning investigations:

set pages 9999
spool c.lst
select elapsed_time/1000000 secs, executions,
  elapsed_time/1000000/greatest(executions,1) secs_per_exec,
sql_text from v$sql
where executions > 50
and elapsed_time/1000000/greatest(executions,1) > 1
order by 3 desc
/
spool off
ed c.lst

For older versions of oracle, use buffer_gets or disk_reads in place of elapsed_time.