oracle数据库SQL相关指标统计分析



---查询sql当前的指标：
select
    a.HASH_VALUE, a.sql_text,
       a.PLAN_HASH_VALUE,
    round(a.BUFFER_GETS/a.EXECUTIONS) bg_per_exec,
    round(a.CPU_TIME/1000/a.EXECUTIONS) cpu_per_exec,
    round(a.ELAPSED_TIME/1000/a.EXECUTIONS) time_per_exec,     
    round(a.ROWS_PROCESSED/a.EXECUTIONS) rows_per_exec,
       a.EXECUTIONS,
       a.BUFFER_GETS,
       a.DISK_READS,
       a.CPU_TIME,
       a.ELAPSED_TIME,
       a.ROWS_PROCESSED
  from v$sql a
 where hash_value=377282797
 and cpu_time<>0
 and buffer_gets<>0
 and rows_processed<>0
order by sql_text
/

--查询sql的历史执行次数变化：
select b.begin_interval_time, a.sql_id,a.executions_delta,a.executions_delta/(15*60)
from dba_hist_sqlstat a, dba_hist_snapshot b where
a.sql_id in  ('0js9jz3qx087j')
and b.begin_interval_time> to_date('2014-03-31 13:00:00','yyyy-mm-dd hh24:mi:ss')
and b.begin_interval_time<to_date('2014-03-31 18:00:00','yyyy-mm-dd hh24:mi:ss')
and a.snap_id=b.snap_id
order by 2,1;

---sql历史上逻辑读、物理读、执行时间等的变化 (毫秒)
select b.begin_interval_time, a.sql_id,a.plan_hash_value,a.executions_delta,
round((a.buffer_gets_delta) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) bg_per_exec,
round((a.disk_reads_delta) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) diskr_per_exec,
round((a.cpu_time_delta/1000) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) ctime_ms_per_exec,
round((a.elapsed_time_delta/1000) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) etime_ms_per_exec,
round((a.APWAIT_DELTA/1000) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) APWAIT_DELTA_ms_per_exec,
round((a.CCWAIT_DELTA/1000) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) CCWAIT_DELTA_ms_per_exec,
round((a.rows_processed_total) /decode(nvl(a.executions_delta, 0),0,1, a.executions_delta),3) row_process_per_exec
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.sql_id = 'atd4sn0445c66'
and b.begin_interval_time> to_date('2014-12-04 09:00:00','yyyy-mm-dd hh24:mi:ss')
and  b.begin_interval_time<to_date('2015-01-07 18:00:00','yyyy-mm-dd hh24:mi:ss')
and a.snap_id=b.snap_id order by 2,1
;

select b.begin_interval_time, a.sql_id,a.plan_hash_value,a.executions_delta,
round(a.buffer_gets_delta/a.executions_delta,3) bg_per_exec,
round(a.disk_reads_delta/a.executions_delta,3) diskr_per_exec,
round(a.cpu_time_delta/1000/a.executions_delta,3) ctime_ms_per_exec,
round(a.elapsed_time_delta/1000/a.executions_delta,3) etime_ms_per_exec,
round(a.APWAIT_DELTA/1000/a.executions_delta,3) APWAIT_ms_DELTA_per_exec,
round(a.CCWAIT_DELTA/1000/a.executions_delta,3) CCWAIT_ms_DELTA_per_exec,
round(a.rows_processed_total/a.executions_delta,3) row_process_per_exec
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.sql_id = 'fvxkpft53fmqj'
and b.begin_interval_time> to_date('2014-12-26 09:00:00','yyyy-mm-dd hh24:mi:ss')
and  b.begin_interval_time<to_date('2015-01-12 18:00:00','yyyy-mm-dd hh24:mi:ss')
and a.snap_id=b.snap_id order by 1
;

----查询一段时间内每个snapshot 间隔内的top sql：

select snap_id,begin_interval_time,sql_id,executions_delta,round(cpu_time_ms/executions_delta) cpu_time_per_exec_ms,ranking from
(select * from
(select a.snap_id,b.begin_interval_time,a.sql_id,a.executions_delta,round(a.cpu_time_delta/1000) cpu_time_ms,
rank() over (partition by a.SNAP_ID order by a.CPU_TIME_DELTA desc) ranking
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.snap_id=b.snap_id
and b.begin_interval_time> to_date('2015-01-04 09:30:00','yyyy-mm-dd hh24:mi:ss')
and  b.begin_interval_time<to_date('2015-01-04 11:30:00','yyyy-mm-dd hh24:mi:ss')
)
where ranking <6)
;