Oracle Database - (Actual|Final) Execution Plan
Table of Contents
About
The actual or final plan is the execution that was executed in order to retrieve the result of a SQL.
The actual execution plan is produced by the optimizer whereas the query plan is an iterative execution plan produced after in the SQL processing steps by the row source generator. It becomes then usable for the rest of the process.
Articles Related
Management
View
Data Dictionary
- V$SQL_PLAN view contains the actual plan for a query that has been executed (Since Oracle 9i)
- V$SQL_PLAN_STATISTICS_ALL: SQL execution runtime statistics for cached SQL cursors
- V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR
- V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle ( > 5 sec)
Trace file
If you have access to SQL_TRACE trace file, you may find the actual plan used there, after using tkprof to format it.
dbms_xplan.display_cursor
dbms_xplan.display
Without the display_cursor function, the actual plan can be:
- retrieve from the v$sql_plan view and inserted:
- in the Oracle Database - Explain Plan table of the explain plan statement.
- or in a view with the same structure
- and printed with the dbms_xplan.display function
Example:
- Creation of a dynamic Oracle Database - Explain Plan.
CREATE OR REPLACE VIEW dynamic_plan_table
AS
SELECT
rawtohex( address )
|| '_'
|| child_number statement_id,
sysdate TIMESTAMP,
operation,
OPTIONS,
object_node,
object_owner,
object_name,
0 object_instance,
optimizer,
search_columns,
id,
parent_id,
position,
cost,
cardinality,
bytes,
other_tag,
partition_start,
partition_stop,
partition_id,
other,
distribution,
cpu_cost,
io_cost,
temp_space,
access_predicates,
filter_predicates ) FROM v$sql_plan
- querying the plan:
SELECT
plan_table_output
FROM
TABLE( dbms_xplan.display( "dynamic_plan_table",
(
SELECT
rawtohex( address )
|| '_'
|| child_number x
FROM
V$sql
WHERE
sql_text = 'Your Sql'
)
, "serial" ) )