How to check SQL execution history from AWR in Oracle

Give a SQL_ID; get its performance history across AWR snapshots for the last ~14 days. Shows average reads, buffer gets, rows, CPU time, and elapsed time per snap so you can see how the same SQL behaves under different loads or plans.

DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT are part of Oracle Diagnostic Pack and require that license on Enterprise Edition.

COL snap_time FOR a15
SET PAGES 1000 LINES 220

WITH s AS (
  SELECT MAX(snap_id)                AS t,
         MAX(snap_id) - (14 * 24 * 4) AS f
  FROM   dba_hist_snapshot
)
SELECT *
FROM (
  SELECT a.instance_number AS inst#,
         a.snap_id         AS snap#,
         a.sql_id,
         TO_CHAR(b.end_interval_time, 'mon-dd hh24:mi') AS snap_time,
         a.executions_delta AS execs,
         (a.disk_reads_delta     / DECODE(a.executions_delta, 0, 1, a.executions_delta)) AS avg_rds,
         (a.buffer_gets_delta    / DECODE(a.executions_delta, 0, 1, a.executions_delta)) AS avg_gets,
         (a.rows_processed_delta / DECODE(a.executions_delta, 0, 1, a.executions_delta)) AS avg_rows,
         (a.cpu_time_delta       / DECODE(a.executions_delta, 0, 1, a.executions_delta) / 1000000) AS avg_cpu_sec,
         a.plan_hash_value,
         (a.elapsed_time_delta   / DECODE(a.executions_delta, 0, 1, a.executions_delta) / 1000000) AS avg_elapsed_sec,
         c.value / 1000000 / 60 / 15 AS aas,
         s.f
  FROM   s,
         dba_hist_sqlstat  a,
         dba_hist_snapshot b,
         (
           SELECT y.dbid, snap_id, instance_number,
                  NVL(value - LAG(value, 1) OVER (
                                PARTITION BY y.stat_name, instance_number, y.dbid
                                ORDER BY snap_id), 0) AS value
           FROM   dba_hist_sys_time_model y, s
           WHERE  y.snap_id BETWEEN s.f AND s.t
           AND    y.stat_name = 'DB time'
         ) c
  WHERE  a.snap_id         = b.snap_id
  AND    b.snap_id         = c.snap_id
  AND    a.dbid            = b.dbid
  AND    b.dbid            = c.dbid
  AND    a.instance_number = b.instance_number
  AND    b.instance_number = c.instance_number
  AND    a.snap_id BETWEEN s.f AND s.t
  AND    a.sql_id IN ('&&sql_id')
  ORDER BY a.instance_number, a.sql_id, a.snap_id
)
WHERE snap# > f;