Skip Headers

Oracle® Database Performance Tuning Guide
10g Release 1 (10.1)

Part Number B10752-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Automatic SQL Tuning

This chapter discusses Oracle automatic SQL tuning features.

This chapter contains the following sections:

Automatic SQL Tuning Overview

Automatic SQL Tuning is a new capability of the query optimizer that automates the entire SQL tuning process. Using the newly enhanced query optimizer to tune SQL statements, the automatic process replaces manual SQL tuning, which is a complex, repetitive, and time-consuming function. The Automatic SQL Tuning features are exposed to the user with the SQL Tuning Advisor.

Query Optimizer Modes

The enhanced query optimizer has two modes, normal and tuning mode.

Normal mode

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

Tuning mode

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be further improved. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When called under the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. The tuning performed by the Automatic Tuning Optimizer is called Automatic SQL Tuning.

Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke the Automatic Tuning Optimizer every time a query has to be hard-parsed. The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for Automatic SQL Tuning. See Chapter 6, "Automatic Performance Diagnostics".

Types of Tuning Analysis

Automatic SQL Tuning includes four types of tuning analysis:

Statistics Analysis

The query optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output:

This auxiliary information is stored in an object called a SQL Profile.

SQL Profiling

The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view. By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions.

See Also:

Oracle Database Reference for information on the SQLTUNE_CATEGORY initialization parameter

It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

SQL Profiles apply to the following statement types:

A complete set of functions are provided for management of SQL Profiles. See "Managing SQL Profiles with APIs".

Access Path Analysis

Indexes can tremendously enhance performance of a SQL statement by reducing the need for full table scans on large tables. Effective indexing is a common tuning technique. The Automatic Tuning Optimizer also explores whether a new index can significantly enhance the performance of a query. If such an index is identified, it recommends its creation.

Because the Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload, it also recommends running a the SQLAccess Advisor utility on the SQL statement along with a representative SQL workload. The SQLAccess Advisor looks at the impact of creating an index on the entire SQL workload before making any recommendations. See "SQLAccess Advisor".

SQL Structure Analysis

The Automatic Tuning Optimizer identifies common problems with structure of SQL statements than can lead to poor performance. These could be syntactic, semantic, or design problems with the statement. In each of these cases the Automatic Tuning Optimizer makes relevant suggestions to restructure the SQL statements. The alternative suggested is similar, but not equivalent, to the original statement.

For example, the optimizer may suggest to replace UNION operator with UNION ALL or to replace NOT IN with NOT EXISTS. An application developer can then determine if the advice is applicable to their situation or not. For instance, if the schema design is such that there is no possibility of producing duplicates, then the UNION ALL operator is much more efficient than the UNION operator. These changes require a good understanding of the data properties and should be implemented only after careful consideration.

SQL Tuning Advisor

The Automatic SQL Tuning capabilities are exposed through a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. A user can choose to accept the recommendation to complete the tuning of the SQL statements.

The SQL Tuning Advisor input can be a single SQL statement or a set of statements. For tuning multiple statements, a SQL Tuning Set (STS) has to be first created. An STS is a database object that stores SQL statements along with their execution context. An STS can be created manually using command line APIs or automatically using Oracle Enterprise Manager. See "SQL Tuning Sets".

Input Sources

The input for the SQL Tuning Advisor can come from several sources. These input sources include:

Tuning Options

SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive.

Advisor Output

After analyzing the SQL statements, the SQL Tuning Advisor provides advice on optimizing the execution plan, the rationale for the proposed optimization, the estimated performance benefit, and the command to implement the advice. You simply have to choose whether or not to accept the recommendations to optimize the SQL statements.

Accessing the SQL Tuning Advisor with Oracle Enterprise Manager

The primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control. To access the SQL Tuning Advisor through Oracle Enterprise Manager Database Control:

Using SQL Tuning Advisor APIs

While the primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, the advisor can be administered with procedures in the DBMS_SQLTUNE package. To use the APIs the user must have been granted the DBA role and the ADVISOR privilege.

Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:

  1. Create a SQL tuning task
  2. Execute a SQL tuning task

    See Also:

    PL/SQL Packages and Types Reference for detailed information on the DBMS_SQLTUNE package

Creating a SQL Tuning Task

You can create tuning tasks from the text of a single SQL statement, a SQL Tuning Set containing multiple statements, a SQL statement selected by SQL identifier from the cursor cache, or a SQL statement selected by SQL identifier from the Automatic Workload Repository.

For example, to use the SQL Tuning Advisor to optimize a specified SQL statement text, you need to create a tuning task with the SQL statement passed as a CLOB argument. For the following PL/SQL code, the user HR has been granted the ADVISOR privilege and the function is run as user HR on the employees table in the HR schema.

 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
 my_sqltext := 'SELECT /*+ ORDERED */ * '                      ||
               'FROM employees e, locations l, departments d ' ||
               'WHERE e.department_id = d.department_id AND '  ||
                     'l.location_id = d.location_id AND '      ||
                     'e.employee_id < :bnd';

         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'HR',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'my_sql_tuning_task',
         description => 'Task to tune a query on a specified employee');

In this example, 100 is the value for bind variable :bnd passed as function argument of type SQL_BINDS, HR is the user under which the CREATE_TUNING_TASK function analyzes the SQL statement, the scope is set to COMPREHENSIVE which means that the advisor also performs SQL Profiling analysis, and 60 is the maximum time in seconds that the function can run. In addition, values for task name and description are provided.

