To get the details about query executions,reads:
set lines 190 pages 200;
col START_TIME for a20;
col SQL_ID for a20;
select to_char(s.BEGIN_INTERVAL_TIME,'MON-DD:HH24:MI') START_TIME, sql_id,
sum(ss.executions_delta) sum_execs,
sum(ss.disk_reads_delta) sum_disk_reads,
sum(ss.buffer_gets_delta) sum_buffer_gets,
sum(ss.rows_processed_delta) sum_rows_processed,
sum(ss.cpu_time_delta)/1000000 sum_cpu_time,
sum(ss.elapsed_time_delta)/1000000 sum_elapsed_time
from dba_hist_sqlstat ss,
dba_hist_snapshot s
where ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.snap_id = s.snap_id
and ss.sql_id = ('8drpg36hd9jy3') --- set sql_id
and ss.executions_delta > 0
and s.begin_interval_time >= sysdate-20 ------- how many days back in time to look
group by to_char(s.BEGIN_INTERVAL_TIME,'MON-DD:HH24:MI'), sql_id
order by 1 desc;
START_TIME SQL_ID SUM_EXECS SUM_DISK_READS SUM_BUFFER_GETS SUM_ROWS_PROCESSED SUM_CPU_TIME SUM_ELAPSED_TIME
-------------------- -------------------- ---------- -------------- --------------- ------------------ ------------ ----------------
NOV-22:14:00 8drpg36hd9jy3 1 88763 801169 311 11.287735 120.676823
NOV-22:11:00 8drpg36hd9jy3 1 105158 1288418 316 15.681973 689.428523
NOV-22:10:00 8drpg36hd9jy3 1 11085 68606 0 6.379018 342.579322
3 rows selected.
-------------------- -------------------- ---------- -------------- --------------- ------------------ ------------ ----------------
NOV-22:14:00 8drpg36hd9jy3 1 88763 801169 311 11.287735 120.676823
NOV-22:11:00 8drpg36hd9jy3 1 105158 1288418 316 15.681973 689.428523
NOV-22:10:00 8drpg36hd9jy3 1 11085 68606 0 6.379018 342.579322
3 rows selected.
To GET Plan_hash_value on specific SQL_ID:
select
ss.snap_id, ss.instance_number node, begin_interval_time, sql_id,
plan_hash_value,
nvl(executions_delta,0)
execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
from
DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
sql_id in ('2w8sgb99bfy33')
and
ss.snap_id = S.snap_id
and
ss.instance_number = S.instance_number
and
executions_delta > 0
--
order by 3,2,1
order
by begin_interval_time desc
/
SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ---------- ------------------------------ -------------------- --------------- ---------- ---------- ----------
27147 1 22-NOV-17 02.00.23.243 PM 8drpg36hd9jy3 1183286463 1 120.676823 801169
27144 1 22-NOV-17 11.00.33.416 AM 8drpg36hd9jy3 1183286463 1 689.428523 1288418
27143 1 22-NOV-17 10.00.16.173 AM 8drpg36hd9jy3 1183286463 1 342.579322 68606
---------- ---------- ------------------------------ -------------------- --------------- ---------- ---------- ----------
27147 1 22-NOV-17 02.00.23.243 PM 8drpg36hd9jy3 1183286463 1 120.676823 801169
27144 1 22-NOV-17 11.00.33.416 AM 8drpg36hd9jy3 1183286463 1 689.428523 1288418
27143 1 22-NOV-17 10.00.16.173 AM 8drpg36hd9jy3 1183286463 1 342.579322 68606