How to Run Oracle 12c’s SQL Tuning Advisor

By Chris Ruel, Michael Wessler

Oracle 12c offers a useful utility called the SQL Tuning Advisor. You can use this built-in tool to provide suggestions or recommendations about certain SQL statements. Although it may not always give perfect advice, just like anything else, having it in your toolbox of tuning techniques is beneficial.

Use PL/SQL and the internal package DBMS_SQL_TUNE to create a tuning task. Type this:

DECLARE
 l_sql        VARCHAR2(500);
 l_sql_tune_task_id VARCHAR2(100);
BEGIN
 l_sql := 'SELECT first_name, last_name, department_name ' ||
      'FROM emp JOIN departments USING (department_id) ' ||
      'WHERE last_name = ''Hopkins''';
 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
             sql_text  => l_sql,
             user_name  => 'HR',
             scope    => DBMS_SQLTUNE.scope_comprehensive,
             time_limit => 60,
             task_name  => 'emp_dept_tuning_task',
             description => 'Tuning task for an EMP to DEPARTMENT join query.');
 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

You should see the following:

PL/SQL procedure successfully completed.

In the preceding command, note the TIME_LIMIT of 60. That limits the processing time to 60 seconds. You may not always want to run something like this for long periods in your database, because it incurs system overhead.

Execute the tuning advisor with your task by typing this:
```
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');
```
Because of the limit of 60 seconds provided in the task creation, this step may take up to 60 seconds to complete. During this time, your prompt won’t come back.

When it completes, you should see this:
```
PL/SQL procedure successfully completed.
```
If you’ve set a longer time and are getting impatient, you can open another SQL window to make sure that the task is still executing by typing
```
SELECT task_name, status, execution_start
FROM dba_advisor_log WHERE owner = 'HR';
```
You see something like the following:
```
TASK_NAME           STATUS     EXECUTION_START
------------------------------ --------------- --------------------
emp_dept_tuning_task      EXECUTING    19-JUL-2013 15:35:42
```

When the execution is complete, you can view the results by running the BMS_SQLTUNE.report_tuning_task procedure. Type the following:

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

For the sake of space, we’ve snipped some sections from the output that follows, but you see something like this:

RECOMMENDATIONS
---------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------
Tuning Task Name  : emp_dept_tuning_task
Tuning Task Owner : HR
Workload Type   : Single SQL Statement
Scope       : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : INTERRUPTED
Started at     : 07/19/2013 15:21:39
Completed at    : 07/19/2013 15:22:43
---------------------------------------------------------------------------------
Error: ORA-13639: The current operation was interrupted because it timed out.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Schema Name: HR
SQL ID   : 47uvvzcuu5mdg
SQL Text  : SELECT first_name, last_name, department_name FROM emp JOIN
       departments USING (department_id) WHERE last_name = 'Hopkins'
RECOMMENDATIONS
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
---------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
 The execution plan of this statement can be improved by creating one or more
 indices.
 Recommendation (estimated benefit: 99.98%)
 ------------------------------------------
 - Consider running the Access Advisor to improve the physical schema design
  or creating the recommended index.
  create index HR.IDX$$_03170001 on HR.EMP("LAST_NAME");
 Rationale
 ---------
  Creating the recommended indices significantly improves the execution plan
  of this statement. However, it might be preferable to run "Access Advisor"
  using a representative SQL workload as opposed to a single statement. This
  will allow to get comprehensive index recommendations which takes into
RECOMMENDATIONS
--------------------------------------------------------------------------------
  account index maintenance overhead and additional space consumption.
...output snipped...

The latter part of the report shows the before and after execution plans. In this case, you’ve seen the before when you were generating execution plans. Go ahead and add the index, regenerate the execution plan, and see whether you’ve made an improvement.

Before you add the index, note that the recommendations give the SQL to add the index:

 Recommendation (estimated benefit: 99.98%)
 ------------------------------------------
 - Consider running the Access Advisor to improve the physical schema design
  or creating the recommended index.
  create index HR.IDX$$_03170001 on HR.EMP("LAST_NAME");

Also note that Oracle gives a warning:

RECOMMENDATIONS
--------------------------------------------------------------------------------
  account index maintenance overhead and additional space consumption.

Add the index with your own name by typing this:
```
CREATE INDEX emp_last_name_idx ON emp(last_name);
```
You should see something like the following:
```
Index created.
```

Take a look at the execution plan. Type the following:

explain plan for
SELECT first_name, last_name, department_name
FROM emp join departments using (department_id)
WHERE last_name = 'Hopkins';
Explained.
Elapsed: 00:00:00.09

And then type

@?rdbmsadminutlxpls

You should see output like this:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1505300146
---------------------------------------------------------------------------------
| Id | Operation               | Name       | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |          |   1 |  34 |   5  (0)| 00:00:01 |
|  1 | NESTED LOOPS             |          |    |    |      |    |
|  2 |  NESTED LOOPS            |          |   1 |  34 |   5  (0)| 00:00:01 |
|  3 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP        |   1 |  18 |   4  (0)| 00:00:01 |
|* 4 |   INDEX RANGE SCAN         | EMP_LAST_NAME_IDX |   1 |    |   3  (0)| 00:00:01 |
|* 5 |  INDEX UNIQUE SCAN         | DEPT_ID_PK    |   1 |    |   0  (0)| 00:00:01 |
|  6 |  TABLE ACCESS BY INDEX ROWID     | DEPARTMENTS    |   1 |  16 |   1  (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  4 - access("EMP"."LAST_NAME"='Hopkins')
  5 - access("EMP"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")

Now that you’ve added the index, a few things are evident:

The cost of the plan dropped from 40336 to 5.
There are now six steps.
The full table scan is gone. Instead you see the use of your new index.

Often one of the tough parts about tuning a database is having a solid understanding of the application and the data. The issue might not always be obvious. Sometimes engaging other application and data experts helps.

Explain to them your findings and what you propose. They may be able to help you come to a conclusion. Also, if the data is part of a packaged third-party application, sometimes opening a ticket with the vendor is the way to go.