Uncovering Hidden Pitfalls of Oracle's Cost‑Based Optimizer and How to Fix Them

1. CBO Optimizer Pitfalls

The Cost‑Based Optimizer (CBO) is Oracle's default optimizer that evaluates many possible execution plans using statistics and chooses the lowest‑cost plan, a clear improvement over the legacy Rule‑Based Optimizer (RBO). However, CBO is complex and contains numerous bugs and limitations that can cause severe performance degradation, especially when statistics are inaccurate or when query transformations fail.

Key components of the optimizer include the Transformer (query transformer) , the Estimator (cost estimator) , and the Plan Generator . The transformer rewrites queries into semantically equivalent forms that are easier for the estimator to cost. Two transformation approaches exist: heuristic (rule‑based) and cost‑based. Heuristic transformations handle simple statements reliably, while cost‑based transformations are powerful but riddled with bugs, especially for complex subqueries.

Common transformation techniques include view merging, subquery unnesting (SU), join predicate push‑down (JPPD), filter push‑down (FPD), OR expansion, order‑by elimination, join elimination, and transitive predicate propagation. When transformations fail, the optimizer may fall back to a FILTER operation, which can become a performance killer.

2. Solving CBO Pitfalls

Typical issues and remedies are illustrated with real‑world cases.

2.1 FILTER Performance Killer

Single‑node FILTER: simple filtering, usually cheap.

Multi‑node FILTER: behaves like a nested‑loops join; if the inner side is large, execution can take days.

NOT IN Subqueries

When a NOT IN subquery contains NULL values, Oracle may not unnest it, leading to a FILTER with multiple child nodes. In 11g, the NULL‑AWARE anti‑join feature can rewrite such subqueries into a JOIN, dramatically improving performance. If the database version does not apply NULL‑AWARE, possible fixes are:

Declare NOT NULL constraints on the involved columns.

Add explicit IS NOT NULL predicates.

Rewrite the query using NOT EXISTS.

Convert the logic to an ANTI‑JOIN.

These rewrites often reduce execution time from hours or days to seconds.

OR Subqueries

OR combined with subqueries usually prevents unnesting, causing FILTER‑style execution. The recommended approach is to rewrite the OR logic as a UNION ALL view followed by a semi‑join or anti‑join, or to replace the OR with equivalent JOIN conditions.

2.2 Class‑like FILTER Issues

UPDATE statements that use WHERE EXISTS can exhibit FILTER‑like behavior, executing the subquery once per row. Adding a unique index on the join column or rewriting the UPDATE as a MERGE or inline‑view UPDATE can eliminate the repeated subquery execution.

Scalar subqueries also act like FILTERs, executing once per outer row. Converting them to outer joins (or regular joins when possible) allows the optimizer to use HASH JOINs and dramatically reduces runtime.

2.3 TABLE Function Cardinality Problem

The TABLE function creates a pseudo‑table with a default cardinality of 8 168 rows, which can mislead the optimizer into choosing HASH JOINs over more efficient nested‑loops. Using the cardinality(tab, n) hint or collecting a histogram for the column can correct the estimate and force the optimizer to pick the optimal plan.

2.4 Selectivity Mis‑calculation

When statistics on CHAR columns are inaccurate, Oracle converts strings to RAW then to numbers, rounding to 15 digits, which can cause wildly incorrect selectivity estimates. Collecting a histogram for the column stores the actual endpoint values, enabling the estimator to choose the correct index and reduce runtime from hours to seconds.

2.5 New Feature‑Induced Errors

Version upgrades may introduce new optimizer features (e.g., NULL‑AWARE, GROUP‑BY placement) that, if not properly configured, cause execution‑plan changes and errors. Disabling the offending hidden parameters (e.g., _optimizer_group_by_placement) restores correct behavior.

2.6 Poor Pagination

Three‑level pagination using ROWNUM prevents the optimizer from applying the STOPKEY technique, leading to full scans. A two‑level pagination that filters first and then applies ROWNUM allows Oracle to use index‑range scans and STOPKEY, cutting execution time from seconds to milliseconds.

2.7 HASH‑JOIN Collisions

HASH JOINs are efficient for large data sets, but if the build table has low selectivity (many duplicate values), hash buckets become overloaded, causing performance similar to nested loops. Rewriting the query to include additional selective columns or to use UNION ALL can reduce bucket size and eliminate collisions.

3. Strengthening SQL Review to Prevent Issues Early

Proactive SQL review during development can catch many of the above problems before they reach production. A four‑step workflow—SQL collection, analysis, optimization, and post‑deployment tracking—helps enforce best practices, such as avoiding unnecessary type conversions, ensuring proper statistics, and using appropriate hints only when needed.

Tools like SQLT (including the XPLORE feature) automate the detection of hidden parameters, statistics problems, and optimizer bugs, dramatically shortening the troubleshooting cycle.

By consistently applying these techniques—accurate statistics, thoughtful query rewriting, selective hint usage, and automated review—DBAs and developers can keep Oracle CBO performance healthy and avoid costly runtime surprises.

Q&A

Q1: Does a hash join involve sorting?

A1: No, a hash join does not require sorting; that is a key difference from a SORT‑MERGE join. Performance issues usually stem from insufficient hash area size or hash collisions.

Q2: When does Oracle choose not to use an index?

A2: Common reasons include inaccurate statistics, low selectivity, or operations on indexed columns (e.g., functions, arithmetic) that prevent index usage. See MOS document ID 67522.1 for details.