Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 30, 2005

Automatic SQL Tuning using SQL Tuning Advisor

By DatabaseJournal.com Staff

by Sreeram Surapaneni


SQL tuning is one of the challenging tasks faced by Database Administrators. It is an interesting and creative, but at the same time, daunting task. Manual tuning of SQL statements requires through knowledge of how the statements are executed in the background and experience to understand suitable access paths to yield better response times. Moreover, in a typical application, there are just too many SQL statements to tune and advise the developers.

Oracle Database 10g introduces many useful and easy-to-use tuning tools and methodologies. In this article, we will examine one of these new and improved features - SQL Tuning Advisor.

SQL Tuning Advisor is primarily designed to replace the manual tuning of SQL statements or any third-party tools currently used by some DBAs. SQL Tuning Advisor examines poorly executing SQL statements and evaluates resource consumption in terms of CPU, I/O, and temporary space. The advisor receives one or more SQL statements as input and provides advice on how to optimize their execution plans, gives the rationale for the advice, the estimated performance benefit, and the actual command to implement the advice.

In its normal mode, the query optimizer needs to make decisions about execution plans in a very short time. As a result, it may not always be able to obtain enough information to make the best decision. Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.

SQL Tuning Advisor also identifies any objects with missing statistics referenced in the query. Thus, the advisor performs four distinct types of tasks:

  • Checks if objects have valid, usable statistics for proper optimization
  • Attempts to rewrite queries for better performance and suggests rewriting
  • Checks the access path to see if performance could be improved by adding additional structures such as indexes and materialized views
  • Creates SQL profiles and attaches them to specific queries.

In tuning mode, the optimizer performs the following analysis:

  • Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
  • SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible, the information is stored in an SQL profile. If accepted, this information can then be used by the optimizer when running in normal mode. Unlike a stored outline, which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it is sensible to update profiles periodically. The SQL profiling is not performed when the tuning optimizer is run in limited mode.
  • Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path. Its index recommendations relate to a specific statement, so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
  • SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact performance. The implementation of these suggestions requires human intervention to check their validity.

Using Enterprise Manager:

The below example was formulated to illustrate how to use SQL Tuning Advisor in command-line mode, which is very useful for scripting these tasks proactively. In most cases, however, you will need to perform tuning in response to problems reported by an end user. Enterprise Manager 10g comes in handy in those cases.

From the Database home page, click on the link "Advisor Central" at the bottom of the screen, which launches the page containing all of the advisors. Next, click on "SQL Tuning Advisor" at the top of the screen as shown in Figure A.

figure 4
Figure A: Advisor Central in Enterprise Manager

Now you have launched the SQL Tuning Advisor. Choose "Top SQL" from the next page as shown in Figure B.

figure 5
Figure B: SQL Tuning Advisors

When you choose Top SQL, it launches a page with a graph containing the various wait classes, traced along a time dimension Dragging the mouse to a location on the graph where the CPU wait is high, you would be able to see relevant SQL statements and be able to identify areas with the highest activity and maximum CPU consumption.

Now we focus on the PL/SQL API, as the Enterprise Manager interface is a reasonably intuitive method. The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning set or specified manually.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

CONN sys/password AS SYSDBA
CONN scott/tiger

The first step when using the SQL tuning advisor is to create a new tuning task using the CREATE_TUNING_TASK function.


-- Tuning task created for specific a statement from the AWR.
  l_sql_tune_task_id  VARCHAR2(100);
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

-- Tuning task created for specific a statement from the cursor cache.
  l_sql_tune_task_id  VARCHAR2(100);
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

-- Tuning task created from an SQL tuning set.
  l_sql_tune_task_id  VARCHAR2(100);
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

-- Tuning task created for a manually specified statement.
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
  l_sql := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'scott',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

If the TASK_NAME parameter is specified, its value is returned as the SQL tune task identifier. If omitted, a system generated name such as "TASK_1478" is returned. If the SCOPE parameter is set to scope_limited the SQL profiling analysis is omitted. The TIME_LIMIT parameter simply restricts the time the optimizer can spend compiling the recommendations.

The following examples will reference the last tuning set, as it has no external dependencies other than the SCOTT schema. The NVL in the SQL statement was put in to provoke a reaction from the optimizer. In addition, we can delete the statistics from one of the tables to provoke it even more:

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');

With the tuning task defined, the next step is to execute it using the EXECUTE_TUNING_TASK procedure:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

During the execution phase, you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:

-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME                      STATUS
------------------------------ -----------
emp_dept_tuning_task           COMPLETED

1 row selected.

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:

SET LONG 10000;
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;

In this case, the output looks like this:

Tuning Task Name   : emp_dept_tuning_task
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/06/2004 09:29:13
Completed at       : 05/06/2004 09:29:15

SQL ID  : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM   emp e JOIN dept d ON e.deptno = d.deptno
          WHERE  NVL(empno, '0') = :empno


1- Statistics Finding
  Table "SCOTT"."EMP" and its indices were not analyzed.

    Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)

    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

2- Restructure SQL finding (see plan 1 in explain plans section)
  The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
  contains an expression on indexed column "EMPNO". This expression prevents
  the optimizer from selecting indices on table "SCOTT"."EMP".

    Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.


1- Original
Plan hash value: 1863486531

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT             |         |     1 |   107 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |   107 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

   - dynamic sampling used for this statement


1 row selected.

Once the tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure:

  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');


SQL Tuning Advisor is a powerful tool that has the "brains" to analyze performance metrics and offer recommendations based on best practices and accepted methodologies professed by seasoned Oracle professionals, all automatically. This functionality can tell the DBA not only what happened and why, but most important, what to do next.

Sreeram Surapaneni is an Oracle Certified DBA (7.3,8,8i,9i) working currently as DBA at Research In Motion Ltd, Canada and can be reached at Sreeram Surapaneni.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM