Tuning Queries That Cannot be Modified

PURPOSE

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 hardcoded 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 & APPLICATION

Analysts & customers trying to tune SQL queries.

The main thrust of the 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.

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. Also note that some of the suggestions will have no affect on queries that use the Rule based optimizer because they are specific to Cost based. These suggestions are highlighted as such.

SQL Query Tuning suggestions

There are usually 2 situations when a query needs be tuned.

• Query cannot be modified (e.g. where the query is generated from within the application).
• Query in question can be modified

Query cannot be modified

Often a query that is performing badly cannot be modified. This may be because the query is embedded in an application or is generated code. There are a number of possibilities for tuning such queries.

Remember that all these options can also be used for queries that can be edited.

N.B. It should be noted that all the suggestions below are workarounds to the problem of inflexibility in the front end application. Oracle provides a mechanism to modify the query plan (hints). Ultimately, if none of the workarounds below are acceptable there is little that can be done. In this case, perhaps the base application needs to be modified to accept query hints...

• Modify Statistics (CBO ONLY)

The cost based optimizer uses table, column and index statistics to determine the access path for a query.

Modification of these statistics (either reducing or increasing or even deleting) may cause the optimizer to choose a different access path for the query. Remember that the statistics that have been gathered for the optimizer's use are not 100% complete. There is a trade off between plan accuracy, the time taken to gather statistics and the space that statistics take up in the database. Also there is no attempt to hold statistics on columns whose values are clearly related in application terms. For example some column values may actually be related in reality (e.g. Town and State – Paris is always in France). Oracle does not maintain any statistics that would record this relationship.

Gather table statistics with a different sample size:

Choosing a different sample size may gather statistics that reflect the actuality of your data more closely. Usually a higher sample size is recommended but sometimes a lower sample or even no statistics can give the desired results. See the following articles for information regarding gathering statistics: Note:114671.1 and Note:44961.1

Add histograms to relevant columns:

Column histograms are a valuable tool that can increase the accuracy of statistics for columns with an uneven data distribution. Changing column statistics modifies the selectivity for predicates on those columns and so can have a favourable affect on queries. See Oracle9i Database Performance Guide and Reference Page 3-20 “Using Histograms” for more details.

• Add or remove indexes (BOTH CBO/RBO)

Additional indexes may provide a better access path for slow queries. Equally an unselective index may be causing an inefficient access path. The CBO should be able to determine the relative usefulness of different indexes if the statistics provided are adequate. If indexes are causing a problem but cannot be removed then they can be disabled using hints or other methods. See Note:122986.1

• Are there 8 or more tables in the join?

If so then the CBO may not be even considering the join order that you want to use. Consider using the Parameter:OPTIMIZER_MAX_PERMUTATIONS parameter to change the maximum number of permutations considered. Also See Note:73489.1 for information on maximum permutations considered by the optimizer.

• Create a view with embedded hints (BOTH CBO/RBO)

Often creating and then referencing a view that contains hints that force the access path that you require can force a desired access path. This is especially useful for distributed queries where a view can be built on a remote node. There is a distributed query example in Note:33838.1 Determining the execution plan for a distributed query

• Create a Materialised View (Snapshot) (BOTH CBO/RBO)

This is similar to view creation except that you store the query results somewhere. It operates by prefetching some or all of the data for the query so that it does not have to be fetched at execution time. See the Oracle9i Data Warehousing Guide Chapter 8 “Materialized Views”

• Modify Instance Parameters (BOTH CBO/RBO)

Modification of Instance wide parameters can help change the access path for queries but be aware that it may also change other access paths in a detrimental fashion.

• Modify the degree of parallelism on the table/indexes (CBO ONLY)

Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make FTS look less attractive and ultimately will stop PQO being used (if degree set to 1 or noparallel). See Note:203238.1 Using Parallel Query for more details.

• Consider using stored outlines to maintain the desired access path (CBO ONLY)

The stored outline facility provides the opportunity for developers to fix the plan of any query they submit to the database and control how it executes.

Some example implementations may include:

Test system where the good plan can be generated

This plan that could be stored and then transferred to the live environment.

Generation of a good plan for storing as an outline by Manipulating the schema to force a specific plan

For example by removing all the indexes that should not be selected / making indexes look very attractive for example by using punitive FTS parameters (reduce DB_FILE_MULTIBLOCK_READ_COUNT to 1) and encouraging index parameters (OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING) a desired plan may be chosen since no other choices are available. Then storing an outline under these conditions, reverting the changes and applying the outline to force the desired plan.

For reference, please refer to:

Note:67536.1 Stored Outline Quick Reference

Note:463288.1 How to generate an outline with a good plan loaded into shared_pool

Note:102311.1 How to Move Stored Outlines for One Application from One Database to Another

Oracle9i Database Performance Guide and Reference Chapter7 “Using Plan Stability”

@ Also See Note:92202.1 which shows how you can manipulate outlines – this is NOT supported.

Query can be modified

Modifying the query should really be the preferred option as hints are the recommended method for modifying access paths.

• Add Hints (BOTH CBO/RBO)

Hints are directives added to the select list of a query. They force the query to operate in a particular manner. Hints are available to alter all manner of factors that affect queries including single table access methods, join order and methods.

See Note:29236.1 QREF: SQL Statement HINTS

Advanced hint usage:

See the following notes for more information on using hints effectively:

Note:50607.1 How to specify an index hint

Note:62364.1 Hints and subqueries

Note:69992.1 Why is my hint ignored?

Note:68084.1 Using hints to optimize an Update with subquery not using index on updated table

• Create inline views (BOTH CBO/RBO)

Inline views are a technique that effectively embeds a view definition within the from clause of a query. It is useful as the inline view can be modified to prevent merging occurring and force query blocks to be executed separately from the rest of the query due to view merging restrictions. Forcing the query block to execute separately can also have performance benefits in some circumstances.

• Add more selective predicates (BOTH CBO/RBO)

It is possible that the existing predicates for a query are unselective and that other predicates can be added to reduce the result set earlier in the query execution. This may have the effect of reducing the number of rows handled by the query early on and affect the performance of the query. Obviously it is important not to change the result of the query.

• Divide up the work of one query into multiple queries (BOTH CBO/RBO)

A query may be able to be broken into a number of smaller queries. These could then be run concurrently or serially. The effect of this may be to reduce memory and sort space requirements. The outputs of the smaller queries could be inserted in to a temporate the global result.

• Rewrite the query in plsql (BOTH CBO/RBO)

It may be possible to rewrite the query in pl/sql and thus take advantage of the procedural code that this offers. Some queries can benefit from running procedurally.

• Rewrite the query (BOTH CBO/RBO)

Re-writing a complex query may open up additional access paths or force the query to operate in a different way.

Final Note

Remember that if your query:

• Returns large volumes of data
• Has unselective predicates that cause much data to be searched to return the data you want
• Is maxing out physical aspects of the hardware

then you may not be able to improve its performance other than increasing the processing power of your hardware (i.e. cpu/memory upgrades etc).

RELATED DOCUMENTS

Note:73489.1 Affect of Number of Tables on Join Order Permutations

Note:33838.1 Determining the execution plan for a distributed query

Note:203238.1 Using Parallel Query

@ Note:92202.1 Manipulating Stored Outlines – this is NOT supported

Note:29236.1 QREF: SQL Statement HINTS

Note:50607.1 How to specify an index hint

Note:62364.1 Hints and subqueries

Note:69992.1 Why is my hint ignored?

Note:68084.1 Using hints to optimize an Update with subquery not using index on updated table