Cost-Based Optimizer, Query Transformation, Join Order, Access Paths, and Cost Model

Learning outcomes

A ServiceHub report changes from milliseconds to seconds after the data distribution shifts. The SQL text is unchanged and all indexes still exist. One engineer says a plan with cost 900 should run in about 900 milliseconds; another wants to force the written join order. Both diagnoses misunderstand Oracle's cost-based optimizer (CBO). Oracle treats SQL as a declarative request, explores legal transformations and candidate plans, estimates their resource demand, and chooses among alternatives using a relative cost model.

1. Optimization is a pipeline, not a magic black box

After parsing and semantic checks, Oracle can transform the query into equivalent forms. The estimator predicts selectivity and row counts. The plan generator considers legal access paths, join orders, join methods, sorts, aggregations, and transformations. It assigns relative cost to alternatives and searches for a low-cost plan within optimization-time limits.

2. Cost is relative resource demand, not a clock

The optimizer cost model incorporates estimated I/O, CPU, memory-sensitive work, cardinality, data-set sizes, data distribution, and access structures. Cost is meaningful for comparing candidate plans for the same statement under the same optimizer environment. It is not milliseconds, seconds, or an SLA. Costs of unrelated queries are not a ranking of which query will finish first.

EXPLAIN PLAN SET STATEMENT_ID = 'SH09L1_COST'FORSELECT w.work_order_id, r.region_nameFROM servicehub_opt_work_order wJOIN servicehub_opt_region r ON r.region_id = w.region_idWHERE w.status_code = 'OPEN'  AND r.region_code = 'NORTH';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH09L1_COST','BASIC +COST +ROWS +PREDICATE'));

3. Query transformation changes the search space

Subquery unnesting can turn an eligible nested query into a join-like form so its tables participate in join ordering. Predicate pushing can move filters into an unmerged view or query block where they can reduce rows earlier. Other transformations include view merging, OR expansion, join factorization, and more. The optimizer only performs transformations that are legal, and cost-based transformations only when the transformed alternative is estimated to be beneficial.

EXPLAIN PLAN SET STATEMENT_ID = 'SH09L1_EXISTS'FORSELECT r.region_id, r.region_nameFROM servicehub_opt_region rWHERE EXISTS (  SELECT 1  FROM servicehub_opt_work_order w  WHERE w.region_id = r.region_id    AND w.status_code = 'OPEN');SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH09L1_EXISTS','TYPICAL +PREDICATE +ALIAS'));

If the plan shows a SEMI join or other transformed evidence, explain it. If it does not, do not claim the transformation happened merely because Oracle supports it.

4. Join order, access path, and join method are interdependent

Suppose a region lookup produces one row. Starting from that row can make an index probe into work orders attractive. If the region predicate is unselective, a scan plus hash join may cost less. Oracle therefore does not choose “the index” in isolation: estimated selectivity changes candidate join order; join order changes which index probes are feasible; available access paths change join costs.

SELECT table_name, num_rows, blocks, last_analyzedFROM user_tablesWHERE table_name IN ('SERVICEHUB_OPT_REGION','SERVICEHUB_OPT_WORK_ORDER')ORDER BY table_name;SELECT index_name, table_name, distinct_keys, clustering_factor, leaf_blocksFROM user_indexesWHERE table_name IN ('SERVICEHUB_OPT_REGION','SERVICEHUB_OPT_WORK_ORDER')ORDER BY table_name,index_name;SELECT table_name,column_name,num_distinct,num_nulls,histogramFROM user_tab_col_statisticsWHERE table_name IN ('SERVICEHUB_OPT_REGION','SERVICEHUB_OPT_WORK_ORDER')  AND column_name IN ('REGION_CODE','REGION_ID','STATUS_CODE')ORDER BY table_name,column_name;

5. Wrong repair: force join order before proving the estimate

Hints such as LEADING, USE_NL, and INDEX can constrain choices, but they do not explain why Oracle preferred another plan. If the regression comes from a correlated pair of columns being estimated independently, a hint treats the symptom. The repair sequence is: verify result semantics; inspect statistics and predicates; compare estimated and actual rows; find the first material cardinality error; repair statistics/modeling/query logic where possible; then use plan control only if stability is still required.

6. Hands-on lab: skewed ServiceHub workload

DROP TABLE servicehub_opt_work_order IF EXISTS PURGE;DROP TABLE servicehub_opt_region IF EXISTS PURGE;CREATE TABLE servicehub_opt_region (  region_id NUMBER PRIMARY KEY, region_code VARCHAR2(12) UNIQUE NOT NULL, region_name VARCHAR2(80) NOT NULL);CREATE TABLE servicehub_opt_work_order (  work_order_id NUMBER PRIMARY KEY, region_id NUMBER NOT NULL, status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL, created_at TIMESTAMP NOT NULL,  CONSTRAINT sh09_l1_region_fk FOREIGN KEY(region_id) REFERENCES servicehub_opt_region(region_id));INSERT INTO servicehub_opt_region VALUES (1,'NORTH','North');INSERT INTO servicehub_opt_region VALUES (2,'SOUTH','South');INSERT INTO servicehub_opt_region VALUES (3,'EAST','East');INSERT INTO servicehub_opt_region VALUES (4,'WEST','West');INSERT INTO servicehub_opt_work_orderSELECT LEVEL, CASE WHEN LEVEL<=9000 THEN 1 ELSE 1+MOD(LEVEL,4) END,       CASE WHEN MOD(LEVEL,20)=0 THEN 'OPEN' ELSE 'CLOSED' END,       MOD(LEVEL*17,10000)/10, TIMESTAMP '2026-01-01 00:00:00'+NUMTODSINTERVAL(LEVEL,'MINUTE')FROM dual CONNECT BY LEVEL<=10000;CREATE INDEX sh09_l1_region_status_ix ON servicehub_opt_work_order(region_id,status_code);BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_OPT_REGION',cascade=>TRUE); DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_OPT_WORK_ORDER',cascade=>TRUE);END;/

EXPLAIN PLAN SET STATEMENT_ID='SH09L1_LAB' FORSELECT w.work_order_id,w.amountFROM servicehub_opt_work_order wJOIN servicehub_opt_region r ON r.region_id=w.region_idWHERE r.region_code='NORTH' AND w.status_code='OPEN';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH09L1_LAB','TYPICAL +ROWS +COST +PREDICATE'));DROP TABLE servicehub_opt_work_order PURGE;DROP TABLE servicehub_opt_region PURGE;

7. Production judgment

Investigate optimizer surprises through estimates and evidence. SQL text order is not execution order, a full scan is not automatically bad, and a small numeric cost is not a performance guarantee. Avoid hidden/underscore parameters and unexplained optimizer-feature toggles. No extra pack, restart, or COMPATIBLE change is required here.

Lesson 2 focuses on the CBO's most important data model: optimizer statistics, including skew, correlated columns, stale detection, preferences, and incremental partition statistics.

Authoritative references

• Query Optimizer Concepts — optimizer pipeline, selectivity, cardinality and cost
• Query Transformations — predicate pushing, unnesting and cost-based transformations
• Optimizer Access Paths — access-path choice
• DBMS_XPLAN — plan display