| Purpose |
|---|
| Best Practices |
|---|
| Pro-Active Problem Avoidance and Diagnostic Collection |
|---|
| Questions and Answers |
|---|
| Where is there an Overview of the Main Query Tuning Articles? |
|---|
| Where is there Information About Performance Related Features |
|---|
| Where can I find Performance Information Centres? |
|---|
| Where can I find Performance Troubleshooting Information? |
|---|
| Common Issues/Problem Avoidance |
|---|
| What information is there on Optimizer Statistics? |
|---|
| Community: Database Tuning |
|---|
| Where can Performance Documentation be found? |
|---|
| Where can White Papers and Blog Entries Related to the Oracle Optimizer be found? |
|---|
| Why is an index not used? |
|---|
| Why is a particular query slow? |
|---|
| Why is a particular query slower than it used to be? |
|---|
| Why is a particular query slower on one machine than another? |
|---|
| Why is a particular query slower since upgrading? |
|---|
| Why does a particular query's runtime vary? |
|---|
| Why does the execution plan for a particular query change? |
|---|
| Why does a particular query's runtime vary with bind variables rather than literals? |
|---|
| What should I do if a Query Returns Unexpected Results? |
|---|
| Which optimizer is a query using? |
|---|
| Where is the information regarding how to use hints? |
|---|
| Can Optimizer Parameters be hinted? |
|---|
| Why is partition elimination not occurring? |
|---|
| What is the importance of Global Statistics? |
|---|
| How to Store and Apply Query Outlines |
|---|
| Diagnostics |
|---|
| Action Plans for Common Scenarios |
|---|
| How to Gather Information for Query Tuning Problems |
|---|
| How to Gather Access Path Information for Queries |
|---|
| How to Gather Trace for Query Tuning Issues |
|---|
| How to Transfer Optimizer Statistics to Support |
|---|
| References |
|---|
Applies to:
Oracle Database – Enterprise Edition – Version 6.0.0.0 and later
Oracle Database – Personal Edition – Version 7.1.4.0 and later
Oracle Database – Standard Edition – Version 7.0.16.0 and later
Information in this document applies to any platform.
Purpose
This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statements.
Best Practices
Pro-Active Problem Avoidance and Diagnostic Collection
Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. For information on proactive preparations and diagnostics, see:
Questions and Answers
–
Where is there an Overview of the Main Query Tuning Articles?
Please refer to the following Document
Document 199083.1 For Information about General Database Performance as opposed to the performance of individual queries, refer to:
–
Where is there Information About Performance Related Features
For recommended information regarding Performance Related Features see:
Document 1361401.1 Where to Find Information About Performance Related Features
–
Where can I find Performance Information Centres?
Please refer to the following Documents: Document 1512292.2
–
Where can I find Performance Troubleshooting Information?
Please refer to the following Documents: Document 1543445.2
–
Common Issues/Problem Avoidance
One of the most common and avoidable reasons for poor SQL performance is inadequate / missing statistics or that the general setup of the environment your query runs in means it cannot perform efficiently. The following articles can help you find and address missing statistics and other issues in your queries. Also, by performing a 'health check' on the query you may be able to find issues before they occur.
–
What information is there on Optimizer Statistics?
–
Community: Database Tuning
A community has been set up for My Oracle Support (MOS) users moderated by Oracle. The goal of this community is to exchange database related Tuning knowledge and concepts. The community can be found here: Database Tuning –
Where can Performance Documentation be found?
Please refer to the following note for links to Performance related documentation on OTN:
–
Where can White Papers and Blog Entries Related to the Oracle Optimizer be found?
Please refer to the following note for links to White Papers and Blog Entries for Oracle Optimizer:
–
Why is an index not used?
The following note is a comprehensive discussion of a number of reasons why indexes may not be selected for certain queries:
–
Why is a particular query slow?
If a new query has been created that is slow on 10g and above, then Oracle Support advises users to utilise the 10g SQL Access Advisor to suggest some suitable modifications.
See: Oracle10g Database Performance Tuning Guide
_ Oracle10g Database Performance Tuning Guide
10g Release 2 (10.2) Part Number B14211-01
Chapter 17 SQL Access Advisor_
Note that the 10g SQL Access Advisor is part of The Oracle Tuning Pack accessed through Enterprise Manager licensed links and it's functionality can be used after purchasing a license for the Tuning Pack: See:
_ Oracle10g Enterprise Manager Licensing Information
10g Release 4 (10.2.0.4.0)
Part Number B40010-08
Chapter 2 Enterprise Database Management_
Alternatively, various tuning articles are available to assist with manual tuning on all releases:
–
Why is a particular query slower than it used to be?
There are a number of factors that can change a query's response time. If a query's performance has changed then it follows that something in the environment has changed to initiate this. There could me more data involved, incomplete or inaccurate statistics, i/o performance changes, access path changes among others. The following articles address various query tuning issues:
Occasionally, changes in Oracle software through upgrading or patching can cause execution sub-optimal execution plans.
See the section: " Why is this query slower since upgrading?" below for help with this kind of problem. –
Why is a particular query slower on one machine than another?
In this case, again, there are a number of factors that can affect a query's response time in addition to those already mentioned. Assuming that the query access paths are the same in the different environments, review the following articles (the techniques are the same as for an upgrade example):
Differences in parameters, specification, memory, disk i/o speed, cpu speed can all have an effect on the performance of queries.
If access paths for queries are different then refer to:
–
Why is a particular query slower since upgrading?
As Oracle continues to develop the Database Software, changes are introduced in the optimizer that are designed to give better performance. Occasionally, changes that provide improved performance for many, can have an adverse effect for a small number of others. New versions can also require different approaches in system management to maintain, or achieve, better performance.
Bug fixes or security patches may also mean a previously fast execution plan is no longer possible. For example, queries may have being using a bugged access method that could cause security issues or other problems in specific circumstances. In resolving the bug, and removing the issue, the access path that was previously used may no longer be available.
The following notes give guidance on what to do when upgrading to ensure good performance is maintained and what to do if a performance regression is encountered:
–
Why does a particular query's runtime vary?
Given the same inputs, a statement will always return the same outputs. For performance to vary, one of the inputs must have changed. There are a very large number of variables that can affect the performance of a query.
See:
–
Why does the execution plan for a particular query change?
The explain plan for a query can change if one of the factors used by the CBO has changed. These can include:
- Initialisation parameters – certain initialisation parameter changes can result in access path changes due to perceived statistical differences. For example, changes to the
may make multi-block I/O requests look more/less attractive to the optimizer. at a certain threshold, this may switch access paths towards different access methods that make use of these changes. If the system is unable to service the I/O rate that the parameter indicates, this can cause poor plan selections.
- Object statistics – The Cost Based Optimizer (CBO) uses statistics to determine the execution plan for a particular query. Differences in statistics can cause the CBO to generate different execution plans, which in turn can lead to differences in performance of the same query. Potentially, with reduced sample sizes, sampling could produce different statistics due to chance groupings of data that may be the result of differing loading methods etc. On 10g and above it is recommended to use an estimate sample size of 100% (if it is possible for this to fit within the maintenance window), even if that means that statistics are gathered on a reduced frequency. If 100% is not feasible, try using at least an estimate of 30%. Generally, the accuracy of the statistics overall outweighs the day to day changes in most applications. Note that the defaults for different versions of statistics gathering are not necessarily the same, for example:
See:
–
- Differing System statistics – System statistics gathered using the DBMS_STATS.GATHER_SYSTEM_STATS procedure and stored in the SYS.AUX_STATS$ table will be used by default when the "_OPTIMIZER_COST_MODEL" (hidden) parameter (which defaults to CHOOSE) is set to CHOOSE or CPU. These statistics can cause access paths for queries to change dependent upon the recorded capability of a system. Systems with different capabilities may use different access paths for the same query. See:
- Dynamic Sampling – If dynamic sampling is enabled then the optimizer may sample information at runtime to determine a better plan for a query. If data distribution or volume is different then this sampling could return different statistics and this could clearly make a difference to the chosen execution plan. See:
- Predicate value changes
- Bind Variable Value Differences: See: Why does a particular query's runtime vary with bind variables rather than literals?
You can use the following script to compare differences that might be causing the execution plan to change. Run the script for each case and compare the outputs. Document 215187.1 If explain plans change despite no differences being evident, then it is possible that you are hitting an issue discussed in the following article: Document 338113.1
–
Why does a particular query's runtime vary with bind variables rather than literals?
For details on this issue see:
–
What should I do if a Query Returns Unexpected Results?
If a query returns output that is unexpected refer to the following:
If those articles do not resolve the issue, refer to the following for assistance:
–
Which optimizer is a query using?
Often it is important to be able to determine which optimizer is being used by a query. On 10g and above this is somewhat redundant as there is only one supported optimizer but on earlier releases, the following article helps with that:
–
Where is the information regarding how to use hints?
For assistance See:
–
Can Optimizer Parameters be hinted?
From 10g Release 2, optimizer parameters can be set with a hint. See:
–
Why is partition elimination not occurring?
Partition elimination or Pruning occurs when the predicates and join order for a query provide sufficient information to allow a set of partitions to be eliminated from the result set without accessing the data. Details of different pruning methods can be found in the following articles: Document 179518.1
Document 209070.1 Please also see the Why are Global Statistics Required? section. –
What is the importance of Global Statistics?
It is important to collect Global Statistics as well as partition level statistics when gathering statistics against partitioned tables. Oracle Corporation recommends setting the GRANULARITY parameter to AUTO to gather both types of partition statistics.
See:
_ Oracle(R) Database Performance Tuning Guide
10g Release 2 (10.2)
Part Number B14211-01
Chapter 14 Managing Optimizer Statistics
Section 14.3.1.3 Statistics on Partitioned Objects _
–
How to Store and Apply Query Outlines
–
Diagnostics
–
Action Plans for Common Scenarios
–
How to Gather Information for Query Tuning Problems
–
How to Gather Access Path Information for Queries
–
How to Gather Trace for Query Tuning Issues
–
How to Transfer Optimizer Statistics to Support
References
NOTE:199083.1 – * Master Note: SQL Query Performance Overview
NOTE:402983.1 – * Master Note: Database Performance Overview
NOTE:225598.1 – How to Obtain Tracing of Optimizer Computations (EVENT 10053)
NOTE:1482811.1 – Best Practices: Proactively Avoiding Database and Query Performance Issues
NOTE:1477599.1 – Best Practices: Proactive Data Collection for Performance Issues