SQL Profiles-PartII--老熊

在上一篇《SQL Profiles-Part I》，我向大家介紹了什麼是SQL Profiles及其作用，如何使用SQL Tuning Advisor來生成SQL Profile，以及生成的SQL Profile產生的Hint。同時也介紹了SQL的signature。那麼在今天，將向大家介紹如何手工建立SQL Profiles（即不透過SQL Tuning Advisor)來達成2個目的：

• 鎖定或者說穩定SQL執行計劃。
• 在不能修改應用的SQL的情況下，來改變或者說是強制使SQL使用我們指定的執行計劃，即使原始的SQL包含了Hints。

那麼，這裡最關鍵的一點是，如何來手工建立SQL Profiles?
答案是，正如上一篇中有朋友的留言，使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE過程。

這個過程其名字與實際功能有所差異，其實可以理解為CREATE OR REPLACE SQL_PROFILE。過程中的PROFILE引數為SYS.SQLPROF_ATTR，這種型別其實就是VARCHAR2的集合型別(COLLECTION)：

下面我們就用這個過程來建立SQL PROFILE：
為避免干擾，將上一篇測試中生成的SQL Profile刪除掉，同時恢復T1表的統計資訊中的錶行數：

現在我們手工建立一個SQL Profile：

下面執行SQL Profiles對應的SQL：

可以看到，SQL使用了SQL Profile，不過沒有達到我們預期的效果。

看起來是SQL Profile使用的Hints有問題。我們重新設定SQL Profile的Hints，在Hints中加上“Query Block Name”。這一次在執行IMPORT_SQL_PROFILE過程時，將REPLACE引數設定為TRUE，以替換現有的SQL Profile：

再次執行下面的SQL：

這一次達到了預期的效果。看起來在SQL Profiles中對Hints還有一定的要求。
那麼我們再一次手工修改T1表的統計資訊，看看結果如何：

可以看到，Oracle最佳化器評估表T1經過Like條件過濾後返回的行數雖然很大，但是這裡的執行計劃仍然與未修改統計資訊之前一樣，使用range scan+ nested loop join。

透過以上的測試，我們明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用，同時也驗證了這種方式的有效性，SQL Profiles能夠像Outlines一樣，能夠穩定SQL的執行計劃。

接下來我們需要完成兩個任務。
任務一：對現有的SQL穩定其執行計劃。
這裡的問題是：穩定一條SQL語句的Hints從哪裡來？簡單的sql，沒問題，我們可以手工構造，但是複雜的SQL，手工構造相對比較複雜，同時手工構造的Hints不一定能夠保證SQL的執行計劃就會穩定。從10g開始，v$sql_plan中就包括了SQL語句OUTLINE資料，也就是穩定執行計劃的Hints。從下面可以看到：

上面所顯示的“Outline Data”即是我們穩定SQL執行計劃需要的Hints(我們甚至可以將這些Hints直接寫到我們的SQL中）。對需要穩定執行計劃的SQL，我們所要做的就是如前面所示，將Hints與SQL文字一起建立一個SQL Profile。這裡不得不提到一個SQL指令碼，來自MOS。”SQLT (SQLTXPLAIN) – Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]“，在這篇文章中，可以下載到sqlt.zip，這個壓縮檔案內有一個檔案：coe_xfr_sql_profile.sql。這個指令碼可以用於從shared pool、awr中提取指定的SQL ID的Outline Data並建立SQL Profile。下面是示例：

coe_xfr_sql_profile.sql這個指令碼首先要求輸入sql id，然後從shared pool、awr中獲取sql執行的各個執行計劃的統計資訊（執行計劃不穩定的SQL通常會有多個不同的執行計劃），然後輸入你認為是正確的、需要穩定的執行計劃的hash value，指令碼就會生成另一個指令碼，這裡為coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql，然後執行這個指令碼，就會建立出穩定執行計劃所需要的SQL Profile，SQL Profile的名字為：coe+sql_id+plan_hash_value，這裡為coe_b4zvp712np1bp_2959412835。注意，這裡建立的SQL Profile，force match預設為FALSE，我們可以手工修改指令碼將其改為TRUE，同時我們也可以按意願來修改生成的指令碼的其他內容。

