• Dynamic Sampling

動態取樣（Dynamic Sampling）技術的最初提出是在Oracle 9i R2，在段（表，索引，分割槽）沒有分析的情況下，為了使CBO 優化器得到足夠的資訊以保證做出正確的執行計劃而發明的一種技術，可以把它看做收集統計資訊的一種手段。 當段物件沒有統計資訊時（即沒有做分析），動態取樣技術可以通過直接從需要分析的物件抽樣資料塊來抽象出CBO需要的統計資訊。

• Dynamic Statistics

到了12C 之後，動態取樣由於得到功能性的強化，名稱也改為 dynamic statistics . 強化後的動態取樣，不僅僅支援單表掃描，還支援表關聯和group by 操作。 在已有統計結果基礎上得到更加準確的的基數統計。

• OPTIMIZER_FEATURES_ENABLE

  該引數可以改變優化器基於當前RDBMS版本的優化行為。值是資料庫版本，表示使用哪個版本的資料庫的優化特性。 例如，有時候我們會定期對資料庫進行升級，升級後，又擔心新的優化器規則特性裡，會出現效能問題，那麼我們就可以指定該引數值為升級前的優化器版本。以保證優化器解析SQL語句的行為和規則不會發生變化。 不同版本該引數可以設定的值也不同,具體見下表:

Table 1: values could be set to optimizer_features_enable

版本	可設定值
9.2	8.0.[0-6], 8.1.[03-7],9.0.[0-1],9.2.0
10.2	9.2[*] + 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.[1-5]
11.2	10.2[*] + 11.1.0.6 | 11.1.0.7 | 11.2.0.[1-4]
12.2	11.2[*] + 12.1.0.[1-2] | 12.2.0.1

每個版本中支援的優化器特性參見OPTIMIZER_FEATURE

• OPTIMIZER_DYNAMIC_SAMPLING
  • 引數值可選範圍

  該引數決定了優化器動態取樣時使用的級別。該引數值受OPTIMIZER_FEATURE_ENABLE引數影響.不同的版本採用的值是不一樣的，具體見下表:

Table 2: Values could be set to optimizer_dynamic_sampling

OPTIMIZER_FEATURE_ENABLE	OPTIMIZER_DYNAMIC_SAMPLING	VALUE
9.0.1 or lower	0	0-10
ABOVE 9.2.0 LOWER 10.0.0	1	0-10
BETWEEN 10.0.0 AND 11.1	2	0-10
ABOVE 11.2	2	0-11

如果該引數值設定為10.0.0 或者更高，optimizer_dynamic_sampling 的預設值為2.

• 取樣級別

  在11G R2 之前，資料庫取樣的級別由optimizer_dynamic_sampling引數決定，或者由hint 指定，而之後，Oracle 引入了ADS 功能（自適應動態統計),Oracle 會自己決定使用哪種級別。

Table 3: level of dynamic sampling

級別	說明
0	不使用動態取樣
1	滿足以下條件時，對所有沒有統計資訊的表採用動態取樣：
	1) 查詢中至少有一個無統計資訊的表
	2) 無統計資訊的表或與其他表進行關聯或是在子查詢中或在不可merge的檢視中出現
	3) 無統計資訊的表上無索引
	4) 動態取樣預設取樣32個資料塊。無統計資訊的表佔用的資料塊應大於32個塊
