Cardinality Errors, SQL Profiles/Baselines/Patches, Hints, and Evidence-Driven Plan Control

Learning outcomes

After a release, one ServiceHub statement flips from an acceptable plan to a plan that floods the buffer cache. An emergency hint may contain the incident, but durable tuning needs a control ladder: first find the cardinality/selectivity error and repair its cause; then, if stability remains necessary, choose the narrowest plan-control mechanism with a documented owner, rollback, and review date.

1. Start from E-Rows versus A-Rows

If nested loops process 100,000 outer rows after Oracle estimated 10, replacing the join with a hash join may improve one execution while leaving the estimation defect in place. Find the first large divergence and ask whether skew, correlation, stale stats, expressions, binds, or semantic predicates explain it.

SELECT /*+ GATHER_PLAN_STATISTICS */ work_order_id,amountFROM servicehub_control_work_orderWHERE status_code='ESCALATED' AND region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));

2. Hints are direct instructions in SQL text

Hints can influence access paths, join order, join methods, transformations, and parallelism. An invalid or inapplicable hint can be ignored. A hint can also age badly as data changes. It is appropriate when the SQL is owned/tested and the reason is measured; it is weak as a substitute for missing statistics.

SELECT /*+ INDEX(w sh09_control_status_region_ix) GATHER_PLAN_STATISTICS */ work_order_id,amountFROM servicehub_control_work_order wWHERE status_code='ESCALATED' AND region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));

3. SQL plan baselines govern accepted plan history

SQL Plan Management (SPM) associates SQL with accepted plan baselines. The optimizer can use accepted plans; supported offerings can evaluate/evolve alternatives before accepting them. A baseline constrains plan choice—it does not fix bad cardinality estimates. Current 26ai licensing states that SQL Plan Management does not require Diagnostics Pack or Tuning Pack, although certain Standard/BaseDB-SE offerings have one-baseline/evolution restrictions.

DECLARE n NUMBER;BEGIN n:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>:sql_id); DBMS_OUTPUT.PUT_LINE('plans loaded='||n);END;/SELECT sql_handle,plan_name,enabled,accepted,fixed,reproducedFROM dba_sql_plan_baselinesWHERE sql_text LIKE '%SERVICEHUB_CONTROL_WORK_ORDER%'ORDER BY created DESC;

DECLARE n NUMBER;BEGIN n:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle=>:sql_handle,plan_name=>:plan_name); DBMS_OUTPUT.PUT_LINE('plans dropped='||n);END;/

4. SQL Profiles improve optimizer estimates

A SQL Profile is auxiliary information recommended/created through SQL tuning that helps the optimizer improve estimates and choices. It is not a fixed plan. Current 26ai licensing lists SQL Profiles under Oracle Tuning Pack. Tuning Pack is included in Free and selected cloud offerings, but is extra-cost on EE/EE-ES and requires Diagnostics Pack there.

5. SQL Patches attach hints without changing application SQL

A SQL Patch associates hints with matching SQL text/signature. Oracle commonly creates them through SQL Repair Advisor for critical compilation/execution failures; DBMS_SQLDIAG.CREATE_SQL_PATCH can also create one from user-supplied hints and requires CREATE ANY SQL PROFILE. A patch can outlive its root cause, so it needs explicit rollback and review. This chapter treats patch creation as optional/privileged rather than a mandatory tuning recipe.

DECLARE p VARCHAR2(128);BEGIN p:=DBMS_SQLDIAG.CREATE_SQL_PATCH(   sql_id=>:sql_id,   hint_text=>'INDEX(@"SEL$1" "W"@"SEL$1" "SH09_CONTROL_STATUS_REGION_IX")',   name=>'SH09_CONTROL_PATCH',   description=>'Temporary containment; review after root-cause fix'); DBMS_OUTPUT.PUT_LINE(p);END;/-- rollbackEXEC DBMS_SQLDIAG.DROP_SQL_PATCH('SH09_CONTROL_PATCH');

6. Compare the mechanisms

7. Wrong practice: emergency controls with no expiry

A hint, baseline, Profile, or Patch that rescued one incident can suppress future improvements after statistics, indexes, optimizer versions, or data distributions change. Every intervention needs the affected SQL signature, old/new evidence, reason, required entitlement, exact rollback, owner, and review/expiry condition.

8. Hands-on lab: repair statistics before persistence

DROP TABLE servicehub_control_work_order IF EXISTS PURGE;CREATE TABLE servicehub_control_work_order(work_order_id NUMBER PRIMARY KEY,status_code VARCHAR2(12) NOT NULL,region_code VARCHAR2(12) NOT NULL,amount NUMBER(10,2) NOT NULL);INSERT INTO servicehub_control_work_orderSELECT LEVEL,CASE WHEN LEVEL<=9950 THEN 'CLOSED' ELSE 'ESCALATED' END,CASE WHEN MOD(LEVEL,10)=0 THEN 'BAKU' ELSE 'NORTH' END,MOD(LEVEL*23,10000)/10FROM dual CONNECT BY LEVEL<=10000;CREATE INDEX sh09_control_status_region_ix ON servicehub_control_work_order(status_code,region_code);BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_CONTROL_WORK_ORDER',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1'); END;/SELECT /*+ GATHER_PLAN_STATISTICS */ work_order_id,amount FROM servicehub_control_work_orderWHERE status_code='ESCALATED' AND region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));

BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_CONTROL_WORK_ORDER',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO'); END;/SELECT /*+ GATHER_PLAN_STATISTICS */ work_order_id,amount FROM servicehub_control_work_orderWHERE status_code='ESCALATED' AND region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));DROP TABLE servicehub_control_work_order PURGE;

If the estimate improves, statistics were the root repair. If a production regression still needs containment, test a baseline/hint/other mechanism separately and record rollback.

9. Governance checklist and chapter bridge

• Measured regression: SQL ID/signature, child cursor, old/new runtime evidence, business impact.
• Root cause: first estimate divergence and data/statistics/index/environment change.
• Control: why this mechanism is narrower than alternatives.
• Entitlement: offering, Diagnostics/Tuning Pack status, privileges.
• Rollback: exact disable/drop/source-revert operation tested first.
• Review: owner and date/condition for revalidation.
• Post-change validation: correctness, A-Rows, Buffers, latency, concurrency, representative binds.

Chapter 10 now moves inside the shared pool and cursor lifecycle: parse, bind, execute, fetch, child cursors, bind peeking, adaptive cursor sharing, session cursor cache, and parse storms.

Authoritative references

• Licensing Information — SPM and Diagnostics/Tuning Pack boundaries
• Managing SQL Plan Baselines — SPM purpose
• Managing SQL Profiles — Profile mechanism
• DBMS_SQLDIAG — SQL Patch API
• DBMS_XPLAN — post-control runtime verification