sql tuning advisor为Oracle提供的sql优化工具，分析复杂SQL性能问题时可以通过sql tuning advisor参考oracle的建议。
1.创建测试数据
create user ttest identified by ttest;
grant connect,resource,dba to ttest;
conn ttest
/ttest
create table t1 as
select
* from dba_tables;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
commit;
create table t2 as
select
* from dba_users;
create table t3 as
select
* from dba_objects;
insert into t3 select * from t3;
insert into t3 select * from t3;
insert into t3 select * from t3;
insert into t3 select * from t3;
insert into t3 select * from t3;
commit;
create table t4 as
select
* from dba_data_files;
create view v1 as
select
t1.table_name,t1.owner,t1.TABLESPACE_NAME,t1.NUM_ROWS,t3.CREATED,t3.LAST_DDL_TIME from t1,t3 where t1.table_name=t3.object_name and t1.owner=t3.owner;
2.收集统计信息及测试效果
exec
dbms_stats.gather_schema_stats(
'TTEST'
);
select
* from v1 where table_name=
'SDO_FEATURE_USAGE'
;
3.执行sql tuning advisor
select sql_id,sql_text from v$sql where sql_text like '%SDO_FEATURE_USAGE%';
生成task：
DECLARE
my_task_name VARCHAR2(30);BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id =>
'20vymfjk9wqw8'
,
scope =>
'COMPREHENSIVE'
,
time_limit => 3600,
task_name =>
'sql_tuing_task'
,
description =>
'SQL TUNE TEST'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name =>
'sql_tuing_task'
);END;/
运行task，查看结果：
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
'sql_tuing_task'
) FROM DUAL;
按照建议执行新建两个索引，重新收集统计信息后，原sql执行耗时大大缩短，效果如下：
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69994536/viewspace-2761708/ ，如需转载，请注明出处，否则将追究法律责任。
