Oracle Execution Plan Analysis: How to Read Plans and Tune SQL Performance

Introduction

When facing performance issues in an Oracle database, one of the most critical tasks is analyzing the Execution Plan. While tools like AWR and ASH can help identify high-load SQL statements, understanding the execution plan is essential to determine the root cause of why a query is slow.

In this article, we systematically explain everything from the basics of Oracle execution plans to practical analysis and tuning techniques that can be applied in real-world scenarios.

What Is an Execution Plan?

When Oracle receives a SQL statement, it does not execute it as-is. Instead, it internally converts the SQL into what it determines to be the most efficient sequence of operations. This sequence is called the execution plan.

An execution plan determines key aspects such as:

• Table access methods (full scan vs. index access)
• Join order of tables
• Join methods (nested loops, hash join, etc.)

Even for the same SQL, the execution plan can change depending on data volume and statistics, leading to significant performance differences. For example, a query that completes in milliseconds using an index may take several seconds if a full table scan is chosen.

When SQL performance issues occur, reviewing the execution plan—alongside AWR and ASH analysis—helps identify bottlenecks and enables more effective tuning.

For details on AWR and ASH analysis, refer to the following:

• Oracle AWR Report Guide: Key Analysis Points for Performance Tuning and Troubleshooting
• Oracle ASH Analysis Guide: Practical Techniques to Diagnose CPU, I/O, and Lock Issues

How to Obtain an Execution Plan

Let’s start by retrieving an execution plan. In this example, we create a test table for analysis.

SQL> CREATE TABLE test_table AS
SELECT
  LEVEL AS id,
  DBMS_RANDOM.STRING('A', 100) AS data
FROM dual
CONNECT BY LEVEL <= 1000000;

Using DBMS_XPLAN.DISPLAY_CURSOR (Actual Execution Plan)

By using the DBMS_XPLAN.DISPLAY_CURSOR function, you can view both estimated and actual execution statistics for executed SQL. When investigating performance issues, this is recommended over DBMS_XPLAN.DISPLAY.

You can also retrieve plans using a specific SQL_ID.

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cp7rzu66ff0qt', NULL, 'ALLSTATS LAST'));

Parameters

• First argument (‘cp7rzu66ff0qt’)
  Specifies the SQL_ID. If NULL, the most recently executed SQL is used.
• Second argument (NULL)
  Specifies the child cursor. NULL targets all child cursors.
• Third argument (‘ALLSTATS LAST’)
  Specifies the display format.
  This option shows actual execution statistics, including:
  • Actual rows (A-Rows)
  • I/O metrics
  • Execution time

This allows comparison with optimizer estimates (E-Rows), which is crucial for SQL tuning.

Executing the Target SQL

SELECT /*+ gather_plan_statistics */ *
FROM test_table
WHERE data LIKE 'ABC%';

Retrieve the SQL_ID:

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%test_table%';

Then fetch the execution plan:

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7bs0s1umbq307', NULL, 'ALLSTATS LAST'));

Key Metrics Explained

• SQL_ID: Identifier of the SQL statement
• Child Number: Identifies a specific child cursor
• Plan Hash Value: Unique identifier of the execution plan structure
• Id: Step number in the execution plan
• Operation: Operation performed
• Name: Object name
• Starts: Number of executions
• E-Rows: Estimated rows
• A-Rows: Actual rows
• A-Time: Actual execution time
• Buffers: Logical I/O (important for tuning)
• Reads: Physical I/O

The most important point is the gap between E-Rows (estimate) and A-Rows (actual), which often indicates the root cause of performance issues.

Using DBMS_XPLAN.DISPLAY (Estimated Plan)

DBMS_XPLAN.DISPLAY shows execution plans generated by EXPLAIN PLAN. These are estimates, not actual results.

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

To generate a plan without executing SQL:

EXPLAIN PLAN FOR
SELECT * FROM test_table WHERE data LIKE 'ABC%';

Then:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

Key Metrics

• Rows: Estimated rows
• Bytes: Estimated data size
• Cost: Relative cost
• %CPU: CPU cost ratio (not actual CPU usage)
• Time: Estimated execution time

Note: This does not include actual execution statistics.

How to Analyze Execution Plans

Execution plans have a tree structure and should be read from bottom to top (right-indented operations first).

Table Access Methods

In execution plans, table access methods appear in the Operation column.

Example:
TABLE ACCESS FULL

Common Access Methods

• TABLE ACCESS FULL
  Reads all rows sequentially
  → Slow for large tables (high I/O)
• TABLE ACCESS BY INDEX ROWID
  Uses index to fetch ROWIDs, then retrieves rows
  → Two-step access
• INDEX RANGE SCAN
  Scans a portion of an index
  → Efficient for range queries
• INDEX UNIQUE SCAN
  Fetches a single row via unique index
  → Very fast

Example: Adding an Index

CREATE INDEX idx_test_data ON test_table(data);

After adding the index, the plan changes from full scan to index access, significantly improving performance.

Join Order

The optimizer determines the join order.

Key principle:
Start with smaller datasets to reduce intermediate result size.

Example findings:

• Optimized plan: ~3.7 seconds
• Forced join order: ~11.1 seconds

Incorrect join order increases processing time significantly.

Important points:

• Optimizer decisions are often optimal
• Forcing with hints does not always improve performance
• Focus on Buffers (logical I/O) rather than just execution time

Join Methods

Join method selection has a major impact on performance.

Common Join Types

• NESTED LOOPS JOIN
  Best for small datasets with indexes
  Weak for large datasets
• HASH JOIN
  Best for large datasets without indexes
  Requires sufficient memory
• SORT MERGE JOIN
  Used when sorting is required
  Higher cost due to sort operations

Example Comparison

• HASH JOIN: ~3.53 seconds
• NESTED LOOPS: ~16.58 seconds

Nested loops caused excessive random I/O due to repeated index access.

Summary

Key takeaways:

• Execution plans reveal the true nature of SQL performance
• Always check actual execution stats (DISPLAY_CURSOR)
• Compare E-Rows vs. A-Rows
• Focus on Buffers (logical I/O)
• Join order and join method greatly impact performance
• Hints should be a last resort

By properly understanding execution plans, you can accurately identify SQL bottlenecks and achieve effective performance tuning.