SQL Tuning Advisor

Even though Oracle Enterprise Manager 10g Grid Control is a very handy tool, it is possible to use the SQL Tuning Advisor via SQL*Plus, so if you don’t have access to OEM, or if you simply are a command line fan, here is the alternative to using OEM for the SQL Tuning Advisor.

Below is a SQL*Plus script execution output, this script (available below) simply requires to paste in the query to be tuned.

SQL> @tune

Enter value for query: select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno and a.ename = ”SMITH”

This script will execute the SQL Tuning Advisor and will provide the same recommendations returned by OEM.

Here is the output:

TUNING_TASK
———————————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : TASK_33842
Tuning Task Owner : STEEVE
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 07/24/2007 09:33:08
Completed at : 07/24/2007 09:33:08
Number of Index Findings : 1

——————————————————————————-
Schema Name: STEEVE
SQL ID : 8uu071up6vk3q
SQL Text : select a.empno, a.ename, b.dname from emp a, dept b where
a.deptno = b.deptno and a.ename = ‘SMITH’

——————————————————————————-
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: 100%)
—————————————-
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index STEEVE.IDX$$_84320001 on STEEVE.EMP(‘ENAME’);

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
account index maintenance overhead and additional space consumption.

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 3487251775

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 26 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“A”.”ENAME”=’SMITH’)
4 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

2- Using New Indices
——————–
Plan hash value: 2308654808

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_84320001 | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

3 – access(“A”.”ENAME”=’SMITH’)
5 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

——————————————————————————-

The scripts can be downloaded here: tune_advisor.txt and tune.txt

Note: The user running the script needs the Advisor role.

References:

SQL Tuning Advisor

Using SQL Tuning Advisor APIs

Required License