20 Analyzing SQL with SQL Tuning Advisor

This chapter explains the concepts and tasks relating to SQL Tuning Advisor.

This chapter contains the following topics:

About SQL Tuning Advisor

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. You can submit one or more SQL statements as input to the advisor and receive advice or recommendations for how to tune the statements, along with a rationale and expected benefit.

This section contains the following topics:

Purpose of SQL Tuning Advisor

SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.

Tuning recommendations include:

Collection of object statistics
Creation of indexes
Rewriting SQL statements
Creation of SQL profiles
Creation of SQL plan baselines

The recommendations generated by SQL Tuning Advisor help you achieve the following specific goals:

Avoid labor-intensive manual tuning

Identifying and tuning high-load SQL statements is challenging even for an expert. SQL Tuning Advisor uses the optimizer to tune SQL for you.
Generate recommendations and implement SQL profiles automatically

You can configure an Automatic SQL Tuning task to run nightly in maintenance windows. When invoked in this way, the advisor can generate recommendations and also implement SQL profiles automatically.
Analyze database-generated statistics to achieve optimal plans

The database contains a vast amount of statistics about its own operations. SQL Tuning Advisor can perform deep mining and analysis of internal information to improve execution plans.
Enable developers to tune SQL on a test system instead of the production system

When suboptimally performing SQL statements occur on a production database, developers may not want to investigate and tune directly on the production database. The DBA can transport the problematic SQL statements to a test database where the developers can safely analyze and tune them.

When tuning multiple statements, SQL Tuning Advisor does not recognize interdependencies between the statements. Instead, SQL Tuning Advisor offers a convenient way to get tuning recommendations for many statements.

Note:

Data visibility and privilege requirements may differ when using SQL Tuning Advisor with pluggable databases. The advisor can tune a query in the current pluggable database (PDB), and in other PDBs in which this query has been executed. In this way, a container database (CDB) administrator can tune the same query in many PDBs at the same time, whereas a PDB administrator can only tune a single PDB.

See Also:

"Managing SQL Plan Baselines " to learn about SQL plan management
Oracle Database Administrator’s Guide for a table that summarizes how manageability features work in a CDB

SQL Tuning Advisor Architecture

Automatic Tuning Optimizer is the central tool used by SQL Tuning Advisor. The advisor can receive SQL statements as input from the sources shown in Figure 20-1, analyze these statements using the optimizer, and then make recommendations.

Invoking Automatic Tuning Optimizer for every hard parse consumes significant time and resources (see "SQL Parsing"). Tuning mode is meant for complex and high-load SQL statements that significantly affect database performance.

Figure 20-1 shows the basic architecture of SQL Tuning Advisor.

Figure 20-1 SQL Tuning Advisor Architecture

Description of "Figure 20-1 SQL Tuning Advisor Architecture"

Invocation of SQL Tuning Advisor

SQL Tuning Advisor is invoked in either of the following ways:

Automatically

You can configure SQL Tuning Advisor to run during nightly system maintenance windows. When run by AUTOTASK, the advisor is known as Automatic SQL Tuning Advisor and performs automatic SQL tuning. See "Managing the Automatic SQL Tuning Task".
On-Demand

In on-demand SQL tuning, you manually invoke SQL Tuning Advisor to diagnose and fix SQL-related performance problems after they have been discovered. Oracle Enterprise Manager Cloud Control (Cloud Control) is the preferred interface for tuning SQL on demand, but you can also use the DBMS_SQLTUNE PL/SQL package. See "Running SQL Tuning Advisor On Demand".

SQL Tuning Advisor uses Automatic Tuning Optimizer to perform its analysis. This optimization is "automatic" because the optimizer analyzes the SQL instead of the user. Do not confuse Automatic Tuning Optimizer with automatic SQL tuning, which in this document refers only to the work performed by the Automatic SQL Tuning task.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_SQLTUNE

Input to SQL Tuning Advisor

Input for SQL Tuning Advisor can come from several sources, including the following:

Automatic Database Diagnostic Monitor (ADDM)

The primary input source is ADDM (pronounced Adam). By default, ADDM runs proactively once every hour and analyzes key statistics gathered by Automatic Workload Repository (AWR) over the last hour to identify any performance problems including high-load SQL statements. If a high-load SQL is identified, then ADDM recommends running SQL Tuning Advisor on the SQL. See Oracle Database Performance Tuning Guide to learn about ADDM.
AWR

AWR takes regular snapshots of system activity, including high-load SQL statements ranked by relevant statistics, such as CPU consumption and wait time.

You can view the AWR and manually identify high-load SQL statements. You can run SQL Tuning Advisor on these statements, although Oracle Database automatically performs this work as part of automatic SQL tuning. By default, AWR retains data for the last eight days. You can locate and tune any high-load SQL that ran within the retention period of AWR using this technique. See Oracle Database Performance Tuning Guide to learn about AWR.
Shared SQL area

The database uses the shared SQL area to tune recent SQL statements that have yet to be captured in AWR. The shared SQL area and AWR provide the capability to identify and tune high-load SQL statements from the current time going as far back as the AWR retention allows, which by default is at least 8 days. See Oracle Database Concepts to learn about the shared SQL area.
SQL tuning set

