20 Analyzing SQL with SQL Tuning Advisor

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

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

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

20.1.3.1 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"

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

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

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

20.1.3.2.2 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"

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

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.

20.1.3.4 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. For example, if the indexed cust_id column has a VARCHAR2 data type, then the predicate WHERE cust_id=7777 does not use the index.

• Design mistakes

  A classic example of a design mistake is a missing join condition that leads to 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.

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

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 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);

Example 20-1 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.

In Example 20-2, 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.

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

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

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

20.2.1.4 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:

1. Enable the Automatic SQL Tuning task.

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

2. Optionally, configure the Automatic SQL Tuning task.

   See "Configuring the Automatic SQL Tuning Task".

3. Display the results of the Automatic SQL Tuning task.

   "Viewing Automatic SQL Tuning Reports".

4. Disable the Automatic SQL Tuning task.

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

20.2.2.1 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: 

1. Access the Database Home page, as described in "Accessing the Database Home Page in Cloud Control."

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

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

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

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

20.2.2.2 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: 

1. 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;
     /
     

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

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

2. Set STATISTICS_LEVEL to BASIC as follows:

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

20.2.3.1 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: 

1. Access the Database Home page, as described in "Accessing the Database Home Page in Cloud Control."

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

3. Click Automatic SQL Tuning.

   The Automatic SQL Tuning Result Summary page appears.

4. Under Task Settings, click Configure next to Automatic SQL Tuning (SYS_AUTO_SQL_TUNING_TASK).

   The Automated Maintenance Tasks Configuration page appears.

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

6. Make the desired changes and click Apply.

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

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: 

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

2. 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;
   /
   

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;
/

20.2.4.1 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: 

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

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

3. 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
   -------------------------------------------------------------------------------
   object 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 Maximum (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.
   

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

20.3.1.2 User Interfaces for On-Demand SQL Tuning

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

20.3.1.3 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:

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

2. Create a SQL tuning task.

   See "Creating a SQL Tuning Task".

3. Optionally, configure the SQL tuning task that you created.

   See "Configuring a SQL Tuning Task".

4. Execute a SQL tuning task.

   See "Executing a SQL Tuning Task".

5. Optionally, check the status or progress of a SQL tuning task.

   "Monitoring a SQL Tuning Task".

6. Display the results of a SQL tuning task.

   "Displaying the Results of a SQL Tuning Task".

7. Implement recommendations as appropriate.