使用sql tuning advisor最佳化sql

zhcunique發表於2021-03-08
SQL

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部落格 ” ，連結：http://blog.itpub.net/69994536/viewspace-2761708/，如需轉載，請註明出處，否則將追究法律責任。

相關文章