Oracle 動態取樣

lhb_immortal發表於2017-09-21

Dynamic_Sampling

    請注意,文中連線均為我的本地連線。在網頁上不可用。連線內容都是在文章內部。

1 什麼是動態取樣

  • Dynamic Sampling

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

  • Dynamic Statistics

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

2 相關引數及設定

  • 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;  --> 設定全庫級別
當然還有其他的引數,這裡先不做研究。

3 作用

  • 分析無統計資訊的物件
CBO 生成執行計劃,依賴的是物件的統計資訊。但是在實際情況下並不是每張表上都有統計資訊。為了保證CBO生成執行計劃儘可能的準確,Oracle 推出動態取樣這一特性,以收集儘量多的統計資訊。
  • 分析全域性臨時表
通常來講,臨時表的資料是不做分析的,因為它存放的資料是臨時性的,可能很快就釋放了。當然,如果一張臨時表資料是不是比較多,而且要被多次使用,最好還是手動收集下統計資訊。
當一個查詢關聯到無統計資訊的臨時表時,CBO要想獲得臨時表上的統計資訊分析資料,就只能依賴於動態取樣了。

4 何時使用

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

4.1 預設

  • Oracle 11.2 之前

    對沒有統計資訊的表

  • Oracle 11.2中

    對大表開啟併發(parallel) 查詢時,即使表上有統計資訊仍會開啟動態取樣。

  • Oracle 12.1 以後

動態取樣不再根據表上是否有統計資訊決定是否使用動態取樣功能,而是引入了一個新的功能叫ADS(Adaptive Dynamic Statistics).

如果動態統計開啟,優化器自動判定兩個問題:

  1. 動態統計(Dynamic Statistics)是否對決定執行計劃有幫助
  2. 使用什麼級別的動態統計

在12C 中,OPTIMIZER_DYNAMIC_SAMPLING 預設設定為2時,如果設定為0則關閉。ADS 是預設開啟的。只要滿足以下條件,優化器就會啟用ADS:

  • OPTIMIZER_DYNAMIC_SAMPLING 使用的是預設設定,或者被設定為11
  • 通過SQL hint 強制使用動態統計
  • 併發執行語句 
  • SQL進行軟解析(The query was executed before and its history is avaliable).

優化器通過分析適應性查詢優化技術(Adaptive Query Optimization techniques),比如適應性執行計劃(Adaptive plan),統計資訊反饋(Statistics Feedback), SQL 計劃提示(SQL Plan directives)來決定是否使用動態統計。

4.2 HINT

HINT 的作用是人干預優化器生成執行計劃的一種方式. 在這裡需要說明的是,通過hint告訴優化器是否使用動態取樣,如果使用,使用哪個級別的動態取樣.

  • 示例
select/*+ dynamic_sampling(a 3) */ * from x$ksuse a
  • 說明

格式:

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

table_alias 指的是表的別名。 level 指的是動態取樣級別。各級別代表的意義請參見 動態取樣級別 .

5 識別

  • 通過執行計劃識別
    • 執行計劃示例
      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 記錄的都是解釋以後的真正用來執行的語句。

6 建議

在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';

7 Enhanced Dynamic Sampling 引發效能問題一例(借用中亦一例)

客戶反饋一個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 語句的動態取樣或者取消會話級別的動態取樣 即可

Author: HALBERD Tel: 18258160531

Created: 2017-09-18 Mon 17:56

Emacs 26.0.50.1 (Org mode 8.2.10)

Validate

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25313300/viewspace-2145286/,如需轉載,請註明出處,否則將追究法律責任。

相關文章