FAQ: SQL Query Performance - Frequently Asked Questions (Doc ID 398838.1)
In this Document
Oracle Database - Standard Edition - Version 18.104.22.168 and later
Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Database - Personal Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statements.
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:
Where can I find Performance Troubleshooting Information?
Please refer to the following Documents:
Troubleshooting Assistant: Troubleshooting Performance Issues
Troubleshooting Assistant: SQL Performance Issues
Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
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.
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:
TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance
Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues
Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving
Upgrading from 8.1.X to 9.X - View Issues - Diagnosing and Resolving
Upgrading from 8.1.X to 9.X - Btree Bitmap Plan Issues - Diagnosing and Resolving
Upgrading from 9i to 10g - Potential Query Tuning Related Issues
Query with unchanged execution plan is slower after database upgrade
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.
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:
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.
If explain plans change despite no differences being evident, then it is possible that you are hitting an issue discussed in the following article:
SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement:
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:
ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates
'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:
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:
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.
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 188.8.131.52 Statistics on Partitioned Objects
How to Store and Apply Query Outlines
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
Recommended Method for Obtaining 10046 trace for Investigating SQL Query Performance
How to Transfer Optimizer Statistics to Support
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-1135904/，如需转载，请注明出处，否则将追究法律责任。