A SQL tuning set (STS) is a database object that stores SQL statements along with their execution context. An STS can include SQL statements that are yet to be deployed, with the goal of measuring their individual performance, or identifying the ones whose performance falls short of expectation. When a set of SQL statements serve as input, the database must first construct and use an STS. See "About SQL Tuning Sets".

Output of SQL Tuning Advisor

After analyzing the SQL statements, SQL Tuning Advisor produces the following types of output:

Advice on optimizing the execution plan
Rationale for the proposed optimization
Estimated performance benefit
SQL statement to implement the advice

The benefit percentage shown for each recommendation is calculated using the following formula:

abnf% = (time_old - time_new)/(time_old)

For example, assume that before tuning the execution time was 100 seconds, and after implementing the recommendation the new execution time is expected to be 33 seconds. This benefit calculation for this performance improvement is as follows:

67% = (100 - 33)/(100)

You choose whether to accept the recommendations to optimize the SQL statements. Depending on how it is configured, Automatic SQL Tuning Advisor can implement the SQL profile recommendations to tune the statement without user intervention. When invoked on demand, SQL Tuning Advisor can recommend that the user implement a SQL profile, but can never implement it automatically.

Automatic Tuning Optimizer Concepts

In tuning mode, the optimizer has more time to consider options and gather statistics. For example, Automatic Tuning Optimizer can use dynamic statistics and partial statement execution. The following graphic depicts the different types of analysis that Automatic Tuning Optimizer performs.

Description of the illustration tgsql_vm_057.eps

This section contains the following topics:

See Also:

Query Optimizer Concepts

Statistical Analysis

The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer can generate suboptimal plans. Automatic Tuning Optimizer checks each query object for missing or stale statistics, and recommends gathering fresh statistics if needed. Figure 20-2 depicts the process of statistical analysis.

Figure 20-2 Statistical Analysis by Automatic Tuning Optimizer

Description of "Figure 20-2 Statistical Analysis by Automatic Tuning Optimizer"

SQL Profiling

SQL profiling is the verification by the Automatic Tuning Optimizer of its own estimates. By reviewing execution history and testing the SQL, the optimizer can ensure that it has the most accurate information available to generate execution plans. SQL profiling is related to but distinct from the steps of generating SQL Tuning Advisor recommendations and implementing these recommendations.

The following graphic shows SQL Tuning Advisor recommending a SQL profile and automatically implementing it. After the profile is created, the optimizer can use the profile as additional input when generating execution plans.

Description of the illustration tgsql_vm_058.eps

See Also:

"About SQL Profiles"

How SQL Profiling Works

The database can profile the following types of statement:

DML statements (SELECT, INSERT with a SELECT clause, UPDATE, DELETE, and the update or insert operations of MERGE)
CREATE TABLE statements (only with the AS SELECT clause)

After SQL Tuning Advisor performs its analysis, it either recommends or does not recommend implementing a SQL profile.

The following graphic shows the SQL profiling process.

Description of the illustration tgsql_vm_062.eps

During SQL profiling, the optimizer verifies cost, selectivity, and cardinality for a statement. The optimizer uses either of the following methods:

Samples the data and applies appropriate predicates to the sample

The optimizer compares the new estimate to the regular estimate and, if the difference is great enough, applies a correction factor.
Executes a fragment of the SQL statement

This method is more efficient than the sampling method when the predicates provide efficient access paths.

The optimizer uses the past statement execution history to determine correct settings. For example, if the history indicates that a SQL statement is usually executed only partially, then the optimizer uses FIRST_ROWS instead of ALL_ROWS optimization (see "Choosing an Optimizer Goal").

SQL Profile Implementation

If the optimizer generates auxiliary information during statistical analysis or SQL profiling, then the optimizer recommends implementing a SQL profile. As shown in Figure 20-3, the following options are possible:

When SQL Tuning Advisor is run on demand, the user must choose whether to implement the SQL profile.
When the Automatic SQL Tuning task is configured to implement SQL profiles automatically, advisor behavior depends on the setting of the ACCEPT_SQL_PROFILE tuning task parameter (see "Configuring the Automatic SQL Tuning Task Using the Command Line"):
- If set to true, then the advisor implements SQL profiles automatically.
- If set to false, then user intervention is required.
- If set to AUTO (default), then the setting is true when at least one SQL statement exists with a SQL profile, and false when this condition is not satisfied.
Note:

The Automatic SQL Tuning task cannot automatically create SQL plan baselines or add plans to them (see "Plan Evolution").

Figure 20-3 Implementing SQL Profiles

Description of "Figure 20-3 Implementing SQL Profiles"

At any time during or after automatic SQL tuning, you can view a report. This report describes in detail the SQL statements that were analyzed, the recommendations generated, and any SQL profiles that were automatically implemented.

See Also:

"About SQL Profiles"

Access Path Analysis

An access path is the means by which the database retrieves data. For example, a query using an index and a query using a full table scan use different access paths. In some cases, indexes can greatly enhance the performance of a SQL statement by eliminating full table scans.

The following graphic illustrates access path analysis.

Description of the illustration tgsql_vm_063.eps

Automatic Tuning Optimizer explores whether a new index can significantly enhance query performance and recommends either of the following:

Creating an index

