My Oracle Support Banner

Query with Unchanged Execution Plan is Slower than Previously (Doc ID 223806.1)

Last updated on OCTOBER 24, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
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
Information in this document applies to any platform.

Purpose

This article serves as a checklist for investigating the drop in performance of a query (or other SQL statement) after a database upgrade. In this case, although the performance has degraded, the access path used by the query is unchanged. This is important because it means that the issue is with the implementation of the query in the new environment rather than a problem with the optimization of the query (of course this does assume that the performance of the original plan is still good in the new environment).

NOTE: this document was originally written in the Oracle 8i Timeframe but still contains information and methods that may be pertinent to more recent releases

Performance differences for queries with unchanged execution plans can occur in a number of scenarios not least with changes of hardware or with software version amendments. The suggestions in this article are applicable to most such scenarios.

This article only covers scenarios where the execution plan has NOT changed. For other possibilities please refer to:

<Document 160089.1> Why are my queries slow after upgrading my database?

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.