f Oracle SQL Tuning Advisor 你手頭上的SQL 語句 ~ 迪貝之家

Oracle SQL Tuning Advisor 你手頭上的SQL 語句

 declare

  TaskID  varchar2(500);

  VAR_SQL     varchar2(2000);

begin

  -- 要被Tune 的SQL

  VAR_SQL := 'delete STEVEN.SAMPLE '

          ||' where  ( code, dis  ) in ( select a.id ,b.dis '

  ||' from   STEVEN.SAMPLE a, STEVEN.RE b '

  ||' where  a.system_id = b.system_id '

  ||' and   to_char(a.create_date,''YYYYMMDD'') >= ''20101001'' )';

  

  -- 建立  Tuning Task

  TaskID := dbms_sqltune.create_tuning_task(

             sql_text => VAR_SQL

           , user_name => 'Your Schema'       

           , scope => 'COMPREHENSIVE'  

           , task_name => 'STEVEN' 

           );

  

  -- 執行 Tuning Task

  dbms_sqltune.execute_tuning_task( 'STEVEN' );

end;

/


== 查看 Tuning Task 狀態==

select status

  from dba_advisor_log

 where owner = 'Your Schema'

   and task_name = 'STEVEN';


== 查看Recommandation=====

SET long 2000000000 <-這很重要, 太小, Recommandation出不來

SET LONGCHUNKSIZE 1000

SET LINESIZE 100

select dbms_sqltune.report_tuning_task( 'STEVEN' )

  from dual;

這是結果:

-------------------------------------------------------------------------------

ADDITIONAL INFORMATION SECTION


DBMS_SQLTUNE.REPORT_TUNING_TASK('STEVEN')                                                           

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

- 不支援的 SQL 敘述句類型.

哈~~  SQL  寫太爛了...這種寫法是我曾經看過PG寫過的IN Group的語法
我就寫來看看....果然是有很嚴重的效能問題
連工具都不願意做調整

================================================
如果有sys權限,就直接看ADDM報表了
SQL> select task_name, status from dba_advisor_tasks where task_id=
(select max(task_id) from dba_advisor_tasks where status='COMPLETED' );

ADDM:4005309656_8927 COMPLETED


SQL> SET LONG 1000000 PAGESIZE 0;
SQL>SELECT DBMS_ADDM.GET_REPORT('ADDM:4005309656_8927') from dual;

Tuning Advisor 的權限 :
grant all on dbms_advisor to urname;
grant advisor to urname;
grant ADMINISTER SQL TUNING SET to urname;