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
(
select
sql_id,
sql_plan_hash_value,
event,sql_exec_id,
sql_exec_start,current_obj#,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options,
SUM (delta_read_io_requests)
lio_read ,
SUM (delta_read_io_bytes)
pio_read ,
count(*) count_1
from
dba_hist_active_sess_history
where
sql_id='xxxx'
and
snap_id >= :strt_snap
and
snap_id <= :end_snap
group by
sql_id,
sql_plan_hash_value,
event,sql_exec_id,
sql_exec_start,
current_obj#,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options
)
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".
Here are
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,
optimizer_index_cost_adj)
-
Enabling parallelism
In a related notes, see how to find
historical SQL by
date.
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)
tables.
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
SQL:
Tracking nested loop SQL joins
Tracking Oracle full table scans
Tracking Oracle hash joins
Note:
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
here on
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
called "sp_hgistory.sql"
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
DS.TRU
CK_ID = :b1 AND (DS.DELIVERY_ID IN (SELECT
DL.DELIVERY_ID FRO
M WSH_DELIVERIES DL WHERE DL.STATUS_CODE IN ('CL','CA','CB')
)) FOR UPDATE OF DS.DELIVERED_STATUS
Avg
Avg
Nbr
Disk
Buffer
Snapshot
Of
Reads Gets
Time
Execs Per Exec
Per Exec
------------ -------- ---------------
---------------
07-JUL 06:00
0
0.00
0.00
07-JUL 07:00
0
0.00
0.00
07-JUL 08:00
0 0.00
0.00
07-JUL 09:00
57 1,988.43
748,398.57
07-JUL 10:00
912
720.25 748,447.08
07-JUL 11:00
621
503.90 748,551.95
07-JUL 12:00
512
193.33 748,653.17
07-JUL 13:00 99
1,079.44 748,710.11
07-JUL 14:00
215
618.73 748,788.53
07-JUL 15:00
616
220.50 748,865.94
07-JUL 16:00
521
310.86 748,962.76
07-JUL 17:00
4
0.00 749,024.75
07-JUL 18:00
0
0.00
0.00
07-JUL 19:00
0
0.00
0.00
07-JUL 20:00
0
0.00
0.00
and include the URL for the page.
