12 Automatic SQL Tuning

12.1.1 Query Optimizer Modes

The enhanced query optimizer has two modes:

• Normal mode

• Tuning mode

12.1.2 Types of Tuning Analysis

Automatic SQL Tuning includes four types of tuning analysis:

• Statistics Analysis

• SQL Profiling

• Access Path Analysis

• SQL Structure Analysis

12.2.1 Input Sources

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

• Automatic Database Diagnostic Monitor

  The primary input source is the Automatic Database Diagnostic Monitor (ADDM). By default, ADDM runs proactively once every hour and analyzes key statistics gathered by the 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, ADDM recommends running SQL Tuning Advisor on the SQL. See "Automatic Database Diagnostic Monitor".

• High-load SQL statements

  The second most important input source is the high-load SQL statements captured in Automatic Workload Repository (AWR). The AWR takes regular snapshots of the system activity including high-load SQL statements ranked by relevant statistics, such as CPU consumption and wait time. A user can view the AWR and identify the high-load SQL of interest and run SQL Tuning Advisor on them. By default, the AWR retains data for the last seven days. This means that any high-load SQL that ran within the retention period of the AWR can be located and tuned using this feature. See "Overview of the Automatic Workload Repository".

• Cursor cache

  The third likely source of input is the cursor cache. This source is used for tuning recent SQL statements that are yet to be captured in the AWR. The cursor cache and AWR together 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 7 days.

• SQL Tuning Set

  Another possible input source for the SQL Tuning Advisor is the 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 are used as input, a SQL Tuning Set (STS) has to be first constructed and stored. See "SQL Tuning Sets".

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

• If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.

• If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes.

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

12.2.4 Using SQL Tuning Advisor APIs

The recommended interface for running the SQL Tuning Advisor is the Oracle Enterprise Manager. Whenever possible, you should run the SQL Tuning Advisor using Oracle Enterprise Manager, as described in the Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can run the SQL Tuning Advisor using procedures in the DBMS_SQLTUNE package. To use the APIs, the user must be granted specific privileges.

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

1. Create a SQL Tuning Set (if tuning multiple SQL statements)

2. Create a SQL tuning task

3. Execute a SQL tuning task

4. Display the results of a SQL tuning task

5. Implement recommendations as appropriate

A SQL tuning task can be created for a single SQL statement. 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".

Figure 12-1 shows the steps involved when running the SQL Tuning Advisor using the DBMS_SQLTUNE package.

Figure 12-1 SQL Tuning Advisor APIs

Description of "Figure 12-1 SQL Tuning Advisor APIs"

This section covers the following topics:

• Creating a SQL Tuning Task

• Executing a SQL Tuning Task

• Checking the Status of a SQL Tuning Task

• Checking the Progress of the SQL Tuning Advisor

• Displaying the Results of a SQL Tuning Task

• Additional Operations on a SQL Tuning Task

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   => 'my_sql_tuning_task',
         description => 'Task to tune a query on a specified employee');
END;
/

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = 'HR';

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';

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

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

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sql_tuning_set', 
    description  => 'I/O intensive workload');
END;
/

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.

12.3.2 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 select only the top 30 SQL statements ordered by elapsed time. 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.

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  'peak baseline',
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set',
             populate_cursor => baseline_cursor);
END;
/

12.3.3 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 the SQL in the STS using different filtering criteria. The SELECT_SQLSET procedure is provided for this purpose.

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

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET( 
   'my_sql_tuning_set',
   '(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.

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

BEGIN
  DBMS_SQLTUNE.DELETE_SQLSET(
      sqlset_name  => 'my_sql_tuning_set',
      basic_filter => 'executions < 50');
END;
/

12.3.5 Transporting a SQL Tuning Set

SQL Tuning Sets can be transported to another system by first exporting the STS from one system to a staging table, then importing the STS from the staging table into another system.

To transport a SQL Tuning Set:

1. Use the CREATE_STGTAB_SQLSET procedure to create a staging table where the SQL Tuning Sets will be exported.

   The following example shows how to create a staging table named staging_table.

   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'staging_table' );
   END;
   /
   

2. Use the PACK_STGTAB_SQLSET procedure to export SQL Tuning Sets into the staging table.

   The following example shows how to export a SQL Tuning Set named my_sts to the staging table.

   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
         sqlset_name  => 'my_sts',
         staging_table_name => 'staging_table');
   END;
   /
   

3. Move the staging table to the system where the SQL Tuning Sets will be imported using the mechanism of choice (such as datapump or database link).

4. On the system where the SQL Tuning Sets will be imported, use the UNPACK_STGTAB_SQLSET procedure to import SQL Tuning Sets from the staging table.

   The following example shows how to import SQL Tuning Sets contained in the staging table.

   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
         sqlset_name  => '%',
         replace  => TRUE,
         staging_table_name => 'staging_table');
   END;
   /
   

12.3.6 Dropping a SQL Tuning Set

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

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'my_sql_tuning_set' );
END;
/

12.3.7 Additional Operations on SQL Tuning Sets

You can use the following APIs to manage an STS:

• Updating the attributes of SQL statements in an STS

  The UPDATE_SQLSET procedure updates the attributes of SQL statements (such as PRIORITY or OTHER) in an existing STS identified by STS name and SQL identifier.

• Capturing the full system workload

  The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as opposed to the AWR—which only captures the workload of high-load SQL statements—or the LOAD_SQLSET procedure, which accesses the data source only once.

• Adding and removing a reference to an STS

  The ADD_SQLSET_REFERENCE function adds a new reference to an existing STS to indicate its use by a client. The function returns the identifier of the added reference. The REMOVE_SQLSET_REFERENCE procedure is used to deactivate an STS to indicate it is no longer used by the client.

12.4.1 Accepting a SQL Profile

When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. 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:

DECLARE
 my_sqlprofile_name VARCHAR2(30);
BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name    => 'my_sql_tuning_task',
    name         => 'my_sql_profile',)
    force_match  => TRUE);
END;

In this example, my_sql_tuning_task is the name of the SQL tuning task and my_sql_profile is the name of the SQL Profile that you want to accept.

Typically, an accepted SQL Profile is associated with the SQL statement through a special SQL signature that is generated using a hash function. This hash function normalizes the SQL statement for case (changes the entire SQL statement to upper case) and white spaces (removes all extra whites spaces) before generating the signature. The same SQL Profile thus will work for all SQL statements that are essentially the same, where the only difference is in case usage and white spaces. However, by setting force_match to true, the SQL Profile will additionally target all SQL statements that have the same text after normalizing literal values to bind variables. This may be useful for applications that use literal values rather than bind variables, since this will allow SQL with text differing only in its literal values to share a SQL Profile. If both literal values and bind variables are used in the SQL text, or if this parameter is set to false (the default value), literal values will not be normalized.

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

12.4.2 Altering a SQL Profile

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

BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
     name            => 'my_sql_profile', 
     attribute_name  => 'STATUS', 
     value           => 'DISABLED');
END;
/

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.

12.4.3 Dropping a SQL Profile

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

BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/

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.