Oracle 11g系統調優之dbms_sqltune包的使用

前沿：隨著資料庫版本的提升，Oracle也提供了越來越多的效能診斷工具，針對SQL的調優，DBMS_SQLTUNE就是其中一個比較優秀的包。
DBMS_SQLTUNE最開始是在10G裡面出現，11G裡面則對其進行了加強，使得其更加符合實際需求。

1.查詢系統可能存在問題的SQL

一般什麼樣的SQL可能會存在效能問題呢？
我們第一時間能想到的肯定是執行時間很長的SQL、其次是IO很高的SQL，這裡就針對執行時間很長的SQL來做測試。
獲取類似的SQL有多種方法，AWR、ADDR、動態檢視等，這裡我們就透過動態檢視v$session_longops來獲取，因為這個檢視裡面的語句是最近執行的，有比較強的及時性。

以下語句可查詢最近資料庫中執行時間比較長的SQL，包括執行時間。

執行結果如下：


其中SUMS列為此SQL執行的總時間，上面我主要選取了‘Table Scan’這個型別的操作作為主要的時間損耗，從實際上來看也是如此，表掃描的方式直接關係SQL執行的效率，
表掃描佔整個SQL執行時間的比重最大。

從上面，我們選取一條SQL，ID為“3c3ch9a4xdwn1”作為需要最佳化的SQL。


2.DBMS_SQLTUNE包
DBMS_SQLTUNE包提供了很多的子程式來對SQL進行診斷和對執行計劃進行處理，這裡我們只是簡單的測試一下DBMS_SQLTUNE的調優功能，主要涉及到3個子過程。

DBMS_SQLTUNE.CREATE_TUNING_TASK    #建立一個SQL調優任務

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,                                             ---------->SQL ID，必填項
  plan_hash_value  IN NUMBER    := NULL,                           ----------->執行計劃的HASN值(選填)
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,  ----------->任務型別，有limited和comprehensive兩種
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,       ----------->此任務最長的執行時間
  task_name        IN VARCHAR2  := NULL,                           ----------->任務名
  description      IN VARCHAR2  := NULL)                             ----------->任務描述
RETURN VARCHAR2;

EXECUTE_TUNING_TASK                              #執行一個SQL調優任務

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,                                      ------------>任務名
   execution_name    IN VARCHAR2               := NULL,         ------------>執行時的名稱，可為空
   execution_params  IN dbms_advisor.argList   := NULL,        ------------>執行引數，預設可為空
   execution_desc    IN VARCHAR2               := NULL);         ------------>執行描述

DROP_TUNING_TASK                                   #刪除一個SQL調優任務

DBMS_SQLTUNE.DROP_TUNING_TASK(
 task_name         IN VARCHAR2);                                      ------------->任務名


ACCEPT_SQL_PROFILE                               #接受及應用一個SQL_PROFILE執行計劃給某條SQL

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name    IN  VARCHAR2,                                         -------------->執行最佳化的任務名
   object_id    IN  NUMBER   := NULL,                                -------------->物件編號，一般不填
   name         IN  VARCHAR2 := NULL,                               -------------->制定的sql_profile名稱，如果不填則由系統指派
   description  IN  VARCHAR2 := NULL,                               -------------->該執行計劃的描述資訊
   category     IN  VARCHAR2 := NULL);                              ------------->需要與該SESSION的sqltune_category引數相匹配
   task_owner   IN VARCHAR2  := NULL,                             ------------->任務的所有者
   replace      IN BOOLEAN   := FALSE,                               ------------->如果此sql_profile已存在，則決定是否替換，預設值為不替換
   force_match  IN BOOLEAN   := FALSE,                           ------------->是否強制匹配此執行計劃與所有HASH值相同的SQL，類似CURSOR_SHARING引數的FORCE
   profile_type IN VARCHAR2  := REGULAR_PROFILE);          ------------->sql_profile的型別，預設為REGULAR_PROFILE，可修改為PX_PROFILE，表示此執行計劃變更為並行執行


