SQL Tuning 基础概述09 - SQL Access Advisor
2015-12-23 13:27 AlfredZhao 阅读(727) 评论(1) 编辑 收藏 举报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.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的限制条件列创建索引;被驱动表的连接条件创建索引。
6. 删除任务
exec dbms_sqltune.drop_tuning_task('zjy_sql_access_task1');
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」