SQL Tuning 基础概述09 - SQL Access Advisor

-- 删除测试表
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;

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

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

create or replace directory tune_jy_scripts as '/tmp';

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

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;

exec dbms_sqltune.drop_tuning_task('zjy_sql_access_task1');