Troubleshooting: Tuning Queries that Cannot Be Modified (11g and Above)
(Doc ID 1442213.1)
Last updated on DECEMBER 14, 2020
Oracle Database Cloud Service - Version N/A and later Oracle Database - Enterprise Edition - Version 18.104.22.168 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 Information in this document applies to any platform.
NOTE: This article supersedes the original Document:
<Document 122812.1> * TROUBLESHOOTING: Tuning Queries That Cannot be Modified (10g and below)
which was created in the 9i time frame.
Although most of the techniques are still valid in design, they may have been superseded in implementation and new techniques have become available in later versions as reflected here. For information on Tuning queries on earlier versions, see the earlier article above.
Query Tuning issues can often be alleviated by modification of the query in question, either to add hints or to change the query structure to avoid an issue. However in some cases the query cannot be modified because it is hard-coded in an application or is generated by application code. This document suggests methods that you can use to attempt to alter the access path taken by a SQL query without changing it.
The main thrust of this article is to provide information on how to affect query plans where the query cannot be modified. By way of completeness suggestions have also been provided for queries that can be modified. Remember that all these options can also be used for queries that can be edited.
N.B. Much of the information discussed here is recorded in other articles. However, the idea of this article is to group the required information together and reference other material as necessary.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!