Oracle Database - (Actual|Final) Execution Plan

Data Dictionary

• VSQL_PLAN view contains the actual plan for a query that has been executed (Since Oracle 9i)
• VSQL_PLAN_STATISTICS_ALL: SQL execution runtime statistics for cached SQL cursors
• apps/search/search.jsp displays plan level monitoring statistics for each SQL statement found in apps/search/search.jsp
• VSQL_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

Oracle Database - Explain Plan from Cursor Cache (DBMS_XPLAN.DISPLAY_CURSOR)

dbms_xplan.display

Without the display_cursor function, the actual plan can be:

• retrieve from the vsql_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" ) )