cbo心得(選擇率,基數,直方圖)(一)

一，概述

關於CBO最佳化器你上網可以搜到一大堆的介紹。用一句話來解釋CBO：CBO是一種根據執行代價來選擇執行計劃的機制。對於CBO來說，最重要的就是各種不同的執行計劃的代價的計算。和RBO比較起來，CBO確實要聰明很多。CBO會根據物件上的統計資訊來進行執行計劃的代價評估，選擇出較為高效的。而不是象RBO那樣根據一些既有的規則來選擇執行計劃，愣頭青一樣的。

既然是根據物件上的統計資訊來計算代價，那麼統計資訊對於CBO的重要性就不用多說了。所以在CBO的資料庫上，統計資訊一定要定期收集，保證不光要有統計資訊，而且也要儘量保證統計資訊的準確性。不準確的統計資訊可能比沒有統計資訊還要糟糕。

最近一直在看關於CBO的東西，有了一些簡單的體會，拿出來分享一下，對我自己來說也是一個再提高的過程。

下面主要就是對CBO中幾個最基本的概念的說明。它們主要是：選擇率，基數和直方圖。

二，基礎知識

選擇率：目標結果集佔全部資料的百分比

基數：目標結果集的大小

直方圖：列上的統計資訊，主要用於傾斜度很高的列上

選擇率和基數的含義差不多太多，都是為了描述目標結果集的大小的。選擇率和基數是CBO的基礎，在選擇SQL執行計劃的主要參考的就是這兩個結果。比如說：

Create table sunwg as select * from dba_objects;

Create index ind_sunwg_1 on sunwg(object_id);

Create index ind_sunwg_2 on sunwg(owner);

對於查詢select * from sunwg where object_id = 12345來說，是選擇索引掃描還是選擇全表掃描呢？答案很簡單，哪個效率更好就選擇哪個。CBO會根據表和列上的統計資訊得知這個查詢大概會返回1行記錄，在5W多條記錄中選擇一條，那麼基數就是1，使用索引的效果會更好，這樣大概幾個IO就可以搞定。

對於查詢select * from sunwg where owner = ‘SYS’來說，這個查詢會返回幾W條記錄，那麼自然而然就會想到全表掃描會更加的快。

所以說選擇率和基數對於執行計劃的選擇起著相當重要的影響。

直方圖也是列上統計資訊的一種。我們常用的統計資訊主要包括表上的統計資訊，索引上的統計資訊，在有就是列上的統計資訊了。

檢視錶上的統計資訊

SQL> select NUM_ROWS, --表中的記錄數

BLOCKS, --表中資料所佔的資料塊數

EMPTY_BLOCKS, --表中的空塊數

AVG_SPACE, --資料塊中平均的使用空間

CHAIN_CNT, --表中行連線和行遷移的數量

AVG_ROW_LEN --每條記錄的平均長度

from user_tables

where table_name = 'SUNWG';

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN

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

5050 12 3 3450 6

從上面的結果可以看到表SUNWG一共有5050條記錄，佔用了15個資料塊，其中有12塊是儲存資料的，另外3個資料塊是空的，平均每個塊使用3450B的空間，平均每條記錄的長度是6B。

檢視索引的統計也是類似的

select BLEVEL, --索引的層數

LEAF_BLOCKS, --葉子結點的個數

DISTINCT_KEYS, --唯一值的個數

AVG_LEAF_BLOCKS_PER_KEY, --每個KEY的平均葉塊個數

AVG_DATA_BLOCKS_PER_KEY, --每個KEY的平均資料塊個數

CLUSTERING_FACTOR --群集因子

from user_indexes

where index_name = ‘IND_SUNWG_1;

檢視列上的統計資訊

select NUM_DISTINCT, --唯一值的個數

LOW_VALUE, --列上的最小值

HIGH_VALUE, --列上的最大值

DENSITY, --選擇率因子（密度）

NUM_NULLS, --空值的個數

NUM_BUCKETS, --直方圖的BUCKET個數

HISTOGRAM --直方圖的型別

from user_tab_columns

where table_name ='SUNWG'

and column_name = 'OBJECT_ID';

查詢列上的直方圖資訊

select ENDPOINT_NUMBER,

ENDPOINT_VALUE

from user_tab_histograms

where table_name ='SUNWG'

and column_name = 'OBJECT_ID';

上面的這幾個查詢就是我們工作中經常會遇到的關於統計資訊的查詢。為什麼要統計表，索引和列的統計資訊比較好理解，關於直方圖資訊可能很多人都是比較模糊，不知道為什麼要統計直方圖，直方圖到底有什麼用。下面用個例子來說明：
drop table sunwg purge;

Create table sunwg (id number);

Create index ind_sunwg on sunwg(id);

Insert into sunwg select 1 from dba_objects where rownum < 5000;

Insert into sunwg select rownum from dba_objects where rownum < 5002;

Commit;

對於查詢select * from sunwg where id = 1來說，表中一共有1W條記錄，而id = 1的記錄有5000條，佔了50%，在這種情況下，走全表掃描來說是更明智的。對於查詢select * from sunwg where id = 500來說，表中一共有1W條記錄，而id = 1的記錄只有1條，這時候應該走索引掃描更好。這是我們知道資料分佈的情況下。

假設現在我們沒有收集列ID上的直方圖資訊，我們僅僅有表上的，索引上的和列上的統計資訊。我們能得到的統計資訊大概是這樣的，表上一共有1W條記錄，其中大概有5001個不同的值，ID最小的值是1，ID最大的值是5001。在表中ID的值是平均分佈的情況下，我們可以得到選擇率大概是1/5001，走索引掃描會快一些。那麼對於前面說的id = 1和id = 5000的查詢，CBO會選擇執行索引的掃描。前面我們已經分析了，對於id = 1來說最好的方式就是全表掃描，此時CBO選擇了不合適的執行計劃。這也不能怪CBO，因為我們給它的資訊並不完整，不足以分析出ID上的這些差異。直方圖就是為了解決這樣的問題。

直方圖對於那些列上值分佈不平均，列上資訊明顯傾斜的列十分的有用。有了列上的直方圖資訊後，CBO就可以知道大概的資料分佈，就可以作出相對來說更加正確的選擇。至於CBO是如果使用直方圖資訊的，我們在下面會詳細的介紹。