Saturday 27 December 2008

Top SQLs by Disk Read via V$SQLAREA

with base_data as
  (
  select
  v.HASH_VALUE,
  disk_reads,
  executions,
  v.CPU_TIME,
  sql_text,
  v.FIRST_LOAD_TIME
 from v$sqlarea v
 order by 2 desc, 3 desc
 )
 select
 sysdate snaptime,
 null hash_value,
 sum(disk_reads) disk_reads,
 sum(executions) executions,
 sum(cpu_time)   cpu_time,
 '!ALL SQL SUMMARY' SQL_TEXT,
 NULL first_load_time
 from base_data
 union all
 select  
 sysdate,
 HASH_VALUE,
 disk_reads,
 executions,
 CPU_TIME,
 sql_text,
 FIRST_LOAD_TIME
from base_data
 where rownum <=20

0 comments: