My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Purpose

 

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.

Scope

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.

Details

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
Purpose
Scope
Details
 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
References

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