Oracle Support Master Note for Issues Related to the Performance of SQL Queries.

The Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes for issues related to the performance of SQL queries.
It can be found in My Oracle Support Note:199083.1

Master Note: Query Tuning Overview (Doc ID 199083.1)

SQL Query Handling in Oracle

Every statement submitted to the Oracle engine passes through a number of phases that determine, for example, the statements validity or if it has run before etc. In terms of SQL Tuning the most important area is Query optimization which is nominally part of the PARSE phase. 

Frequently Asked Questions

Note:398838.1 * FAQ: Query Tuning Frequently Asked Questions Frequently Asked Query Tuning questions. Contains links to articles on numerous topics.

Troubleshooting Query Tuning Issues

The following sections can assist with troubleshooting any SQL Tuning issues that may be encountered providing guidance of actions to take and suggested workarounds.

Support Action plans

Support have generated a number of articles containing recommended actions to assist with the troubleshooting of various problems:

Note:742112.1 * Query Performance Degradation - Recommended Actions Recommended actions for troubleshooting issues where the performance of an individual query has worsened
Note:745216.1 * Query Performance Degradation - Upgrade Related - Recommended Actions Recommended actions for troubleshooting issues where the performance of an individual query has worsened following a software upgrade
Note:150895.1 * Wrong Results Issues - Recommended Actions
Recommended actions for troubleshooting issues where the rows returned from a query are unexpected
Guidance on Specific Issues:

Note:67522.1 * Diagnosing Why a Query is Not Using an Index Explains why queries may not be able to fully utilise indexes
Note:69992.1 Why is my hint ignored? Explains why supplied hints may not appear to be used
Note:604256.1 * Why is a Particular Query Slower on One Machine than Another? Suggests reasons why performance on one machine may not be as good as on another
Note:223806.1 * Query with Unchanged Execution Plan is Slower than Previously Suggests reasons why performance of a query may differ even though the access methods it uses are the same
Note:160089.1 Why are my queries slow after upgrading my database? Suggests reasons why performance may degrade after making system changes

Recommended diagnostics

Note:68735.1 * Diagnostics for Query Tuning Problems Suggested Information to collect for resolving Tuning Issues
Note:235530.1 * Recommended Method for Obtaining a Formatted Explain Plan How to collect access path information for a query
Note:376442.1 * Recommended Method for Obtaining 10046 trace for Tuning How to collect 10046 trace
Note:199081.1 Overview Reference for SQL_TRACE, TKProf and Explain Plan Details of Trace, TKProf and Explain
Note:46234.1 Interpreting Explain plan This article details, with explain plan examples, all possible access methods, join methods, view usage, sorting, filtering, parallel execution and handling of bind variables and remote queries. It provides a short (but expert level) introduction to Oracle's SQL processing and gives deep insight in to each step that can be seen in explain plan output. 

Stored Outlines

Stored outlines are a method of fixing the access path for a query without modifying the query itself. Details can be found in the following:


Note:67536.1 Stored Outline Quick Reference Reference article for Stored Outlines
Note:445126.1 HOW TO: Create a Stored Outline Based Upon an Existing Cursor Outline how to create an outline for a query that has been executed
Note:728647.1 How to Transfer Stored Outlines from One Database to another (9i and above) How to move an outline from a 'good' instance to a target instance
Note:730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline How to edit an outline to use a target outline

Query Tuning Advice

For each SQL statement, there is a multitude of different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient way to retrieve this data based upon the evaluation of a number of different criteria.

Oracle currently (as of Oracle11g Release 2) utilises a Cost based approach using a Cost Based Optimizer (CBO). The CBO bases optimization choices on pre-gathered table and index statistics as well as the capability to dynamically sample statistical data to accurately reflect real data volumes.

For more information on the Oracle optimizer see:

Note:10626.1  Cost Based Optimizer (CBO) Overview This article is a comprehensive description about the CBO. Most suitable for beginners in the tuning area. It was written for version 7 so does not consider the new methods. 
Note:754931.1 Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
This reference article covers many areas of query optimization and predominately focuses on solving common issues.

For further information on the optimizer see:

Oracle Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E10821-05
Chapter 11 The Query Optimizer
Section 11.4 Understanding the Query Optimizer

Statistics

Optimizer decisions are based on statistics collected against the objects in the database. For advice and recommendations on the collection of statistics, see the following:


Note:1226841.1  How To: Gather Statistics for the Cost Based Optimizer Recommendations for object statistics gathering
Note:470316.1 Using Actual System Statistics (Collected CPU and IO information Recommendations for gathering system statistics
Note:44961.1 Gathering statistics frequency and strategy guidelines General advice and overall strategy for gathering statistics

Query Tuning

The vast majority of queries submitted to the Oracle optimizer(s) perform adequately and consistently return correct results. Sometimes, however, the optimizer is presented with information that, when it is compared to the real information, is incorrect or insufficient for determination of the optimal access method for the query. In these rare cases, manual intervention may be necessary to obtain the desired performance. Support has created a large number of articles to help guide analysts tune queries. A number of these are listed below together with brief descriptions of their contents.


Additional detail can be found in the following:
 
Note:179668.1 * TROUBLESHOOTING: Tuning Slow Running Queries Suggests methods that can be used to modify the performance of slow queries

Note:33089.1 * TROUBLESHOOTING GUIDE: SQL Tuning Presents a number of common Solutions to SQL Tuning Issues
Note:372431.1 * TROUBLESHOOTING: Tuning a New Query Suggests methods that can be used to tune a new query
Note:122812.1 * TROUBLESHOOTING: Tuning Queries That Cannot be Modified Suggests methods that can be used to modify the performance of queries that cannot be changed
Note:163563.1 * TROUBLESHOOTING: Advanced Query Tuning Provides step by step guidelines for dealing with Query Tuning problems

Hints

Hints are a mechanism that can be used to force the optimizer to choose a specific access path. For details see:

Note:29236.1 QREF: SQL Statement HINTS Reference article explaining hint syntax. Hints provide a mechanism to direct the optimizer to choose a certain query execution plan. 
Note:50607.1  How to specify an INDEX Hint Explains how to specify working index hints in queries with worked SQL and explain plan examples.

For more background information see:

Oracle Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E10821-05
Chapter 19 Using Optimizer Hints

Documentation

Note:1195363.1 Database Performance and SQL Tuning Documentation on OTN Links to the main Tuning and Performance documentation

Tuning Support

See the following article for details of what Query Tuning Support offers:

Note:166650.1 Working Effectively With Global Customer Support Refer to the "Performance Tuning" section for details

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today