SQL Profiles-Part I--老熊

Oracle 11g從釋出到現在，也有幾個年頭了。而在國內來說，Oracle 10g仍然是主流，甚至一些電信運營商的核心繫統仍然在使用9i。作為Oracle 10g的一項新特性，SQL Profiles被使用得並不太多。不管是在論壇、個人的BLOG還是其他一些地方，SQL Profiles的介紹也相對較少。對我個人來說，已經在多個最佳化場合中使用SQL Profiles，在這裡向大家介紹SQL Profiles，就是希望能夠了解Oracle資料庫的這一功能。

SQL Profiles可以說是Outlines的進化。Outlines能夠實現的功能SQL Profiles也完全能夠實現，而SQL Profiles具有Outlines不具備的最佳化，個人認為最重要的有2點：

• SQL Profiles更容易生成、更改和控制。
• SQL Profiles在對SQL語句的支援上做得更好，也就是適用範圍更廣。

關於這2方面的優點，我後面會詳細地闡述。

現在我在使用Outlines的場合，均使用SQL Profiles來替代。有一次準備對1條SQL語句使用Outline進行執行計劃的穩定，結果使用Outline之後，系統出現大量的library cache latch的爭用，不得不關閉Outline的使用，但是改用SQL Profiles不再有這個問題。這或許是個BUG，不過既然能用SQL Profiles代替，也就沒再深入去研究這個問題。

使用SQL Profiles無非是兩個目的：

• 鎖定或者說是穩定執行計劃。
• 在不能修改應用中的SQL的情況下使SQL語句按指定的執行計劃執行。

那麼SQL Profile到底是什麼？在我看來，SQL Profile就是為某一SQL語句提供除了系統統計資訊、物件（表和索引等）統計資訊之外的其他資訊，比如執行環境、額外的更準確的統計資訊，以幫助最佳化器為SQL語句選擇更適合的執行計劃。這些說法顯得比較枯燥，還是來看看下面的測試。

首先建2個測試表：

然後看看下面這一條SQL：

這裡省略了SELECT出來的具體資料，但是我們關心的是返回的結果行數、執行計劃以及邏輯讀這些資訊。
首先從執行計劃可以看到，這條SQL語句在2個表上都是全表掃描。在第1個表T1上，有 like ‘%T1%’這樣的條件，導致只能全表掃描，這沒有問題。但是第2個表，也是全表掃描，這裡有沒有問題呢？或者說是有沒有最佳化的餘地，答案顯然是肯定的。
這裡的問題在於執行計劃ID=1的那一行，Oracle最佳化器評估T1 like ‘%T1%’返回的結果行數為2498行，即T1表總行數的5%，如果2個表採用index range scan+nested loop連線，oracle評估的成本會高於full table scan+hash join。下面可以看到Oracle最佳化器評估的index range_scan+nested loop的成本：

從執行計劃可以看到Oracle最佳化器評估的成本為5061，遠遠高於原來的219。
但是實際的邏輯讀是多少呢？

加了HINT之後實際的邏輯讀只有290，低於原始SQL的932。所以這裡可以看出來，由於Oracle最佳化器過高地估計了T1表經過like操作過濾返回的行數，也就過高地估計了nest loop的成本，最終也就選擇了不是最優的執行計劃。

下面我們用Oracle的SQL Tuning Advisor來嘗試這條SQL：

上面程式碼中的sql_id是從v$sql來，對應的是沒有加hint的SQL。
結果看起來非常棒，SQL Tuning Advisor為我們找到了理想的執行計劃，T1表上經過謂詞過濾後返回的行數評估為29，相當地精確。我們要做的就是Accept SQL Profile，接受這個SQL Profile。

那麼我們再執行其他的類似SQL看看：

這一次，儘管我們更改了LIKE 後面的值，但是執行計劃與SQL Tuning Advisor產生的執行計劃完全一樣。從執行計劃的”Note“一節也可以看到，SQL Profile起作用了。SQL Profile的名字為”SYS_SQLPROF_014b39f084c88000″。

一些複雜的SQL，我經常會先透過SQL Tuning Advisor來分析一下，看能不能讓Oracle自已找出一個更好的執行計劃。

我們來看看，SQL Profiles實際上是些什麼：

從sys.sqlprof$attr這個數字字典裡面，我們可以看到兩樣東西：signature和attr。
signature是什麼？可以理解為與sql_id、sql_hash_value類似的值，用來標識SQL。在10g以上的版本中，檢視v$sql的定義就可以發現2列：exact_matching_signature、force_matching_signature。透過下面的資料可以看出區別：

從上面的資料可以看出：

• 第2、3條SQL的exact_matching_signature相同，第7、8、9、10條SQL的exact_matching_signature相同。
• 第2、3條SQL的force_matching_signature相同，第4、5條SQL的force_matching_signature相同，第7、8、9、10條的SQL的force_matching_signature相同。第1、6條SQL的force_matching_signature相同

有如下的結論：對SQL語句，去掉重複的空格（不包括字元常量），將大小寫轉換成相同，比如均為大寫（不包括字元常量）後，如果SQL相同，那麼SQL語句的exact_matching_signature就是相同的。對SQL語句，去掉重複的空格（不包括字元常量），將大小寫轉換成相同，比如均為大寫（不包括字元常量），然後去掉SQL中的常量，如果SQL相同，那麼SQL語句的force_matching_signature就是相同的。但是例外的情況是：如果SQL中有繫結變數，force_matching_signature就會與exact_matching_signature一樣的生成標準。

可以看到，現在exact_matching_signature與force_matching_signature完全一樣了。
從force_matching_signature的特性，我們可以想到一個用途，用於查詢沒有使用繫結變數的SQL語句，類似於使用plan_hash_value來查詢。

回到前面，accept_sql_profile這個過程，force_match引數設為TRUE，那麼dba_sql_profiles中的signature則是由SQL的force_matching_signature而來，否則便是exact_matching_signature。對於Outlines來說，則只能是exact_matching_signature。從這個角度上講，Sql Profiles比Outlines的使用範圍更廣，因為Sql profiles對沒有使用繫結變數的SQL也支援得很好。值得注意的是，Sql profiles的force_match屬性是不能更改的，只能在建立時指定，如果要更改，則只能重新建立改Sql Profile。

下面來看看sys.sqlprof$attr資料字典。這裡面沒有SQL Profile的名字，而是用的sql的signature。大家從attr_val的結果發現了什麼？

可以看到，SQL Profiles的attr_val實際上就是一些Hints，這跟Outlines沒有本質上的區別。只是SQL Profiles中的Hint，沒有指定SQL使用哪個索引，也沒有指定表的連線方法和連線順序。這裡只指定了T1表評估返回的行數，與原始的評估返回的行數的放大縮小的倍數。2498*0.01161091426正好為29。這裡就是告訴Oracle最佳化器，T1表經過謂語過濾後返回行數應該為評估的0.01161091426倍。從這裡可以看出，SQL Profiles並不會鎖定SQL的執行計劃，只是提供了更多、更準確的統計資訊給最佳化器。看下面的測試：

將T1表的統計資訊中的錶行數改為500萬，Oracle就會評估為返回5000000*5%*0.01161091426=2903行。這裡執行計劃又變回為full scan+hash join。可以看到，雖然SQL Profile起作用了，但是並沒有鎖定執行計劃。

小結：本文簡單介紹了什麼是SQL Profiles及其作用，如何使用SQL Tuning Advisor來生成SQL Profile，以及生成的SQL Profile產生的Hint。同時也介紹了SQL的signature。

下一篇將會介紹如何手工來為建立、生成SQL Profile，以及如何讓SQL Profile也能像Outlines一樣鎖定SQL的執行計劃，以保持SQL執行計劃的穩定性。

--&gt>轉載於: