Oracle Execution Plans in Oracle 26ai: How to Read Them, Why They Change, and How to Fix Bad Plans Fast

A developer called me on a Friday afternoon. A report that always finished in under a minute was now running for 20 minutes. Nothing changed in the code. The data volume was similar. A statistics refresh job had run the night before.

I checked the execution plan. The optimizer had switched from an index range scan on a highly selective column to a full table scan on a 50-million-row table.

The fix took 10 minutes once I understood what the execution plan was actually telling me.

This is exactly why every DBA must know how to read execution plans correctly.

The Problem

A query that consistently runs fast suddenly becomes slow:

• Same SQL

• Same application

• Similar data

Yet performance drops dramatically.

This is almost always caused by a change in execution plan, not a change in the SQL itself.

The Cause: Why Execution Plans Change

An execution plan represents the sequence of operations Oracle performs to execute a SQL statement. It defines:

• Access paths (index vs full scan)

• Join methods (nested loops, hash join)

• Join order

The optimizer selects the plan with the lowest calculated cost. That cost changes when its inputs change.

A plan that was optimal yesterday may not be optimal today.

The Commands: How to Generate and Read a Plan

Step 1 — Generate the Plan

EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name, e.salary
FROM   employees e, departments d
WHERE  e.salary < 3000
AND    e.department_id = d.department_id
ORDER BY e.salary DESC;

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

Step 2 — Read the Plan Correctly

Execution plans are not read top to bottom.

• Start from operations without children

• Move upward through the tree

• Follow how rows flow

Always check:

👉 Predicate Information section
👉 It shows filters and join conditions applied at each step

Step 3 — Use the Correct DBMS_XPLAN Format

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id => 'your_sql_id',
    format => 'ALLSTATS LAST'
  )
);

👉 This is the most important format for performance tuning

Step 4 — Identify the Root Cause (E-Rows vs A-Rows)

• E-Rows → Estimated rows

• A-Rows → Actual rows

👉 Large differences indicate incorrect optimizer assumptions.

🔥 Real Production Case

Problem

• Query runtime: 20+ minutes

• High CPU usage

• Full table scan on large table
  

Diagnosis

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '8gxmk3t2pqn94',
    0,
    'ALLSTATS LAST'
  )
);

Observed:

• E-Rows = 142

• A-Rows = 4,287,391
  

👉 Severe misestimation caused the optimizer to choose an inefficient plan.

Root Cause

• Histogram removed during statistics refresh

• Column selectivity misrepresented

Fix

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SALES',
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS STATUS SIZE 254'
  );
END;
/

Validation

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    FORMAT => 'ALLSTATS LAST'
  )
);

Result

👉 Query runtime improved from over 20 minutes to 38 seconds

👉 E-Rows aligned with A-Rows

👉 Optimizer selected correct index-based plan

The Solution: Fixing Bad Execution Plans

Fix 1 — Gather Statistics

✔ Most common and effective fix

Fix 2 — SQL Plan Baseline

✔ Stabilizes known good plans

Fix 3 — Hints (Temporary Only)

SELECT /*+ INDEX(e emp_salary_ix) */ ...

✔ Immediate fix

❌ Not adaptive to future data changes

Understanding Query Transformations

Execution plans often look different from your SQL because Oracle rewrites queries internally.

OR Expansion

Transforms OR conditions into UNION ALL

View Merging

Combines inline views into outer queries

Subquery Unnesting

Converts subqueries into joins

👉 These transformations improve performance but change the visible plan structure.

🔎 Quick DBA Checklist

When a query becomes slow:

• Check execution plan using DISPLAY_CURSOR

• Compare E-Rows vs A-Rows

• Identify estimation errors

• Validate statistics and histograms

• Check recent system changes

• Apply targeted fixes

Real Example: The Friday Afternoon Fix

Step 1 — Identify SQL

SELECT sql_id, elapsed_time/1e6 AS secs
FROM   v$sql
WHERE  sql_text LIKE '%monthly_revenue%'
ORDER BY elapsed_time DESC
FETCH FIRST 3 ROWS ONLY;

Step 2 — Analyze Plan

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '8gxmk3t2pqn94',
    0,
    'ALLSTATS LAST'
  )
);

Step 3 — Apply Fix

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SALES',
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS STATUS SIZE 254'
  );
END;
/

Step 4 — Validate

👉 E-Rows and A-Rows aligned

👉 Plan switched back to index access

👉 Final runtime: 38 seconds (from 20+ minutes)

Key Takeaways

• Execution plans change when inputs change

• DISPLAY_CURSOR shows real execution, not theory

• E-Rows vs A-Rows reveals root cause

• Statistics and histograms are critical

• Query transformations affect plan shape

Conclusion

Execution plan issues are not random—they are predictable and diagnosable.

By understanding:

• How Oracle estimates cost

• How plans are generated

• Where assumptions break

You can resolve performance issues quickly and accurately.

👉 The key is not guessing—it is reading what the plan is telling you and acting on real data.

Next in This Series

Next post:

👉 Oracle access paths — index vs full scan and how the optimizer decides