Actual execution plans show the true execution path that Oracle took when running a query, as opposed to estimated execution plans which show what the optimizer thinks will happen. Actual execution plans provide real statistics including actual row counts, buffer gets, physical reads, and execution times.

To generate actual execution plans, Oracle must collect runtime statistics during query execution. This is accomplished using the gather_plan_statistics hint or by enabling statistics gathering at the session or system level.

This table and sample data is used in the examples below:

CREATE TABLE table_exec_plan_1
(
    id1 NUMBER(5,0) NOT NULL
,   id2 NUMBER(5,0) NOT NULL
,   type1 NUMBER(1,0) NOT NULL
);

The following PL/SQL block populates the table with 50,000 rows, alternating the type1 column between values 1 and 2:

DECLARE
indexLoop NUMBER(5,0);

BEGIN
FOR indexLoop IN 1..50000 LOOP
    IF MOD(indexLoop,2) = 0 THEN
        INSERT INTO table_exec_plan_1 (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 1);
    ELSE
        INSERT INTO table_exec_plan_1 (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 2);
    END IF;
END LOOP;
END;
/

COMMIT;

An index is created on the type1 column to demonstrate index usage in the execution plans:

CREATE INDEX ix_table_exec_plan_1_type1 ON table_exec_plan_1 (type1) COMPUTE STATISTICS;

The COMPUTE STATISTICS clause ensures that Oracle collects index statistics immediately, which helps the optimizer make better decisions about index usage.

The dbms_xplan.display_cursor function displays actual execution plans for queries that have been executed and cached in the shared pool. This function requires that runtime statistics were collected during query execution.

Here is the step-by-step process to view an actual execution plan:

SELECT /*+ gather_plan_statistics */ /* ora_sql_hint_exec_plan */ * FROM table_exec_plan_1 WHERE type1 = 1;

The gather_plan_statistics hint instructs Oracle to collect detailed runtime statistics during query execution. The comment with ora_sql_hint_exec_plan serves as a unique identifier to locate this specific query in the shared pool.

• First, retrieve the query information from the v$sql view to find the SQL_ID and child number:
  

  select sql_id, child_number, sql_text
  from v$sql
  where sql_text like '%ora_sql_hint_exec_plan%';
  

  Query result:
  

  +---------------+--------------+---------------------------------------------------------------------------------------------------------------+
  | SQL_ID        | CHILD_NUMBER | SQL_TEXT                                                                                                      |
  +---------------+--------------+---------------------------------------------------------------------------------------------------------------+
  | aqwka0b74ph07 | 0            |  SELECT /*+ gather_plan_statistics */ /* ora_sql_hint_exec_plan */ * FROM table_exec_plan_1 WHERE type1 = 1 |
  +---------------+--------------+---------------------------------------------------------------------------------------------------------------+
  

  
  
• Display the actual execution plan using the retrieved SQL_ID and child number:
  

  SELECT * FROM table(dbms_xplan.display_cursor('__SQL_ID__', __CHILD_NUMBER__, 'ALLSTATS LAST'));
  

  Replace "__SQL_ID__" with the correct value (in this example: "aqwka0b74ph07") and "__CHILD_NUMBER__" with the correct value (in this example: "0").
  
  The 'ALLSTATS LAST' format parameter provides comprehensive statistics from the most recent execution, including row counts, buffer gets, and timing information.
  
  Query result:
  

  SQL_ID  aqwka0b74ph07, child number 0
  -------------------------------------
  SELECT /*+ gather_plan_statistics */ /* ora_sql_hint_exec_plan */ * FROM table_exec_plan_1 WHERE type1 = 1
  
  Plan hash value: 10875406
  
  -----------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
  -----------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |                            |      1 |        |     50 |00:00:00.49 |       3 |      4 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_EXEC_PLAN_1          |      1 |  24687 |     50 |00:00:00.49 |       3 |      4 |
  |*  2 |   INDEX RANGE SCAN          | IX_TABLE_EXEC_PLAN_1_TYPE1 |      1 |    198 |     50 |00:00:00.01 |       2 |      4 |
  -----------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - access("TYPE1"=1)
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  

  The Predicate Information section shows the filter and access conditions applied at each step.
  

The actual execution plan output contains several important columns that provide insight into query performance:

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |      1 |        |     50 |00:00:00.49 |       3 |      4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_EXEC_PLAN_1          |      1 |  24687 |     50 |00:00:00.49 |       3 |      4 |
|*  2 |   INDEX RANGE SCAN          | IX_TABLE_EXEC_PLAN_1_TYPE1 |      1 |    198 |     50 |00:00:00.01 |       2 |      4 |
-----------------------------------------------------------------------------------------------------------------------------

• Id: The step number in the execution plan. Steps are executed in a specific order, typically from the innermost (highest Id) to outermost (Id 0).
  
  
• Operation: The specific operation performed at each step, such as TABLE ACCESS FULL, INDEX RANGE SCAN, or NESTED LOOPS.
  
  
• Starts: The number of times each operation was executed. This is particularly important for operations inside loops.
  
  
• E-Rows: The estimated number of rows the optimizer expected this operation to return. Large discrepancies with A-Rows often indicate stale or missing statistics.
  
  
• A-Rows: The actual number of rows returned by the operation during execution. This is the real row count that occurred.
  
  
• A-Time: The actual elapsed time spent in this operation. This helps identify which steps consumed the most time during execution.
  
  
• Buffers: The number of logical I/O operations (buffer gets) performed by this step. High buffer counts may indicate inefficient access methods.
  
  
• Reads: The number of physical I/O operations required when data was not found in the buffer cache.