On the Eighth Day of 26ai, Viscosity Gave To Me…

Upgrade Performance Checklist (Excerpt from Upgrading Oracle Databases)

Upgrading to Oracle 26ai brings powerful new capabilities, but performance preparation still matters. As part of our 12 Days of Oracle 26ai series, we’re sharing a direct excerpt from the Upgrade Performance Checklist in our book Upgrading Oracle Databases. This checklist highlights key items DBAs should evaluate before, during, and after an upgrade to ensure a smooth, high-performance transition.

Below is a selected portion of the chapter.

Excerpt: Upgrade Performance Checklist

Looking for a quick start on what to check or do while upgrading to help maintain performance? There are several items to consider when endeavoring to have a successful and trouble-free upgrade. Using the AUTOUPGRADE tool will cover some of these items, though it is always good to review the logs and make sure you cover all the items listed here.

• Check for known issues in the version of Oracle database you are currently running as well as the version you are upgrading to. See MOS note 1227443.1 “Database PSU/BP/Update/Revision - Known Issues Primary Note”
• Adjust AWR top N SQL setting to collect more information a few weeks before upgrading. This example increases the number to 100.
• Set OPTIMIZER_FEATURES_ENABLE to the highest possible version unless you know you will have issues. This will ensure that you are taking advantage of all the new features in the upgraded database version. You can always set this lower in a session or at a SQL statement level if needed.
• Check the SYSAUX tablespace for free space and cleanup where needed. Use the script (utlsyxsz.sql).
• Clean up the audit trail in the database by moving the data and reducing the number of records in the audit tables. This will increase performance on gather stats and running upgrade scripts.
  Look at using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
• Gather statistics before your upgrade, having up-to-date statistics will allow the upgrade-related scripts to run faster when changing versions.
  • Dictionary – this is information about what is in your database
  • Fixed Objects – these are tables used internally by Oracle
  • Schema – these are the application-related schemas
• Install the latest version of OPatch.
• Apply recommended patches (consider using the latest Release Update).
• Apply the DataPump bundle patch prior to your upgrade.
• Capture your pre-upgrade performance statistics and AWR data for comparison post-upgrade.
• Adjust database parameters based on the documentation.
• Test your application for performance changes in the new version, where possible.
• After the upgrade, look for poor-performing SQL statements using AWR, SQL Monitor, Explain Plan, or SQLT.
• Use SQL plan stability tools (SPM, SQL Profile, SQL Patch).
• Post-upgrade, review the automatic statistics job and adjust settings as needed.
• After the upgrade is completed, only adjust database parameters that are required by the new version or based on feedback from memory advisors or specific incidents.
• A week or two after your upgrade is completed and you are happy with system performance, adjust AWR top N SQL back to your desired setting.
• Before every upgrade, create a Guaranteed Restore Point (GRP). GRPs serve as the first line of defense against database upgrade failures and unforeseen issues during the upgrade.

Closing Thoughts

As you explore the new capabilities of Oracle 26ai, performance remains foundational. Whether you're planning a major upgrade or preparing for an Autonomous AI Database deployment, a disciplined checklist like this one can save time, reduce risk, and ensure your environment runs smoothly from day one.

For the full set of guidance, best practices, and hands-on upgrade strategies, you can purchase our book written by our Oracle ACE Directors, Charles Kim, Sean Scott, and Gary Gordhamer, by clicking the button below: 

Happy Holidays! 🎄

Ready to Take Your Oracle Skills to the Next Level?

Join OraPub, Viscosity’s training hub for Oracle professionals, packed with expert-led courses and exclusive paid member benefits.

Check out Viscosity’s event page for upcoming virtual and on-site training opportunities.