除了上面提到的指令碼，這個BLOG裡面也有許多與SQL Profiles相關的指令碼。其中create_sql_profile.sql可完成類似的功能，只不過功能相對簡單，只能從shared pool中生成SQL Profile，因此也更方便。

任務二：在不能修改SQL的情況下改變並固定SQL的執行計劃，即使原始的SQL使用了Hints。
常常遇到這樣的情況，SQL語句其執行計劃有問題，或者是SQL使用了錯誤的Hints（比如 /*+ RULE */）導致SQL效能較差，但是應用又不能修改或者時間內不能修改，那麼我們怎麼來改變SQL的執行計劃呢。有3種辦法，一種是調整統計資訊，這個不建議使用，因為比較複雜、不穩定可靠（統計資訊可能會重新收集），影響面廣（會影響其他訪問此物件的SQL）。第二種是使用OUTLINE，這種方法比較複雜。第三種就是我們今天要介紹的使用SQL Profiles了。

使用SQL Profiles來改變SQL的執行計劃，其本質上就是使用Hints來改變SQL的執行計劃。對於簡單的SQL，我們同樣可以像前面一樣手工構造Hints然後再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE來實現。但是這種方法還是略顯煩瑣。那麼通常的方法就是”乾坤大挪移“了：

• 取得原始SQL的文字（如有可能還包括sql id)
• 構造一個與原始SQL在邏輯上、結構上完全相同的SQL。這裡強制邏輯上和結構上相同，SQL解析的使用者名稱、SQL中引用物件的使用者名稱甚至是一些predicate條件都可以不同。當然能夠與原始SQL完全一樣就更省事。
• 執行我們構造的SQL，並取得構造的SQL的Outline Data。
• 使用原始SQL的文字和構造的SQL的Outline Data建立SQL Profile。

下面我們來演示一下整個過程。我們這裡要修改執行計劃的SQL是：

我們首先需要執行這一條SQL，然後取得的SQL ID為gmvb9bp7f9kqd：

然後我們構造一條SQL，讓這條SQL按我們希望的執行計劃執行，構造的SQL其ID為cymak300cycmd：

然後使用coe_xfr_sql_profile.sql指令碼來提取我們構造的SQL的Outline Data，生成的結果為coe_xfr_sql_profile_cymak300cycmd_3787413387.sql，開啟結果檔案，可以看到有這麼一段：

再針對gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql，生成的結果檔案為coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql。手工修改這個檔案，將裡面h := SYS.SQLPROF_ATTR…那一段替換成我們之前得到的那一段。這一次我們將這個檔案中的force_match從FALSE改成TRUE。
最後我們執行coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql這個指令碼檔案：

這樣就完成了我們所需要的SQL Profile的建立。下面再看看原來的SQL執行情況（這裡我故意將like條件改了一下，以檢視force match是否起作用）：

可以看到SQL Profile起作用了。

最後一步，生成SQL Profile時稍顯複雜，不過我們可以修改之前提到的create_sql_profile.sql檔案來達到同樣的目的，只不過前幾個步驟仍然是不可省略的。將裡面的程式碼：

改為

注意這裡modi_sql_id和modi_child_no為我們構造的SQL執行後的id及child_number。同時這2個變數在檔案前面需要定義，此處不再細述。

小結：本文承接上一篇，介紹瞭如何利用SQL Profile來穩定執行計劃；如何利用SQL Profile來改變SQL的執行計劃。對於SQL Profiles來說，不屬於任何一個使用者，比Outlines更具有操控性靈活性。對於SQL Profiles的category，這裡不做介紹，有興趣的朋友請參考文件。

–The end—

--&gt>轉載於: