Oracle Database - (Actual|Final) Execution Plan

> Database > Oracle Database

1 - 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.

Advertising

3 - Management

3.1 - View

3.1.1 - Data Dictionary

3.1.2 - 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.

3.1.3 - dbms_xplan.display_cursor

3.1.4 - dbms_xplan.display

Without the display_cursor function, the actual plan can be:

Example:

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" ) )
Advertising