Oracle 26ai Cost-Based SQL Optimization
This module develops a practical understanding of Oracle AI Database 26ai cost-based optimization. The optimizer compares possible ways to execute SQL using estimates of the work involved. Your role is to express the required result correctly, provide useful information about the data, and evaluate what actually happens when the statement runs.
Cost-based optimization is the modern planning model, not a switch to try only after rule-based optimization fails. Module 7 provides historical context for RBO. Here, the focus is supported methods and evidence from the current workload.
What you will learn
- Distinguish SQL validation, optimization, execution, and measurement.
- Explain how selectivity and cardinality estimates influence candidate plans.
- Investigate poor estimates without assuming that every full scan is a defect.
- Inspect and gather optimizer statistics with DBMS_STATS in a controlled environment.
- Recognize column histograms, data skew, and the limits of estimates involving bind values.
- Compare results and measured work before retaining a tuning change.
Estimates are inputs to decisions
A query asks for a result, not a physical sequence of reads. Oracle may transform a query, consider index and table access, and compare join methods and orders. A cost value is a relative planning estimate. It is not a stopwatch reading and does not prove that every possible plan was considered.
For example, retrieving a small number of scattered rows can suit an index lookup, while retrieving much of a table can suit a scan. Requested columns, row organization, joins, and other work change the comparison. There is no universal percentage threshold that separates the two.
Use a reproducible investigation
- Define the correct output, including nulls and duplicates.
- Identify representative inputs and whether the application fetches some or all rows.
- Record the relevant cursor and available execution statistics.
- Investigate a specific estimate or expensive operation.
- Test one justified change and compare the same cases again.
Keep statistics maintenance separate from casually rewriting application SQL. Refreshing statistics can change plan choices, so timing, scope, and recovery options matter. Adding an index can affect writes and storage as well as reads.
How to use the examples
The SQL snippets assume the named training tables exist and that you have the necessary privileges. Bind placeholders receive values from your application or SQL client. PL/SQL blocks use the SQL*Plus/SQLcl slash terminator where shown. Examples are instructional, not captured benchmarks, and no Oracle commands were executed to publish this module.
Use an isolated training schema for changes. Preserve a baseline and record what you changed. Continue with the optimizer process, where planning decisions are separated from runtime validation.