SQL workload history with DBA_HIST_SQLSTAT

Using the DBA_HIST_XXX views from the AWR (automatic workload repository), it has become easier for a DBA to track changes in workload metrics over time. Through a SR I logged with Oracle Support they have supplied me with the following sql statement to track such changes for a single SQL statement.

Just substitute XXX with your SQL_ID.

set trimspool on
set lines 220
set long 10000
set longchunk 10000
set pages 99
set longchunk 100000
set long 10000
set time on
undefine SQL_ID
undefine PLAN_HASH_VALUE
undefine SQL_TEXT

Select distinct dbid, sql_id, plan_hash_value, timestamp
from dba_hist_sql_plan where sql_id='XXX'
order by dbid, timestamp
/

undefine PLAN_HASH_VALUE
accept PLAN_HASH_VALUE prompt 'Please enter PLAN_HASH_VALUE to show Statistics for: '
col iowait_delta format 9999999.99 heading iowaitdelta(ms)
col iowait_total format 9999999.99 heading iowaittotal(ms)
col ELAPSED_TIME_TOTAL format 9999999.99 heading elapsdtimetotal(ms)
col ELAPSED_TIME_DELTA format 9999999.99 heading elapsdtimedelta(ms)
col PLAN_HASH_VALUE heading plan_hashvalue
col CONCURRENCY_WAIT_TOTAL format 9999999.99 heading concwaittotal(ms)
col CONCURRENCY_WAIT_delta format 9999999.99 heading concwaitdelta(ms)
col CLUSTER_WAIT_DELTA format 9999999.99 heading clustwaitdelta(ms)
col CLUSTER_WAIT_TOTAL format 9999999.99 heading clustwaittotal(ms)
col APWAIT_TOTAL format 9999 heading applwaittimetotal(micro)
col APWAIT_DELTA format 9999 heading applwaittimedelta(micro)
col PLSEXEC_TIME_TOTAL format 9999 heading plsqlexectimetotal(micro)
col PLSEXEC_TIME_DELTA format 9999 heading plsqlexectimedelta(micro)
col JAVAEXEC_TIME_DELTA format 9999 heading javaexectimedelta(micro)
col JAVAEXEC_TIME_TOTAL format 9999 heading javaexectimetotal(micro)
col optimizer_cost format 9999 heading optcostcol optimizer_mode format a10 heading optimmode
col kept_versions format 999 heading keptvers
col invalidations_total format 999 heading invalidtot
col invalidations_delta format 999 heading invaliddlt
col parse_calls_total format 99999 heading parsecallstotal
col parse_calls_delta format 99999 heading parsecallsdelta
col executions_total format 999999 heading exectotal
col executions_delta format 999999 heading execdelta
col fetches_total format 9999999 heading fetchestotal
col fetches_delta format 9999999 heading fetchesdelta
col end_of_fetch_count_total format 9999 heading endoffetchcalltotal
col end_of_fetch_count_delta format 9999 heading endoffetchcalldelta
col buffer_gets_total format 99999999 heading buffergetstotal
col buffer_gets_delta format 99999999 heading buffergetsdelta
col disk_reads_total format 999999 heading diskreadstotal
col disk_reads_delta format 9999999 heading diskreadsdelta
col rows_processed_total format 9999999 heading rowsprocessedtotal
col rows_processed_delta format 9999999 heading rowsprocesseddelta
col rows_ex format 999999 heading rowsexeccol snap_id format 99999 heading snapid
col ela_ex format 9999999.99 heading elapsedperexecution
col cwt_ex format 9999999.99 heading cwtperexecution
col instance_number format 99 heading inID

select sql_id, plan_hash_value,dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm_hh24:mi') snap_beg,dba_hist_sqlstat.instance_number,invalidations_delta,
parse_calls_delta,executions_delta,fetches_delta,buffer_gets_delta,
disk_reads_delta,rows_processed_delta,elapsed_time_delta/1000 elapsed_time_delta,iowait_delta/1000 iowait_delta,clwait_delta/1000 cluster_wait_delta,ccwait_delta/1000 concurrency_wait_delta,optimizer_mode, optimizer_cost,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when rows_processed_delta = 0 then NULL
else(rows_processed_delta/executions_delta)end rows_ex,
case when executions_delta = 0 then NULL
when clwait_delta = 0 then NULL
else(clwait_delta/executions_delta)/1000 end cwt_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else(elapsed_time_delta/executions_delta)/1000 end ela_ex
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='XXX'
and plan_hash_value='&PLAN_HASH_VALUE'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number,dba_hist_sqlstat.snap_id
/

The view DBA_HIST_SQLSTAT displays historical information about SQL statistics. Each statistic is stored in two separate columns:
metric_TOTAL for the total value of the statistic since instance startup.
metic_DELTA for the change in a statistic’s value between BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME that is stored in the DBA_HIST_SNAPSHOT view.

You can also query DBA_HIST_SQL_PLAN to compare the execution plans, if PLAN_HASH_VALUE has changed .