What is an Execution Plan?
An execution plan is the step-by-step route Oracle chooses to run your SQL: which indexes, which join method, in which order. Reading plans is the central skill of SQL tuning.
Method 1: EXPLAIN PLAN (Estimated)
Method 2: The Real Plan with Actual Rows
This shows E-Rows (estimated) and A-Rows (actual). When they are very different, the statistics are probably wrong.
Common Plan Operations
| Operation | Meaning | Good or bad? |
|---|---|---|
TABLE ACCESS FULL | Reads the whole table | Fine for small tables or when most rows are needed |
INDEX UNIQUE SCAN | Finds one row by a unique key | Excellent |
INDEX RANGE SCAN | Reads a range of index entries | Good |
TABLE ACCESS BY INDEX ROWID | Fetches the row after the index | Normal |
NESTED LOOPS | For each row on the left, look up the right | Good for few rows |
HASH JOIN | Builds a hash table in memory and probes it | Good for large sets |
SORT ORDER BY, HASH GROUP BY | Sorting and grouping | Watch memory and TEMP |
How to Read a Plan
- Look at the indentation. The most indented line runs first.
- At the same level, the first child runs first.
- Check Rows or A-Rows: where do millions of rows appear?
- Look for
TABLE ACCESS FULLon a big table with a very selective WHERE. - Compare E-Rows with A-Rows to detect bad statistics.
Predicate Information
At the bottom of the plan you see access and filter predicates. access means the condition was used to find rows efficiently (through an index). filter means rows were read first and then thrown away.
A Plan Before and After an Index
Find the Plan of a Slow Query by SQL_ID
Conclusion
An execution plan shows how Oracle runs your SQL. Look for big row counts, unwanted full scans and gaps between estimated and actual rows.

