col end_snap_time heading ‘End|Snap|Time’ for a20
col PLAN_HASH_VALUE heading ‘Plan|Hash|Value’
col execs_d heading 'Exec|Delta| ’ for 99999999
col parscal_d heading ‘Parse|Calls|Delta’ for 99999
col buffgets_d heading ‘Buffer|Gets|Delta’ for 9999999999
col diskreads_d heading ‘Disk|Reads|Delta’ for 99999999
col cput_d heading ‘CPU|Time|Delta(ms)’ for 99999999
col elapst_d heading ‘Elapsed|Time|Delta(ms)’ for 99999999
col iot_d heading ‘IO Wait|Delta(ms)’ for 99999999
col appt_d heading ‘App|Wait|Delta(ms)’ for 99999999
col concurrt_d heading ‘ConCurr|Wait|Delta(ms)’ for 99999999
col elapst_avg heading ‘Elapsed|Time|Avg(ms)’ for 99999999
col cput_avg heading ‘CPU|Time|Avg(ms)’ for 99999999
col parscal_avg heading ‘Parse|Calls|Avg’ for 99999999
col buffgets_avg heading ‘Buffer|Gets|Avg’ for 9999999999
– col clust_d for 99999999
pro
pro Parameter 1:
pro SQL_ID (required)
pro
def l_sql_id = ‘&1’;
pro
pro Parameter 2:
pro Start time (required)
pro
def l_start_t = ‘&2’;
pro
pro Parameter 3:
pro End time (required)
pro
def l_end_t = ‘&3’
pro
break on end_snap_time
select
to_char(b.END_INTERVAL_TIME, ‘YYYY-MM-DD HH24:MI:SS’) end_snap_time
– , a.sql_id
, a.plan_hash_value
, a.EXECUTIONS_DELTA execs_d
, a.PARSE_CALLS_DELTA parscal_d
, a.BUFFER_GETS_DELTA buffgets_d
, a.DISK_READS_DELTA diskreads_d
, a.ELAPSED_TIME_DELTA/1000 elapst_d
, a.CPU_TIME_DELTA/1000 cput_d
, IOWAIT_DELTA/1000 iot_d
– , CLWAIT_DELTA/1000 clust_d
, APWAIT_DELTA/1000 appt_d
, CCWAIT_DELTA/1000 concurrt_d
, decode(a.EXECUTIONS_DELTA, 0, a.ELAPSED_TIME_DELTA/1000, a.ELAPSED_TIME_DELTA/1000 / a.EXECUTIONS_DELTA ) elapst_avg
, decode(a.EXECUTIONS_DELTA, 0, a.CPU_TIME_DELTA/1000, a.CPU_TIME_DELTA/1000 / a.EXECUTIONS_DELTA ) cput_avg
, decode(a.EXECUTIONS_DELTA, 0, a.PARSE_CALLS_DELTA, a.PARSE_CALLS_DELTA / a.EXECUTIONS_DELTA ) parscal_avg
, decode(a.EXECUTIONS_DELTA, 0, a.BUFFER_GETS_DELTA, a.BUFFER_GETS_DELTA / a.EXECUTIONS_DELTA ) buffgets_avg
from
dba_hist_sqlstat a
, dba_hist_snapshot b
where
a.SNAP_ID =b.SNAP_ID
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER
– and extract(hour from b.end_interval_time) between 15 and 18
– and extract(day from b.end_interval_time) = 17
and b.end_interval_time between to_date(’&l_start_t’, ‘YYYY-MM-DD HH24:MI:SS’) and to_date(’&l_end_t’, ‘YYYY-MM-DD HH24:MI:SS’)
– and b.snap_id between 2986 and 2987
and a.sql_id=’&l_sql_id’
– group by a.plan_hash_value
order by 1 desc
;
undefine 1 2 3 l_sql_id l_start_t l_end_t
clear column