Index recommendations are specific to the SQL statement processed by SQL Tuning Advisor. Sometimes a new index provides a quick solution to the performance problem associated with a single SQL statement.
Running SQL Access Advisor

Because the Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload, it also recommends running SQL Access Advisor on the SQL statement along with a representative SQL workload. SQL Access Advisor examines the effect of creating an index on the SQL workload before making recommendations.

SQL Structural Analysis

During structural analysis, Automatic Tuning Optimizer tries to identify syntactic, semantic, or design problems that can lead to suboptimal performance. The goal is to identify poorly written SQL statements and to advise you how to restructure them.

Figure 20-4 illustrates structural analysis.

Figure 20-4 Structural Analysis

Description of "Figure 20-4 Structural Analysis"

Some syntax variations negatively affect performance. In structural analysis, the automatic tuning optimizer evaluates statements against a set of rules, identifies inefficient coding techniques, and recommends an alternative statement if possible.

As shown in Figure 20-4, Automatic Tuning Optimizer identifies the following categories of structural problems:

Inefficient use of SQL constructors

A suboptimally performing statement may be using NOT IN instead of NOT EXISTS, or UNION instead of UNION ALL. The UNION operator, as opposed to the UNION ALL operator, uses a unique sort to ensure that no duplicate rows are in the result set. If you know that two queries do not return duplicates, then use UNION ALL.
Data type mismatches

If the indexed column and the compared value have a data type mismatch, then the database does not use the index because of the implicit data type conversion. Also, the database must expend additional resources converting data types, and some SQL statements may fail because data values do not convert correctly. Common mistakes include columns that contain numeric data but are never used for arithmetic operations: telephone numbers, credit card numbers, and check numbers. To avoid poor cardinality estimates, suboptimal plans, and ORA-01722 errors, developers must ensure that bind variables are type VARCHAR2 and not numbers.
Design mistakes

A classic example of a design mistake is a missing join condition. If n is the number of tables in a query block, then n-1 join conditions must exist to avoid a Cartesian product.

In each case, Automatic Tuning Optimizer makes relevant suggestions to restructure the statements. The suggested alternative statement is similar, but not equivalent, to the original statement. For example, the suggested statement may use UNION ALL instead of UNION. You can then determine if the advice is sound.

Alternative Plan Analysis

While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding. The follow graphic shows SQL Tuning Advisor finding two alternative plans and generating an alternative plan finding.

Description of the illustration tgsql_vm_064.eps

SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you can create a SQL plan baseline to instruct the optimizer to choose these plans in the future.

Example 20-1 Alternative Plan Finding

The following example shows an alternative plan finding for a SELECT statement:

2- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.
 
  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.
 
  id plan hash  last seen            elapsed (s)  origin          note
  -- ---------- -------------------- ------------ --------------- ----------------
   1 1378942017  2009-02-05/23:12:08        0.000 Cursor Cache    original plan
   2 2842999589  2009-02-05/23:12:08        0.002 STS
 
  Information
  -----------
  - The Original Plan appears to have the best performance, based on the
    elapsed time per execution.  However, if you know that one alternative
    plan is better than the Original Plan, you can create a SQL plan baseline
    for it. This will instruct the Oracle optimizer to pick it over any other
    choices in the future.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_XXXXX',
            object_id => 2, task_owner => 'SYS', plan_hash => xxxxxxxx);

The preceding example shows that SQL Tuning Advisor found two plans, one in the shared SQL area and one in a SQL tuning set. The plan in the shared SQL area is the same as the original plan.

SQL Tuning Advisor only recommends an alternative plan if the elapsed time of the original plan is worse than alternative plans. In this case, SQL Tuning Advisor recommends that users create a SQL plan baseline on the plan with the best performance. In Example 20-1, the alternative plan did not perform as well as the original plan, so SQL Tuning Advisor did not recommend using the alternative plan.

Example 20-2 Alternative Plans Section

In this example, the alternative plans section of the SQL Tuning Advisor output includes both the original and alternative plans and summarizes their performance. The most important statistic is elapsed time. The original plan used an index, whereas the alternative plan used a full table scan, increasing elapsed time by .002 seconds.

Plan 1
------
 
  Plan Origin                 :Cursor Cache
  Plan Hash Value             :1378942017
  Executions                  :50
  Elapsed Time                :0.000 sec
  CPU Time                    :0.000 sec
  Buffer Gets                 :0
  Disk Reads                  :0
  Disk Writes                 :0
 
Notes:
  1. Statistics shown are averaged over multiple executions.
  2. The plan matches the original plan.
 
--------------------------------------------
| Id  | Operation            | Name        |
--------------------------------------------
|   0 | SELECT STATEMENT     |             |
|   1 |  SORT AGGREGATE      |             |
|   2 |   MERGE JOIN         |             |
|   3 |    INDEX FULL SCAN   | TEST1_INDEX |
|   4 |    SORT JOIN         |             |
|   5 |     TABLE ACCESS FULL| TEST        |
--------------------------------------------
 
Plan 2
------
 
  Plan Origin                 :STS
  Plan Hash Value             :2842999589
  Executions                  :10
  Elapsed Time                :0.002 sec
  CPU Time                    :0.002 sec
  Buffer Gets                 :3
  Disk Reads                  :0
  Disk Writes                 :0
 
