Oracle Database 26ai: Fixing SQL Plan Regression After an Upgrade Using SQL Tuning Advisor, SQL Profiles, and SQL Plan Baselines

A database upgrade completed successfully over the weekend.

No application errors. No invalid objects. No failed post-upgrade checks.

But Monday morning was a disaster.

A critical reporting query that normally completed in under 2 seconds suddenly started running for more than 80 seconds. CPU usage increased sharply, users complained about slow dashboards, and the operations team opened a production incident.

The SQL itself had not changed.

The optimizer had.

This is one of the most common and dangerous issues after database upgrades: SQL plan regression.

Oracle Database 26ai includes multiple technologies designed to detect, analyze, and stabilize execution plans, but understanding how to use them correctly is critical for DBAs working in performance-sensitive environments.

This article walks through a complete production-style troubleshooting workflow using:

• SQL Tuning Sets (STS)

• SQL Tuning Advisor

• SQL Profiles

• SQL Plan Baselines

• Automatic SQL Plan Management (SPM)

Environment

• Oracle Database 26ai

• Reporting schema: SH

• SALES table: ~220 million rows

• CUSTOMERS table: ~8 million rows

• Query type: sales reporting dashboard

• Runtime before upgrade: ~2 seconds

• Runtime after upgrade: ~80 seconds

The Problem Query

SELECT c.cust_last_name,
       c.cust_first_name,
       s.prod_id,
       s.amount_sold,
       s.quantity_sold,
       s.time_id
FROM   sh.sales     s
JOIN   sh.customers c
       ON s.cust_id = c.cust_id
WHERE  s.time_id >= ADD_MONTHS(TRUNC(SYSDATE,'MM'), -1)
AND    s.amount_sold > 1000
ORDER BY s.time_id DESC;

Root Cause Analysis

After the upgrade:

• Optimizer statistics remained unchanged

• SQL text remained unchanged

• Indexes remained valid

However, Oracle selected a completely different execution plan.

Before upgrade:

• Nested loops

• Index range scan

• Low I/O
  

After upgrade:

• Hash joins

• Full table scans

• Large temporary tablespace usage

This is classic SQL plan regression.

Step 1: Identify High-Impact SQL

SELECT sql_id,
       executions,
       ROUND(elapsed_time / NULLIF(executions,0) / 1e6, 2) AS avg_secs,
       ROUND(buffer_gets / NULLIF(executions,0))           AS avg_buffer_gets,
       ROUND(disk_reads / NULLIF(executions,0))            AS avg_disk_reads
FROM   v$sql
WHERE  parsing_schema_name = 'SH'
AND    executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Step 2: Compare Execution Plans

Current Plan

SELECT *
FROM   TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id          => 'your_sql_id',
    cursor_child_no => NULL,
    format          => 'ALLSTATS LAST +PREDICATE +NOTE'
  )
);

Key Findings

Observed after upgrade:

HASH JOIN
TABLE ACCESS FULL SALES
TEMP SPACE

Observed before upgrade:

NESTED LOOPS
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID

The optimizer estimated much larger row counts after the upgrade and selected a different join strategy.

Step 3: Create SQL Tuning Set (STS)

SQL Tuning Sets are used to capture high-impact SQL for tuning analysis.

Create SQL Tuning Set

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'UPGRADE_REGRESSION_STS',
    description => 'SQL statements affected after database upgrade'
  );
END;
/

Load SQL Into STS

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p)
    FROM   TABLE(
             DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
               'parsing_schema_name = ''SH'''
             )
           ) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_name     => 'UPGRADE_REGRESSION_STS',
    populate_cursor => cur
  );
END;
/

Step 4: Run SQL Tuning Advisor

DECLARE
  l_task VARCHAR2(100);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
              sqlset_name => 'UPGRADE_REGRESSION_STS',
              task_name   => 'TUNE_26AI_REGRESSION');

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
    task_name => l_task
  );
END;
/

Step 5: Review Advisor Recommendations

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
         'TUNE_26AI_REGRESSION'
       ) AS report
FROM dual;

Advisor Findings

Recommendations included:

• Create SQL Profile

• Accept alternative execution plan

• Improve cardinality estimates

• Replace full scan with index access
  

Step 6: Accept SQL Profile

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name   => 'TUNE_26AI_REGRESSION',
    name        => 'SALES_REPORT_PROFILE',
    replace     => TRUE,
    force_match => FALSE
  );
END;
/

Step 7: Validate Improved Plan

SELECT *
FROM   TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id => 'your_sql_id',
    format => 'ALLSTATS LAST'
  )
);

Expected Plan

NESTED LOOPS
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID

Step 8: Stabilize the Plan with SQL Plan Baseline

SQL Profiles improve estimates, but they do not permanently lock execution plans.

SQL Plan Baselines help prevent future regressions.

Load Plan From Cursor Cache

DECLARE
  l_plans_loaded NUMBER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
                      sql_id => 'your_sql_id'
                    );

  DBMS_OUTPUT.PUT_LINE('Plans Loaded: ' || l_plans_loaded);
END;
/

Step 9: Verify SQL Plan Baseline

SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed
FROM   dba_sql_plan_baselines
ORDER BY created DESC;

Step 10: Enable Automatic SQL Plan Management

Oracle Database 26ai includes Automatic SQL Plan Management enhancements.

Enable Auto SPM

EXEC DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');

Verify Configuration

SELECT parameter_name,
       parameter_value
FROM   dba_sql_management_config;

Validation

Performance Improvement

Understanding the Difference

SQL Profile

• Improves optimizer estimates

• Helps choose better plans

• Does not lock plan permanently

SQL Plan Baseline

• Stores approved plans

• Prevents regressions

• Stabilizes execution behavior

SQL Tuning Set

• Captures high-load SQL

• Useful for testing and upgrades

• Helps compare workload behavior
  

Key DBA Lessons

• Upgrades can silently change execution plans

• SQL plan regression is often optimizer-related

• SQL Profiles and SPM work best together

• SQL Tuning Sets are essential for upgrade validation

• Always validate plans after major upgrades

Conclusion

The database upgrade itself was successful.

The problem was execution plan regression caused by optimizer behavior changes after the upgrade.

By using:

• SQL Tuning Sets

• SQL Tuning Advisor

• SQL Profiles

• SQL Plan Baselines

the problematic query returned to stable performance without changing application code.

Oracle Database 26ai provides extremely powerful SQL tuning and plan management capabilities, but DBAs must understand how to use them correctly in real production scenarios.

SQL tuning is not guesswork.

It is controlled investigation backed by execution plans, optimizer statistics, and validated runtime evidence.