SQL Tuning 基础概述09

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
2. 定义调整任务
3. 创建调优脚本的目录
4. 把调优脚本内容输出
5. 查看调优脚本内容

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;

<h1 id="2"> 2. 定义调整任务</h1>

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;
/

<h1 id="3"> 3. 创建调优脚本的目录</h1>

create or replace directory tune_jy_scripts as '/tmp';

<h1 id="4"> 4. 把调优脚本内容输出</h1>

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

<h1 id="5"> 5. 查看调优脚本内容</h1>

more /tmp/zjy_sql_access_task_01.sql
Rem SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem Username: JINGYU
Rem Task: zjy_sql_access_task1
Rem 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的限制条件列创建索引；被驱动表的连接条件创建索引。

<h1 id="6"> 6. 删除任务</h1>

exec dbms_sqltune.drop_tuning_task('zjy_sql_access_task1');