Step-by-Step Oracle 19c Upgrade: Performance Comparison, SPA, and AutoUpgrade

Performance Comparison Before and After Upgrade

We upgraded the database from 11.2.0.4 to 19c, generated identical workloads with HammerDB, and captured AWR snapshots (pre‑upgrade snapshot 117 and post‑upgrade snapshot 118). An awrdiff.html report was produced to compare CPU_TIME and ELAPSED_TIME, showing overall performance improvement.

SQL Performance Analyzer (SPA)

Using the provided SPA scripts we compared CPU_TIME and ELAPSED_TIME for the SQL Tuning Set STS_CaptureCursorCache before and after the upgrade. The SPA reports highlighted SQL ID 13dn4hkrzfpdy as an example of performance change.

SQL Plan Management

We created a SQL Plan Baseline for the identified SQL using Carlos Sierra’s script. After accepting the baseline, we re‑ran the SPA scripts to verify that the new plan improved performance.

SQL Tuning Advisor

Running sta_cc.sql against the STS generated tuning recommendations. The advisor suggested index creations and other changes; we executed them (ignoring duplicate‑index errors) and regenerated the SPA reports to observe the effect.

Automatic Upgrade with AutoUpgrade Tool

The AutoUpgrade tool (version 20191125) was used to upgrade a 12.2.0.1 database to 19c. A configuration file ( config.cfg) was prepared, the upgrade was launched, and progress was monitored with lsj and status -job. After the upgrade, the database compatibility was set to 19c and the instance restarted.

By following these steps you can assess the impact of an Oracle database upgrade, tune SQL statements with SPA and the Tuning Advisor, manage execution plans with SQL Plan Management, and automate the upgrade process while ensuring post‑upgrade compatibility.