Oracle 11g 新特性--自適應游標共享

作者：開心果譯 2009-01-11 08:16:00

任何SQL語句優化引擎的核心是判定獲得SQL語句目標數據的最廉價和最有效的訪問路徑的能力，使用綁定變量可以限制需要存儲在庫緩存中返回相似結果集的SQL語句游標的數量。本文介紹Oracle 11g中的新特性——自適應游標共享，并說明實際情況中該如何使用它，以及如何限制帶有綁定變量的SQL語句的硬解析，同時提高這些語句的執行效率。

正如我過去做老師時告訴學生的那樣，我有一個好方法可以避免數據庫性能降低，說來也很簡單，就是要求應用程序開發人員不要在數據庫運行任何SQL語句，但這的確引來不少人的笑聲和嘲笑，還是回到現實中來吧，討論一下建立有效SQL語句的途徑，我認為沒有什么標準可供借鑒，但構造不佳的SQL語句遲早會導致聯機事務處理(OLTP)系統、決策支持系統(DSS)或混合數據庫表現不佳。

“偷窺”綁定變量：這不是作弊

幸運的是，Oracle數據庫為我們提供了一些很好的工具，用以確定需要改善性能的SQL語句，并為提升它們的性能提供建議，這個方法的核心是當確實需要一個新的執行計劃時，我們可以修改基于成本的SQL優化器解析SQL語句的方式。當然，當一條語句***次執行時必須硬解析，這樣優化器可以確定獲取所需數據的***路徑，因為解析是一個相對費時的操作，因此，DBA通常會限制存儲在庫緩存中的唯一性游標的數量，特別是在聯機事務處理環境下，相同的語句可能會被執行成百上千次，它們為用戶會話返回相似的結果集。

設置有效的游標共享最簡單的方法就是按照應用程序工作量的需要為CURSOR_SHARING初始化參數設定合適的值，將這個參數的值設為SIMILAR告訴優化器當SQL語句完全相同，除了謂詞部分外，可以使用游標共享，執行計劃提供相等或更優的性能，同樣，將其值設為CURSOR_SHARING時，不論是否存在更好的執行計劃，告訴優化器強制共享游標，當SQL語句包含綁定變量時，有極好的機會提供這方面的性能優勢，但當優化器在不知道是什么值來填充產生的游標時，怎樣才能構建一個有效的執行計劃呢?

早在Oracle 9i就進行了一些嘗試，為了克服可能出現的非***的執行計劃，引入了綁定變量偷窺，顧名思義，當一個包含綁定變量的SQL語句***執行時，Oracle會快速查看這些綁定變量的一個真實值，以便構建一個***的執行計劃，這個方法的優點很明顯：不再是猜測***的執行計劃了，因為那樣產生的執行計劃可能并不是***的，基于成本的優化器使用真實的值來構建執行計劃。

但不幸的是，這個方法對于非OLTP系統的缺點也很明顯，例如，如果決策支持系統下次運行的查詢指定了一套綁定變量的值，那此時要想高效地執行查詢，需要一個完全不同的執行計劃嗎?實際上，這種數據倉庫環境并不罕見，對于決策支持系統，它可能非常希望有多個可用的執行計劃作為候選，因為一個綁定變量集可能返回的結果集只包含幾百行的數據，而另一套綁定變量可能返回幾百萬行數據，因此，Oracle建議保留CURSOR_SHARING作為該初始化參數的默認值，以強制產生一個新的更有效的執行計劃。

自適應游標共享：更靈活的綁定

Oracle 11g提供自適應游標共享(ACS)以克服不該共享時的游標共享，ACS使用了兩個新的度量機制：綁定敏感度和綁定感知。

綁定敏感度：無論何時，當包含綁定變量的SQL語句***執行時，優化器在偷窺了綁定變量的值后，會為其標記一個綁定敏感度，以確定語句的謂詞，但偷窺結束時也類似，因為它也為后面相同語句相同綁定變量不同值時進行對比，以確定是否要產生新的執行計劃。

