oracle 11g中auto_sample_size是如何工作的

在oracle 11g中auto_sample_size是如何工作的?
當要準備收集統計資訊時,一個最重要的決定是你將使用什麼樣的抽樣大小.一個100%的抽樣大小能確保生成準確的統計資料但是它可能要收集很長時間.如是執行1%的抽樣將會快速完成收集但是可能會生產不準確的統計資料.

在dbms_stats.gather_*_stats過程中estimate_percent引數當收集統計資訊時控制著抽樣大小,而且它的預設值是auto_sample_size

首先來看看auto抽樣大小在oracle 11g中的增強
oracle管理統計資訊是透過pl/sql包dbms_stats來管理的.dbms_stats包提供了一些pl/sql過程來對錶,方案或資料庫收集統計資訊.這些過程有一個estimate_percent引數,它用來指定收集統計資訊時抽樣大小的百分比.使用者可以指定0到100的任何數字.例如,有一個表TEST,可以對它指定1%的抽樣百分比:

exec dbms_stats.gather_table_stats(user,'TEST',estimate_percent => 1);

使用者要指定一個合適的抽樣百分比是不容易的.如果你指定的抽樣百分比太高,那麼收集統計資訊會花費很長的時間.相反如果資料極端的傾斜且指定的抽樣大小太低,那麼生成的統計資訊可能是不準確的.由於這個原因,oracle對estimate_percent引數引入用auto抽樣大小.例如,可以對錶TEST指定auto抽樣大小:

exec dbms_stats.gather_table_stats(null,'TEST',estimate_percent => dbms_stats.auto_sample_size);

使用auto抽樣大小比使用固定的抽樣大小有兩個優勢.第一,當指定auto抽樣大小時,系統會自動判斷一個合適的抽樣百分比.第二,auto抽樣大小與固定的抽樣大小更靈活.一個固定的抽樣百分比在有些時候是好的,但是表的資料分佈發生變化後可能就不合適了.換句話說當auto值被使用時當資料分佈發生改變後oracle將會自動調整抽樣大小.

當oracle使用auto抽樣大小來讓oracle選擇一個合適的抽樣大小時生成的統計資訊是足夠準確的.然而,它在資料極端傾斜的情況下收集統計資訊是不準確的.在oracle11g中,當使用auto抽樣時已經改變了它的行為.第一,auto抽樣現在能生成確定性的統計資訊.第二也是更重要的是,auto抽樣生成的統計資訊與100%抽樣生成的統計資訊幾乎是一樣的準確但是auto抽樣比100%抽樣花費的時間要少.下面做一個測試比較使用固定抽樣大小的效能,和在oracle10g和oracle11g中比較auto抽樣的情況.我們收集的表名為KCR5,表大小有35G,627228900行.

 desc kcr5
Name   Type                      
------ ------------ 
AKB020 VARCHAR2(20)                                   
AAZ218 VARCHAR2(20)                                   
PKA001 NUMBER(5)                                        
PKA438 VARCHAR2(1)                   
PKA435 VARCHAR2(30)                                             
AAE100 VARCHAR2(1)                         
PKA439 VARCHAR2(20) Y                                     
PKA044 VARCHAR2(1)  Y                

下面的表格給出了不同抽樣百分比收集統計資訊的時間
抽樣百分比                                執行時間(秒)
1%抽樣大小                              154
100%抽樣大小                        3404
oracle10g的auto抽樣大小  503
oracle11g的auto抽樣大小   356

對35G的表KCR5使用不同抽樣百分比收集統計後可以比較收集統計資訊的質量.在一個列的所有統計資料中,不重複值的數量的準確性以前是一個問題.列的不重複值的準確率的計算公式定義如下:
accuracy rate=1-(estimated ndv -actual ndv)/actual ndv.
這個accuracy rate準確率從0%到100%.這個準確率越高,收集的統計資訊越準確.因此100%的抽樣的準確率總是100%.我們不用關注準確率100的資料,只要關注準確率小於99.9%的下面的是使用不同抽樣百分比抽樣的資料
列名             實際不重複值數量        11g中的auto抽樣      1%抽樣
AKB020    34000000                     98.3%                          49.7%
PKA001    12048687                       98.7%                         23.4%
PKA438     7000458                        99.1%                          98.4%
PKA435    5084956                          99.5%                          99.3%
PKA439     3075965                         99.6%                          99.4%

從上面的資訊可以知道,在oracle11g中使用auto抽樣大小的收集時間只有使用100%抽樣大小的十分之一,但是收集的統計資訊準確率是接近的.

在oracle11g中使用auto_sample_size收集統計資訊時收集時間和準確性與oracle10g相比都有提高.

這裡我們主要是討論一個與oracle11g中新auto_sample-size演算法相近的演算法和這個演算法是如何影響收集統計資訊的準確性的.

在研究新的收集演算法之前,先來看一下舊的演算法:
第一步:oracle在開始收集統計資訊時使用一個較小的抽樣百分比,如果有直方圖需要被收集,oracle可能會根據抽樣的百分比物化這個抽樣