Notes:
  1. Statistics shown are averaged over multiple executions.
 
-------------------------------------
| Id  | Operation           | Name  | 
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  SORT AGGREGATE     |       |
|   2 |   HASH JOIN         |       |
|   3 |    TABLE ACCESS FULL| TEST  |
|   4 |    TABLE ACCESS FULL| TEST1 |
-------------------------------------

To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible plan.

Managing the Automatic SQL Tuning Task

When your goal is to identify SQL performance problems proactively, configuring SQL Tuning Advisor as an automated task is a simple solution. The task processes selected high-load SQL statements from AWR that qualify as tuning candidates.

This section explains how to manage the Automatic SQL Tuning task. This section contains the following topics:

See Also:

Oracle Database Administrator's Guide to learn more about automated maintenance tasks

About the Automatic SQL Tuning Task

This section contains the following topics:

Purpose of Automatic SQL Tuning

Many DBAs do not have the time needed for the intensive analysis required for SQL tuning. Even when they do, SQL tuning involves several manual steps. Because several different SQL statements may be high load on any given day, DBAs may have to expend considerable effort to monitor and tune them. Configuring automatic SQL tuning instead of tuning manually decreases cost and increases manageability.

The automated task does not process the following types of SQL:

Ad hoc SQL statements or SQL statements that do not repeat within a week
Parallel queries
Queries that take too long to run after being SQL profiled, so that it is not practical for SQL Tuning Advisor to test-execute them
Recursive SQL

You can run SQL Tuning Advisor on demand to tune the preceding types of SQL statements.

Automatic SQL Tuning Concepts

Oracle Scheduler uses the automated maintenance tasks infrastructure (known as AutoTask) to schedules tasks to run automatically. By default, the Automatic SQL Tuning task runs for at most one hour in a nightly maintenance window. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week.

See Also:

Oracle Database Administrator's Guide to learn about Oracle Scheduler
Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_AUTO_TASK_ADMIN

Command-Line Interface to SQL Tuning Advisor

On the command line, you can use PL/SQL packages to perform SQL tuning tasks. Table 20-1 describes the most relevant packages.

Table 20-1 SQL Tuning Advisor Packages

Package	Description
`DBMS_AUTO_SQLTUNE`	Enables you run SQL Tuning Advisor, manage SQL profiles, manage SQL tuning sets, and perform real-time SQL performance monitoring. To use this API, you must have the `ADVISOR` privilege.
`DBMS_AUTO_TASK_ADMIN`	Provides an interface to `AUTOTASK`. You can use this interface to enable and disable the Automatic SQL Tuning task.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_SQLTUNE ad DBMS_AUTO_TASK_ADMIN

Basic Tasks for Automatic SQL Tuning

This section explains the basic tasks in running SQL Tuning Advisor as an automatic task. Figure 20-5 shows the basic workflow.

Figure 20-5 Automatic SQL Tuning APIs

Description of "Figure 20-5 Automatic SQL Tuning APIs"

As shown in Figure 20-6, the basic procedure is as follows:

Enable the Automatic SQL Tuning task.

See "Enabling and Disabling the Automatic SQL Tuning Task".
Optionally, configure the Automatic SQL Tuning task.

See "Configuring the Automatic SQL Tuning Task".
Display the results of the Automatic SQL Tuning task.

"Viewing Automatic SQL Tuning Reports".
Disable the Automatic SQL Tuning task.

See "Enabling and Disabling the Automatic SQL Tuning Task".

Enabling and Disabling the Automatic SQL Tuning Task

This section explains how to enable and disable the Automatic SQL Tuning task using Cloud Control (preferred) or a command-line interface.

Enabling and Disabling the Automatic SQL Tuning Task Using Cloud Control

You can enable and disable all automatic maintenance tasks, including the Automatic SQL Tuning task, using Cloud Control.

To enable or disable the Automatic SQL Tuning task using Cloud Control:

Access the Database Home page, as described in "Accessing the Database Home Page in Cloud Control."
From the Administration menu, select Oracle Scheduler, then Automated Maintenance Tasks.

The Automated Maintenance Tasks page appears.

This page shows the predefined tasks. You access each task by clicking the corresponding link to get more information about the task.
Click Automatic SQL Tuning.

The Automatic SQL Tuning Result Summary page appears.

The Task Status section shows whether the Automatic SQL Tuning Task is enabled or disabled. In the following graphic, the task is disabled:

Description of the illustration sta_task_status_disabled.gif
In Automatic SQL Tuning, click Configure.

The Automated Maintenance Tasks Configuration page appears.

Description of the illustration sta_maint_task_config.gif

By default, Automatic SQL Tuning executes in all predefined maintenance windows in MAINTENANCE_WINDOW_GROUP.
Perform the following steps:
1. In the Task Settings for Automatic SQL Tuning, select either Enabled or Disabled to enable or disable the automated task.
2. To disable Automatic SQL Tuning for specific days in the week, check the appropriate box next to the window name.
3. To change the characteristics of a window, click Edit Window Group.
4. Click Apply.

Enabling and Disabling the Automatic SQL Tuning Task from the Command Line

If you do not use Cloud Control to enable and disable the Automatic SQL Tuning task, then you have the following options:

Run the ENABLE or DISABLE procedure in the DBMS_AUTO_TASK_ADMIN PL/SQL package.