DROP_SQL_PROFILE                                     #刪除一個SQL_PROFILE的應用，讓系統自動選擇

DBMS_SQLTUNE.DROP_SQL_PROFILE (
   name          IN  VARCHAR2,
   ignore        IN  BOOLEAN  := FALSE);



3.具體演示過程

SQL Profile是10g中的新特性，作為自動SQL調整過程的一部分，由Oracle企業管理器來管理。除了OEM，SQL Profile可以透過DBMS_SQLTUNE包來進行管理。

查詢最佳化器有時候會因為缺乏足夠的資訊，而對一條SQL語句做出錯誤的估計，生成糟糕的執行計劃。而自動SQL調整透過SQL概要分析來解決這個問題，自動調整最佳化器會生成這條SQL語句的一個概要，稱作SQL Profile。它由針對這條語句的一些輔助統計資訊組成，透過取樣和區域性執行技術來確認，必要的話，會調整執行計劃中的估計值。在SQL概要分析中，自 動調整最佳化器還可以透過一條SQL語句的執行歷史資訊來設定合適的最佳化器引數，比如將OPTIMIZER_MODE引數由ALL_ROWS改為 FIRST_ROWS。

換句話說，SQL概要是一個物件，它包含了可以幫助查詢最佳化器為一個特定的SQL語句找到高效執行計劃的資訊。這些資訊包括執行環境、物件統計和對查詢優 化器所做評估的修正資訊。它的最大優點之一就是在不修改SQL語句和會話執行環境的情況下影響查詢最佳化器的決定。(《Oracle效能診斷藝術》)

SQL Profile中包含的並非單個執行計劃的資訊，必須注意的是，SQL Profile不會固定一個SQL語句的執行計劃。當表的資料增長或者索引建立、刪除，使用同一個SQL Profile的執行計劃可能會改變，而儲存在SQL Profile中的資訊會繼續起作用。然而，經過一段很長的時間之後，它的資訊有可能會過時，需要重新生成。

SQL Profile的作用範圍由CATEGORY屬性來控制，這個屬性決定了哪些使用者會話可以應用這個概要。你可以從DBA_SQL_PROFILES中的 CATEGORY欄位來檢視這個屬性。預設情況下，所有概要檔案都建立為DEFAULT範疇，這意味著所有SQLTUNE_CATEGORY初始化引數為 DEFAULT的使用者會話都可以使用這個概要。你可以修改這個屬性，比如將其改為DEV，則SQLTUNE_GATEGORY引數為DEV的使用者會話才能 使用它，利用這個功能，你可以在一個受限制的環境中來測試一個SQL Profile。

簡單來說，Sql_Profile是用來影響資料庫執行計劃生成的一組資訊檔案的集合，可以在不改變原有SQL語句的前提下，達到類似HINTS改變其執行計劃的目的。


建立一個sqltune的調優任務(即建立sql_profile的相關資訊)

記住，DBMS_SQLTUNE.CREATE_TUNING_TASK是一個函式，必須要有返回值。
上面在定義了一個TASK後，可透過DBMS_SQLTUNE.EXECUTE_TUNING_TASK來執行此調優過程。


獲取調優任務的詳細資訊

可透過dbms_sqltune.report_tuning_task，輸入任務名，及可獲取調優的相關資訊，如下：

重點關注紅字部分，即為SQL_TUNE任務給出的調優建議，我們看到對於上面的語句，ORACLE建議其並行執行，下面也列出來了當前的執行計劃和調整後的執行計劃。


對上面的SQL_PROFILE進行應用

查詢已存在的SQL_PROFILE
select * from DBA_SQL_PROFILES;

DBA_SQL_PROFILES檢視可檢視當前系統中所有的SQL_PROFILES資訊。


查詢已存在的SQLTUNING TASK
select * from USER_ADVISOR_TASKS
USER_ADVISOR_TASKS檢視可用來檢視當前使用者下所建立的調優任務


刪除已應用的SQL_PROFILE

刪除當前使用者建立的SQLTUNING TASK