Troubleshooting Query Performance Degradation - Recommended Actions
(Doc ID 742112.1)
Last updated on OCTOBER 31, 2019
Applies to:Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup 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 Note provides an action plan for gathering Query Performance Degradation diagnostics and suggests ideas for quickly recovering the situation.
There are a number of reasons why the performance of a query or queries may degrade. The most likely cause is that the query being executed is now using a different access path than previously. Common causes are:
- Object Statistics Changes
- Schema Changes
- Data Changes
- Bind Peeking Issues
NOTE: This article does not specifically cover cases where software has changed or has been upgraded. For these cases, See:
<Document 745216.1> Troubleshooting Upgrade Related Query Performance Degradation - Recommended Actions
<Document 160089.1> Troubleshooting Server Upgrade Resulting in Slow Query Performance
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
For Database wide performance Issues see:
<Document 1377446.1> Troubleshooting Performance Issues
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