2	對所有無統計資訊的表使用動態取樣,取樣資料塊數量為預設取樣塊資料的2倍
3	level2 + 判斷predicate(where 條件）為需要動態取樣的相關表。如果表上有統計信
	息，取樣資料塊為預設32塊，如果表上沒有統計資訊，取樣資料塊的數量為2倍
4	level3 + single-table predicates reference 2 or more columns.
5-10	level4 , 取樣的資料塊較多，5:2*32, 6:4*32,7:8*32,8:32*32,9:128*32,10:all
11	11GR2 版本以上。oracle 會自動決定是否使用動態取樣及使用何種級別

一般情況下，我們手動開啟動態取樣時，使用最多的級別4. 一般來說，取樣級別設定的數字越大，取樣的範圍就越廣，取樣時抽樣的資料塊就越多。設定優化器取樣級別的方式如下：

ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0; --> 設定會話級別
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING = 4;  --> 設定全庫級別

當然還有其他的引數，這裡先不做研究。

• 分析無統計資訊的物件

CBO 生成執行計劃，依賴的是物件的統計資訊。但是在實際情況下並不是每張表上都有統計資訊。為了保證CBO生成執行計劃儘可能的準確，Oracle 推出動態取樣這一特性，以收集儘量多的統計資訊。

• 分析全域性臨時表

通常來講，臨時表的資料是不做分析的，因為它存放的資料是臨時性的，可能很快就釋放了。當然，如果一張臨時表資料是不是比較多，而且要被多次使用，最好還是手動收集下統計資訊。
當一個查詢關聯到無統計資訊的臨時表時，CBO要想獲得臨時表上的統計資訊分析資料，就只能依賴於動態取樣了。

我們需要知道在什麼情況下會使用動態取樣。

select/*+ dynamic_sampling(a 3) */ * from x$ksuse a

select/*+ dynamic_sampling(table_alias level) */ column_list from table_name table_alias;

• 通過執行計劃識別
  • 執行計劃示例

    Plan hash value: 2818410146
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation		     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT	     |		    |	 42 |  3948 |	 17   (0)| 00:00:01 |
    |   1 |  INLIST ITERATOR	     |		    |	    |	    |		 |	    |
    |   2 |   TABLE ACCESS BY INDEX ROWID| XX_RXXXON    |	 42 |  3948 |	 17   (0)| 00:00:01 |
    |*  3 |    INDEX UNIQUE SCAN	     | PK_XX_RXXXON |	  8 |	    |	  9   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    3 - access("A"."RXXXON_ID"=10 OR "A"."RXXXON_ID"=51 OR "A"."RXXXON_ID"=101 OR
    	 "A"."RXXXON_ID"=11910 OR "A"."RXXXON_ID"=107569 OR "A"."RXXXON_ID"=100301590 OR
    	 "A"."RXXXON_ID"=100301591 OR "A"."RXXXON_ID"=100301592)
    
    Note
    -----
    - dynamic sampling used for this statement (level=2)
    
    21 rows selected.
    

    • 關鍵資訊

      - dynamic sampling used for this statement (level=2)

      在執行計劃資訊中，predicate 部分之後，跟隨著一個Note 說明部分，該部分告訴我們採用了動態取樣。"dynamic sampling used"翻譯成中文即為: 採用動態取樣。

• 10053 定位 10053 是分析為什麼Oracle 會選擇該執行計劃，因此此過程中也會告訴我們哪一步採用了什麼操作。10053 只針對第一次物理解析。對於軟體解析是不追蹤的。 
  • 開啟10053trace

    sqlplus / as sysdba
    oradebug setmypid;
    oradebug unlimit
    oradebug event 10053 trace name context forever,level 1;
    執行相關sql
    oradebug tracefile_name;
    

  • 查詢內容

    ** Performing dynamic sampling initial checks. **
       Column (#1):
         NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:177208
       Column (#1): REGION_ID(
         AvgLen: 6 NDV: 177208 Nulls: 0 Density: 0.000006 Min: 62 Max: 100376691
         Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255
       Using prorated density: 0.000006 of col #1 as selectvity of out-of-range/non-existent value pred
       Using prorated density: 0.000006 of col #1 as selectvity of out-of-range/non-existent value pred
    ** Dynamic sampling initial checks returning TRUE (level = 2).
    *** 2017-09-15 01:10:09.901
    ** Generated dynamic sampling query:
       query text :
       SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
       .......
       AMPLE BLOCK (2.509960 , 1) SEED (1) "alias") SAMPLESUB 
       .....
    

    這是一個關鍵資訊, 我們可以通過”dynamic“關鍵詞，或者”DYN_SAMP“, SAMPLESUB 等等進行搜尋 。

    有時，我們會做10046trace, 如果SQL語句在執行的過程中也出現SELECT / OPT_DYN_SAMP */ /*+ ALLROWS ….. 這樣類似的語句，也說明Oracle在使用動態取樣。是由於10046 記錄的都是解釋以後的真正用來執行的語句。

在Oracle 11.2 中，由於動態取樣增強特性的引入, 有可能會引發BUG, 導致執行計劃不準確。 由於功能的增強，對於SQL 語句執行的效率提升是有幫助的，對於發現的BUG,我們在相關的SQL上將其避免掉即可，儘量不要從系統級別關閉功能。 因此建議，在11.2 版本中所有對大表的併發查詢會話中取消動態取樣:

-- 建議:
alter session set “_fix_control”=’7452863:OFF';
select/*+dynamic_sampling(table_alias 0) */ .. from table_name table_alias;
-- 不建議:
alter system set "_fix_control"='7452863:OFF';

客戶反饋一個SQL語句執行效率特別慢，但是已經按照SQL優化三板斧砍過了這塊木頭，但是沒效果，用的時候還是慢。 “三板斧”過後，仍無法提高SQL執行效率，要麼SQL的寫法有問題，要麼ORACLE 自己有問題。先來看看SQL 語句，執行計劃吧:

• SQL 語句

  SELECT
  ...........
   FROM XXX_STMT _X  T1
   LEFT JOIN(SELECT P.ACCOUNT AS XACCOUNT,
                     P.MONTH_NBR,
                     ... AS SHOPPING_POINT
                FROM XXX_PTLOG P
               WHERE P.CREATE_DATE <=SYSDATE+1
                 AND P.CREATE_DATE > ADD_MONTHS(SYSDATE,-12)
               GROUPBY P.ACCOUNT, P.MONTH_NBR) T2
     ON T1.XACCOUNT = T2.XACCOUNT
    AND T1.MONTH_NBR = T2.MONTH_NBR
   LEFT JOIN XXX_STMT_X T3
     ONT1.XACCOUNT = T3.XACCOUNT
    ANDT1.MONTH_NBR -1= T3.MONTH_NBR
    ANDT3.STMT_DATE <= ADD_MONTHS(SYSDATE,-1)
    ANDT3.STMT_DATE > ADD_MONTHS(SYSDATE,-13)
  WHERET1.STMT_DATE <=SYSDATE+1
  AND T1.STMT_DATE > ADD_MONTHS(SYSDATE,-12)
  

  • SQL 語句特點
    • 語句中存在兩張表XXX_STMT _X（別名為t1,t3),被兩次訪問，另外一張表XXX_PTLOG(別名P)
    • T1,T3 上有時間過濾條件，欄位為stmt_date
    • P 表上有時間過濾條件，create_date
• 執行計劃 通過awrsqlrpt 取下執行計劃：

• 分析
  • 異常點 通過執行計劃，可以看出: T1 與T3 先進行關聯，得出結果集，而T1 是全表掃描，T3 是索引的範圍掃描，T1 與 T3 掃描資料的時間跨度為1年（但是掃描範圍不一樣，請注意). 一年的資料怎麼可能只有一條呢？ 實際按過濾條件過濾資料後應有900萬+的資料量。 統計資訊不對？我們檢視統計資訊是新收集的。那問題來了。有正確的統計資訊，而生成了錯誤的執行計劃。*,遇到BUG了吧?!
  • 分析執行計劃錯誤的原因 檢視執行計劃是如何生成的，用一把10053號碧血劍，把這個黑盒子一點兒一點兒剝開。 通過10053 trace 發現如下資訊：

    ccesspath analysis for XXX_STMT_X
    ***************************************
    SINGLE TABLE ACCESS PATH  --估算單表訪問路徑
    Single Table CardinalityEstimation for XXX_STMT_X[T1]
    
    ** Performing dynamicsampling initial checks. **  --啟動了動態取樣檢查
    Column (#107):
    NewDensity:0.000557,OldDensity:0.000000 BktCnt:5387, PopBktCnt:5387, PopValCnt:126, NDV:126
    Column (#107): STMT_DATE(
    AvgLen: 8 NDV: 126Nulls: 0 Density: 0.000557 Min: 2456779 Max: 2457734
    Histogram: Freq  #Bkts: 126 UncompBkts: 5387  EndPtVals: 126
    ** Dynamic sampling initialchecks returning TRUE (level = 5). --動態才有檢查完成，級別為5
    
    ** Generated dynamicsampling query:  --生成動態取樣SQL
      query text :
    ………………
    ** Executed dynamic sampling query:
    level : 5
    sample pct. : 0.015328—取樣比0.015328
    total partitions : 128 –-分割槽表分割槽總數128，實際分割槽數是127
      partitions forsampling : 128
     partitions actuallysampled from : 32  --真正取樣的分割槽個數為32個
    actual sample size : 0     --採集到的行數為0
    filtered sample card. : 0  ------->  滿足過濾條件的為0
    

    在生成執行計劃時，資料庫使用了動態取樣。按照oracle 11G R2 之前的版本常識來看， 表上有統計資訊，是不會使用動態取樣的。這裡為什麼會出現動態取樣呢？這就是Oracle 11G R2 中的enhanced dynamic sampling. 在Oracle 12C 中被稱為ADS . 在11g R2中的 Dynamic Sampling特性 ,我們知道即使表上有統計資訊，Oracle 仍會使用動態取樣，以 求得到更精確的cardinality。但是事與願違，動態取樣錯誤評估了資料。相當於統計信 息不正確，從而生成了錯誤的執行計劃，兩張八九百萬行的資料集進行等值連線,連線方 式應該是JASH JOIN。因此面對這個問題解決方案為:  取消SQL 語句的動態取樣或者取消會話級別的動態取樣 即可