Reading an execution plan for the first time feels like looking at a secret code. But once you understand the logic, it becomes your most powerful tool for fixing slow queries.

To read a plan effectively, you need to follow the "Inside-Out" Rule and understand the Hierarchy.

1. The Structure of a Plan

When you generate a plan (using dbms_xplan.display), it looks like a table with several columns. Here is what the most important columns actually mean:

• Id: The step number (useful for referencing).

• Operation: What Oracle is actually doing (e.g., scanning a table, joining data).

• Name: The name of the table or index being used.

• Rows (Cardinality): How many rows Oracle thinks it will find at this step. (Crucial for tuning!)

• Cost (%CPU): The "energy" Oracle thinks it will spend.

2. The Golden Rule: Inside-Out, Top-to-Bottom

You don't read a plan from line 1 to the end like a book. Instead, you look for the most indented operation.

1. Start at the most indented step: This is the first action Oracle takes.

2. Move Upward: If two steps have the same indentation, the one on top happens first.

3. Parent-Child Relationship: A step that is "less indented" is the Parent. It consumes the data produced by the Children (the more indented steps below it).

3. A Real-World Example

Let’s look at a simple join between EMP and DEPT:

---------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |    14 |     4   (0)|
|   1 |  NESTED LOOPS            |             |    14 |     4   (0)|
|   2 |   TABLE ACCESS FULL      | DEPT        |     4 |     3   (0)|
|* 3 |   INDEX RANGE SCAN       | EMP_DEPT_IX |     4 |     0   (0)|
---------------------------------------------------------------------------

How to read this:

1. Step 2 and Step 3 are the most indented.

2. Step 2 is higher than Step 3, so it starts first. Oracle performs a Full Table Scan on the DEPT table.

3. Step 3 is next. For every row found in DEPT, Oracle does an Index Range Scan on the EMP_DEPT_IX index.

4. Step 1 is the parent. It takes the results from steps 2 and 3 and joins them using a Nested Loop.

5. Step 0 returns the final result to you.

4. What to Look For (The Red Flags)

A. The "Rows" Discrepancy

Look at the Rows column. If Oracle says it expects 1 row but you know the table has 1 million rows, the Optimizer has "stale statistics." It will pick a plan that is way too slow because it's working with bad information.

B. Table Access Full (FTS)

A TABLE ACCESS FULL isn't always bad (it's great for small tables). However, if you see it on a table with millions of rows while a user is waiting for a single record, you are likely missing an index.

C. Cartesian Products (MERGE JOIN CARTESIAN)

This usually happens when you forget a join condition (e.g., WHERE a.id = b.id). Oracle is forced to join every row of Table A with every row of Table B. If both have 1,000 rows, you get 1,000,000 results. Avoid this at all costs.

5. Using Predicates

At the bottom of most plans, you'll see a Predicate Information section.

• Access: This tells you the filter used to find the data in an index (The "fast" part).

• Filter: This tells you the data Oracle had to discard after reading it (The "slow" part). If you see a lot of "Filters" on a large table, you might need a better index.

Summary Checklist

• [ ] Find the most indented line (start here).

• [ ] Check if the Rows count looks realistic.

• [ ] Look for "Full Table Scans" on large tables.

• [ ] Check the "Join Method" (Nested Loops for small data, Hash Joins for large).