This package is the recommended command-line technique. For both the ENABLE or DISABLE procedures, you can specify a particular maintenance window with the window_name parameter. See Oracle Database PL/SQL Packages and Types Reference for complete reference information.
Set the STATISTICS_LEVEL initialization parameter to BASIC to disable collection of all advisories and statistics, including Automatic SQL Tuning Advisor.

Because monitoring and many automatic features are disabled, Oracle strongly recommends that you do not set STATISTICS_LEVEL to BASIC. See Oracle Database Reference for complete reference information.

To enable or disable Automatic SQL Tuning using DBMS_AUTO_TASK_ADMIN:

Connect SQL*Plus to the database with administrator privileges, and then do one of the following:

To enable the automated task, execute the following PL/SQL block:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (
    client_name => 'sql tuning advisor'
,   operation   => NULL
,   window_name => NULL
);
END;
/

To disable the automated task, execute the following PL/SQL block:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (
    client_name => 'sql tuning advisor'
,   operation   => NULL
,   window_name => NULL
);
END;
/

Query the data dictionary to confirm the change.

For example, query DBA_AUTOTASK_CLIENT as follows (sample output included):

COL CLIENT_NAME FORMAT a20

SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'sql tuning advisor';
 
CLIENT_NAME          STATUS
-------------------- --------
sql tuning advisor   ENABLED

To disable collection of all advisories and statistics:

Connect SQL*Plus to the database with administrator privileges, and then query the current statistics level setting.

The following SQL*Plus command shows that STATISTICS_LEVEL is set to ALL:

sys@PROD> SHOW PARAMETER statistics_level
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
statistics_level                     string      ALL

Set STATISTICS_LEVEL to BASIC as follows:

sys@PROD> ALTER SYSTEM SET STATISTICS_LEVEL ='BASIC';
 
System altered.

Configuring the Automatic SQL Tuning Task

This section explains how to configure settings for the Automatic SQL Tuning task.

Configuring the Automatic SQL Tuning Task Using Cloud Control

You can enable and disable all automatic maintenance tasks, including the Automatic SQL Tuning task, using Cloud Control. You must perform the operation as SYS or have the EXECUTE privilege on the PL/SQL package DBMS_AUTO_SQLTUNE.

To configure the Automatic SQL Tuning task using Cloud Control:

Access the Database Home page, as described in "Accessing the Database Home Page in Cloud Control."
From the Administration menu, click Oracle Scheduler, then Automated Maintenance Tasks.

The Automated Maintenance Tasks page appears.

This page shows the predefined tasks. You access each task by clicking the corresponding link to get more information about the task itself.
Click Automatic SQL Tuning.

The Automatic SQL Tuning Result Summary page appears.
Under Task Settings, click Configure next to Automatic SQL Tuning (SYS_AUTO_SQL_TUNING_TASK).

The Automated Maintenance Tasks Configuration page appears.
Under Task Settings, click Configure next to Automatic SQL Tuning.

The Automatic SQL Tuning Settings page appears.

Description of the illustration auto_sql_tuning_settings.gif
Make the desired changes and click Apply.

Configuring the Automatic SQL Tuning Task Using the Command Line

The DBMS_AUTO_SQLTUNE package enables you to configure automatic SQL tuning by specifying the task parameters using the SET_AUTO_TUNING_TASK_PARAMETER procedure. Because the task is owned by SYS, only SYS can set task parameters.

The ACCEPT_SQL_PROFILE tuning task parameter specifies whether to implement SQL profiles automatically (true) or require user intervention (false). The default is AUTO, which means true if at least one SQL statement exists with a SQL profile and false if this condition is not satisfied.

Note:

When automatic implementation is enabled, the advisor only implements recommendations to create SQL profiles. Recommendations such as creating new indexes, gathering optimizer statistics, and creating SQL plan baselines are not automatically implemented.

Assumptions

This tutorial assumes the following:

You want the database to implement SQL profiles automatically, but to implement no more than 50 SQL profiles per execution, and no more than 50 profiles total on the database.
You want the task to time out after 1200 seconds per execution.

To set Automatic SQL Tuning task parameters:

Connect SQL*Plus to the database with the appropriate privileges, and then optionally query the current task settings.

For example, connect SQL*Plus to the database with administrator privileges and execute the following query:

COL PARAMETER_NAME FORMAT a25 
COL VALUE FORMAT a10   

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  ( (TASK_NAME = 'SYS_AUTO_SQL_TUNING_TASK') AND
         ( (PARAMETER_NAME LIKE '%PROFILE%') OR 
           (PARAMETER_NAME = 'LOCAL_TIME_LIMIT') OR
           (PARAMETER_NAME = 'EXECUTION_DAYS_TO_EXPIRE') ) );

Sample output appears as follows:

PARAMETER_NAME            VALUE
------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE  30
LOCAL_TIME_LIMIT          1000
ACCEPT_SQL_PROFILES       FALSE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES     10000

Set parameters using PL/SQL code of the following form:

BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
    task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,   parameter => parameter_name
,   value     => value
);
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for complete reference information for DBMS_AUTO_SQLTUNE

Example 20-3 Setting SQL Tuning Task Parameters

The following PL/SQL block sets a time limit to 20 minutes, and also automatically implements SQL profiles and sets limits for these profiles:

BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'LOCAL_TIME_LIMIT', 1200);
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'ACCEPT_SQL_PROFILES', 'true');
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'MAX_SQL_PROFILES_PER_EXEC', 50);
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'MAX_AUTO_SQL_PROFILES', 10002);
END;
/

Viewing Automatic SQL Tuning Reports

At any time during or after the running of the Automatic SQL Tuning task, you can view a tuning report. This report contains information about all executions of the automatic SQL tuning task.

Depending on the sections that were included in the report, you can view information in the following sections:

General information

This section has a high-level description of the automatic SQL tuning task, including information about the inputs given for the report, the number of SQL statements tuned during the maintenance, and the number of SQL profiles created.
Summary

This section lists the SQL statements (by their SQL identifiers) that were tuned during the maintenance window and the estimated benefit of each SQL profile, or the execution statistics after performing a test execution of the SQL statement with the SQL profile.
Tuning findings

This section contains the following information about each SQL statement analyzed by SQL Tuning Advisor:
- All findings associated with each SQL statement
- Whether the profile was implemented on the database, and why
- Whether the SQL profile is currently enabled on the database
- Detailed execution statistics captured when testing the SQL profile
Explain plans

This section shows the old and new explain plans used by each SQL statement analyzed by SQL Tuning Advisor.
Errors

This section lists all errors encountered by the automatic SQL tuning task.

Viewing Automatic SQL Tuning Reports Using the Command Line

To generate a SQL tuning report as a CLOB, execute the DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK function. You can store the CLOB in a variable and then print the variable to view the report. See Oracle Database PL/SQL Packages and Types Reference for complete reference information.

Assumptions

This section assumes that you want to show all SQL statements that were analyzed in the most recent execution, including recommendations that were not implemented.

To create and access an Automatic SQL Tuning Advisor report:

Connect SQL*Plus to the database with administrator privileges, and then execute the DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK function.

The following example generates a text report to show all SQL statements that were analyzed in the most recent execution, including recommendations that were not implemented:
```
VARIABLE my_rept CLOB;
BEGIN
  :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK (
    begin_exec   => NULL
,   end_exec     => NULL
,   type         => 'TEXT'
,   level        => 'TYPICAL'
,   section      => 'ALL'
,   object_id    => NULL
,   result_limit => NULL
);
END;
/

PRINT :my_rept
```

Read the general information section for an overview of the tuning execution.

The following sample shows the Automatic SQL Tuning task analyzed 17 SQL statements in just over 7 minutes:

MY_REPT
---------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 6
Current Execution                       : EXEC_170
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200
Completion Status                       : COMPLETED
Started at                              : 04/16/2012 10:00:00
Completed at                            : 04/16/2012 10:07:11
Number of Candidate SQLs                : 17
Cumulative Elapsed Time of SQL (s)      : 8

Look for findings and recommendations.

If SQL Tuning Advisor makes a recommendation, then weigh the pros and cons of accepting it.

The following example shows that SQL Tuning Advisor found a plan for a statement that is potentially better than the existing plan. The advisor recommends implementing a SQL profile.

---------------------------------------------------------------------------
   SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
---------------------------------------------------------------------------
ob ID  SQL ID        statistics profile(benefit) index(benefit) restructure
------ ------------- ---------- ---------------- -------------- -----------
    82 dqjcc345dd4ak                      58.03%
    72 51bbkcd9zwsjw                                                      2
    81 03rxjf8gb18jg
 
---------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------
 Statements with Results Ordered by Max (Profile/Index) Benefit, Object ID
---------------------------------------------------------------------------
Object ID  : 82
Schema Name: DBA1
SQL ID     : dqjcc345dd4ak
SQL Text   : SELECT status FROM dba_autotask_client WHERE client_name=:1
 
---------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
---------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
  The SQL profile was not automatically created because the verified
  benefit was too low.
 
  Recommendation (estimated benefit: 58.03%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'SYS_AUTO_SQL_TUNING_TASK', object_id => 82, replace => TRUE);
 
  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original 
  plan and measuring their respective execution statistics. A plan 
  may have been only partially executed if the other could be run 
  to completion in less time.
 
                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time(us):               26963              8829      67.25 %
  CPU Time(us):                   27000              9000      66.66 %
  User I/O Time(us):                 25                14         44 %
  Buffer Gets:                      905               380      58.01 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:            7372              7372          0 %
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1
 
  Notes
  -----
  1. The original plan was first executed to warm the buffer cache.
  2. Statistics for original plan were averaged over next 9 executions.
  3. The SQL profile plan was first executed to warm the buffer cache.
  4. Statistics for the SQL profile plan were averaged over 
     next 9 executions.

Running SQL Tuning Advisor On Demand

This section contains the following topics:

About On-Demand SQL Tuning

In this context, on-demand SQL tuning is defined as any invocation of SQL Tuning Advisor that does not result from the Automatic SQL Tuning task.

Purpose of On-Demand SQL Tuning

Typically, you invoke SQL Tuning Advisor on demand in the following situations:

You proactively run ADDM, which reports that some SQL statements do not meet your performance requirements.
You reactively tune SQL statement because users complain about suboptimal SQL performance.

In both situations, running SQL Tuning Advisor is usually the quickest way to fix unexpected SQL performance problems.

User Interfaces for On-Demand SQL Tuning

The recommended user interface for running SQL Tuning Advisor manually is Cloud Control.

Accessing the SQL Tuning Advisor Using Cloud Control

Automatic Database Diagnostic Monitor (ADDM) automatically identifies high-load SQL statements. If ADDM identifies such statements, then click Schedule/Run SQL Tuning Advisor on the Recommendation Detail page to run SQL Tuning Advisor.

To tune SQL statements manually using SQL Tuning Advisor:

Access the Database Home page, as described in "Accessing the Database Home Page in Cloud Control."
From the Performance menu, click SQL, then SQL Tuning Advisor.

The Schedule SQL Tuning Advisor page appears.

Description of the illustration sql_tuning_advisor_empty.gif
See Oracle Database 2 Day + Performance Tuning Guide to learn how to configure and run SQL Tuning Advisor using Cloud Control.

Command-Line Interface to On-Demand SQL Tuning

If Cloud Control is unavailable, then you can run SQL Tuning Advisor using procedures in the DBMS_SQLTUNE package. To use the APIs, the user must have the ADVISOR privilege.

See Also:

Oracle Database PL/SQL Packages and Types Reference for complete reference information

Basic Tasks in On-Demand SQL Tuning

This section explains the basic tasks in running SQL Tuning Advisor using the DBMS_SQLTUNE package. Oracle Database 2 Day + Performance Tuning Guide explains how to tune SQL using Cloud Control.

Figure 20-6 shows the basic workflow when using the PL/SQL APIs.

Figure 20-6 SQL Tuning Advisor APIs

Description of "Figure 20-6 SQL Tuning Advisor APIs"

As shown in Figure 20-6, the basic procedure is as follows:

Prepare or create the input to SQL Tuning Advisor. The input can be either:
- The text of a single SQL statement
- A SQL tuning set that contains one or more statements
Create a SQL tuning task.

See "Creating a SQL Tuning Task".
Optionally, configure the SQL tuning task that you created.

See "Configuring a SQL Tuning Task".
Execute a SQL tuning task.

See "Executing a SQL Tuning Task".
Optionally, check the status or progress of a SQL tuning task.

"Monitoring a SQL Tuning Task".
Display the results of a SQL tuning task.

"Displaying the Results of a SQL Tuning Task".
Implement recommendations as appropriate.

Creating a SQL Tuning Task

To create a SQL tuning task execute the DBMS_SQLTUNE.CREATE_TUNING_TASK function. You can create tuning tasks from any of the following:

The text of a single SQL statement
A SQL tuning set containing multiple statements
A SQL statement selected by SQL identifier from the shared SQL area
A SQL statement selected by SQL identifier from AWR

The scope parameter is one of the most important for this function. You can set this parameter to the following values:

LIMITED

SQL Tuning Advisor produces recommendations based on statistical checks, access path analysis, and SQL structure analysis. SQL profile recommendations are not generated.
COMPREHENSIVE

SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL profiling.

Assumptions

This tutorial assumes the following:

You want to tune as user hr, who has the ADVISOR privilege.

You want to tune the following query:

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;

You want to pass the bind variable 100 to the preceding query.
You want SQL Tuning Advisor to perform SQL profiling.
You want the task to run no longer than 60 seconds.

To create a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.CREATE_TUNING_TASK function.

For example, execute the following PL/SQL program:

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  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';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
          sql_text    => my_sqltext
,         bind_list   => sql_binds(anydata.ConvertNumber(100))
,         user_name   => 'HR'
,         scope       => 'COMPREHENSIVE'
,         time_limit  => 60
,         task_name   => 'STA_SPECIFIC_EMP_TASK'
,         description => 'Task to tune a query on a specified employee'
);
END;
/

Optionally, query the status of the task.

The following example queries the status of all tasks owned by the current user, which in this example is hr:

COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM   USER_ADVISOR_LOG;

Sample output appears below:

TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
------- ------------------------- ----------- --------------
    884 STA_SPECIFIC_EMP_TASK     INITIAL

In the preceding output, the INITIAL status indicates that the task has not yet started execution.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SQLTUNE.CREATE_TUNING_TASK function

Configuring a SQL Tuning Task

To change the parameters of a tuning task after it has been created, execute the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER function. See Oracle Database PL/SQL Packages and Types Reference for complete reference information.

Assumptions

This tutorial assumes the following:

You want to tune as user hr, who has the ADVISOR privilege.
You want to tune the STA_SPECIFIC_EMP_TASK created in "Creating a SQL Tuning Task".
You want to change the maximum time that the SQL tuning task can run to 300 seconds.

To configure a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER function.

For example, execute the following PL/SQL program to change the time limit of the tuning task to 300 seconds:
```
BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
    task_name => 'STA_SPECIFIC_EMP_TASK'
,   parameter => 'TIME_LIMIT'
,   value     => 300
);
END;
/
```

Optionally, verify that the task parameter was changed.

The following example queries the values of all used parameters in task STA_SPECIFIC_EMP_TASK:

COL PARAMETER_NAME FORMAT a25 
COL VALUE FORMAT a15   

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   USER_ADVISOR_PARAMETERS
WHERE  TASK_NAME = 'STA_SPECIFIC_EMP_TASK'
AND    PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;

