Oracle优化07-分析及动态采样-动态采样
思維導圖
07系列文章
Oracle優化07-分析及動態采樣-直方圖
Oracle優化07-分析及動態采樣-DBMS_STATS 包
Oracle優化07-分析及動態采樣-動態采樣
動態采樣Dynamic_sampling
動態采樣（ Dynamic Sampling）技術的最初提出是在 Oracle 9i R2，在段（表，索引，分區）沒有分析的情況下，為了使 CBO 優化器得到足夠的信息以保證做出正確的執行計劃而發明的一種技術，可以把它看做分析手段的一種補充。
當段對象沒有統計信息時（即沒有做分析），動態采樣技術可以通過直接從需要分析的對象上收集數據塊（采樣）來獲得 CBO 需要的統計信息。
示例演示：
SQL> create table t as select object_id , object_name from dba_objects ;Table createdSQL> select count(1) from t; COUNT(1) ---------- 35249SQL>這里創建了一張普通表，沒有做分析，我們在 hint 中用 0 級來限制動態采樣，此時 CBO 唯一可以使用的信息就是表存儲在數據字典中的一些信息，如有多少個extent，有多少個 block，但是這些信息是不夠的。
SQL> select /*+dynamic_sampling(t 0) */ * from t;SQL> select a.SQL_ID,a.CHILD_NUMBER ,a.SQL_TEXT from v$sql a where a.SQL_TEXT like 'select /*+dynamic_sampling(t 0) */ * from t%';SQL> select * from table(dbms_xplan.display_cursor('0crj75han452z',0));PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 0crj75han452z, child number 0 ------------------------------------- select /*+dynamic_sampling(t 0) */ * from t Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 43 (100)| | | 1 | TABLE ACCESS FULL| T | 12498 | 964K| 43 (0)| 00:00:01 | --------------------------------------------------------------------------13 rows selectedSQL>在沒有做動態分析的情況下，CBO 估計的記錄數是 12498 條，與真實的 35249相差甚遠。
我們使用動態采樣來看一下
- dynamic sampling used for this statement (level=2)
在 Oracle 10g 以后默認對沒有分析的段做動態采樣。
dynamic sampling used for this statement (level=2)
使用了Level 2 級的動態采樣， CBO 估計的結果是 37883 與 35249很接近了。
注意一點：
在沒有動態采樣的情況下，對于沒有分析過的段， CBO 也可能錯誤地將結果判斷的程度擴大話。
示例演示：
SQL> delete from t;35249 rows deletedSQL> commit;Commit completeSQL> alter system flush shared_pool;System alteredSQL>查看執行計劃：
SQL> select /*+dynamic_sampling(t 0) */ * from t; SQL> select * from t;SQL> select a.SQL_ID,a.CHILD_NUMBER ,a.SQL_TEXT from v$sql a where a.SQL_TEXT like 'select /*+dynamic_sampling(t 0) */ * from t%';SQL> select a.SQL_ID,a.CHILD_NUMBER ,a.SQL_TEXT from v$sql a where a.SQL_TEXT like 'select * from t%';SQL> select * from table(dbms_xplan.display_cursor('0crj75han452z',0));SQL> select * from table(dbms_xplan.display_cursor('ckzaraqcmkr2f',0));可能看出 2 個執行計劃的差別。 在沒有采用動態分析的情況下， CBO 對 t 表估計的還是 12498 行記錄，但是用動態分析就顯示 1 條記錄。
而表中的數據在查詢之前已經刪除掉了。
出現這種情況的原因是因為高水位。 雖然表的數據已經刪除，但是表分配的 extent 和 block 不會被回收（高水位值不變），所以在這種情況下 CBO 依然認為有那么多的數據在那。
通過這一點，我們可以看出，此時 CBO 能夠使用的信息非常有限，也就是這個表有幾個 extent，有幾個 block。 但動態采樣之后， Oracle 立即發現，原來數據塊中都是空的。
動態采樣的作用
動態采樣有兩方面的作用：
CBO 依賴的是充分的統計分析信息，但是并不是每個用戶都會非常認真，及時地去對每個表做分析. 為了保證執行計劃都盡可能地正確，Oracle 需要使用動態采樣技術來幫助 CBO 獲取盡可能多的信息。
全局臨時表。 通常來講，臨時表的數據是不做分析的，因為它存放的數據是臨時性的，可能很快就釋放了，但是當一個查詢關聯到這樣的臨時表時， CBO 要想獲得臨時表上的統計信息分析數據，就只能依賴于動態采樣了。
動態采樣除了可以在段對象沒有分析時，給 CBO 提供分析數據之外，還有一個獨特的能力，它可以對不同列之間的相關性做統計。
相對的，表分析的信息是獨立的。 如：
（ 1） 表的行數，平均行長。
（ 2） 表的每個列的最大值，最小值，重復率，也可能包含直方圖。
（ 3） 索引的聚合因子，索引葉的塊數目， 索引的高度等。
這些信息相互之間是獨立的，當查詢涉及到列之間的相關性時，這些信息就顯得不足夠了。
案例說明：
##建立T表，2個列 Flag1和Flag2 ，對應 Y，N SQL> create table t as2 select decode(mod(rownum ,2) , 0 , 'Y','N') flag1 ,3 decode(mod(rownum ,2),0,'N','Y') flag24 from all_objects a;Table created##在兩個列上建立一個聯合索引 SQL> create index ind_t on t(flag1,flag2);Index created##對表和索引進行分析 SQL> begin 2 dbms_stats.gather_table_stats(user,'t',method_opt => 'for all indexed columns size 254');3 end ;4 /PL/SQL procedure successfully completedSQL> SQL> select a.NUM_ROWS ,a.NUM_ROWS/2 ,a.NUM_ROWS/2/2 from user_tables a where a.TABLE_NAME='T';NUM_ROWS A.NUM_ROWS/2 A.NUM_ROWS/2/2 ---------- ------------ --------------33872 16936 8468SQL>查看表的總量 , 總量的1/2 , 總量的1/4.
我們獲取這個值是要和CBO估算出來的結果集的數量做對比，以判斷CBO估算的正確性。
下面我們來看下面兩條SQL的執行計劃：
select * from t where t.flag1=’N’;
因為我們對表做了分析，所以CBO使用了統計分析數據，而不會再使用動態采樣。
在這個試驗中，CBO估算出來的結果集我們從圖中可以看到16744 非常接近與實際值 ，因為對已flag1來講 ，等于N的記錄是整個記錄的一半（等于Y的記錄是另一半），這在統計信息里面可以獲取到，此時CBO表現正常。
select * from t where t.flag2=’N’
同樣對于 flag2=N的情況，CBO表現也是完美的。
那我們來看下下面這個情況呢？
select * from t where t.flag1=’N’ and t.flag2=’N’;
我們從表中的數據可以推斷出，其實是不存在這種這種數據的，結果為0。
我們來看下CBO的估算結果集的表現 8467 ，和0天相差甚遠了….
那么CBO是如何估算的呢？
Flag1=N 占總數的1/2
Flag2=N 占總數的1/2
所以Flag1=N and Flag2=N 的記錄數是 1/2*1/2=1/4*33872=8468
所以CBO估算出的結果集比較錯誤，原因是因為它沒有能夠對列之前的相關性做出估算，所以得出了偏差非常大的估算。
那我們來看下動態采樣的效果吧
此時 CBO的估算的結果集是1，和實際數0 非常相近了。
由于結果集很小，所以執行計劃選擇了索引，這才是最優的執行計劃。
這就是動態采樣， 盡管看到動態采樣的優點，但是它的缺點也是顯而易見，否則 Oracle 一定會一直使用動態采樣來取代數據分析：
（ 1） 采樣的數據塊有限，對于海量數據的表，結果難免有偏差。
（ 2） 采樣會消耗系統資源，特別是 OLTP 數據庫，尤其不推薦使用動態采樣。
動態采樣的級別
Level 0
不做動態分析
Level 1
Oracle 對沒有分析的表進行動態采樣，但需要同時滿足以下 4 個條件。
（ 1） SQL 中至少有一個未分析的表
（ 2） 未分析的表出現在關聯查詢或者子查詢中
（ 3） 未分析的表沒有索引
（ 4） 未分析的表占用的數據塊要大于動態采樣默認的數據塊（ 32 個）
Level 2
對所有的未分析表做分析，動態采樣的數據塊是默認數據塊的 2 倍。
Level 3
采樣的表包含滿足 Level 2 定義的所有表，同時包括，那些謂詞有可能潛在地需要動態采樣的表，這些動態采樣的數據塊為默認數據塊，對沒有分析的表，動態采樣的默認塊為默認數據塊的 2 倍。
Level 4
采樣的表包含滿足 Level 3 定義的表，同時還包括一些表，他們包含一個單表的謂詞會引用另外的 2 個列或者更多的列；采樣的塊數是動態采樣默認數據塊數；對沒有分析的表，動態采樣的數據塊為默認數據塊的 2 倍。
Level 5， 6， 7， 8， 9
采樣的表包含滿足 Level 4 定義的表，同時分別使用動態采樣默認數據塊的2， 4， 8， 32， 128 倍的數量來做動態分析。
Level 10
采樣的表包含滿足 Level 9 定義的所有表，同時對表的所有數據進行動態采
樣。
采樣的數據塊越多，得到的分析數據就越接近與真實，但同時伴隨著資源消耗的也越大。
什么時候使用動態采樣
動態采樣也需要額外的消耗數據庫資源，所以，如果 SQL 被反復執行，變量被綁定，硬分析很少，在這樣一個環境中，是不宜使用動態采樣的，就像 OLTP系統。 動態采樣發生在硬分析時，如果很少有硬分析發生，動態采樣的意義就不大.
而在 OLAP 或者數據倉庫環境下， SQL 執行消耗的資源要遠遠大于 SQL 解析，那么讓解析在消耗多一點資源做一些動態采樣分析，從而做出一個最優的執行計劃是非常值得的。 實際上在這樣的環境中，硬分析消耗的資源幾乎是可以忽略的。
所以，一般在 OLAP 或者數據倉庫環境中，將動態采樣的 level 設置為 3 或者 4 比較好。 相反，在 OLTP 系統下，不應該使用動態采樣。
總結
以上是生活随笔為你收集整理的Oracle优化07-分析及动态采样-动态采样的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Oracle优化07-分析及动态采样-D
- 下一篇： Oracle-USERS表空间解读