Oracle Database 使用 Advisor 了解 SQL Tuning 方向

以前,

公司曾經購買過 SQL Expert 來做 SQL Tuning,

但是它是根據 Database 現有環境進行 Tuning,

若你缺少 Index, 它不會告訴你要建 Index,

若你需做 Analyze, 它也不會告訴你,

總之, 不好用也不符合實際需求,

現在,

在 Oracle Database 10g 以上的版本,

Oracle DB 已內建非常好用的 SQL Tuning Advisor,

透過簡單的 dbms_sqltune 套件,

Oracle 會建議你 SQL Tuning 方向 (改善 Database 環境),

如: 建立 Index or Analyze ... 等

範例步驟, 如下 :

 建立與執行 Oracle SQL Tuning 任務

declare
  vTaskID  varchar2(255);
  vSQL     varchar2(1000);
begin
  -- 要進行 SQL Tuning 的語法
  vSQL := 'select ujc.ip_address'
       || '     , ujl.action_object'
       || '     , ujl.action_status'
       || '  from uf_javaftp_control ujc'
       || '     , uf_javaftp_log     ujl'
       || ' where ujc.ip_address = ujl.ip_address';
  
  -- 建立 Oracle SQL Tuning 任務
  vTaskID := dbms_sqltune.create_tuning_task(
             sql_text => vSQL
           , user_name => 'APPS'       -- 用哪個 DB Schema 進行 SQL Tuning
           , scope => 'COMPREHENSIVE'  -- Tuning 範圍
           , task_name => 'tomTuning'  -- Tuning 任務名稱, 隨便給一個
           );
  
  -- 執行 Oracle SQL Tuning 任務
  dbms_sqltune.execute_tuning_task( 'tomTuning' );
end;

 查看某個 Tuning Task 狀態

select status
  from dba_advisor_log
 where owner = 'APPS'
   and task_name = 'tomTuning';

 查看 Oracle SQL Tuning 結果

select dbms_sqltune.report_tuning_task( 'tomTuning' )
  from dual;
 
-- 查看結果:
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : tomTuning
Tuning Task Owner                 : APPS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 02/08/2010 10:37:44
Completed at                      : 02/08/2010 10:37:44
Number of Index Findings          : 1
 
-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : 2cc2vmca5jjsy
SQL Text   : select ujc.ip_address     , ujl.action_object     ,
             ujl.action_status  from uf_javaftp_control ujc     ,
             uf_javaftp_log ujl where ujc.ip_address = ujl.ip_address
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
 此敘述句的執行計劃可以藉由
 
  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - 考慮執行「存取建議程式」來改進實
    create index APPS.IDX$$_01280001 on APPS.UF_JAVAFTP_CONTROL('IP_ADDRESS');
 
  Rationale
  ---------
建立建議的索引可大幅改進此敘述句的執行計劃. 然而, 最好是使用代表性的 SQL 
這將可獲得廣泛的索引建議 (將索引維護成 ...

 刪除 Tuning 任務

begin
  dbms_sqltune.drop_tuning_task( 'tomTuning' );
end;

最後, 程式邏輯引起的效能問題, 這部分還是得靠自己.

以上, 希望對各位有所幫助.

昭佑.天翔