histogram與10053(zt)

histogram是oracle為cbo提供更精確的成本估計而設計的一種直方圖資料。histogram能提供列的資料分佈，每次分析表後列的分佈資訊將會被儲存在統計表裡面，分析時預設的histogram size是75，意思就是採用75個buckets來表示資料分佈。

histogram分為2種型別，基於高度的histogram和基於值的histogram

基於高度的histogram

當histogram buckets的數量少於列的distinct value時，oracle會採用基於高度的直方圖反映資料分佈，每個bucket容納相同數量的值。

基本格式如下所示

SQL> column column_name format a20;

SQL> column ENDPOINT_ACTUAL_VALUE format a20;

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE

2 FROM DBA_HISTOGRAMS

3 WHERE TABLE_NAME ='TEST' AND COLUMN_NAME='OBJECT_ID'

4 ORDER BY ENDPOINT_NUMBER;

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

-------------------- --------------- -------------- --------------------

OBJECT_ID 29 1

OBJECT_ID 44 2

OBJECT_ID 59 3

OBJECT_ID 74 4

OBJECT_ID 75 76

這裡'OBJECT_ID'列有從1到76不同的76個值，bucket數量為75個，所以採取了基於高度的直方圖。

可以看到1的值佔據了1－29號bucket，2的值佔據了30－44號bucket,3的值佔據了45-59號bucket,4的值佔據了60－74號bucket，值5－76佔據了75號bucket。所以值1佔的比例最大。

基於值的histogram

當histogram buckets>=列的distinct values時，那麼Oracle會使用基於值的histogram，每個值將會佔據一個bucket，來看一下

SQL> column column_name format a20;

SQL> column ENDPOINT_ACTUAL_VALUE format a20;

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE

2 FROM DBA_HISTOGRAMS

3 WHERE TABLE_NAME ='TEST' AND COLUMN_NAME='OBJECT_ID'

4 ORDER BY ENDPOINT_NUMBER;

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

-------------------- --------------- -------------- --------------------

OBJECT_ID 19928 1

OBJECT_ID 29927 2

OBJECT_ID 39926 3

...............

OBJECT_ID 49908 74

OBJECT_ID 49909 75

很明顯可以看出與基於高度的histogram的區別，基於值的histogram為每一個值提供了一個bucket，從上面看到值1有19928行，值2有9999行，值3有9999行，值4有9912行，值5－75都分別只有1行，同樣我們可以看出值1佔的比例最大。

直方圖的資料我們也看到了，但cbo怎麼利用它來計算成本呢，做個10053看一下

alter session set events'10053 trace name context forever,level 1';

select object_name from test where object_id=1;

alter session set events'10053 trace name context off';

提取trace檔案中的一段

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: TEST Alias: TEST

#Rows: 49909 #Blks: 707 AvgRowLen: 95.00

Index Stats::

Index: IND_TEST_OBJECT_ID Col#: 4

LVLS: 1 #LB: 179 #DK: 5 LB/K: 35.00 DB/K: 175.00 CLUF: 879.00

***************************************

SINGLE TABLE ACCESS PATH

Column (#4): OBJECT_ID(NUMBER)

AvgLen: 2.00 NDV: 75 Nulls: 0 Density: 1.0018e-05 Min: 1 Max: 75

Histogram: Freq #Bkts: 75 UncompBkts: 49909 EndPtVals: 75

Table: TEST Alias: TEST

Card: Original: 49909 Rounded: 19928 Computed: 19928.00 Non Adjusted: 19928.00

Access Path: TableScan

Cost: 158.56 Resp: 158.56 Degree: 0

Cost_io: 156.00 Cost_cpu: 18011198

Resp_io: 156.00 Resp_cpu: 18011198

Access Path: index (AllEqRange)

Index: IND_TEST_OBJECT_ID

resc_io: 423.00 resc_cpu: 11183699

ix_sel: 0.39929 ix_sel_with_filters: 0.39929

Cost: 424.59 Resp: 424.59 Degree: 1

Best:: AccessPath: TableScan

Cost: 158.56 Degree: 1 Resp: 158.56 Card: 19928.00 Bytes: 0

上面的selectivity就是通過histogram來計算的，如果不存在histogram的話那麼selectivity=(1/number of distinct values),這裡的selectivity=(值為1的行數/總行數）＝(19928/49909)=0.39929

再根據索引掃描成本計算公式

io_cost=resc_io= blevel+FF*leaf_blocks+FF*clustering_factor

＝1＋0.39929*179+0.39929*879

=1+71.47291+350.97591

=423

所以index訪問成本大於table scan成本，最終cbo選擇的路線為採用tablescan.

如果選擇object_id=75呢？

alter session set events'10053 trace name context forever,level 1';

select object_name from test where object_id=75;

alter session set events'10053 trace name context off';

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: TEST Alias: TEST

#Rows: 49909 #Blks: 707 AvgRowLen: 95.00

Index Stats::

Index: IND_TEST_OBJECT_ID Col#: 4

LVLS: 1 #LB: 179 #DK: 75 LB/K: 2.00 DB/K: 11.00 CLUF: 879.00

***************************************

SINGLE TABLE ACCESS PATH

Column (#4): OBJECT_ID(NUMBER)

AvgLen: 2.00 NDV: 75 Nulls: 0 Density: 1.0018e-05 Min: 1 Max: 75

Histogram: Freq #Bkts: 75 UncompBkts: 49909 EndPtVals: 75

Table: TEST Alias: TEST

Card: Original: 49909 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50

Access Path: TableScan

Cost: 158.56 Resp: 158.56 Degree: 0

Cost_io: 156.00 Cost_cpu: 18011198

Resp_io: 156.00 Resp_cpu: 18011198

Access Path: index (AllEqRange)

Index: IND_TEST_OBJECT_ID

resc_io: 2.00 resc_cpu: 15503

ix_sel: 1.0018e-05 ix_sel_with_filters: 1.0018e-05

Cost: 2.00 Resp: 2.00 Degree: 1

Best:: AccessPath: IndexRange Index: IND_TEST_OBJECT_ID

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.50 Bytes: 0

對於non-popular value(數量比較少的值，比如在基於高度histogram裡跨度不超過一個bucket或在基於值的histogram裡行數比較少的值），oracle採取了不再去計算selectivity，而是採用density來做為

做為selectivity，從HIST_HEAD$裡可以看到density，oracle採用density function(密度函式)來估計列的密度。

SQL> select OBJ#,COL#,BUCKET_CNT,ROW_CNT,SAMPLE_SIZE,MINIMUM,MAXIMUM,DISTCNT,DENSITY from sys.HIST_HEAD$ where obj#=51933 AND COL#=4;

OBJ# COL# BUCKET_CNT ROW_CNT SAMPLE_SIZE MINIMUM MAXIMUM DISTCNT DENSITY

---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------

51933 4 49909 75 49909 1 75 75 .000010018

io_cost=resc_io= blevel+FF*leaf_blocks+FF*clustering_factor

＝1＋1.0018e-05*179+1.0018e-05*879

=1+0.001793222+0.008805822

=2（因為最少會讀2個塊）

所以最後cbo選擇了index scan。

這篇文章的目的是為了給需要的人一點提示，希望有興趣的人能夠舉一反三。

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

histogram與10053(zt)

相關文章