SQL Plan Regression After Statistics Gathering
ORDERENTRY is a 19c OLTP database. Query Q1 joins ORDERS (1.4 billion rows) to ORDER_LINES (4.1 billion rows) and normally runs in 1.8 seconds using a nested-loop plan driven by a composite index on ORDERS(CUSTOMER_ID, ORDER_DATE). After the automatic statistics job gathered new statistics on ORDERS with AUTO_SAMPLE_SIZE, the optimizer began using a hash join with a full table scan of ORDERS; Q1 now takes 38 seconds. The good plan is still visible in AWR, but the original cursor has aged out of the shared pool. No SQL profile or SQL plan baseline exists. The application cannot be changed, and the next release is six weeks away. A test clone reproduces the regression. The DBA wants a fix that survives future statistics changes and does not force the nested-loop plan for every possible bind value. There is no pending statistics feature in use.
1. What is the most likely cause, and what evidence would you collect before changing anything?
Read the worked answer
The timing strongly suggests a plan change caused by new statistics on ORDERS: the optimizer's cardinality estimate for the join or for the CUSTOMER_ID, ORDER_DATE predicate changed enough to make a hash join with a full scan look cheaper. Before changing anything, confirm the SQL ID and both plan hash values in AWR. Compare DBA_HIST_SQLSTAT, DBA_HIST_SQL_PLAN, and DBA_HIST_SQLTEXT for the good and bad periods. Check DBA_TAB_STATISTICS.LAST_ANALYZED and DBA_TAB_COL_STATISTICS for ORDERS, and look for histogram changes on CUSTOMER_ID or ORDER_DATE.
Also capture the optimizer environment: optimizer_features_enable, system statistics, and any fixed parameters. Run the query in the test clone with DBMS_XPLAN.DISPLAY_CURSOR including +peeked_binds to see the estimated versus actual rows. The goal is to prove that the plan, not the data volume or an I/O bottleneck, changed.
2. Implement a fix using SQL Plan Management, including how to get the good plan when the cursor is gone.
Read the worked answer
Use SQL Plan Management to accept the known-good plan without hard-coding a hint. Because the original cursor is gone, load the good plan from AWR with DBMS_SPM.LOAD_PLANS_FROM_AWR for the SQL ID and the AWR snapshot range that contains the 1.8-second plan. If the AWR retention window no longer has it, reproduce the good plan in the test clone by restoring the prior statistics or using the same optimizer environment, then load it from the cursor cache with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
Verify the baseline in DBA_SQL_PLAN_BASELINES and the accepted plan in DBA_SQL_PLAN_BASELINES/DBA_SQL_PLAN_BASELINE_PLANS. Do not set FIXED='YES' unless you are certain only one plan is valid for all binds. Keeping the baseline enabled but not fixed lets the optimizer use the accepted plan now, while DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE can test and accept better plans later. That satisfies the requirement to survive statistics changes without forcing a nested-loop plan for every bind value.
3. Would you also restore or lock statistics? Explain the risk and decide.
Read the worked answer
Restoring statistics is a temporary mitigation, not the durable fix. DBMS_STATS.RESTORE_TABLE_STATS to the timestamp before the automatic job can remove the immediate trigger, but it rolls back statistics for the whole table and affects every query that uses ORDERS, not just Q1. That may fix Q1 while regressing other statements.
Locking statistics with DBMS_STATS.LOCK_TABLE_STATS prevents future automatic gathering, but it also freezes stale or incorrect statistics and is dangerous on a table that changes significantly. My decision: implement the SPM baseline first, because it is statement-scoped. Then, if analysis shows the new ORDERS statistics are objectively wrong (for example, a bad histogram or a sampling problem), restore the prior statistics as a short-term correction and investigate the statistics-gathering bug. Do not lock statistics permanently unless you have a controlled statistics-management process with documented refresh and testing. Re-test Q1 and a sample of other ORDERS queries after either change.