Sample output appears below:

PARAMETER_NAME            VALUE
------------------------- ---------------
DAYS_TO_EXPIRE            30
DEFAULT_EXECUTION_TYPE    TUNE SQL
EXECUTION_DAYS_TO_EXPIRE  UNLIMITED
JOURNALING                INFORMATION
MODE                      COMPREHENSIVE
SQL_LIMIT                 -1
SQL_PERCENTAGE            1
TARGET_OBJECTS            1
TEST_EXECUTE              AUTO
TIME_LIMIT                300

Executing a SQL Tuning Task

To execute a SQL tuning task, use the DBMS_SQLTUNE.EXECUTE_TUNING_TASK function. The most important parameter is task_name.

Note:

You can also execute the automatic tuning task SYS_AUTO_SQL_TUNING_TASK using the EXECUTE_TUNING_TASK API. SQL Tuning Advisor performs the same analysis and actions as it would when run automatically.

Assumptions

This tutorial assumes the following:

You want to tune as user hr, who has the ADVISOR privilege.
You want to execute the STA_SPECIFIC_EMP_TASK created in "Creating a SQL Tuning Task".

To execute a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.EXECUTE_TUNING_TASK function.

For example, execute the following PL/SQL program:
```
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_SPECIFIC_EMP_TASK');
END;
/
```

Optionally, query the status of the task.

The following example queries the status of all tasks owned by the current user, which in this example is hr:

COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM   USER_ADVISOR_LOG;

Sample output appears below:

TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
------- ------------------------- ----------- --------------
    884 STA_SPECIFIC_EMP_TASK     COMPLETED

See Also:

Oracle Database PL/SQL Packages and Types Reference for complete reference information about the DBMS_SQLTUNE.EXECUTE_TUNING_TASK function

Monitoring a SQL Tuning Task

When you create a SQL tuning task in Cloud Control, no separate monitoring step is necessary. Cloud Control displays the status page automatically.

If you do not use Cloud Control, then you can monitor currently executing SQL tuning tasks by querying the data dictionary and dynamic performance views. Table 20-2 describes the relevant views.

Table 20-2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK Parameters

View	Description
`USER_ADVISOR_TASKS`	Displays information about tasks owned by the current user. The view contains one row for each task. Each task has a name that is unique to the owner. Task names are just informational and no uniqueness is enforced within any other namespace.
`V$ADVISOR_PROGRESS`	Displays information about the progress of advisor execution.

Assumptions

This tutorial assumes the following:

You tune as user hr, who has the ADVISOR privilege.
You monitor the STA_SPECIFIC_EMP_TASK that you executed in "Executing a SQL Tuning Task".

To monitor a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then determine whether the task is executing or completed.

For example, query the status of STA_SPECIFIC_EMP_TASK as follows:
```
SELECT STATUS 
FROM   USER_ADVISOR_TASKS
WHERE  TASK_NAME = 'STA_SPECIFIC_EMP_TASK';
```
The following output shows that the task has completed:
```
STATUS
-----------
EXECUTING
```

Determine the progress of an executing task.

The following example queries the status of the task with task ID 884:

VARIABLE my_tid NUMBER;  
EXEC :my_tid := 884
COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999

SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK, 
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM   V$ADVISOR_PROGRESS
WHERE  TASK_ID = :my_tid;

Sample output appears below:

   TASK_ID ADVISOR_NAME         SOFAR TOTALWORK %_COMPLETE
---------- -------------------- ----- --------- ----------
       884 SQL Tuning Advisor       1         2         50

See Also:

Oracle Database Reference to learn about the V$ADVISOR_PROGRESS view

Displaying the Results of a SQL Tuning Task

To report the results of a tuning task, use the DBMS_SQLTUNE.REPORT_TUNING_TASK function. The report contains all the findings and recommendations of SQL Tuning Advisor. For each proposed recommendation, the report provides the rationale and benefit along with the SQL statements needed to implement the recommendation.

Assumptions

This tutorial assumes the following:

You want to tune as user hr, who has the ADVISOR privilege.
You want to access the report for the STA_SPECIFIC_EMP_TASK executed in "Executing a SQL Tuning Task".

To view the report for a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.REPORT_TUNING_TASK function.

For example, you run the following statements:

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' )
FROM   DUAL;

Truncated sample output appears below:

DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_SPECIFIC_EMP_TASK')
---------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------
Tuning Task Name   : STA_SPECIFIC_EMP_TASK
Tuning Task Owner  : HR
Workload Type      : Single SQL Statement
Execution Count    : 11
Current Execution  : EXEC_1057
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 04/22/2012 07:35:49
Completed at       : 04/22/2012 07:35:50
 
---------------------------------------------------------------------------
Schema Name: HR
SQL ID     : dg7nfaj0bdcvk
SQL Text   : 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
Bind Variables :
 1 -  (NUMBER):100
 
---------------------------------------------------------------------------
FINDINGS SECTION (4 findings)
-----------------------------------------------

Interpret the results, as described in "Viewing Automatic SQL Tuning Reports Using the Command Line".

See Also:

Oracle Database PL/SQL Packages and Types Reference for complete reference information