SQL Tuning Advisor : dbms_sqltune

在Oracle10g之前，最佳化SQL是個比較費力的技術活，不停的分析執行計劃，加hint，
分析統計資訊等等，當然也有SQL調優輔助工具可以使用，只是要麼價格昂貴，要麼
效果不佳。在10g中，Oracle推出了自己的SQL最佳化輔助工具，這就是新的DBMS_SQLTUNE
包。執行DBMS_SQLTUNE包進行sql最佳化需要有advisor的許可權：

SQL> grant advisor to SFIS1;

使用DBMS_SQLTUNE包進行SQL最佳化，大致可以分為四個步驟：

建立最佳化任務
執行最佳化任務
顯示最佳化任務的結果
按照建議執行最佳化

 

一、建立最佳化任務

使用 dbms_sqltune.create_tuning_task 函式來建立最佳化任務，該引數的宣告如下：

FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
SQLSET_NAME VARCHAR2 IN
BASIC_FILTER VARCHAR2 IN DEFAULT
OBJECT_FILTER VARCHAR2 IN DEFAULT
RANK1 VARCHAR2 IN DEFAULT
RANK2 VARCHAR2 IN DEFAULT
RANK3 VARCHAR2 IN DEFAULT
RESULT_PERCENTAGE NUMBER IN DEFAULT
RESULT_LIMIT NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
PLAN_FILTER VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT

 

具體每個引數的含義請參考Oracle官方文件的說明。函式的返回值為建立的任務名。
下面我們建立一個叫sql_tuning_test的任務：

DECLARE
    my_task_name VARCHAR2 (30);
    my_sqltext CLOB;
   BEGIN
    my_sqltext := 'select  sfis1.C_PCB_PRINT_T.*, sfis1.C_PCB_PRINT_T.ROWID 
    from sfis1.C_PCB_PRINT_T  
    WHERE IN_TIME>=:V0001  
    AND IN_TIME<=:V0002 ' ; 
    my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
    bind_list => sql_binds (anydata.convertnumber (9)),
    user_name => 'SFIS1', 
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'sql_tuning_test',
    description => 'Tuning Task'
    );
    END;

 

二、執行最佳化任務

透過呼叫dbms_sqltune.execute_tuning_task過程來執行前面建立好的最佳化任務。

SQL> exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

 

 

三、檢查最佳化任務的狀態

透過檢視user_advisor_tasks/dba_advisor_tasks檢視可以檢視最佳化任務的當前狀態。

SQL> conn sfis1/sfis1

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test' ;

STATUS
———–
COMPLETED

 

 

四、得到最佳化任務執行的結果

透過dbms_sqltune.report_tuning_task函式可以獲得最佳化任務的結果。

SQL>SET LONG 999999
SQL>set serveroutput on size 999999
SQL>SET LINESIZE 100
SQL>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
SQL> conn sfis1/sfis1

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_test
Tuning Task Owner                 : SFIS1
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 09/01/2011 10:33:40
Completed at                      : 09/01/2011 10:33:40
Number of Errors                  : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SFIS1
SQL ID     : bjbvdub79t3td
SQL Text   : select  sfis1.C_PCB_PRINT_T.*, sfis1.C_PCB_PRINT_T.ROWID
                 from sfis1.C_PCB_PRINT_T
                 WHERE IN_TIME>=:V0001
                 AND IN_TIME<=:V0002

 

.......

 

然後我們就可以根據Recommendation部分的建議來執行最佳化操作了。

 

 

五、刪除最佳化任務

透過呼叫dbms_sqltuen.drop_tuning_task可以刪除已經存在的最佳化任務

SQL>exec dbms_sqltune.drop_tuning_task(‘sql_tuning_test’);

PL/SQL procedure successfully completed.