几个有用的oracle dba_hist_*查询语句

耗CPU最多的10条语句 



select * from 

   (select 

    s.SQL_ID, 

    sum(s.CPU_TIME_DELTA), 

    sum(s.DISK_READS_DELTA), 

    count(*) 

    from DBA_HIST_SQLSTAT s 

   group by s.SQL_ID 

   order by sum(s.CPU_TIME_DELTA) desc 

   ) 

 where rownum < 11 

 / 


最近7天，指定时间段（8:00-16:00）最消耗CPU的10条语句 



select * from 

   (select 

      s.SQL_ID, 

      sum(s.CPU_TIME_DELTA), 

      sum(s.DISK_READS_DELTA), 

      count(*) 

    from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p 

    where 1=1 

     and s.SNAP_ID = p.SNAP_ID 

     and EXTRACT(HOUR FROM p.END_INTERVAL_TIME) between 8 and 16 

     and p.END_INTERVAL_TIME between SYSDATE-7 and SYSDATE 

    group by s.SQL_ID 

    order by sum(s.CPU_TIME_DELTA) desc 

   ) 

 where rownum < 11 

 / 


可以进一步关联DBA_HIST_SQLTEXT视图得到详细的SQL语句 



select * from 

   (select 

    s.SQL_ID, s.SQL_TEXT 

    sum(s.CPU_TIME_DELTA), 

    sum(s.DISK_READS_DELTA), 

    count(*) 

   from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p, DBA_HIST_SQLTEXT t 

   where 1=1 

    and s.SNAP_ID = p.SNAP_ID 

    and s.SQL_ID = t.SQL_ID 

    and EXTRACT(HOUR FROM p.END_INTERVAL_TIME) between 8 and 16 

    and t.COMMAND_TYPE != 47 –- Exclude PL/SQL blocks from output 

    and p.END_INTERVAL_TIME between SYSDATE-7 and SYSDATE 

   group by s.SQL_ID 

   order by sum(s.CPU_TIME_DELTA) desc 

   ) 

 where rownum < 11 

 / 


分析指定SQL语句各版本执行计划的资源消耗情况 



select st.SQL_ID, 

   st.PLAN_HASH_VALUE, 

   sum(st.EXECUTIONS_DELTA) EXECUTIONS, 

   sum(st.ROWS_PROCESSED_DELTA) CROWS, 

   trunc(sum(st.CPU_TIME_DELTA)/1000000/60) CPU_MINS, 

   trunc(sum(st.ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS 

 from DBA_HIST_SQLSTAT st 

 where st.SQL_ID in (‘&1′) 

 group by st.SQL_ID , st.PLAN_HASH_VALUE 

 order by st.SQL_ID, CPU_MINS; 


分析对比SQL语句在不同执行计划中的执行情况 



SELECT st2.SQL_ID, 

   st2.PLAN_HASH_VALUE, 

   st_long.PLAN_HASH_VALUE l_PLAN_HASH_VALUE, 

   st2.CPU_MINS, 

   st_long.CPU_MINS l_CPU_MINS, 

   st2.ELA_MINS, 

   st_long.ELA_MINS l_ELA_MINS, 

   st2.EXECUTIONS, 

   st_long.EXECUTIONS l_EXECUTIONS, 

   st2.CROWS, 

   st_long.CROWS l_CROWS, 

   st2.CPU_MINS_PER_ROW, 

   st_long.CPU_MINS_PER_ROW l_CPU_MINS_PER_ROW 

 FROM 

   (SELECT st.SQL_ID, 

     st.PLAN_HASH_VALUE, 

     SUM(st.EXECUTIONS_DELTA) EXECUTIONS, 

     SUM(st.ROWS_PROCESSED_DELTA) CROWS, 

     TRUNC(SUM(st.CPU_TIME_DELTA)                                         /1000000/60) CPU_MINS , 

     DECODE( SUM(st.ROWS_PROCESSED_DELTA), 0 , 0 , (SUM(st.CPU_TIME_DELTA)/1000000/60)/SUM(st.ROWS_PROCESSED_DELTA) ) CPU_MINS_PER_ROW , 

     TRUNC(SUM(st.ELAPSED_TIME_DELTA)                                     /1000000/60) ELA_MINS 

   FROM DBA_HIST_SQLSTAT st 

   WHERE 1                     =1 

   AND ( st.CPU_TIME_DELTA    !=0 

   OR st.ROWS_PROCESSED_DELTA !=0) 

   GROUP BY st.SQL_ID, 

     st.PLAN_HASH_VALUE 

   ) st2, 

   (SELECT st.SQL_ID, 

     st.PLAN_HASH_VALUE, 

     SUM(st.EXECUTIONS_DELTA) EXECUTIONS, 

     SUM(st.ROWS_PROCESSED_DELTA) CROWS, 

     TRUNC(SUM(st.CPU_TIME_DELTA)                                         /1000000/60) CPU_MINS , 

     DECODE( SUM(st.ROWS_PROCESSED_DELTA), 0 , 0 , (SUM(st.CPU_TIME_DELTA)/1000000/60)/SUM(st.ROWS_PROCESSED_DELTA) ) CPU_MINS_PER_ROW , 

     TRUNC(SUM(st.ELAPSED_TIME_DELTA)                                     /1000000/60) ELA_MINS 

   FROM DBA_HIST_SQLSTAT st 

   WHERE 1                                         =1 

   AND ( st.CPU_TIME_DELTA                        !=0 

   OR st.ROWS_PROCESSED_DELTA                     !=0) 

   HAVING TRUNC(SUM(st.CPU_TIME_DELTA)/1000000/60) > 10 

   GROUP BY st.SQL_ID, 

     st.PLAN_HASH_VALUE 

   ) st_long 

 WHERE 1                                                                            =1 

 AND st2.SQL_ID                                                                     = st_long.SQL_ID 

 AND st_long.CPU_MINS_PER_ROW/DECODE(st2.CPU_MINS_PER_ROW,0,1,st2.CPU_MINS_PER_ROW) > 2 

 ORDER BY l_CPU_MINS DESC, 

   st2.SQL_ID, 

   st_long.CPU_MINS DESC, 

   st2.PLAN_HASH_VALUE;