SQL Profile（第一篇）

SQL Profile是什麼

SQL Profile是我非常喜歡的一個技術，透過它可以在不修改SQL語句的情況下，為SQL繫結任何需要的hint，與SQL程式碼是分離的，而且相對Baseline、outline來說，操作步驟簡單，如果使用熟練可以在極短的時間內修正一個查詢語句的執行計劃，我已經在多個場合使用SQL Profile解決了資料庫的效能問題。SQL Profile是ORACLE 10G出現的一個功能，SQL Profile在Oracle文件中被描述為作為SQL Tuning Advisor的一部分，只能透過SQL Tuning Advisor來使用，一般是透過執行SQL Tuning Advisor的JOB，JOB執行結束後可以檢視SQL Tuning Advisor給出的最佳化建議，這些建議裡可能會包含讓DBA採用SQL Profile（hint的集合），作為ORACLE 10G後提供的新功能，DBA可以將SQL調優的工作交給SQL Tuning Advisor來做。正常情況下，一個SQL語句交給SQL 最佳化器後，最佳化器需要在非常短的時間內，給出解析結果，但是SQL Tuning Advisor卻不同，它為了產生一個高效的執行計劃，可能會花費很長的時間。進一步講，它還會使用一些非常耗時的技術如Wath-If 分析，並加強對動態取樣技術的利用來核實最佳化器的估計值。SQL Tuning Advisor的任務是分析SQL語句並建議如何使用一些方法來提高語句的效能，包括收集遺漏或過時的物件統計資訊，建立新索引，改變SQL語句或者採用SQL Profile。按照官方的說法，SQL Profile只能透過SQL Tuning Advisor來使用，但是本節後面的幾個章節都會使用手工建立SQL Profile的方式來告訴讀者如何更快速的使用、建立SQL Profile，但是手工建立SQL Profile並不被ORACLE技術支援所支援。

簡單來說SQL Profile是一個物件，包含了一系列的hint，這些hint包含了可以幫助最佳化器為一個特定的SQL語句找到高效執行計劃的額外資訊。這些hint包含執行環境，物件統計資訊，和對查詢最佳化器所作評估的修正資訊，例如廣為流傳的OPT_ESTIMATE就是由SQL Profile所引入的hint，它主要的作用是設定物件統計資訊、放大或縮小最佳化器的評估基數、修正表連線的基數，例如，select * from a where status='Inactive'，真實的返回基數為10000，但是由於統計資訊的陳舊，導致最佳化器認為只返回100，因此透過OPT_ESTIMATE會糾正這一資訊，例如透過OPT_ESTIMATE(@"SEL$1", TABLE, "A"@"SEL$1", SCALE_ROWS=100)來告訴最佳化器，對於表A經過謂詞過濾後返回的基數為：最佳化器的評估基數再放大100倍，也就是最佳化器評估基數100，再乘以放大係數100，最終的基數為10000（注意OPT_ESTIMATE等hint的糾正資訊並不會儲存、更新到表、索引等物件的統計資訊裡）。從這裡我們也可以看出，SQL Profile並不是真正的鎖定執行計劃，而只是告訴最佳化器一些更加真實的資訊，讓最佳化器根據真實的資訊來得到更加合理的執行計劃。因此，即使一個SQL使用了SQL Profile，最佳化器在選擇執行計劃的時候也具有很大的靈活性，隨著資料的變化，時間的推移，這些修正因子可能也已經不再準確，因此可能會出現在某些使用了SQL Profile的SQL,剛開始這些SQL Profile工作的很好，但是不久就會產生一些問題。不過SQL Profile跟outline、Baseline一樣都是基於儲存一些hint來發揮作用的，SQL Profile雖然預設不會使用例如index、full等我們常見的hint，但是這些hint依然可以在SQL Profile裡發揮作用。

SQL Profile在10G和11G預設是被開啟的，可以透過設定引數SQLTUNE_CATEGORY為false來關閉SQL Profile。每個SQL Profile都被放置在一個特定的category中，可以在建立SQL Profile時指定category的值，如果不指定會被放在SQLTUNE_CATEGORY為defualt的category中。如果SQLTUNE_CATEGORY的值被設定為非default的值，那麼只有SQL Profile的category的值為引數SQLTUNE_CATEGORY設定值的才會生效。利用這一特性來校驗SQL Profile的效能是一種很好的方式，本章後面將會對這一技術進行詳細說明。

n Note:SQL Profile可以為SQL語句新增任何hint，例如：bind_aware這個hint並不能透過SQL Baseline起作用，但是可以透過SQL Profile起作用。因為SQL Profile作為一種基於hint修正SQL執行計劃的機制，做的比較傻瓜化，它僅僅是把hint應用到特定簽名的SQL上，不會做其他校驗，SQL Baseline不僅僅只是應用hint，還需要做plan_hash_value值的計算校驗,因此對於bind_aware這種hint並不能對SQL Baseline起作用，因為這個hint會導致執行計劃的不穩定性，而Baseline建立的時候是跟具體的plan_hash_value掛鉤的，不能與這種具有不穩定性的hint掛鉤。 n SQL Profile可以在DataGuard中使用，也就是說在主庫建立SQL Profile後，備庫可以自動使用到在主庫上建立的SQL Profile，但是Baseline不能在DataGuard中使用。