TopSQL,計算某條sql的CPU time.

select aa.*,

 (select sql_text from perfstat.top_sql_monitor where aa.hash_value = perfstat.top_sql_monitor.hash_value and rownum =1 and
   snap_time between to_date('2012-04-20 10:35:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-04-20 11:36:00','yyyy-mm-dd hh24:mi:ss')
) sql_text
,(select b.username from perfstat.top_sql_monitor, dba_users b where aa.hash_value = perfstat.top_sql_monitor.hash_value and rownum =1 and
   snap_time between to_date('2012-04-20 10:35:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-04-20 11:36:00','yyyy-mm-dd hh24:mi:ss')
   and perfstat.top_sql_monitor.parsing_user_id = b.user_id
) parsing_user_id
from (
select hash_value, round(sum(cpu_time)/1000000,0) "cpu_time(s)"
, round(sum(total_cpu_time)/1000000,0) "total_cpu_time(s)"
--, round(sum(total_cpu_time)/1000000/15/60,0) "cpu_time_ps(s)"
, round(sum(total_cpu_time)/1000000/60/60,0) "cpu_time_ps(s)"
, round(sum(cpu_time)/sum(total_cpu_time),3) "percent_cpu_time(s)"
, round(sum(elapsed_time)/1000000,1) "elapsed_time(s)", sum(executions) executions, sum(buffer_gets) buffer_gets, sum(disk_reads) disk_reads from (select
       s.snap_time,
       s.hash_value,
       decode(sign(cpu_time - lag(cpu_time, 1) over( partition by hash_value order by s.snap_time )),
              -1,
              cpu_time,
              cpu_time - lag(cpu_time, 1) over(partition by hash_value order by s.snap_time  )) as cpu_time,
       decode(sign(total_cpu_time - lag(total_cpu_time, 1) over( partition by hash_value order by s.snap_time )),
              -1,
              total_cpu_time,
              total_cpu_time - lag(total_cpu_time, 1) over(partition by hash_value order by s.snap_time  )) as total_cpu_time,
       decode(sign(s.executions - lag(executions, 1)
                   over(partition by hash_value order by s.snap_time )),
              -1,
              executions,
              s.executions - lag(executions, 1) over(partition by hash_value order by s.snap_time)) as executions,
       decode(sign(s.elapsed_time - lag(elapsed_time, 1)
                   over(partition by hash_value order by s.snap_time)),
              -1,
              elapsed_time,
              s.elapsed_time - lag(elapsed_time, 1) over(partition by hash_value order by s.snap_time)) as elapsed_time,
       decode(sign(s.buffer_gets - lag(buffer_gets, 1)
                   over(partition by hash_value order by s.snap_time )),
              -1,
              buffer_gets,
              s.buffer_gets - lag(buffer_gets, 1) over(partition by hash_value order by s.snap_time)) as buffer_gets,
       decode(sign(s.disk_reads - lag(disk_reads, 1)
                   over(partition by hash_value order by s.snap_time )),
              -1,
              disk_reads,
              s.disk_reads - lag(disk_reads, 1) over(partition by hash_value order by s.snap_time )) as disk_reads,
       decode(sign(s.fetches - lag(fetches, 1) over(partition by hash_value order by s.snap_time )),
              -1,
              fetches,
              s.fetches - lag(fetches, 1) over(partition by hash_value order by s.snap_time )) as fetches,
       decode(sign(s.sorts - lag(sorts, 1) over(partition by hash_value order by s.snap_time )),
              -1,
              sorts,
              s.sorts - lag(sorts, 1) over(partition by hash_value order by s.snap_time )) as sorts
  from perfstat.top_sql_monitor s
where
   snap_time between to_date('2012-04-20 10:35:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-04-20 11:36:00','yyyy-mm-dd hh24:mi:ss')
   --and parsing_user_id = 97
) where executions is not null
group by hash_value
order by 2 desc ) aa where rownum <1000