Oracle Database - (Actual|Final) Execution Plan

Card Puncher Data Processing

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.

Management

View

Data Dictionary

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:

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





Discover More
Card Puncher Data Processing
Oracle Database - Actual (Runtime) Plan Statistics

The original Optimizer estimates are shown in the E-Rows (E stands for Estimate) column while the actual statistics of an actual plan gathered during execution are shown in the A-Rows column. A Rows:...
Card Puncher Data Processing
Oracle Database - DBMS_XPLAN

Dbms_xplan is a method for: querying and displaying the explain plan output. display the plan of a statement stored: in the Automatic Workload Repository (AWR) or in a SQL tuning set. It...
Oracle Database Sga
Oracle Database - Shared sql area

The Shared sql area is an area in the library cache that contains: the cached SQL cursors the actual plan the parse tree for a SQL statement. Only one shared SQL area exists for a unique statement....
Card Puncher Data Processing
Oracle Database - V$SQL_PLAN

V contains the actual execution plan information for each child cursor loaded in the library cache. library cache Join Column Equality Predicate Join Table In order to add ADDRESS and HASH_VALUE...
Oracle Database Sql Processing
SQL Engine - Row source generator

The row source generator performs the row source generation steps of an SQL processing. It receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan,...
Logical Query Plan
Sql Engine - Logical Plan (Query)

A execution plan is an ordered set of steps to execute a query During SQL processing, the row source generator receives the optimal execution plan from the optimizer and produces an iterative plan, called...
Query Plan For The Same Query Response Time
Sql Engine - (Physical|Execution) Plan

A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query A logical plan is also a ordered tree of relational operator but without the...



Share this page:
Follow us:
Task Runner