Diagnosing Cost-Based Optimizer Limitations
Cost-based optimization depends on estimates. A sophisticated model can still make a poor choice when its information does not represent the statement's workload. The remedy is a focused investigation within supported Oracle optimization, not a switch to the historical rule-based optimizer.
Start with the symptom
Identify whether the problem is initial response, complete fetching, resource use, or a regression for particular inputs. Record the SQL and its relevant environment. Confirm that the result is correct before trying to improve speed. Comparing unrelated runs can confuse a plan issue with changes in caching, concurrency, or data volume.
Common sources of estimation difficulty
- Statistics missing important data changes or newly loaded values.
- Skew that is not represented adequately for the predicates.
- Related columns whose combined selectivity differs from an independence assumption.
- Expressions, conversions, or complex query structures that complicate estimates.
- Bind-sensitive workloads for which different inputs require substantially different work.
For example, if 50% of employees are retired and 5% work in Finance, multiplying those fractions predicts 2.5% only under an independence assumption. The true intersection could differ considerably. Appropriate column-group statistics can help with related predicates, but do not guarantee exact cardinalities.
Read evidence before choosing a fix
| Observation | Investigate |
|---|---|
| Estimated and actual rows differ | Starts, filter values, statistics, and predicate relationships. |
| Many repeated table accesses | Outer-row counts, inner access costs, and join alternatives. |
| A plan changed after maintenance | Statistics history, optimizer environment, and representative before/after cases. |
| Same plan, slower execution | Data growth, resource contention, I/O, and application fetching. |
Collection and optimization have costs
Statistics collection consumes resources and must fit the maintenance strategy. Scope it to a justified need and examine scheduling, sampling, and preferences. Avoid generating ANALYZE commands for every schema as a routine response to one slow statement.
Hard parsing also requires work. Cursor sharing can reduce repeated parsing; statements are not necessarily reoptimized every time they run. A plan may change when relevant inputs change, and some adaptive mechanisms have specific applicability. Neither a permanently fixed plan nor universal automatic adaptation describes all executions.
Manage regressions deliberately
Before changes, preserve evidence and a way to restore the relevant configuration or statistics. SQL plan management can help manage accepted plans and evaluate alternatives. It does not freeze response time or guarantee that an old plan remains best after major workload changes.
Hints may be useful for a targeted investigation, but check whether they were used and compare complete performance. Hash joins did not replace nested loops, and joining more than three tables does not inherently make a full scan wrong. The selected method and access paths require evidence.
The next lesson demonstrates bounded statistics inspection and collection. Its examples replace the former whole-database ANALYZE script with a controlled DBMS_STATS workflow.