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