dbms_sqltune包的使用

dbms_sqltune包用於sql語句的調優，包括sql tuning advisor子程式、sql profile子程式、sql tuning set子程式、實時sql監控子程式、sql效能報告子程式。

簡單來說，Sql tuning advisor是一組建議，能自動調整sql語句，使其效能更好。

使用的步驟為：

（1）使用create_tuning_task函式為一條語句或一組語句建立調優任務

（2）使用execute_tuning_task儲存過程執行當前建立的調優任務

（3）由report_tuning_task顯示調優結果

（4）使用script_tuning_task函式建立可以執行推薦建議的指令碼

（一）sql tuning advisor

包括如下子程式:

CANCEL_TUNING_TASK ：取消當前調優任務

CREATE_SQL_PLAN_BASELINE ：為已存在的計劃建立基線

CREATE_TUNING_TASK ：Creates a tuning of a single statement or SQL tuning set for either the SQL Tuning Advisor

DROP_TUNING_TASK ：刪除調優任務

EXECUTE_TUNING_TASK ：執行調優任務

IMPLEMENT_TUNING_TASK ：匯入由sql tuning advisor推薦的sql profile

INTERRUPT_TUNING_TASK ：中斷當前執行的調優任務

REPORT_AUTO_TUNING_TASK：顯示自動調優任務的報告

REPORT_TUNING_TASK ：顯示調優結果

RESET_TUNING_TASK ：重置當前執行的調優計劃

RESUME_TUNING_TASK：重啟以前中斷的任務

SCRIPT_TUNING_TASK ：建立指令碼用於執行調優建議

SET_TUNING_TASK_PARAMETER：修改調優引數

1.建立調優任務

可以有多種輸入方式，包括：單條語句文字、快取中的語句、負載報告（快照）中的語句、sql tuning set、sql performance analyzer。

語法如下：

SQL text format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text IN CLOB,

bind_list IN sql_binds := NULL,

user_name IN VARCHAR2 := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

SQL ID format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

Workload Repository format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

begin_snap IN NUMBER,

end_snap IN NUMBER,

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

SQLSET format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

sqlset_name IN VARCHAR2,

basic_filter IN VARCHAR2 := NULL,

object_filter IN VARCHAR2 := NULL,

rank1 IN VARCHAR2 := NULL,

rank2 IN VARCHAR2 := NULL,

rank3 IN VARCHAR2 := NULL,

result_percentage IN NUMBER := NULL,

result_limit IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL

plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',

sqlset_owner IN VARCHAR2 := NULL)

RETURN VARCHAR2;

SQL Performance Analyzer format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

spa_task_name IN VARCHAR2,

spa_task_owner IN VARCHAR2 := NULL,

spa_compare_exec IN VARCHAR2 := NULL,

basic_filter IN VARCHAR2 := NULL,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

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

以下是我在以前一個專案中使用自動調優建議進行sql優化的例子：

使用sql tuning advisor自動生成調優建議。

如果某條sql有更優的計劃，則系統自動生成sql profile，可以使用DBMS_SQLTUNE.REPORT_TUNING_TASK看到；如果統計資訊過舊，調優建議中會提示進行統計資訊的收集；如果缺少索引，調優建議中會包含建立索引的語句。

如執行以下語句：

spool sql_tuning

SET LONG 10000000 LONGCHUNKSIZE 1000000 LINESIZE 150 pagesize 0 serveroutput on size 1000000

variable stmt_task VARCHAR2(64);

EXEC :stmt_task1 := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'xxxxxx');

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:stmt_task1 );

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :stmt_task1 ) from dual;

Spool off;

顯示結果如下：

TASK_NAME STATUS

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

tuning_sql_test1 COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TAS

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

GENERAL INFORMATION SECTION

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

Tuning Task Name : tuning_sql_test1

Tuning Task Owner : SYSTEM

Scope : COMPREHENSIVE

Time Limit(seconds) : 60

Completion Status : COMPLETED

Started at : 02/17/2014 17:29:00

Completed at : 02/17/2014 17:29:06

Number of SQL Profile Findings : 1

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

Schema Name: SYS

SQL ID : 9ta2yn452fyj6

SQL Text : SELECT DT.TABLE_NAME, DT.OWNER FROM DBA_TABLES DT WHERE DT.OWNER

= 'SGPMS' AND TABLE_NAME IN ('A_RCVBL_PL_FLOW',

'A_RCVED_PL_FLOW', 'A_RCVBL_FLOW', 'A_RCVED_FLOW',

'A_CASH_ENTRY', 'A_OTHER_RCVPAY_ENTRY', 'PUB_A_RCVBL_FLOW',

'A_PC_TRAN', 'PUB_P_MSG_SEND', 'A_ACCT_BAL ',

'S_SUBSCIBE_CONT', 'PUB_A_GP_RCVBL_FLOW', 'A_OTHERINCOME_ENTRY',

'A_CASHCHK_FLOW', 'A_DEPOSIT_ENTRY', 'A_TRANSIT',

'A_GP_AGREEMENT', 'PUB_A_ACCT_YM', 'A_REFUND', 'A_ACCT',

'A_ACCT_TRAN', 'A_INNER_RCVPAY', 'A_ACCT_VER')

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

為此語句找到了效能更好的執行計劃。

Recommendation (estimated benefit: 80.02%)

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

- 考慮接受推薦的 SQL 概要檔案。

execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_sql_test1',

replace => TRUE);

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

EXPLAIN PLANS SECTION

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

1- Original With Adjusted Cost

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

Plan hash value: 2987781044

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

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

| 0 | SELECT STATEMENT | | 85 | 14875 |

|* 1 | HASH JOIN RIGHT OUTER | | 85 | 14875 |

| 2 | TABLE ACCESS FULL | USER$ | 89 | 267 |

