Oracle官方文档对SQL Access Advisor的描述如下：

SQL Access Advisor, which is a tuning tool that provides advice on improving the performance of a database through partitioning, materialized views, indexes, and materialized view logs.

本文介绍下如何通过DBMS_ADVISOR的quick_tune过程使用SQL Access Advisor，当然，使用EM来执行SQL Access Advisor会更加方便。

1. 构建测试表T1,T2

-- 删除测试表drop table t1 cascade constraints purge;drop table t2 cascade constraints purge;-- 创建测试表create table t1( id number not null, n number, contents varchar2(4000) );create table t2( id number not null, t1_id number not null, n number, contents varchar2(4000) );-- 初始化测试数据execute dbms_random.seed(0);insert into t1  select rownum, rownum, dbms_random.string('a',50)   from dual   connect by level <= 1000   order by dbms_random.random;commit;  insert into t2  select rownum, rownum, rownum, dbms_random.string('b',60)  from dual  connect by level <= 100000  order by dbms_random.random;commit;-- 查询表数据量select count(1) from t1;select count(1) from t2;-- 查询段大小select segment_name, BYTES/1024/1024 "MB" from user_segments where segment_name in ('T1','T2');-- 分析表（必须有统计信息）analyze table t1 estimate statistics;analyze table t2 estimate statistics;

测试SQL：

select * from t1, t2 where t1.id = t2.t1_id and t1.n = 19;

2. 定义调整任务

begin dbms_advisor.quick_tune (dbms_advisor.sqlaccess_advisor, 'zjy_sql_access_task1', 'select * from t1, t2 where t1.id = t2.t1_id and t1.n = 19' );end;/

3. 创建调优脚本的目录

create or replace directory tune_jy_scripts as '/tmp';

4. 把调优脚本内容输出

begin dbms_advisor.create_file  (dbms_advisor.get_task_script('zjy_sql_access_task1'),   'TUNE_JY_SCRIPTS',   'zjy_sql_access_task_01.sql'  );end;/

5. 查看调优脚本内容

more /tmp/zjy_sql_access_task_01.sqlRem  SQL Access Advisor: Version 11.2.0.4.0 - ProductionRem  Rem  Username:        JINGYURem  Task:            zjy_sql_access_task1Rem  Execution date:   Rem  CREATE INDEX "JINGYU"."T1_IDX$$_02330000"    ON "JINGYU"."T1"    ("N")    COMPUTE STATISTICS;CREATE INDEX "JINGYU"."T2_IDX$$_02330001"    ON "JINGYU"."T2"    ("T1_ID")    COMPUTE STATISTICS;

可以看到这里给出的优化建议，在这里还是很准确的。

即：驱动表T1的限制条件列创建索引；被驱动表的连接条件创建索引。

6. 删除任务

exec dbms_sqltune.drop_tuning_task('zjy_sql_access_task1');