為了說明這些綁定敏感度是如何工作的，我在Oracle 11g的SH示例方案中的SH.SALES表上構造了一個簡單的查詢，因為它是方案中***的表了，并且也按時間范圍進行分區，如列表1所示：

列表1：

-- 清空緩沖去緩存和共享池

　　ALTER SYSTEM FLUSH SHARED_POOL;

　　ALTER SYSTEM FLUSH BUFFER_CACHE;

--聲明綁定變量

　　VARIABLE cust_start NUMBER;

　　VARIABLE cust_end NUMBER;

　　VARIABLE time_start DATE;

　　VARIABLE time_end DATE;

　　VARIABLE total_sold NUMBER;

　　VARIABLE total_qty NUMBER;

-- 測試#1

　　BEGIN

　　:cust_start := 2;

　　:cust_end := 38;

　　:time_start := '01 JAN 1998';

　　:time_end := '31 MAR 1998';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

這個查詢使用了四個綁定變量來確定SH.SALES.TIME_ID和SH.SALES.CUST_ID的開始和結束范圍。

自適應游標共享元數據：Oracle 11g提供了三個新的視圖，并在v$sql視圖中添加了兩個新列以便讓Oracle DBA確定優化器是否已經決定SQL語句是否適合自適應游標共享，優化器使用業務規則將SQL語句的執行計劃進行分類以便共享：

表1 自適應游標共享視圖
視圖 V$SQL V$SQL_CS_HISTOGRAM	描述添加了兩列： IS_BIND_SENSITIVE：表示SQL語句是否綁定敏感，如果這一列的值為Y，以為著優化器已經偷窺了綁定變量的值，以便確定每個謂詞的選擇。 IS_BIND_AWARE：表示優化器執行額外的語句后決定SQL語句的游標是否有綁定感知。 Oracle 11g使用分配的頻率決定SQL語句是否綁定敏感，包括執行的次數，特別是子游標被執行的次數。
V$SQL_CS_SELECTIVITY	包括有關SQL語句謂詞的相對選擇性信息，包括謂詞自身及高值、低值范圍，這些值也被稱為游標的選擇性立方體。
V$SQL_CS_STATISTICS	列出自適應游標是否被共享以及如何共享的統計信息，如果綁定設置已經用于構造自適應游標，PEEKED列會顯示一個Y值。

#p#

在列表2中我在這些視圖上構造一些簡單的查詢及格式化輸出。

列表2 ：

-- 目的：顯示優化器選擇哪個SQL語句進行自適應游標共享

　　TTITLE 'SQL Statements With Bind Sensitivity Enabled|(from V$SQL)'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL plan_hash_value FORMAT 99999999999 HEADING 'Plan|Hash|Value'

　　COL iba_flag FORMAT A06 HEADING 'Bind|Aware?'

　　COL sql_text FORMAT A80 HEADING 'SQL Text'

　　SELECT

　　sql_id

　　,hash_value

　　,plan_hash_value

　　,is_bind_sensitive ibs_flag

　　,is_bind_aware iba_flag

　　,sql_text

　　FROM v$sql

　　WHERE ((is_bind_sensitive <> 'N') OR (is_bind_aware <> 'N'))

　　ORDER BY hash_value

　　;

　　TTITLE OFF

--目的:顯示當前自適應游標共享元數據的分布情況

　　TTITLE 'Histograms for Adaptive Cursor Sharing|(from V$SQL_CS_HISTOGRAM)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL bucket_id FORMAT 9999 HEADING 'Bckt|ID#'

　　COL count FORMAT 999999 HEADING 'Exec-|ution|Count'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,bucket_id

　　,count

　　FROM v$sql_cs_histogram

　　;

　　TTITLE OFF

-- 目的:顯示使用了自適應游標共享的游標執行統計情況

　　TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_STATISTICS)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL bind_set_hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL peeked FORMAT A05 HEADING 'Peek?'

　　COL executions FORMAT 999999 HEADING '# of|Exec-|utions'

　　COL rows_processed FORMAT 999999 HEADING '# of|Rows'

　　COL buffer_gets FORMAT 999999 HEADING 'Buffer|Gets'

　　COL cpu_time FORMAT 999999 HEADING 'CPU|Time'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,bind_set_hash_value

　　,peeked

　　,executions

　　,rows_processed

　　,buffer_gets

　　,cpu_time

　　FROM v$sql_cs_statistics

　　;

　　TTITLE OFF

-- 目的:顯示自適應游標共享決定兩個不同綁定變量的游標是否要創建新的執行計劃的選擇性度量

　　TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_SELECTIVITY)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL range_id FORMAT 9999 HEADING 'Rng|ID#'

　　COL low FORMAT A12 HEADING 'Low Value'

　　COL high FORMAT A12 HEADING 'High Value'

　　COL predicate FORMAT A80 HEADING 'Predicates'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,range_id

　　,low

　　,high

　　,predicate

　　FROM v$sql_cs_selectivity

　　;

　　TTITLE OFF

我將在本文剩下的部分中使用到它們以說明自適應游標共享是如何工作的，此外，在列表3中我顯示了在這個元數據上***次執行這個語句的影響。

列表3：

SQL Statements With Bind Sensitivity Enabled (from V$SQL)

　　Plan Bind

　　Hash Hash Sensi- Bind

　　SQL ID Value Value tive? Aware? SQL Text

　　---------------- ------------ ------------ ------ ------

　　87qtpurhk664g 3777173647 787661731 Y N SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　page 1

　　Histograms for Adaptive Cursor Sharing

　　(from V$SQL_CS_HISTOGRAM)

　　Exec-

　　Hash Chld Bckt ution

　　Value SQL ID # ID# Count

　　------------ ---------------- ----- ----- -------

　　3777173647 87qtpurhk664g 0 0 0

　　3777173647 87qtpurhk664g 0 1 1

　　3777173647 87qtpurhk664g 0 2 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_STATISTICS)

　　# of

　　Hash Chld Hash Exec- # of Buffer CPU

　　Value SQL ID # Value Peek? utions Rows Gets Time

　　------------ ---------------- ----- ------------ ----- ------- -------

　　3777173647 87qtpurhk664g 0 4302390 Y 1 1098 3178 0

***次這個語句被硬解析后，它的游標自動被標記為綁定敏感，但還沒有綁定感知，查詢的綁定變量值在***次執行期間被放在自適應游標共享三個直方圖的中間位置。

綁定感知：一旦SQL語句的游標被標記為綁定敏感，優化器可能還會決定將其視為綁定感知，優化器是通過檢查提供給綁定變量的值是否與相同查詢后面的執行計劃匹配來實現的，如果優化器決定它可以使用現有的執行計劃，那就只需要更新游標執行直方圖以反應語句的執行情況，換句話說，如果綁定變量值發了重大變化，優化器可能會決定創建一個全新的子游標和執行計劃，如果是這樣的話，Oracle 11g也會存儲自適應游標共享元數據中的子游標的相對選擇性。

我覺得它有助于把這些選擇性評級作為“電子云”或影響范圍的中心點，Oracle文檔了使用的術語是“選擇性立方體”，在隨后游標的執行過程中，優化器會使用游標最近執行的統計信息與現有的選擇性統計信息進行比較，如果它觀察到大多數執行都使用系統的選擇性范圍，游標將會被標記為綁定感知。

我用同一個查詢的另外兩次執行來說明了這個概念，但使用了完全不同的綁定變量，如列表4所示：

列表4：

-- Execution #2

　　-----

　　BEGIN

　　:cust_start := 42999;

　　:cust_end := 50000;

　　:time_start := '01 JAN 1997';

　　:time_end := '31 MAR 1998';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　/

　　-----

　　-- Execution #3

　　-----

　　BEGIN

　　:cust_start := 1000;

　　:cust_end := 1400;

　　:time_start := '01 JAN 1996';

　　:time_end := '31 MAR 1997';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

為查詢游標指定的自適應游標共享元數據產生的變化顯示在列表5中。

列表5：

SQL Statements With Bind Sensitivity Enabled

　　(from V$SQL)

　　Plan Bind

　　Hash Hash Sensi- Bind

　　SQL ID Value Value tive? Aware? SQL Text

　　---------------- ------------ ------------ ------ ------

　　87qtpurhk664g 3777173647 2855975716 Y Y SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　87qtpurhk664g 3777173647 787661731 Y N SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　Histograms for Adaptive Cursor Sharing

　　(from V$SQL_CS_HISTOGRAM)

　　Exec-

　　Hash Chld Bckt ution

　　Value SQL ID # ID# Count

　　------------ ---------------- ----- ----- -------

　　3777173647 87qtpurhk664g 1 0 1

　　3777173647 87qtpurhk664g 1 1 0

　　3777173647 87qtpurhk664g 1 2 0

　　3777173647 87qtpurhk664g 0 0 1

　　3777173647 87qtpurhk664g 0 1 1

　　3777173647 87qtpurhk664g 0 2 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_STATISTICS)

　　# of

　　Hash Chld Hash Exec- # of Buffer CPU

　　Value SQL ID # Value Peek? utions Rows Gets Time

　　------------ ---------------- ----- ------------ ----- ------- -------

　　3777173647 87qtpurhk664g 1 1601990286 Y 1 1 2 0

　　3777173647 87qtpurhk664g 0 4302390 Y 1 1098 3178 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_SELECTIVITY)

　　Hash Chld Rng

　　Value SQL ID # ID# Low Value High Value Predicates

　　------------ ---------------- ----- ----- ------------ ------------

　　3777173647 87qtpurhk664g 1 0 0.000616 0.000753 <=B1

　　3777173647 87qtpurhk664g 1 0 0.900000 1.100000 >=B2

　　3777173647 87qtpurhk664g 1 0 0.109520 0.133858 <=B3

　　3777173647 87qtpurhk664g 1 0 0.821710 1.004312 >=B4

注意，Oracle 11g已經為hash值為2855975716的SQL語句創建了新的子游標，不將它們都標記為綁定敏感和綁定感知，元數據中為這些游標指定的選擇性度量值也更新了。

當綁定變量的值超出了現有綁定感知游標影響的范圍時，執行包含這個綁定變量的查詢會發生什么?在語句的硬解析期間，優化器可能只會選擇擴大選擇范圍，以包括新的綁定值，這是通過創建新的子游標結合這兩套綁定變量值，然后刪除舊的、范圍小的游標來實現的，顯然，這樣只會產生幾個的確需要的幾個子游標。

那么如何激活這一新功能呢?好消息是在Oracle 11g中默認就已經啟動了，它完全與CURSOR_SHARING初始化參數無關，這大大增加了在OLTP/DSS系統中SQL語句使用綁定變量的機會。

對SQL計劃管理(SPM)的影響：如果你讀過我之前寫的SQL計劃管理方面的文章，你可能會疑惑自適應游標共享是否會影響SQL計劃管理捕獲和保存SQL執行計劃到SQL管理基礎庫中的功能，下面列出它們之間交互的摘要信息：

如果初始化參數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES被設置為TRUE以激活自動捕獲執行計劃，那么帶有綁定變量的SQL語句也會被標記為啟用和接收執行計劃。

如果同一個語句構建了第二個執行計劃 – 并不是自適應游標共享 – 那么該計劃只會簡單地添加到語句的計劃歷史中，但它不會立即被使用，因為SPM首先會要求校驗這個新的執行計劃。

不幸的是，這意味著一個很好的執行計劃會被忽略，解決這個問題的一個好辦法是將自動捕獲計劃設置為FALSE，然后在庫緩存中將所有子游標捕獲到SMB中，這樣將會強制所有子游標的計劃被標記為SQL計劃基線。

結束語

Oracle 11g的新特性自適應游標共享為包含有綁定變量的SQL語句有效共享執行計劃提供了一個更簡單的方法，但只有綁定變量有值時才有意義，自適應游標共享有時也會產生新的執行計劃，但共享的游標會保持相對小的數量。

【編輯推薦】

Oracle 11g 新特性- RMAN
解析Oracle 11g閃回數據歸檔新功能
Oracle 11g新特性之模式管理

責任編輯：楊鵬飛來源： IT168

自適應游標共享新特性 Oracle 11g Oracle 數據庫