Execution History of an SQL ID

-- This script allows you to view the execution history of a SQL ID. Use it to investigate things like:

-- 1. Changing plan hash values.

-- 2. High elapsed time per execution.

-- 3. High % of cluster waits.

-- 4. Whether a SQL profile is being used.

SELECT   snap_id "Snap"
       , sql_id "SQL ID"
       , plan_hash_value "Plan"
       , end_interval_time "End Time"
       , executions "Execs"
       , ROUND(CASE WHEN executions > 0 THEN elapsed_time / executions ELSE elapsed_time END, 4) "ElapPexec"
       , ROUND(CASE WHEN executions > 0 THEN rows_processed / executions ELSE rows_processed END, 2) "RowPexec"
       , ROUND(CASE WHEN executions > 0 THEN buffer_gets / executions ELSE buffer_gets END, 0) "GetPexec"
       , ROUND(CASE WHEN executions > 0 THEN disk_reads / executions ELSE disk_reads END, 2) "ReadPexec"
       , ROUND(CASE WHEN executions > 0 THEN gc_time / executions ELSE gc_time END, 4)  "GCpExec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (gc_time / elapsed_time) ELSE 0 END, 2)  "GC %"
       , ROUND(CASE WHEN executions > 0 THEN cpu_time / executions ELSE cpu_time END, 4)  "CPUpExec"
       , ROUND(CASE WHEN executions > 0 THEN ccwait / executions ELSE ccwait END, 4)  "ConcPexec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (cpu_time / elapsed_time) ELSE 0 END, 2)  "CPU %"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (ccwait / elapsed_time) ELSE 0 END, 2)  "Conc %"
       , ROUND(elapsed_time, 1) "Elapsed"
       , parsing_schema_name "Schema"
       , sql_profile "SQL profile"
       , min_inst_id "LowInst"
       , max_inst_id "HghInst"
       , ROUND(cpu_time, 1) "CPU"
       , buffer_gets "Gets"
       , disk_reads  "Reads"
       , ROUND(gc_time, 1) "Cluster"
       , rows_processed "Rows"
       , ROUND(ccwait, 1) "Concurrency"
       , ROUND(CASE WHEN executions > 0 THEN plsql_time / executions ELSE plsql_time END, 4) "PLSpExec"
       , ROUND(CASE
                  WHEN executions > 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / executions / rows_processed / 1000000
                  WHEN executions = 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / rows_processed / 1000000
                  ELSE
                     0
               END
             , 4)
            "ElapProw"
       , sql_text "SQL Text"
FROM     (SELECT   s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI') end_interval_time
                 , SUM(sq.executions_delta) executions
                 , SUM(sq.disk_reads_delta) disk_reads
                 , SUM(sq.buffer_gets_delta) buffer_gets
                 , SUM(sq.rows_processed_delta) rows_processed
                 , SUM(sq.cpu_time_delta) / 1000000 cpu_time
                 , SUM(sq.plsexec_time_delta) / 1000000 plsql_time
                 , SUM(sq.clwait_delta) / 1000000 gc_time
                 , SUM(sq.ccwait_delta) / 1000000 ccwait
                 , SUM(elapsed_time_delta) / 1000000 elapsed_time
                 , sq.force_matching_signature
                 , MIN(sq.instance_number) min_inst_id
                 , MAX(sq.instance_number) max_inst_id
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1) sql_text
          FROM     dba_hist_sqlstat sq, dba_hist_snapshot s, dba_hist_sqltext sl
          WHERE    1 = 1
          AND      sq.sql_id = :sqlid
          --and   sq.plan_hash_value=3983895616
          AND      s.snap_id = sq.snap_id
          --and   s.snap_id between 37279 and 37305
          AND      TRUNC(s.end_interval_time, 'MI') > SYSDATE - 90
          --and   sq.parsing_schema_name in ('APPS_FR')
          AND      s.dbid = sq.dbid
          AND      sq.dbid = sl.dbid
          AND      s.instance_number = sq.instance_number
          AND      sq.sql_id = sl.sql_id
          AND      sq.elapsed_time_delta > 0
          GROUP BY s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.force_matching_signature
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI')
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1))
ORDER BY 1 DESC;

-- Once you have the SQL, use the following SQL to extract all the execution plans:

SELECT * FROM TABLE(DBMS_XPLAN.display_awr(sql_id => :sqlid , format => 'ADVANCED -ALIAS -OUTLINE'))