Query Tuning Overview

Purpose

This document gives a overview of the SQL tuning issues.

Scope & Application

For all DBAs

SQL Query Handing 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.

@ See Unpublished Note 199273.1 Overview of SQL Statement Processing Phases for more information on the stages a query passes through.

Query Optimization

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 Oracle9i Release 2) provides 2 different optimization approaches. These are Cost Based Optimization (CBO) and Rule Based Optimization (RBO). The CBO bases optimization choices on pre-gathered table and index statistics while the RBO makes it's decisions based on a set of 'best practice' rules and does not rely on any statistical information. CBO's reliance on statistics makes it vastly more flexible than the RBO since as long as up to date statistics are maintained it will accurately reflect real data volumes. The RBO is Oracle's legacy optimizer and is to be desupported in Oracle10i.

Oracle Chooses between optimizers on a number of criteria: See Note 66484.1 Which Optimizer is Used? for details

For more information on the Oracle optimizer see:

Note 66484.1 Which Optimizer is Used? This article is a checklist for determining which optimizer a particular statement will use
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 35934.1 Cost Based Optimizer-Common Misconceptions and Issues This reference article covers many areas of query optimization and predominately focuses on solving common issues.
Note 43065.1 Rule Based Optimizer -- Changing Query Access Path. This article explain how to modify the access paths of queries that use the RBO
Note 189702.1 Rule Based Optimizer is to be Desupported in Oracle10i Announces the pending Desupport of the RBO

Oracle9i Database Performance Guide and Reference Chapter 1: Understanding the Optimizer

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.

Helpful Query Tuning Articles:

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.
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.

Oracle9i Database Performance Guide and Reference Chapter 5: Optimizer Hints

Troubleshooting Query Tuning Issues

The following articles can assist with troubleshooting any SQL Tuning issues that may be encountered:

Note 163563.1 Resolving Query Tuning Issues Provides step by step guidelines for dealing with Query Tuning problems
Note 179668.1 Suggested Query Tuning Workflow Focuses in on best practices for tuning queries
Note 67522.1 Why is my index not used? 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 122812.1 Tuning Suggestions When Query Cannot be Modified Suggests methods that can be used to modify the performance of queries that cannot be changed
Note 160089.1 Why are my queries slow after upgrading my database? Suggests reasons why performance may degrade after making system changes
Note 150895.1 Handling Wrong Results Issues Describes steps to work through when it is suspect that a query has returned an incorrect result.
Note 33089.1 TROUBLESHOOTING GUIDE: SQL Tuning Presents a number of common Solutions to SQL Tuning Issues
Note 68735.1 Information required to diagnose a Query Performance Problem Outlines all the information that you should gather in order to successfully diagnose query performance issues