第二步:oracle收集基本列的統計資訊樣本時.例如,表T只有一個列c1,那麼基本的統計收集查詢語句就類似下面的(它不是一個真實的語法)

select count(*),count(c1),count(distinct c1),sum(sys_op_opnsize(c1)),min(c1),max(c1)
from T sample(x.0000000000);

查詢是在oracle10G中使用auto_sample_size來收集基本的列的統計資訊.這個查詢的select列表中的專案對應查詢表t中的行數,不為null值的記錄數,不重複值的記錄數,總的列長,C1列的最小值和最大值.在from子句中的"x.0000000000"由於oracle決定的抽樣百分比.

第三步:如果直方圖需要收集,oracle會對每一個請求直方圖的列使用sql查詢來抽樣.

第四步:對於每個列要求直方圖時oracle使用幾個指標來判斷當前抽樣是否滿足要求.
非重複值指標:對於這個列抽取的樣品中是否包含了足夠的非重複值
重複值指標:重複值的數量是否能夠適當的從抽到的樣品是進行擴充套件

第五步:如果在第四步中的所有指標都透過了,oracle認為當前的抽樣大小是足夠的且會對列完成直方圖的建立.否則會認為抽樣大小不夠要增加抽樣大小且重複上而後步驟直到找到一個滿足條件的抽樣大小或接近100%的抽樣大小.

注意第三步到第五步對於每一個列都要進行.例如,如果表中有3個列請求建立直方圖,在第一次迭代中我們得到一個樣本並物化它,我們會使用3個查詢,每個列一個,在相同的物化樣本中收集直方圖資訊.假設oracle認為抽樣大小對於第一列和第二列是足夠的但對於第三列是不夠的,那麼會增加抽樣大小.在第二次迭代中只有一個查詢在修改抽樣大小後的樣品中對第三列收集直方圖.

就如我們看到的如果有幾次迭代被請求時舊的auto_sample_size可能會失效.幾次迭代的主要原因是不能使用小的抽樣來收集真實的重複值的數量.如果資料有傾斜,那麼大量的低頻率的值不會被抽取到樣品中因為對於重複值指標來說抽樣是失敗的.

在oracle11g中我們對於基本列統計使用完不同的收集方法.我們使用下面的查詢來收集列基本的列統計

select count(c1),min(c1),max(c1) from T;

查詢是在oracle11g中使用auto_sample_size選項收集基本列統計資訊的查詢.注意在新的基本列統計收集查詢中,沒有抽樣子句被使用.替代它的是執行一個全表掃描.所以這裡沒有count(distinct c1)來收集c1的重複值數量,相反當執行這個查詢時會注入特殊的統計資訊收集行資源.這個特殊的收集行資源使用一次透過基於雜湊的不重複演算法來收集重複值的資訊.這個演算法要求完全掃描資料,使用有限期數量的記憶體來生成高度精確的重複值資料與100%抽樣幾乎接近.這種特殊統計收集行資源的方法也收集行的數量,null值的數量和列的平均長度.因為對錶執行了完全掃描,行的數量,列的平均長度,最小值和最大值都是100%的準確.

auto_sample_size也會影響直方圖和索引統計資訊的收集

auto_sample_size對直方圖收集的影響
使用新的auto_sample_size演算法時,直方圖的收集是脫離基本列統計收集的(它們以前是在相同的抽樣樣品中進行收集的).因此當判斷我們是否要增加抽樣大小時,新的auto_sample_size演算法不再執行重複值指標檢查,因為不能從這個樣品中得到重複值.對於直方圖來說只有當抽樣樣品包含太多的null值或太少的行源時才需要增加抽樣大小.這能夠減少建立直方圖所需要的迭代次數.

如果最小(或最大)值出現在用於收集直方圖的樣品中它不是在基本統計資訊中被收集的最小(或最大)值,將會修改直方圖因此在基本統計中收集的最小(或最大)值在直方圖中會作為最一個(或最後一個)桶的端點而出現.

auto_sample_size對索引統計收集的影響
新的auto_sample_size演算法也會影響索引統計資訊的收集.索引統計資訊收集是抽樣的基礎.它可能要經過幾次迭代因為它要麼包含太少的資料塊要麼為了收集重複鍵值抽樣的大小太小.使用新的auto_sample_size演算法,如果這個索引定義在一個單列上,或者索引定義在多列(一組列)上,那麼列或列組的重複值將會被用作索引的重複鍵.那麼在這種情況下索引統計收集查詢將不會再收集重複鍵.這有助於減少因為索引統計收集而要增加抽樣大小的成本.

小結:
1.新的auto_sample_size演算法收集基本列統計時執行全表掃描
2.透過新的auto_sample_size收集重複列值與100%抽樣大小收集有一樣的準確率
3.其它的基本列統計象null值的數量,列的平均長度,最小和最大值與100%抽樣大小收集有相同的準確率
4.基於新的auto_sample_size演算法,直方圖和索引統計收集仍使用抽樣,但是新的auto_sample_size演算法有助於緩解增加抽樣的樣本量.

參考
https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size