Question: I have a developer who e-mailed
me a SQL statement that he ran an hour ago and he wants to know the
SQL explain plan that was used by the SQL statement. Does
Oracle keep the execution plan for finished SQL statements? If
so, how do I display the execution plan for a completed SQL query?
Answer: Oracle keeps SQL in the v$sql_plan view
until it gets aged-out, and Oracle MIGHT keep the SQL
statement in dba_hist_sql_plan if the SQL query meets the
AWR/STATSPACK threshold collection values. For a
complete explanation see my book
"Advanced Oracle SQL Tuning The Definitive Reference"
Assuming that the SQL is still in the library cache, you
can use the SQL statement to extract the "address" column
from v$sqlarea and then use the address to display the
execution plan from v$sql_plan. Get the
free Oracle
reference poster to know all of the structures.
Step 1: Plug-in some of the SQL source code into the
query below:
select
db_id,
sql_id,
sql_text,
from
v$sqlarea
where
upper(sql_text) like '%MY_TABLE_NAME%'
order by
disk_reads desc;
2. Use the address to query
dba_hist_sql_plan:
select * from
table(dbms_xplan.display_awr('sql_id'));
or
select
s.begin_interval_time,
s.end_interval_time
,
q.snap_id,
q.dbid,
q.sql_id,
q.plan_hash_value,
q.optimizer_cost,
q.optimizer_mode
from
dba_hist_sqlstat
q,
dba_hist_snapshot s
where
q.dbid = nnnnnnn
and
q.sql_id = 'xxxxxx'
and q.snap_id = s.snap_id
and s.begin_interval_time between
sysdate-2 and sysdate
order by s.snap_id desc;
