set pagesize 66 linesize 180 trimspool on
 set echo off
 column executions      heading "Execs"      format 99999999
 column rows_processed  heading "Rows Procd" format 99999999
 column loads           heading "Loads"      format 999999.99
 column buffer_gets     heading "Buffer Gets"
 column disk_reads     heading "Disk Reads"
 column elapsed_time   heading "Elasped Time" format 999999999
 column cpu_time       heading "CPU Time" format 999999999
 column  Text        heading "SQL Text"   format a50 wrap
 column avg_cost        heading "Avg Cost"   format 99999999
 column etime_per_exec   heading "ETime Per Exec"   format 999999999
 column ctime_per_exec   heading "CPU Time Per Exec"   format 999999999
 column gets_per_exec   heading "Gets Per Exec"   format 99999999
 column reads_per_exec   heading "Read Per Exec"   format 99999999
 column rows_per_exec   heading "Rows Per Exec"   format 99999999

 break on report
 compute sum      of rows_processed     on report
 compute sum      of executions         on report
 compute avg      of avg_cost           on report
 compute avg      of etime_per_exec      on report
 compute avg      of ctime_per_exec      on report
 compute avg      of gets_per_exec      on report
 compute avg      of reads_per_exec      on report
 compute avg      of row_per_exec      on report

 PROMPT 
 PROMPT Top 10 most expensive SQL (Elapsed Time (secs) )...
 PROMPT
 select rownum as rank, a.*
 from (
 select elapsed_Time/1000000 elapsed_time, 
 executions,
 buffer_gets,
 disk_reads,
 cpu_time
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where elapsed_time/1000000 > 5
 order by elapsed_time desc) a
 where rownum < 11
 /

 PROMPT 
 PROMPT Top 10 most expensive SQL (Elapsed Time (secs) Per Exec )...
 PROMPT
 select rownum as rank, a.*
 from (
 select elapsed_Time/1000000 elapsed_time, 
 executions,
 elapsed_Time / (1000000 * decode(executions,0,1, executions) ) etime_per_exec,
 buffer_gets,
 disk_reads,
 cpu_time
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where elapsed_time/1000000 > 5
 order by etime_per_exec desc) a
 where rownum < 11
 /

 PROMPT 
 PROMPT Top 10 most expensive SQL (CPU Time (secs) )...
 PROMPT
 col substr(sql_text,1,50) Text for a99
 select rownum as rank, a.*
 from (
 select cpu_time/1000000 cpu_time, 
 executions,
 buffer_gets,
 disk_reads,
 cpu_time
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where cpu_time/1000000 > 5
 order by cpu_time desc) a
 where rownum < 11
 /

 PROMPT 
 PROMPT Top 10 most expensive SQL (CPU Time (secs) per Exec)...
 PROMPT
 select rownum as rank, a.*
 from (
 select cpu_time/1000000 cpu_time, 
 executions,
 cpu_time / (1000000 * decode(executions,0,1, executions)) ctime_per_exec,
 buffer_gets,
 disk_reads,
 cpu_time
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where cpu_time/1000000 > 5
 order by ctime_per_exec desc) a
 where rownum < 11
 /


 PROMPT 
 PROMPT Top 10 most expensive SQL (Buffer Gets)...
 PROMPT
 select rownum as rank, a.*
 from (
 select buffer_gets, 
 executions,
 buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where buffer_gets > 50000
 order by buffer_gets desc) a
 where rownum < 11
 /

 PROMPT 
 PROMPT Top 10 most expensive SQL (Buffer Gets by Executions)...
 PROMPT
 select rownum as rank, a.*
 from (
 select buffer_gets, 
 executions,
 buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where buffer_gets > 50000
 order by gets_per_exec desc) a
 where rownum < 11
 /

 PROMPT
 PROMPT Top 10 most expensive SQL (Physical Reads)...
 PROMPT
 select rownum as rank, a.*
 from (
 select disk_reads, 
 executions,
 disk_reads / decode(executions,0,1, executions) reads_per_exec,
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where disk_reads > 10000
 order by disk_reads desc) a
 where rownum < 11
 /

 PROMPT Top 10 most expensive SQL (Physical Reads by Executions)...
 PROMPT
 select rownum as rank, a.*
 from (
 select disk_reads, 
 executions,
 disk_reads / decode(executions,0,1, executions) reads_per_exec,
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where disk_reads > 10000
 order by reads_per_exec desc) a
 where rownum < 11
 /

 PROMPT Top 10 most expensive SQL (Rows Processed by Executions)...
 PROMPT
 select rownum as rank, a.*
 from (
 select rows_processed, 
 executions,
 rows_processed / decode(executions,0,1, executions) rows_per_exec,
 hash_value,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where rows_processed > 10000
 order by rows_per_exec desc) a
 where rownum < 11
 /

 PROMPT 
 PROMPT Top 10 most expensive SQL (Buffer Gets vs Rows Processed)...
 PROMPT
 select rownum as rank, a.*
 from (
 select buffer_gets, lpad(rows_processed ||
 decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
 executions, loads,
 (decode(rows_processed,0,1,1)) *
 buffer_gets/ decode(rows_processed,0,1,
 rows_processed) avg_cost,
 substr(sql_text,1,50) Text
 from  v$sqlarea
 where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
 order by 5 desc) a
 where rownum < 11
 /

 rem Check to see if there are any candidates for procedures or 
 rem for using bind variables. Check this by comparing UPPER 
 rem
 rem This May be a candidate application for using the init.ora parameter
 rem CURSOR_SHARING = FORCE|SIMILAR

 select rownum as rank, a.*
 from (
 select     upper(substr(sql_text, 1, 65)) sqltext, count(*)
 from     v$sqlarea   
 group by upper(substr(sql_text, 1, 65))
 having count(*) > 1
 order by count(*) desc) a
 where rownum < 11
 /