Oracle中使用ADVISOR

Oracle中使用ADVISOR

 

ADVISOR真是oracle中超神奇的東西

不僅可以幫你分析語法,連建議都會跟你講

這邊做一下筆記

 

沒有權限的話要先建立建立權限

grant ADVISOR to prfuser

建立一個TUNING TASK


   my_task_name   VARCHAR (30);
   my_sqltext     CLOB;
   ouid           VARCHAR (5);
BEGIN
   ouid := 'QC06';
   my_sqltext :=
      'SELECT   *
                    FROM      user.XXX ps
                     INNER JOIN
                        user.YYY pc
                     ON ps.id = pc.id
                    WHERE   ps.id = :id';
   my_task_name := 
      dbms_sqltune.create_tuning_task (
         sql_text      => my_sqltext,
         bind_list      => sql_binds (anydata.convertnumber(9)),
         user_name     => 'USER',
         scope         => 'COMPREHENSIVE',
         time_limit    => 60,
         task_name     => 'sql_tuning_test',
         description   => 'tuning Task'
      );
END;

SELECT看看

clip_image001

然後把剛剛的TASK_NAME執行

exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

確認TASK已經被執行

SELECT * FROM USER_ADVISOR_TASKS

clip_image002

確認STATUS是COMPLETED

確認執行計畫

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

 

clip_image003_2

按照分析建議優化

最後就可以把建立的TASK刪除了

exec dbms_sqltune.drop_tuning_task('sql_tuning_test');

其實以上的步驟在toad也可以輕鬆的執行

步驟如下

database>Optimize>Oracle Tuning Advisor

clip_image004

按下新增

clip_image005

在SQL Text寫要分析的Sql Command,按下OK後就收成囉

clip_image006

畫面如下

clip_image007_thumb

然後就照著他的建議去做吧。