Upgrading an Oracle database is never just a technical exercise—it’s a risk management exercise. For most DBAs, the biggest fear during a major upgrade isn’t downtime or syntax changes. It’s performance regression.
Oracle Database 23ai introduces powerful new features, modern SQL enhancements, and AI-driven capabilities. But before going live, every DBA must answer one critical question:
👉 Will my existing SQL run the same—or better—after the upgrade?
This is where SQL Performance Analyzer (SPA) and OCI SQL Performance Watch become your best friends.
In this blog, we’ll walk through a practical, step-by-step approach to migrating from Oracle 19c to Oracle Database 23ai, while detecting, analyzing, and fixing SQL regressions before production cutover.
Why Performance Validation Is Critical When Moving to Oracle 23ai
Oracle Database 23ai comes with:
- Optimizer enhancements
- New SQL parsing behavior
- Improved execution strategies
- AI-assisted optimizations
While Oracle guarantees backward compatibility, execution plans can still change—sometimes for the better, sometimes not.
Without proper testing, DBAs risk:
- Slow reports after upgrade
- Batch jobs exceeding SLA
- Application timeouts
- Unexpected CPU or I/O spikes
That’s why Oracle strongly recommends SQL Performance Analyzer as part of every major upgrade.
Overview: Tools Used in This Migration
🔹 SQL Performance Analyzer (SPA)
A built-in Oracle tool that:
- Captures real SQL workloads
- Replays them on the upgraded database
- Compares execution plans and performance
- Highlights improved, unchanged, and regressed SQL
🔹 OCI SQL Performance Watch (Optional)
A cloud-based service that:
- Monitors SQL behavior during upgrades
- Predicts SQL regressions
- Integrates with OCI Database Management
- Reduces manual analysis effort
Both tools complement each other, especially in OCI or hybrid environments.
Step 1: Prepare Your Oracle 19c Source Database
Before capturing any workload, make sure:
- The system represents real production behavior
- Statistics are up to date
- SQL plan baselines are stable
Recommended Pre-Checks
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Ensure:
- No major tuning activities are ongoing
- AWR snapshots are enabled
- System load reflects normal usage
Step 2: Capture SQL Workload Using SQL Performance Analyzer
The goal here is to capture actual SQL statements executed by the application, not synthetic test queries.
Create a SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PRE_23AI_STS',
description => 'Workload before Oracle 23ai upgrade'
);
END;
/
Load SQL from AWR
BEGIN
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'PRE_23AI_STS',
populate_cursor => DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 100,
end_snap => 110
)
);
END;
/
📌 Tip for DBAs:
Capture workloads during peak business hours for realistic results.
Step 3: Export the SQL Tuning Set
You’ll need the same workload available in Oracle 23ai.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'PRE_23AI_STS',
staging_table_name => 'STS_STAGE'
);
Export the staging table using Data Pump.
Step 4: Upgrade or Migrate to Oracle Database 23ai
At this stage:
- Perform your 19c → 23ai upgrade (DBUA, manual, or OCI tools)
- Apply latest RU
- Gather optimizer statistics
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Do not tune anything yet—we want a clean comparison.
Step 5: Import SQL Workload into Oracle 23ai
Import the staging table and unpack the SQL set:
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'PRE_23AI_STS',
staging_table_name => 'STS_STAGE'
);
Now Oracle 23ai has the exact same SQL workload as 19c.
Step 6: Execute SQL Performance Analyzer Trial
Create SPA Task
DECLARE
l_task VARCHAR2(100);
BEGIN
l_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name => 'PRE_23AI_STS',
task_name => 'SPA_19C_VS_23AI'
);
END;
/
Execute Before and After Comparison
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_19C_VS_23AI',
execution_type => 'COMPARE PERFORMANCE'
);
Oracle now:
- Executes SQL in 23ai
- Compares response time, CPU, buffer gets
- Identifies regressions and improvements
Step 7: Analyze SQL Performance Analyzer Report
Generate the report:
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name => 'SPA_19C_VS_23AI',
type => 'HTML'
) FROM dual;
What DBAs Should Focus On
- SQL with performance regression
- Change in execution plans
- Increased I/O or CPU usage
- Missing indexes or cardinality shifts
Typically, you’ll see:
- 80–90% unchanged or improved
- Small percentage regressed (these need attention)
Step 8: Fix Regressed SQL Before Go-Live
Common DBA fixes include:
✅ SQL Plan Baselines
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;
✅ Optimizer Parameters (Session Level)
Test changes safely without global impact.
✅ Statistics Refresh
Some regressions are simply stale stats.
✅ Minor SQL Rewrite
In rare cases, small query adjustments help the optimizer.
📌 Golden Rule:
Fix issues before production, not after complaints start.
Step 9: Using OCI SQL Performance Watch (Optional but Powerful)
If your database runs on OCI, SQL Performance Watch:
- Continuously tracks SQL behavior
- Flags regressions automatically
- Integrates with Database Management dashboards
- Reduces manual SPA analysis
This is especially useful for:
- Large environments
- Multiple PDBs
- Continuous upgrade testing
Final Pre-Production Checklist for DBAs
Before switching users to Oracle 23ai:
✔ SPA report reviewed
✔ Regressed SQL addressed
✔ Plan baselines validated
✔ Application testing completed
✔ Rollback strategy ready
Final Thoughts: Upgrade with Confidence
Migrating to Oracle Database 23ai doesn’t have to be stressful.
By using SQL Performance Analyzer and OCI SQL Performance Watch, DBAs can:
- Predict performance issues
- Eliminate surprises
- Go live with confidence
- Deliver a smooth upgrade experience
In 2026 and beyond, performance-aware upgrades are no longer optional—they’re a DBA best practice.
