ITPub博客

首页 > 数据库 > Oracle > 使用sql tuning advisor优化sql

使用sql tuning advisor优化sql

原创 Oracle 作者:zhcunique 时间:2021-03-08 14:03:21 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2021-02-04

  • 博文量
    63
  • 访问量
    460000