Cost-Based Optimization: Review and Practical Checklist
Cost-based optimization compares estimated work for candidate execution plans. This module connected that planning model with statistics maintenance and runtime evaluation. The essential skill is to explain a decision through correct query semantics and evidence, rather than a fixed rule about indexes or table position.
Review the workflow
- Foundations: define the required result, realistic inputs, and the application's performance objective.
- Planning: distinguish validation, transformations, estimates, candidate selection, and execution.
- Benefits: use data distribution to reason about possible access strategies without fixed percentage thresholds.
- Limitations: investigate estimate errors, correlations, and regressions within supported optimization.
- Statistics: inspect metadata and use bounded DBMS_STATS operations when justified.
- Histograms: understand column distributions, workload relevance, and interactions with bind-sensitive statements.
Replace historical assumptions
| Old assumption | Current approach |
|---|---|
| The first FROM table always drives CBO. | Inspect the plan's row sources, join order, and join method. |
| An index is always better than a scan. | Evaluate the complete work for representative values. |
| Run ANALYZE on every object whenever data changes. | Use an appropriate automatic and targeted DBMS_STATS strategy. |
| Histograms belong only to indexes. | Histograms are column statistics used for estimation. |
| EXPLAIN PLAN proves improved performance. | Compare executed-cursor evidence and application behavior. |
| Statistics or hints guarantee an optimal plan. | Validate estimates, hint usage where applicable, and observed performance. |
A practical evaluation checklist
- Verify values, null behavior, duplicates, and joins.
- Capture representative inputs and realistic fetching.
- Identify the correct SQL ID and child cursor.
- Collect runtime statistics before requesting actual row-source measurements.
- Compare estimates and actual rows with starts in mind.
- Investigate one supported change and test the same workload again.
- Document evidence, remaining limitations, and recovery options.
A faster isolated execution is not enough if another important input regresses or the answer changes. Likewise, the same plan can slow down as data volume or concurrency changes. Keep measurements tied to their conditions instead of treating a plan hash as a permanent performance guarantee.
Key terms
- Selectivity
- The fraction satisfying a condition, estimated during planning or measured for a dataset.
- Cardinality
- The number of rows produced by an operation.
- Optimizer cost
- A relative estimate used to compare candidate work, not measured response time.
- Histogram
- Column statistics that describe distribution in more detail.
- Cursor
- A SQL execution context associated with shared information such as a parsed statement and plan.
- Plan baseline
- A mechanism within SQL plan management for managing accepted execution plans.
Apply the process
Begin with a specific query and a measurable problem. Use supported statistics and plan tools to investigate, and treat suggestions as hypotheses to test. Module 6 explains optimizer goals; Module 7 provides optional historical context. The work in this module supplies a foundation for evaluating targeted controls such as SQL hints without making them a substitute for diagnosis.