In this Document

Applies to: 

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

 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 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 Gather Access Path Information for Queries
    Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
    
    Document 235530.1 Recommended Method for Obtaining a Formatted Explain Plan
    Document 46234.1 Interpreting Explain plan
  • How to Gather Trace for Query Tuning Issues
    Document 376442.1  Recommended Method for Obtaining 10046 trace for Investigating SQL Query Performance
  • How to Transfer Optimizer Statistics to Support
    Document 242489.1 Transferring Optimizer Statistics to Support
    
    
    
    

References

Related …

Products …

• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Generic SQL Performance, SQL Execution, Query Optimizer
• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Personal Edition > RDBMS > Generic SQL Performance, SQL Execution, Query Optimizer
• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Generic SQL Performance, SQL Execution, Query Optimizer

Keywords …

CBO;FREQUENTLY ASKED QUESTIONS;SQL TUNING;SQLTXPLAIN

Errors …

ORA-1722;ORA-1839;ORA-1847;ORA-1858