獲得消耗cpu較高的topsql

一、
SELECT a.snap_id,
       a.dbid,
       a.instance_number,
       DB_TIME,
       to_char(TRUNC((DB_TIME - lag(DB_TIME, 1, DB_TIME)
                      over(PARTITION BY stat_name,
                           instance_number ORDER BY snap_id)) / 1000000 / 60,
                     2)) DB_TIME_
  FROM (SELECT a.snap_id,
               a.dbid,
               a.instance_number,
               a.stat_name,
               SUM(a.value) DB_TIME
          FROM DBA_HIST_SYS_TIME_MODEL a
         WHERE a.stat_name = 'DB time'
         --AND a.instance_number = 2
         GROUP BY a.snap_id, a.dbid, a.instance_number, a.stat_name) a;

SELECT a.snap_id,
       a.dbid,
       a.instance_number,
       DB_TIME,
/*    to_char(TRUNC((DB_TIME - lag(DB_TIME, 1, DB_TIME)
                      over(PARTITION BY stat_name,
                           instance_number ORDER BY snap_id)) / 1000000 / 60,
                     2)) DB_TIME_,   */
       b.END_INTERVAL_TIME
  FROM (SELECT a.snap_id,
               a.dbid,
               a.instance_number,
               a.stat_name,
               SUM(a.value) DB_TIME
          FROM DBA_HIST_SYS_TIME_MODEL a
         WHERE a.stat_name = 'DB time'
         GROUP BY a.snap_id, a.dbid, a.instance_number, a.stat_name) a,dba_hist_snapshot b where a.snap_id=b.snap_id;
執行上面兩個語句得到資料之後按照snap_id對應起來，可以得到snap_id、DB_TIME_、END_INTERVAL_TIME這仨個欄位，就可以確定某個時間點的db_time值與snap_id所對應的具體的時間。
 

然後把相應時間段的snap_id代入下面的語句即可得到top10的語句

select  to_char(t.INSTANCE_NUMBER) i_n,t.sql_id,to_char(t.username) username,
to_char(t.disk_reads) d_r,to_char(t.buffer_gets_nvl) buffer_nvl,t.bget buffer_gets,t.elap_time,t.cpu_time,
t.exec,t.elap_nvl,t.sql_text
from
(select  sqt.bget,
         sqt.exec,
 username,
 INSTANCE_NUMBER,
 disk_reads,
     decode(sqt.exec, 0, to_number(null) , (sqt.bget / sqt.exec)) buffer_gets_nvl,
     nvl((sqt.cput / 1000000), to_number(null)) cpu_time ,
 nvl((sqt.elap / 1000000), to_number(null)) elap_time,
 (sqt.elap / 1000000) /  sqt.exec  elap_nvl,
 sqt.sql_id,
 st.sql_text
from
(select sql_id,parsing_schema_name username,INSTANCE_NUMBER,
       sum(buffer_gets_delta) bget,
       sum(executions_delta) exec,
       sum(cpu_time_delta) cput,
       sum(elapsed_time_delta) elap,
   sum(DISK_READS_DELTA) disk_reads
from dba_hist_sqlstat
where snap_id>=30281 and snap_id<=30307   ---------------把相關時間點的snap_id寫到這裡即可
group by sql_id,parsing_schema_name,INSTANCE_NUMBER) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and sqt.exec != 0
and st.sql_text not like '%DUAL%%'
order by nvl((sqt.cput / 1000000), to_number(null)) desc, sqt.sql_id ,username,INSTANCE_NUMBER,disk_reads)  t
 where rownum < 65
   and (rownum <= 10);