………………

|* 23 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 |

| 24 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 |

|* 25 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | |

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

Predicate Information (identified by operation id):

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

1 - access("CX"."OWNER#"="CU"."USER#"(+))

6 - access("T"."TS#"="TS"."TS#")

10 - access("KSPPI"."INDX"="KSPPCV"."INDX")

……

23 - access("T"."BOBJ#"="CO"."OBJ#"(+))

25 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))

2- Using SQL Profile

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

Plan hash value: 1758096801

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

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

| 0 | SELECT STATEMENT | | 66 | 11550 |

|* 1 | HASH JOIN | | 66 | 11550 |

| 2 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 |

……

|* 24 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | |

|* 25 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 |

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

Predicate Information (identified by operation id):

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

1 - access("KSPPI"."INDX"="KSPPCV"."INDX")

……

22 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND

"T"."BLOCK#"="S"."BLOCK#"(+))

24 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))

25 - access("T"."BOBJ#"="CO"."OBJ#"(+))

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

根據上面的提示，接受sql profile後就可以使語句效能提高80%。

以下圖片是負載報告中sql相關的欄位：

SQL> desc STAGE_TAB

Name Type Nullable Default Comments

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

NAME VARCHAR2(30) Y

OWNER VARCHAR2(30) Y

DESCRIPTION VARCHAR2(256) Y

SQL_ID VARCHAR2(13) Y

FORCE_MATCHING_SIGNATURE NUMBER Y

SQL_TEXT CLOB Y

PARSING_SCHEMA_NAME VARCHAR2(30) Y

BIND_DATA RAW(2000) Y

BIND_LIST PUBLIC.SQL_BIND_SET Y

MODULE VARCHAR2(48) Y

ACTION VARCHAR2(32) Y

ELAPSED_TIME NUMBER Y

CPU_TIME NUMBER Y

BUFFER_GETS NUMBER Y

DISK_READS NUMBER Y

DIRECT_WRITES NUMBER Y

ROWS_PROCESSED NUMBER Y

FETCHES NUMBER Y

EXECUTIONS NUMBER Y

END_OF_FETCH_COUNT NUMBER Y

OPTIMIZER_COST NUMBER Y

OPTIMIZER_ENV RAW(1000) Y

PRIORITY NUMBER Y

COMMAND_TYPE NUMBER Y

FIRST_LOAD_TIME VARCHAR2(19) Y

STAT_PERIOD NUMBER Y

ACTIVE_STAT_PERIOD NUMBER Y

OTHER CLOB Y

PLAN_HASH_VALUE NUMBER Y

PLAN PUBLIC.SQL_PLAN_TABLE_TYPE Y

SPARE1 NUMBER Y

SPARE2 NUMBER Y

SPARE3 BLOB Y

SPARE4 CLOB Y

使用方法舉例：

下面是一個從負載報告（快照）建立調優任務，載入、執行、報告、接受調優任務，將調優結果匯出到stage表中的一個完整例子。

variable sts_task varchar2(20);
DECLARE
  baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR; --宣告遊標型別
BEGIN
  --1、建立調優任務
  dbms_sqltune.create_sqlset(sqlset_name=>'my_workload',
  description=>'snapid from 368 to 373');
  --2、獲取負載報告的內容
  OPEN baseline_cursor FOR
    SELECT VALUE(p)
     FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap => 360,end_snap => 373)) p;
  --3、將負載報告的內容放入已經定義的STS中
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name     => 'my_sql_tuningset',
                           populate_cursor => baseline_cursor);
  --4、建立調優任務，將buffer_gets作為排序依據，在3600s內完成調優
  :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'my_workload',
  rank1 => 'BUFFER_GETS',
  time_limit => 3600,
  description => 'tune my workload ordered by buffer gets');
  --5、執行調優任務
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task);
END;
/

--6、顯示調優結果
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY') FROM DUAL;
--SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task) from dual;
--SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS',5) from dual;
--7、生成調優指令碼
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL;

  --8、建立stage表，用於存放sql tuning set
  --注意：db_version不能是當前系統的版本，否則報錯ORA-15703、ORA-06512、ORA-06512
  exec dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGE_TAB',schema_name => 'SCOTT',db_version => dbms_sqltune.STS_STGTAB_10_2_VERSION);

--9、將sql tuning set匯入到stage表
--這裡需要注意區分sqlset_name，需要使用的是load_sqlset中的sqlset_name

--另外，執行調優任務後如果系統沒有給出調優建議，下面的語句也會出錯
exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sql_tuningset',staging_table_name => 'STAGE_TAB',db_version => dbms_sqltune.STS_STGTAB_10_2_VERSION);

--10、刪除調優任務
exec dbms_sqltune.drop_tuning_task(task_name => :sts_task);

--11、刪除sql tuning set
exec dbms_sqltune.drop_sqlset(sqlset_name =>'my_workload');

（二）sql profile

sql profile通常是根據sql tuning advisor得到的。

使用方法為：

建立stage表，用於儲存非sys使用者的sql profile資訊：CREATE_STGTAB_SQLPROF

將sql profile資訊放入stage表：PACK_STGTAB_SQLPROF

刪除sql profile：DROP_SQL_PROFILE

接受sql profile：accept_sql_profile

這個子程式的使用可能相對較少。主要是接受和刪除操作。

總結一下：
sql tuning set就是將一系列我們感興趣的sql語句按照特徵存放在一起，方便呼叫和調優。
通過sql tuning advisor為單條sql、sql tuning set提出優化建議，部分建議中生成sql profile。Sql profile是對當前sql進行一系列調整，使其計劃更優的方法。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-1176794/，如需轉載，請註明出處，否則將追究法律責任。

dbms_sqltune包的使用

相關文章