My Oracle Support Banner

Troubleshooting: Tuning Queries that Cannot Be Modified (11g and Above) (Doc ID 1442213.1)

Last updated on SEPTEMBER 20, 2022

Applies to:

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 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!

In this Document
 Query cannot be modified
 Use SQL Plan Management to force the desired access path
 Modify Statistics
 Add or remove indexes
 Create a view with embedded hints
 Create a Materialised View (Snapshot)
 Modify Instance Parameters
 Modify the degree of parallelism on the table/indexes
 Query can be modified
 Add Hints
 Create inline views
 Add more selective predicates
 Divide up the work of one query into multiple queries
 Rewrite the query in PL/SQL
 Rewrite the query
 Final Note

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