четверг, 1 сентября 2016 г.

dba_hist_sqlstat statistics

Template for PL/SQL developer based on link

Counts execution statistics from dba_hist_sqlstat for given sql_id

select to_char(min(s.end_interval_time),'DD.MM.YYYY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),3) sec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
&<name="start_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.end_interval_time >= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
&<name="end_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.begin_interval_time <= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value;

Комментариев нет: