Oracle 11g 新特性--自適應(yīng)游標(biāo)共享
正如我過去做老師時(shí)告訴學(xué)生的那樣，我有一個(gè)好方法可以避免數(shù)據(jù)庫性能降低，說來也很簡(jiǎn)單，就是要求應(yīng)用程序開發(fā)人員不要在數(shù)據(jù)庫運(yùn)行任何SQL語句，但這的確引來不少人的笑聲和嘲笑，還是回到現(xiàn)實(shí)中來吧，討論一下建立有效SQL語句的途徑，我認(rèn)為沒有什么標(biāo)準(zhǔn)可供借鑒，但構(gòu)造不佳的SQL語句遲早會(huì)導(dǎo)致聯(lián)機(jī)事務(wù)處理(OLTP)系統(tǒng)、決策支持系統(tǒng)(DSS)或混合數(shù)據(jù)庫表現(xiàn)不佳。
“偷窺”綁定變量：這不是作弊
幸運(yùn)的是，Oracle數(shù)據(jù)庫為我們提供了一些很好的工具，用以確定需要改善性能的SQL語句，并為提升它們的性能提供建議，這個(gè)方法的核心是當(dāng)確實(shí)需要一個(gè)新的執(zhí)行計(jì)劃時(shí)，我們可以修改基于成本的SQL優(yōu)化器解析SQL語句的方式。當(dāng)然，當(dāng)一條語句***次執(zhí)行時(shí)必須硬解析，這樣優(yōu)化器可以確定獲取所需數(shù)據(jù)的***路徑，因?yàn)榻馕鍪且粋€(gè)相對(duì)費(fèi)時(shí)的操作，因此，DBA通常會(huì)限制存儲(chǔ)在庫緩存中的唯一性游標(biāo)的數(shù)量，特別是在聯(lián)機(jī)事務(wù)處理環(huán)境下，相同的語句可能會(huì)被執(zhí)行成百上千次，它們?yōu)橛脩魰?huì)話返回相似的結(jié)果集。
設(shè)置有效的游標(biāo)共享最簡(jiǎn)單的方法就是按照應(yīng)用程序工作量的需要為CURSOR_SHARING初始化參數(shù)設(shè)定合適的值，將這個(gè)參數(shù)的值設(shè)為SIMILAR告訴優(yōu)化器當(dāng)SQL語句完全相同，除了謂詞部分外，可以使用游標(biāo)共享，執(zhí)行計(jì)劃提供相等或更優(yōu)的性能，同樣，將其值設(shè)為CURSOR_SHARING時(shí)，不論是否存在更好的執(zhí)行計(jì)劃，告訴優(yōu)化器強(qiáng)制共享游標(biāo)，當(dāng)SQL語句包含綁定變量時(shí)，有極好的機(jī)會(huì)提供這方面的性能優(yōu)勢(shì)，但當(dāng)優(yōu)化器在不知道是什么值來填充產(chǎn)生的游標(biāo)時(shí)，怎樣才能構(gòu)建一個(gè)有效的執(zhí)行計(jì)劃呢?
早在Oracle 9i就進(jìn)行了一些嘗試，為了克服可能出現(xiàn)的非***的執(zhí)行計(jì)劃，引入了綁定變量偷窺，顧名思義，當(dāng)一個(gè)包含綁定變量的SQL語句***執(zhí)行時(shí)，Oracle會(huì)快速查看這些綁定變量的一個(gè)真實(shí)值，以便構(gòu)建一個(gè)***的執(zhí)行計(jì)劃，這個(gè)方法的優(yōu)點(diǎn)很明顯：不再是猜測(cè)***的執(zhí)行計(jì)劃了，因?yàn)槟菢赢a(chǎn)生的執(zhí)行計(jì)劃可能并不是***的，基于成本的優(yōu)化器使用真實(shí)的值來構(gòu)建執(zhí)行計(jì)劃。
但不幸的是，這個(gè)方法對(duì)于非OLTP系統(tǒng)的缺點(diǎn)也很明顯，例如，如果決策支持系統(tǒng)下次運(yùn)行的查詢指定了一套綁定變量的值，那此時(shí)要想高效地執(zhí)行查詢，需要一個(gè)完全不同的執(zhí)行計(jì)劃嗎?實(shí)際上，這種數(shù)據(jù)倉庫環(huán)境并不罕見，對(duì)于決策支持系統(tǒng)，它可能非常希望有多個(gè)可用的執(zhí)行計(jì)劃作為候選，因?yàn)橐粋€(gè)綁定變量集可能返回的結(jié)果集只包含幾百行的數(shù)據(jù)，而另一套綁定變量可能返回幾百萬行數(shù)據(jù)，因此，Oracle建議保留CURSOR_SHARING作為該初始化參數(shù)的默認(rèn)值，以強(qiáng)制產(chǎn)生一個(gè)新的更有效的執(zhí)行計(jì)劃。
自適應(yīng)游標(biāo)共享：更靈活的綁定
Oracle 11g提供自適應(yīng)游標(biāo)共享(ACS)以克服不該共享時(shí)的游標(biāo)共享，ACS使用了兩個(gè)新的度量機(jī)制：綁定敏感度和綁定感知。
綁定敏感度：無論何時(shí)，當(dāng)包含綁定變量的SQL語句***執(zhí)行時(shí)，優(yōu)化器在偷窺了綁定變量的值后，會(huì)為其標(biāo)記一個(gè)綁定敏感度，以確定語句的謂詞，但偷窺結(jié)束時(shí)也類似，因?yàn)樗矠楹竺嫦嗤Z句相同綁定變量不同值時(shí)進(jìn)行對(duì)比，以確定是否要產(chǎn)生新的執(zhí)行計(jì)劃。
為了說明這些綁定敏感度是如何工作的，我在Oracle 11g的SH示例方案中的SH.SALES表上構(gòu)造了一個(gè)簡(jiǎn)單的查詢，因?yàn)樗欠桨钢?**的表了，并且也按時(shí)間范圍進(jìn)行分區(qū)，如列表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; -- 測(cè)試#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;
這個(gè)查詢使用了四個(gè)綁定變量來確定SH.SALES.TIME_ID和SH.SALES.CUST_ID的開始和結(jié)束范圍。
自適應(yīng)游標(biāo)共享元數(shù)據(jù)：Oracle 11g提供了三個(gè)新的視圖，并在v$sql視圖中添加了兩個(gè)新列以便讓Oracle DBA確定優(yōu)化器是否已經(jīng)決定SQL語句是否適合自適應(yīng)游標(biāo)共享，優(yōu)化器使用業(yè)務(wù)規(guī)則將SQL語句的執(zhí)行計(jì)劃進(jìn)行分類以便共享：
|
表1 自適應(yīng)游標(biāo)共享視圖 | |
|
視圖 V$SQL V$SQL_CS_HISTOGRAM |
描述 添加了兩列： IS_BIND_SENSITIVE：表示SQL語句是否綁定敏感，如果這一列的值為Y，以為著優(yōu)化器已經(jīng)偷窺了綁定變量的值，以便確定每個(gè)謂詞的選擇。 IS_BIND_AWARE：表示優(yōu)化器執(zhí)行額外的語句后決定SQL語句的游標(biāo)是否有綁定感知。 Oracle |
|
V$SQL_CS_SELECTIVITY |
包括有關(guān)SQL語句謂詞的相對(duì)選擇性信息，包括謂詞自身及高值、低值范圍，這些值也被稱為游標(biāo)的選擇性立方體。 |
|
V$SQL_CS_STATISTICS |
列出自適應(yīng)游標(biāo)是否被共享以及如何共享的統(tǒng)計(jì)信息，如果綁定設(shè)置已經(jīng)用于構(gòu)造自適應(yīng)游標(biāo)，PEEKED列會(huì)顯示一個(gè)Y值。 |
#p#
在列表2中我在這些視圖上構(gòu)造一些簡(jiǎn)單的查詢及格式化輸出。
列表2 ：
-- 目的：顯示優(yōu)化器選擇哪個(gè)SQL語句進(jìn)行自適應(yīng)游標(biāo)共享 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 --目的:顯示當(dāng)前自適應(yīng)游標(biāo)共享元數(shù)據(jù)的分布情況 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 -- 目的:顯示使用了自適應(yīng)游標(biāo)共享的游標(biāo)執(zhí)行統(tǒng)計(jì)情況 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 -- 目的:顯示自適應(yīng)游標(biāo)共享決定兩個(gè)不同綁定變量的游標(biāo)是否要?jiǎng)?chuàng)建新的執(zhí)行計(jì)劃的選擇性度量 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
我將在本文剩下的部分中使用到它們以說明自適應(yīng)游標(biāo)共享是如何工作的，此外，在列表3中我顯示了在這個(gè)元數(shù)據(jù)上***次執(zhí)行這個(gè)語句的影響。
列表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
***次這個(gè)語句被硬解析后，它的游標(biāo)自動(dòng)被標(biāo)記為綁定敏感，但還沒有綁定感知，查詢的綁定變量值在***次執(zhí)行期間被放在自適應(yīng)游標(biāo)共享三個(gè)直方圖的中間位置。
綁定感知：一旦SQL語句的游標(biāo)被標(biāo)記為綁定敏感，優(yōu)化器可能還會(huì)決定將其視為綁定感知，優(yōu)化器是通過檢查提供給綁定變量的值是否與相同查詢后面的執(zhí)行計(jì)劃匹配來實(shí)現(xiàn)的，如果優(yōu)化器決定它可以使用現(xiàn)有的執(zhí)行計(jì)劃，那就只需要更新游標(biāo)執(zhí)行直方圖以反應(yīng)語句的執(zhí)行情況，換句話說，如果綁定變量值發(fā)了重大變化，優(yōu)化器可能會(huì)決定創(chuàng)建一個(gè)全新的子游標(biāo)和執(zhí)行計(jì)劃，如果是這樣的話，Oracle 11g也會(huì)存儲(chǔ)自適應(yīng)游標(biāo)共享元數(shù)據(jù)中的子游標(biāo)的相對(duì)選擇性。
我覺得它有助于把這些選擇性評(píng)級(jí)作為“電子云”或影響范圍的中心點(diǎn)，Oracle文檔了使用的術(shù)語是“選擇性立方體”，在隨后游標(biāo)的執(zhí)行過程中，優(yōu)化器會(huì)使用游標(biāo)最近執(zhí)行的統(tǒng)計(jì)信息與現(xiàn)有的選擇性統(tǒng)計(jì)信息進(jìn)行比較，如果它觀察到大多數(shù)執(zhí)行都使用系統(tǒng)的選擇性范圍，游標(biāo)將會(huì)被標(biāo)記為綁定感知。
我用同一個(gè)查詢的另外兩次執(zhí)行來說明了這個(gè)概念，但使用了完全不同的綁定變量，如列表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;
為查詢游標(biāo)指定的自適應(yīng)游標(biāo)共享元數(shù)據(jù)產(chǎn)生的變化顯示在列表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已經(jīng)為hash值為2855975716的SQL語句創(chuàng)建了新的子游標(biāo)，不將它們都標(biāo)記為綁定敏感和綁定感知，元數(shù)據(jù)中為這些游標(biāo)指定的選擇性度量值也更新了。
當(dāng)綁定變量的值超出了現(xiàn)有綁定感知游標(biāo)影響的范圍時(shí)，執(zhí)行包含這個(gè)綁定變量的查詢會(huì)發(fā)生什么?在語句的硬解析期間，優(yōu)化器可能只會(huì)選擇擴(kuò)大選擇范圍，以包括新的綁定值，這是通過創(chuàng)建新的子游標(biāo)結(jié)合這兩套綁定變量值，然后刪除舊的、范圍小的游標(biāo)來實(shí)現(xiàn)的，顯然，這樣只會(huì)產(chǎn)生幾個(gè)的確需要的幾個(gè)子游標(biāo)。
那么如何激活這一新功能呢?好消息是在Oracle 11g中默認(rèn)就已經(jīng)啟動(dòng)了，它完全與CURSOR_SHARING初始化參數(shù)無關(guān)，這大大增加了在OLTP/DSS系統(tǒng)中SQL語句使用綁定變量的機(jī)會(huì)。
對(duì)SQL計(jì)劃管理(SPM)的影響：如果你讀過我之前寫的SQL計(jì)劃管理方面的文章，你可能會(huì)疑惑自適應(yīng)游標(biāo)共享是否會(huì)影響SQL計(jì)劃管理捕獲和保存SQL執(zhí)行計(jì)劃到SQL管理基礎(chǔ)庫中的功能，下面列出它們之間交互的摘要信息：
如果初始化參數(shù)OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES被設(shè)置為TRUE以激活自動(dòng)捕獲執(zhí)行計(jì)劃，那么帶有綁定變量的SQL語句也會(huì)被標(biāo)記為啟用和接收?qǐng)?zhí)行計(jì)劃。
如果同一個(gè)語句構(gòu)建了第二個(gè)執(zhí)行計(jì)劃 – 并不是自適應(yīng)游標(biāo)共享 – 那么該計(jì)劃只會(huì)簡(jiǎn)單地添加到語句的計(jì)劃歷史中，但它不會(huì)立即被使用，因?yàn)镾PM首先會(huì)要求校驗(yàn)這個(gè)新的執(zhí)行計(jì)劃。
不幸的是，這意味著一個(gè)很好的執(zhí)行計(jì)劃會(huì)被忽略，解決這個(gè)問題的一個(gè)好辦法是將自動(dòng)捕獲計(jì)劃設(shè)置為FALSE，然后在庫緩存中將所有子游標(biāo)捕獲到SMB中，這樣將會(huì)強(qiáng)制所有子游標(biāo)的計(jì)劃被標(biāo)記為SQL計(jì)劃基線。
結(jié)束語
Oracle 11g的新特性自適應(yīng)游標(biāo)共享為包含有綁定變量的SQL語句有效共享執(zhí)行計(jì)劃提供了一個(gè)更簡(jiǎn)單的方法，但只有綁定變量有值時(shí)才有意義，自適應(yīng)游標(biāo)共享有時(shí)也會(huì)產(chǎn)生新的執(zhí)行計(jì)劃，但共享的游標(biāo)會(huì)保持相對(duì)小的數(shù)量。
【編輯推薦】
