Learnitweb

Query Execution Plan in Oracle

1. What Is a Query Execution Plan?

A Query Execution Plan (often called EXPLAIN PLAN) in Oracle shows the sequence of operations that the Oracle optimizer chooses to execute a SQL statement.
It describes how Oracle will access the data — which indexes will be used, which tables will be joined, and in what order.

Think of it as a roadmap showing how Oracle travels through your tables and indexes to produce the final result.

2. Why Is It Important?

The execution plan helps you understand:

  • How Oracle is accessing your data.
  • Whether the optimizer chose the best path.
  • Why a query is running slowly.
  • Whether indexes are being used or ignored.
  • How joins are being performed (nested loop, hash join, merge join).
  • Which part of the query is consuming the most cost.

3. How Oracle Executes a SQL Query (The Four Phases)

Before understanding the plan itself, let’s look at the process Oracle follows to execute a query.

Step 1: Parsing

Oracle checks:

  • SQL syntax correctness.
  • Object names (tables, columns) existence.
  • User privileges.

If all checks pass, Oracle proceeds to optimization.

Step 2: Optimization

This is the most important step.
The Oracle Optimizer evaluates different ways to execute the query and chooses the least cost plan.
It estimates the cost based on:

  • Available indexes.
  • Table statistics (number of rows, blocks, etc.).
  • Join methods.
  • Data distribution.

Oracle’s Cost-Based Optimizer (CBO) uses these statistics to calculate an execution plan cost value — the lower the cost, the better.

Step 3: Row Source Generation

Oracle converts the chosen plan into a series of row-source operations — these are the steps shown in the plan.

Step 4: Execution

Finally, Oracle executes the plan and retrieves data.

4. How to View the Execution Plan

You can view a query’s execution plan in several ways.

Method 1: Using EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explanation:

  • EXPLAIN PLAN FOR generates a hypothetical plan and stores it in a table (PLAN_TABLE).
  • DBMS_XPLAN.DISPLAY displays the plan in a readable format.

Method 2: Using AUTOTRACE in SQL*Plus

If you use SQL*Plus or SQL Developer, you can enable AUTOTRACE:

SET AUTOTRACE ON
SELECT * FROM employees WHERE department_id = 10;

It displays both:

  • The query output.
  • The execution plan and statistics (actual plan after execution).

Method 3: Using DBMS_XPLAN.DISPLAY_CURSOR (for actual plan)

SELECT * FROM employees WHERE department_id = 10;

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

Why use this?
Because it shows the actual execution plan after running the query, not just the estimated one.

5. Understanding the Execution Plan Output

A typical plan looks like this:

---------------------------------------------------------
| Id  | Operation                    | Name       | Cost |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   10 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES  |   10 |
|   2 |   INDEX RANGE SCAN           | EMP_DEPTIX |    3 |
---------------------------------------------------------

Let’s decode each column.

Id

The sequence number for each step.
Steps are executed bottom-up — the last row is executed first.

Operation

The task Oracle performs at each step:

  • TABLE ACCESS FULL → full table scan.
  • INDEX RANGE SCAN → index lookup on a range of values.
  • NESTED LOOPS, HASH JOIN, MERGE JOIN → join methods.

Name

The object used in the step (table or index).

Cost

The estimated cost assigned by the optimizer.
A lower cost generally means a faster plan.

6. Common Access Methods in Oracle Execution Plans

1. TABLE ACCESS FULL

Oracle reads the entire table.
Used when:

  • No suitable index exists.
  • A large portion of the table must be read.

2. TABLE ACCESS BY INDEX ROWID

Oracle first finds rowids from an index, then fetches corresponding rows from the table.

3. INDEX UNIQUE SCAN

Used when the index key is unique (e.g., primary key).

4. INDEX RANGE SCAN

Used for range conditions like:

WHERE salary > 50000

5. INDEX FULL SCAN

Oracle reads the entire index, usually in sorted order.

6. NESTED LOOPS

For each row in one table, Oracle finds matching rows in another table (efficient for small sets).

7. HASH JOIN

Used for joining large tables without suitable indexes.

8. MERGE JOIN

Used when both input sets are sorted.

7. Example of a Multi-Table Execution Plan

EXPLAIN PLAN FOR
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Possible output:

------------------------------------------------------------
| Id | Operation                     | Name          | Cost |
------------------------------------------------------------
|  0 | SELECT STATEMENT              |               |  12  |
|  1 |  NESTED LOOPS                 |               |  12  |
|  2 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS   |   3  |
|  3 |    INDEX UNIQUE SCAN          | DEPT_PK       |   1  |
|  4 |   TABLE ACCESS FULL           | EMPLOYEES     |   9  |
------------------------------------------------------------

Interpretation:

  1. Oracle starts with the EMPLOYEES table.
  2. For each matching row, it finds the corresponding department in DEPARTMENTS using the primary key index.
  3. The join method used is Nested Loops.

8. How to Interpret Performance

  1. High Cost Value
    • Indicates a slower or less efficient plan.
    • Try adding or rebuilding indexes.
    • Check table statistics (use DBMS_STATS.GATHER_TABLE_STATS).
  2. Full Table Scans
    • Acceptable for small tables.
    • Problematic for large tables with restrictive filters.
  3. Join Methods
    • Nested Loop: Good for small result sets.
    • Hash Join: Good for large datasets.
    • Merge Join: Requires sorted data.

9. Useful Tips

  • Always Gather Statistics: EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); This helps the optimizer make better decisions.
  • Use Bind Variables:
    Helps reuse execution plans and improves performance.
  • Avoid Functions on Indexed Columns:
    Using a function like UPPER(column) can prevent index usage.
  • Compare Estimated vs Actual Plan:
    Use DBMS_XPLAN.DISPLAY_CURSOR to see actual runtime behavior.

10. Summary

ConceptDescription
Execution PlanStep-by-step description of how Oracle executes your query
OptimizerDetermines the best way to access data
CostNumeric estimate of resource usage
Join MethodsNested Loop, Hash Join, Merge Join
Access MethodsFull Table Scan, Index Scan
ToolsEXPLAIN PLAN, AUTOTRACE, DBMS_XPLAN.DISPLAY_CURSOR