Question: How can I see
the execution history of a SQL statement? I want a query
that displays the historical SQL execution details.
Answer: Because SQL plans will change whenever we
make changes to the instance, any SQL statement will have a
history of execution plans.
Here is a handy SQL query that will display
a summary of SQL execution history:
select * from
snap_id >= :strt_snap
snap_id <= :end_snap
order by count_1 desc;
This script accepts three parameters, the
SQL ID, the start and end snap numbers, and displays details
about the historical SQL execution.
For complete details on
tracking SQL over elapsed time periods, see my book "Oracle
Tuning: The Definitive Reference".
some common acts that will change execution plans over time:
Enabling dynamic sampling
Re-analyze the schema statistics (done
automatically, starting in 10g)
Changing an optimizer parameter (optimizer_mode,
In a related notes, see how to find
historical SQL by
This execution plan history can be seen by
running scripts against the STATSPACK (stats$sql_plan and
stats$sql_plan_usage) or the AWR (dba_hist_sql_plan)
Oracle AWR and STATSPACK data provide a
wealth of information about time-series SQL execution. At
a holistic level, you can track your whole instance executes
Tracking nested loop SQL joins
Tracking Oracle full table scans
Tracking Oracle hash joins
These scripts will only track SQL that you have
directed Oracle to capture via your threshold settings in AWR or
STATSPACK, and STATSPACK and AWR will not collect "transient
SQL" that did not appear in v$sql at snapshot time. Hence,
not all SQL will appear in these reports. See my notes
adjusting the SQL capture thresholds and
what SQL is
included in AWR/STATSPACK tables?
Once you have acquired the SQL_ID for a statement, you can
use the built-in
dbms_xplan.display_awr procedure to see all the
different execution plans for the query.
The dbms_xplan.display_awr allows you to input only
a SQL_ID, and Oracle will show you the of the explain plans for
that SQL_ID, as recorded in the AWR.
This simple query will show
changes to SQL explain plan history, if you have purchased the
extra-cost licenses for AWR.
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
For more detailed scripts, and free
STATSPACK script to monitor SQL execution patterns with details
on historical SQL plan analysis, see my book "Oracle
Tuning: The Definitive Reference". You can buy it
direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.
Another approach to tracking individual
SQL statements, Tim Gorman has shared an outstanding script
to show the changes to the execution of a single SQL statement
over time, a remarkable and useful script:
Text of SQL statement
SELECT DS.DELIVERED_STATUS FROM DPA_SHIPMENTS DS WHERE
CK_ID = :b1 AND (DS.DELIVERY_ID IN (SELECT
M WSH_DELIVERIES DL WHERE DL.STATUS_CODE IN ('CL','CA','CB')
)) FOR UPDATE OF DS.DELIVERED_STATUS
Execs Per Exec
------------ -------- ---------------
07-JUL 13:00 99