FAQ: SQL Query Performance - Frequently Asked Questions_398838.1

Oracle Database - Standard Edition - Version 7.0.16.0 and later
Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Oracle Database - Personal Edition - Version 7.1.4.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:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Questions and Answers

Where is there an Overview of the Main Query Tuning Articles?

Please refer to the following Document

Document 199083.1 Master Note: Query Tuning Overview

For Information about General Database Performance as opposed to the performance of individual queries, refer to:

Document 402983.1 Master Note: Database Performance Overview
Document 1512292.2 Information Center: Database Performance Overview
Document 1360119.1 FAQ: Database Performance Frequently Asked Questions

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 Information Center: Database Performance Overview
Document 1516494.2 Information Center: SQL Query Performance Overview
Where can I find Performance Troubleshooting Information?
Please refer to the following Documents:
Document 1543445.2 Troubleshooting Assistant: Troubleshooting Performance Issues
Document 1542678.2 Troubleshooting Assistant: SQL Performance Issues

Document 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
Document 60.1 Troubleshooting Assistant: Oracle Database ORA-00060 Errors on Single Instance (Non-RAC) Diagnosing Using Deadlock Graphs in ORA-00060 Trace Files
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.

Document 1366133.1 SQL Tuning Health-Check Script
Document 957993.1 Script to Show Objects That are Missing Statistics
What information is there on Optimizer Statistics?

Document 1369591.1 Optimizer Statistics - Central Point
Document 1226841.1 How To: Gather Statistics for the Cost Based Optimizer
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:

Document 1195363.1 Database Performance and SQL Tuning 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:

Document 1337116.1 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:

Document 67522.1 Diagnosing Why a Query is Not Using an Index
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:

Document 372431.1 TROUBLESHOOTING: Tuning a New Query
Document 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
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:

Document 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Document 163563.1 TROUBLESHOOTING: Advanced Query Tuning

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):

Document 604256.1 Why is a Particular Query Slower on One Machine than Another?
Document 223806.1 Query with Unchanged Execution Plan is Slower than Previously

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:

Document 372431.1 TROUBLESHOOTING: Tuning a New Query
Document 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Document 163563.1 TROUBLESHOOTING: Advanced Query Tuning
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:

Document 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

Document 258167.1 Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues
Document 258945.1 Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving
Document 258946.1 Upgrading from 8.1.X to 9.X - View Issues - Diagnosing and Resolving
Document 259126.1 Upgrading from 8.1.X to 9.X - Btree Bitmap Plan Issues - Diagnosing and Resolving

Document 295819.1 Upgrading from 9i to 10g - Potential Query Tuning Related Issues

Document 223806.1 Query with unchanged execution plan is slower after database upgrade
Document 403739.1 Execution Plans For Parallel SQL Differ After Upgrading From 8i To 9i
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:

Document 372431.1 TROUBLESHOOTING: Tuning a New Query
Document 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
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:
  
  * ESTIMATE_PERCENT: defaults:
  o 9i : 100%
  o 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
  o 11g : DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage)
  * METHOD_OPT: defaults:
  o 9i : "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
  o 10g and 11g : AUTO - DBMS_STATS decides in which columns a histogram may help to produce a better plan.
  
  See:
  
  Document 1226841.1 How To: Gather Statistics for the Cost Based Optimizer
  
  Document 452011.1 Restoring table statistics in 10G onwards
- 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:
  
  Document 470316.1 Using Actual System Statistics (Collected CPU and IO information
  Document 149560.1 Collect and Display System Statistics (CPU and IO) for CBO usage
  Document 153761.1 Scaling the System to Improve CBO optimizer
- 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:
  
  Document 336267.1 Parameter : OPTIMIZER_DYNAMIC_SAMPLING
- 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 SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement:
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 Plans can change despite no stats being regathered

Why does a particular query's runtime vary with bind variables rather than literals?
For details on this issue see:

Document 401068.1 Possible Poor Runtime Performance for Bind Variables when Compared with Literal Values

Document 387394.1 Query using Bind Variables is suddenly slow
Document 377847.1 Unsafe Peeked Bind Variables and Histograms
What should I do if a Query Returns Unexpected Results?

If a query returns output that is unexpected refer to the following:

Document 232243.1 ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates
Document 345048.1 'Group By' Does Not Guarantee a Sort Without Order By Clause In 10g and Above

If those articles do not resolve the issue, refer to the following for assistance:

Document 150895.1 Wrong Results Issues - Recommended Actions
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:

Document 66484.1 Which Optimizer is Being Used ?
Where is the information regarding how to use hints?

For assistance See:

Document 29236.1 QREF: SQL Statement HINTS
Document 221970.1 Forcing a Known Plan Using Hints
Can Optimizer Parameters be hinted?

From 10g Release 2, optimizer parameters can be set with a hint. See:

Document 377333.1 OPT_PARAM Hint in 10g R2
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 Partition Pruning and Joins
Document 166118.1 Partition Pruning/Elimination
Document 209070.1 Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables
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:

Document 236935.1 Global statistics - An Explanation

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

Document 67536.1 Stored Outline Quick Reference
Document 445126.1 HOW TO: Create a Stored Outline Based Upon an Existing Cursor
Document 728647.1 How to Transfer Stored Outlines from One Database to Another (9i and above)

Document 743312.1 How To Match a SQL Statement to a Stored Outline
Document 730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline

Document 726802.1 Editing Stored Outlines in Oracle10g and Oracle11g
Diagnostics
- Action Plans for Common Scenarios
  Document 742112.1 Query Performance Degradation - Recommended Actions
  Document 745216.1 Query Performance Degradation - Upgrade Related - Recommended Actions
- How to Gather Information for Query Tuning Problems
  Document 68735.1 Diagnostics for Query Tuning Problems
- How to Transfer Optimizer Statistics to Support
  Document 242489.1 Transferring 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