Troubleshooting Upgrade Related Query Performance Degradation - Recommended Actions
(Doc ID 745216.1)
Last updated on SEPTEMBER 20, 2022
Applies to:Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
This article provides an action plan for gathering diagnostics when query performance has degraded following an upgrade and suggests ideas for quickly recovering the situation.
There are a number of reasons why the performance of a query or queries may degrade after an upgrade. The most likely cause is that the query being executed is now using a different access path than previously. Common causes are:
- Optimizer changes causing different plan selection -This can be due to bug fixes or new features
- Optimizer changes causing the same plan to perform differently
- Hardware/Environment changes causing the same plan to perform differently
Document This article covers a specific case where Oracle software has changed or has been upgraded. For cases that do not involve upgrades, See:
<Document 742112.1> Troubleshooting Query Performance Degradation - Recommended Actions
If you have changed to different hardware and performance has degraded, the following article suggests a number of useful suggestions:
<Document 604256.1> Why is a Particular Query Slower on One Machine (or Instance) than Another?
For more general Query Performance Issues see:
<Document 398838.1> FAQ: Query Tuning Frequently Asked Questions
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|Common Workarounds and Solutions|