RIYOSU

Database Wide SQL Performance

select x.sql_id||'::'||x.plan_hash_value,
sum(x.executions_delta),
sum(x.elapsed_time_delta)/1000000,
sum(x.cpu_time_delta)/1000000,
sum(x.sorts_delta),
sum(x.rows_processed_delta),
sum(x.disk_reads_delta),
sum(x.IOWAIT_DELTA)/1000000,
sum(x.CLWAIT_DELTA)/1000000,
sum(x.APWAIT_DELTA)/1000000,
sum(x.CCWAIT_DELTA)/1000000
from
(select snap_id,
      sql_id,
      plan_hash_value,
     executions_delta,
     elapsed_time_delta,
     cpu_time_delta,
      sorts_delta,
      rows_processed_delta,
      disk_reads_delta,
      IOWAIT_DELTA,
      CLWAIT_DELTA,
      APWAIT_DELTA,
      CCWAIT_DELTA
     from dba_hist_sqlstat) x,
(select snap_id,
      max(to_char((trunc(begin_interval_time, 'mi')),'DD/MON/YYYY HH24:MI')) as start_time,
      max(to_char((trunc(end_interval_time, 'mi')),'DD/MON/YYYY HH24:MI')) as end_time
      from dba_hist_snapshot
        where begin_interval_time >= sysdate - 1
      group by snap_id ) s 
where s.snap_id = x.snap_id 
group by x.sql_id||'::'||x.plan_hash_value
having sum(x.elapsed_time_delta) > 0 
order by x.sql_id||'::'||x.plan_hash_value