SQL Tuning Advisor簡介

1. 簡介

一直以來，如何有效的診斷和監控高負載的SQL對於DBA來說不是件容易的事情.但是從Oracle 10G 開始,ADDM的引入使得由資料庫自身參與監控成為可能.儘管通過ADDM發現出的高負載的SQL可能只會佔全部SQL的很小一部分比例,但是如何對這些語句進行調優依然需要經驗的積累.

2. 手工調優SQL的挑戰

對SQL語句手工調優需要很多的經驗和技巧,例如:
. 對SQL語句本身進行優化以便獲得更優的執行計劃;
. 合理地調整資料讀取方式(例如通過索引)以便能更快地訪問資料;
. 合理的設計SQL實現方式以實現最優的架構(例如:使用靜態SQL還是動態SQL)
當然,手工調優又是一件非常耗時的工作,因為:
1. 每條SQL可能都是唯一的,這意味著你需要分別優化不同的SQL;
2. 系統可能是很複雜的系統,SQL程式碼非常非常多;
3. SQL調整可能是一件永遠看不到終點的任務,因為系統中的SQL workload可能經常在變化;
4. 一條原來執行效率很高的SQL, 因為資料的改變或者存取方式的改變(例如刪除了索引或者索引已經失效)可能會導致效能的降低.
...

3. 自動SQL調優

SQL Tuning Advisor (SQL調優顧問)是Oracle10G中引入的新特性之一.設計它的目的就是為了代替傳統的手工SQL調整(DBA和開發人員在工作中往往要花費很多時間和精力在SQL調整上).
SQL調優顧問處理的物件包括那些響應時間很慢或者是佔用CPU/DISK很高的SQL.
SQ調優顧問收集這些SQL,並且給出自己的建議,它包括下面的部分:
. 怎樣調整SQL的執行計劃;
. 做出這條建議的理論原理;
. 優化後效率的提升幅度;
. 直接給出推薦使用的命令
使用者可以有選擇性地接受這些建議，然後去調優SQL.
隨著SQL調優顧問的引入,你現在就可以讓Oracle優化器來自動的為你調整SQL.

4. 概述: SQL調優顧問

首先，我們應該知道, SQl調優顧問只是調優過程的一個驅動程式.它只是以一種內部的，特殊的方式呼叫CBO來分析以下存在的物件:
4.1 缺失或者失效的統計資訊:
我們都知道，如果想讓CBO有效的工作，就必須提供最新的統計資訊.如果一個SQL語句中引用的物件的統計資訊不存在或者已經陳舊過時,它們就會被捕捉到, 並且作為建議的一部分;
4.2 SQL Profile(計劃調整分析):
CBO為一些特定的SQL收集一些後臺的資訊作為SQL profile的組成部分,它包括:
. SQL語句的歷史執行資訊
. 實際執行代價和估計執行代價的比較
. 謂詞選擇
實際上,CBO會在後臺執行一條SQL語句中的部分子句,以產生一個複雜的SQLprofile,這樣就使CBO能夠根據這些附加的資訊來為一個複雜的SQL產生更優的執行計劃.
SQL Profile被存放在資料字典中,因此不需要應用程式的干預.
4.3 SQL訪問路徑的分析:
有些情況下, CBO 會推薦使用一個新的索引，這個索引能夠提供更快的執行路徑, SQL Access Advisor可以完成這個工作.
4.4 重新構建SQL
CBO 會鑑定出哪些SQL語句的執行計劃非常糟糕，並且給出相關的建議去重新調整. 推薦使用的SQL語句可能與原有的SQL語句非常相似,因此DBA或者開發人員可以根據具體的應用來靈活的選擇.

5. 怎樣檢測缺失或者陳舊的統計資訊

使用從9i開始引入的DBMS_STATS包,例如:
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'CUSTOMERS',estimate_percent=>dbms_stats.auto_sample_size);
精確的統計資訊是CBO產生最優化執行計劃的前提條件.從Oracle 10G開始引入了Automaic StatisticsGathering(初始化引數STATISTICS_TYPE設成BASIC或者ALL),如果沒有啟用這個特性,統計資訊可能就會丟失或者陳舊.
在統計檢測模式下,Oracle優化器會執行一些額外的檢查以便驗證需要使用的統計資訊,並且會適時的提醒去產生需要的統計資訊.

6. More information aboutSQL profile

通常情況下,CBO會根據當前的統計資訊為一條SQL語句產生一個最優的執行計劃。隨著資料的改變或者存取方式的改變，這個計劃已經不是最優的執行計劃了,由於沒有可用的SQLProfile, CBO會一直認為當前的統計資訊還是很精確的,從而做出了錯誤的選擇. 而如果使用了SQL Profile,CBO會花費一些時間去根據這個SQL語句去搜集一些附加的資訊,從而得到更精確的統計資訊和產生更優的執行計劃，這個過程可能會比較耗時,因為優化器可能要做下面的嘗試:
. 執行原有SQL的一部分和使用動態抽樣技術來收集更準確的統計資訊;
. 進行更深層次的謂詞選擇: 例如分析列和列之間的相互關係，使用不同的JOIN 方式，以及使用更復雜的謂詞(加入函式等等)
. 必要的時候，資料統計資訊可能在統計檢測模式下就已經產生了,這樣就會避免統計資訊失效或者陳舊對CBO帶來的不良影響.
基於這些統計資訊, 優化器就會根據SQL Profile來進行自我調整.

7. More information about  SQL Analysis

簡而言之,SQL Analysis的作用就是幫助DBA和開發人員捕捉那些低效率的SQL, 並且提供自己的修改建議.
例如, NOT EXISTS 和 NOT IN的構造器很相似,但是不完全一樣.因此，需要你自己去決定應該使用哪一個.也正是基於這樣的考慮, CBO不會自動替你重寫整個查詢語句而只是提供建議:
SQl Analysis能夠檢測以下型別的SQL:
1. 在查詢中使用 NOT IN 而不是使用 NOT EXISTS
(說明: Oracle 10G中,如果相關表的相關列上有索引,CBO會為使用了 NOTIN的語句選擇最優的查詢計劃,而不是簡單的使用全表掃描)
2. 如果索引列上的資料型別不匹配，會引入更復雜的謂詞來預防笛卡兒集的產生

8. 怎樣去使用SQL TuningAdvisor

SQL Tuning Advisor 檢測的SQL來源於很多部分:
. ADDM 檢測到的高負荷的SQL
. 當前共享池中和遊標池中的SQL
. AWR快照中反映的SQL: 預設情況下,AWR會保持近一個星期的快照資訊,因此使用者可以使用近七天中任何一個時間段的歷史資訊來選擇需要調整的SQL語句.
. 使用者自定義的workload:使用者可以建立自定義的workload,例如根據自己的需要來選擇SQL語句,這些語句可能不是高負荷的，可能也不在當前的遊標池中
. 對於多個SQL語句,使用者可以建立SQL 優化集(SQL Tuning Set),一個STS可以儲存多個SQL語句以及它們的執行資訊:
執行上下文: 包括繫結變數和具體方案的解析資訊
執行的統計資訊: 執行的次數，平均執行的時間

9. 在EM 10G 中使用SQL AdvisorAnalyzer

STEP 1: 從下面的頁面找到有問題的SQL
. ADDM report page
. Top SQL page
. SQL Tuning Set (STS) page
STEP 2: 檢視 SQL Tuning Advisor 提供的建議
STEP 3: 根據這些建議去實施

對於普通使用者，需要以下許可權:
ADVISOR
CREATE JOB
EXECUTE ON SYS.DBMS_SQLTUNE

如果需要建立STS,還需要ADMINISTER_TUNING_SET的許可權

10. 呼叫DBMS_SQLTUNE包來使用SQLAdvisor Analyzer