SQL Plan Baseline Regression After an Upgrade
After an upgrade from 12.2 to 19c, a financial reporting query (SQL ID 8k3x7y2z1) that ran in 2 seconds now runs 90 seconds. AWR shows the query's plan hash value changed and DB CPU is the top foreground wait. The query is executed 500 times per hour by an application service. A SQL plan baseline for the old plan exists in DBA_SQL_PLAN_BASELINES, and its ENABLED and ACCEPTED columns are YES, but the new plan is being used. The application uses bind variables. The DBA has an active SQL Tuning Set captured before the upgrade. Optimizer statistics were gathered last night with AUTO_SAMPLE_SIZE. There is no SQL profile or SQL patch on the query. The business wants a fix that does not change application SQL.
1. Why might the existing SQL plan baseline not be used even though it is enabled and accepted? What would you check first?
Read the worked answer
First compare the baseline's SQL text and signature with the actual cursor. Query DBA_SQL_PLAN_BASELINES for SQL_TEXT, SQL_HANDLE, PLAN_HASH_VALUE, ENABLED, ACCEPTED, FIXED, ORIGIN, and LAST_EXECUTED; compare with V$SQL.SQL_FULLTEXT and V$SQL.SQL_PLAN_BASELINE. A common cause is that the baseline belongs to a different PDB's SQL Management Base or a different SQL text due to whitespace, comments, or literal versus bind representation, so the optimizer does not match it. Check OPTIMIZER_USE_SQL_PLAN_BASELINES at session and system level. Check whether the new plan was also accepted through auto-evolution or a manual load, because an accepted new plan can be chosen on cost. Also look for a SQL profile or SQL patch that can override baseline choice. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE shows accepted plans and whether the old plan is enabled.
2. How would you use the STS and SQL Tuning Advisor to validate a fix and avoid another regression?
Read the worked answer
Use the pre-upgrade SQL Tuning Set as the stable workload. Create a SQL Tuning Advisor task on the STS with DBMS_SQLTUNE.CREATE_TUNING_TASK, run it, and review the report for stale statistics, missing indexes, or a SQL profile recommendation. Test recommendations against the STS rather than ad hoc on production. If the old plan is still in the STS or cursor cache, load it as an accepted baseline with DBMS_SPM.LOAD_PLANS_FROM_SQLSET or LOAD_PLANS_FROM_CURSOR_CACHE. Compare elapsed time and plan hash after the change. Use SQL Performance Analyzer if available for a before/after workload comparison. The goal is to prove the fix on the same statements and bind patterns; a fix that only helps one literal is not sufficient for 500 executions per hour.
3. What immediate options can force the old plan without editing the SQL, and what are the trade-offs?
Read the worked answer
Make the old plan fixed with DBMS_SPM.ALTER_SQL_PLAN_BASELINE (FIXED => 'YES') after confirming it is the only accepted plan, or load the old plan from AWR or the cursor cache and accept it. A SQL patch or SQL profile can also force the plan, but it is less transparent and should be a last resort. Fixing the baseline prevents the optimizer from choosing a different plan, including a future better plan, so it is a stopgap; revisit it after statistics or code changes. Do not change OPTIMIZER_FEATURES_ENABLE globally just for one query. Validate with DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE and the STS, then monitor V$SQL for the plan hash and elapsed time.