985
動態采樣大法好，讓Oracle執行計劃一步到位！
作者介紹
蔣健，雲趣網絡科技聯合創始人，11g OCM，多年Oracle設計、管理及實施經驗，精通數據庫優化，Oracle CBO及並行原理，曾為多個行業的客戶的 Oracle 係統實施小型機到 X86跨平台遷移和數據庫優化服務。雲趣鷹眼監控核心設計和開發者，資深Python Web開發者。（文章審校：楊建榮）
動態采樣介紹
Oracle 動態采樣（Dynamic Sampling，12c 稱為 Dynamic statistics)，是對統計信息的一個重要補充，當數據動態變化，無法用典型的統計信息描述時，動態采樣可以給在解析時對表中數據進行采樣，為優化器提供準確的估算值（cardinality）。動態采樣的主要有以下幾個應用場景：
-
一個經典的場景就是業務場景中的臨時表，比如 ETL 數據清洗轉換過程中的臨時表，比如 BI 係統中存放計算報表結果的臨時表。這些臨時表可能是 Oracle 中的 global temporary table，也可能是正常的堆表。因為臨時表中的數據時動態變化的，不同時間點，臨時表中的數據量變化很大，沒有一種合適的統計信息使優化器產生合適的執行計劃。這種場景適合采用動態采樣技術，通常會刪除臨時表上的統計信息，並且鎖定統計信息，不讓搜集統計信息的 Job 更新臨時表上的統計信息，查詢臨時表時，優化器會對臨時表進行動態采樣，以確定臨時表的 cardinality。
-
另一個場景是在單表上使用組合過濾條件，並且組合過濾條件並不是簡單的相等操作，或者在過濾列上使用轉換函數，無法使用 column group 擴展統計信息，簡單使用多個列上的統計信息也無法產生合適的統計信息。
-
12c 之前，動態采樣隻能預估單表 cardinality，12c 版本，Oracle 對動態采樣做了很大的增強，可以估算 group by 的聚合結果集和連接結果集的 cardinality。
使用動態采樣，優化器往往可以獲得高質量的估算值，從而產生更優化的執行計劃。本文將介紹三種動態采樣的適用場景。
臨時表和動態采樣
優化器動態采樣解析
實際案例
金融行業客戶 CRM 係統的分析語句執行時間經常需要5分鍾以上，通過分析 Top SQL的執行計劃，發現執行計劃的估算值偏差離譜，比如下圖SQL Monitor 報告中，對於表P_CUST_STAT，優化器估算值為1，實際值為一千四百萬行。導致後續連接方式為 nested loop，被驅動表被訪問了一千四百萬次。
通過表的統計信息，可以發現10月9號搜集統計信息時，表P_CUST_STAT中沒有數據，Num_Rows為0行， 所以優化器估算為1行。雖然P_CUST_STAT是正常對表，但是在應用中被用於臨時表，數據是動態生成和刪除的。
另一個例子，下圖 SQL Monitor 報告中，表B_S_CUST_STAT的過濾條件為Data_date = to_date(20161008,'yyyymmdd'), 估算值同樣為一行，實際值為一千四百萬行，導致後續連接方式為 nested loop outer，被驅動視圖表訪問了一千四百萬次。
通過表B_S_CUST_STAT的統計信息，統計信息收集時間為10月9號早上8點，Num_Rows為一千三百七十萬行記錄，看起來表上的統計信息是正確的。
繼續查看B_S_CUST_STAT列上的統計信息，Data_date 列上隻有一個唯一值，為10月7號。表B_S_CUST_STAT隻存放一天的數據，當統計信息搜集時，表中的數據為10月7號的數據。之後，數據被替換為10月8號的數據，統計信息並沒有及時更新，導致當天之後對表 P_CUST_STAT的使用 Data_date = to_date(20161008,'yyyymmdd') 的查詢的估算值都為1。
create or replace function raw_to_date(i_raw raw)
return date
as
m_n date;
begin
dbms_stats.convert_raw_value(i_raw,m_n);
return m_n;
end;
/
select raw_to_date('78740A07010101') stats_value from dual;
STATS_VALUE
-------------------
2016-10-07 00:00:00
解決方案
刪除表B_S_CUST_STAT和P_CUST_STAT的統計信息並且進行鎖定，保證後續對臨時表的查詢會使用動態采樣，得到準確的估算值。
Exec dbms_stats.delete_table_stats(‘CRM’,’B_S_CUST’);
Exec dbms_stats.lock_table_stats(‘CRM’,’B_S_CUST’);
Exec dbms_stats.delete_table_stats(‘CRM’,’P_CUST_STAT’);
Exec dbms_stats.lock_table_stats(‘CRM’,’P_CUST_STAT’);
複雜查詢的動態采樣
對於有複雜的過濾條件的sql, 為了在執行計劃中得到正確的cardinality, 統計信息未必有幫助, 包括extended statistics. 比如下麵in和like的組合條件, 或者where條件中使用了自定義的函數。
status in (‘COM’, ‘ERR’) and v1 like ‘10%’
這時候dynamic sampling可能是唯一的選擇。下麵是一個例子, 采用level為6的采樣之後，cardinality更為接近真實的數據。
構造一個1百萬行數據的測試表，搜集統計信息。
測試 SQL，估算值為395行，實際值為11113行，差距為30倍左右。
使用動態采樣，級別為6，估算值為16595行，實際為11113行，差距不到2倍，估算值的質量大幅提升。
12c 動態采樣的增強，對連接和 group by 結果集的統計
測試SQL結果集為13行，12c中采樣級別設為11，實際為auto時，CBO估算為12行，準確性很高，並且在表上有統計信息的情況下依然可以進行采樣（采樣級別設為6時，不使用采樣，清除表上的統計信息後，可發現采樣級別為6的時候，CBO估算值15743行，差別很大）
總結
Oracle 動態采樣在性能優化上有諸多應用場景，12c中更是得到加強，更深入了解動態采樣的特性對性能優化有著重要的意義。
原文發布時間為：2017-03-22
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-16 11:01:34