The CREATE_TUNING_TASK function returns the task name that you have provided or generates a unique task name. You can use the task name to specify this task when using other APIs. To view the task names associated with a specific owner, you can run the following:


Executing a Tuning Task

After you have created a tuning task, you need to execute the task and start the tuning process. For example:

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );

You can check the status of the task by reviewing the information in the DBA_ADVISOR_LOG view or check execution progress of the task in the V$SESSION_LONGOPS view. For example:

SELECT status FROM DBA_ADVISOR_LOG WHERE task_name = 'my_sql_tuning_task';

Displaying the Results of a Tuning Task

After a task has been executed, you display a report of the results with the REPORT_TUNING_TASK function. For example:


The report contains all the findings and recommendations of Automatic SQL Tuning. For each proposed recommendation, the rationale and benefit is provided along with the SQL commands needed to implement the recommendation.

Additional information about tuning tasks and results can be found in DBA views. See "SQL Tuning Information Views".

Additional Operations on a Tuning Task

You can use the following APIs for managing SQL tuning tasks:

Managing SQL Profiles with APIs

While SQL Profiles are usually handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process, SQL Profiles can be managed through the DBMS_SQLTUNE package. To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

See Also:

PL/SQL Packages and Types Reference for detailed information on the DBMS_SQLTUNE package

Accepting a SQL Profile

You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database. For example:

 my_sqlprofile_name VARCHAR2(30);
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name => 'my_sql_tuning_task',
    name      => 'my_sql_profile');

where my_sql_tuning_task is the name of the SQL tuning task.

You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

You can alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure. For example:

     name            => 'my_sql_profile', 
     attribute_name  => 'STATUS', 
     value           => 'DISABLED');

In this example, my_sql_profile is the name of the SQL Profile that you want to alter. The status attribute is changed to disabled which means the SQL Profile is not used during SQL compilation.

Dropping a SQL Profile

You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:

  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');

In this example, my_sql_profile is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE is accepted.

SQL Tuning Sets

A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user. An STS includes:

SQL statements can be filtered using the application module name and action, or any of the execution statistics. In addition, the SQL statements can be ranked based on any combination of execution statistics.

A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. While SQL Tuning Sets are usually handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process, SQL Tuning Sets can be managed with DBMS_SQLTUNE package procedures.

Accessing SQL Tuning Sets with Oracle Enterprise Manager

To manage the SQL Tuning Sets through Oracle Enterprise Manager Database Control:

Managing SQL Tuning Sets

The SQL Tuning Set APIs allow you to mange SQL Tuning Sets to determine performance information about SQL statements running on your system. Typically you would use the STS operations in the following sequence:

To use the APIs, you need the ADMINISTER ANY SQL TUNING SET system privilege.

See Also:

PL/SQL Packages and Types Reference for detailed information on the DBMS_SQLTUNE package

Creating a SQL Tuning Set

The CREATE_SQLSET procedure is used to create an empty STS object in the database. For example, the following procedure creates an STS object that could be used to tune I/O intensive SQL statements during a specific period of time:

    sqlset_name => 'my_sql_tuning_set', 
    description  => 'I/O intensive workload');

where my_sql_tuning_set is the name of the STS in the database and 'I/O intensive workload' is the description assigned to the STS.

Loading a SQL Tuning Set

The LOAD_SQLSET procedure populates the STS with selected SQL statements. The standard sources for populating an STS are the workload repository, another STS, or the cursor cache. For both the workload repository and STS, there are predefined table functions that can be used to select columns from the source to populate a new STS.

In the following example, procedure calls are used to load my_sql_tuning_set from an AWR baseline called peak baseline. The data has been filtered to include only those SQL statements that have been executed at least 10 times and have a disk-reads to buffer-gets ratio greater than 50% during the baseline period. The SQL statements are ordered by the disk-reads to buffer-gets ratio with only the top 30 SQL statements selected. First a ref cursor is opened to select from the specified baseline. Next the statements and their statistics are loaded from the baseline into the STS.

 OPEN baseline_cursor FOR
                  'peak baseline',
                  'executions >= 10 AND disk_reads/buffer_gets >= 0.5',
                   NULL, NULL, NULL,
                   30)) p;

             sqlset_name     => 'my_sql_tuning_set',
             populate_cursor => baseline_cursor);

Displaying the Contents of a SQL Tuning Set

The SELECT_SQLSET table function reads the contents of the STS. After an STS has been created and populated, you can browse through the SQL in the STS using the SELECT_SQLSET procedure.

In the following example, the SQL statements in the STS are displayed for statements with a disk-reads to buffer-gets ratio greater than or equal to 75%.

   '(disk_reads/buffer_gets) >= 0.75'));

Additional details of the SQL Tuning Sets that have been created and loaded can also be displayed with DBA views, such as DBA_SQLSET, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_BINDS.

Modifying a SQL Tuning Set

SQL statements can be updated and deleted from a SQL Tuning Set based on a search condition. In the following example, the DELETE_SQLSET procedure deletes SQL statements from my_sql_tuning_set that have been executed less than fifty times.

      sqlset_name  => 'my_sql_tuning_set',
      basic_filter => 'executions < 50');

Dropping a SQL Tuning Set

The DROP_SQLSET procedure is used to drop an STS that is no longer needed. For example:

  DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'my_sql_tuning_set' );

Additional Operations on SQL Tuning Sets

You can use the following APIs to manage an STS:

SQL Tuning Information Views

This section summarizes the views that you can display to review information that has been gathered for tuning the SQL statements. You need DBA privileges to access these views.