How Oracle 26ai Chooses an Execution Plan
Oracle's cost-based optimizer selects an execution plan using the SQL, available objects, optimizer statistics, and the optimization environment. A plan combines access paths with joins and operations such as filtering, sorting, and aggregation. It is more than a choice between an index and a table scan.
A conceptual planning process
- Parse and validate. Oracle checks syntax and resolves references. If a suitable shared cursor can be reused, the statement does not necessarily need a new optimization on every execution.
- Consider transformations. Equivalent query forms may offer different ways to obtain the result. Transformations must preserve SQL semantics.
- Estimate rows and work. Statistics and other information help estimate selectivity, row counts, and resource costs.
- Compare candidate plans. Access paths, join methods, and join orders contribute to the estimated cost. Oracle selects among the alternatives it considers.
- Execute and evaluate. The selected plan runs. A tuning investigation compares the estimates with available execution measurements.
This sequence is a teaching model, not an exact trace of every internal stage. The optimizer does not run each candidate plan to measure its actual duration before choosing one.
Three quantities that should not be confused
| Quantity | Meaning |
|---|---|
| Selectivity | The fraction expected to satisfy a predicate. |
| Cardinality | The number of rows expected from an operation. |
| Cost | An internal estimate used to compare work for candidate plans. |
| Elapsed time | A measurement affected by the actual execution and its environment. |
FROM order does not prescribe join order
SELECT c.customer_name, o.order_number
FROM customer c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_region = :region;
Assume CUSTOMER_ID identifies customers and ORDERS references that identifier. The statement defines the relationship and filter. Writing CUSTOMER first does not by itself force it to be the first accessed source. In nested loops, the outer row source drives access to the inner source; a hash join has build and probe inputs. The most useful method depends on the complete workload.
Validate the executed cursor
EXPLAIN PLAN shows an estimated plan without executing the statement. DBMS_XPLAN.DISPLAY_CURSOR can report a loaded cursor and available runtime statistics. Collect the relevant execution statistics first, identify the intended SQL ID and child cursor, and use ALLSTATS LAST when appropriate. The display option does not collect statistics retroactively.
Compare row estimates with actual rows while accounting for repeated starts. Assess buffer work, elapsed time, and application fetching behavior. A different plan or a lower estimated cost is not sufficient evidence of improvement.
The former diagram sequence described first-in-FROM driving tables and ANALYZE-based collection as general rules. Those historical claims have been replaced here with the current process. Original image files are retained separately.