oracle裡表、索引、列的統計資訊

標籤：執行計畫   節點   bucket   取出   返回   null   distinct   3.1   作用   

一、表的統計資訊

表的統計資訊用於描述表的詳細資料，包括記錄數（num_rows）、表塊的數量（blocks）、平均行長度（avg_row_len）等典型維度。這些維度可以通過資料字典表DBA_TABLES、DBA_TAB_PARTITIONS和DBA_TAB_SUBPARTITIONS來分別查看錶、分區表的分區和分區表的子分區的統計資訊。 二、索引的統計資訊

索引的統計資訊描述了索引的詳細資料，它包含了索引的層級（blevel）、葉子塊數量（leaf_blocks）、聚簇因子（clustering_factor）等典型維度。這些維度可以通過資料字典視圖DBA_INDEXES、DBA_IND_PARTITIONS和DBA_IND_SUBPARTITIONS來分別查看索引、分區索引的分區、局部分區索引的子分區的統計資訊。 1、層級（level）

層級表示從根節點到葉子塊的深度，層級被CBO用於計算訪問索引葉子塊的成本，層級越大，表示從根節點到葉子塊所需要訪問的資料區塊的數量就越多，耗費的i/o就會越多，索引訪問的成本就會越大。在資料庫裡如果需要降低索引的層級，需要rebuild才可以。 2、聚簇因子的含義及重要性

oracle資料庫中，聚簇因子是指按照索引索引值排序的索引行和儲存於對應表中的資料行的儲存順序的相似程度。oracle資料庫按照如下演算法計算聚簇因子：

（1）聚簇因子初始值為1.

（2）oracle首先定位到目標索引處於最左邊的葉子塊。

（3）從最左邊葉子塊的第一個索引索引值所在的索引行開始順序掃描，在順序掃描的過程中，oracle會比較當前索引行的rowid和之前那個索引行的rowid，如果這兩個rowid並不是指向同一個表塊，那麼oracle就將聚簇因子的當前值遞增1；如果這兩個rowid是指向同一個表塊，oracle就不改變聚簇因子的值。oracle在比對rowid時並不會回表去訪問相應的表塊。

（4）上述的比對過程會持續下去，知道掃描完目標索引的所有索引塊的所有索引行。

（5）上述順序掃描完成後，聚簇因子的當前值就是索引統計資訊中的clustering_factor，oracle將其儲存在資料字典裡。

由以上的過程可知：聚簇因子高的索引走索引範圍掃描時比相同條件下聚簇因子低的索引要耗費更多的物理i/o，所以聚簇因子高的索引走索引範圍掃描的成本會比相同條件下聚簇因子低的索引走索引範圍掃描的成本高。即聚簇因子越小越好。

oracle資料庫中，能夠降低聚簇因子的唯一方法就是對錶中資料按照目標索引的索引索引值排序後重新儲存。

oracle資料庫裡，cbo在計算索引範圍掃描（index range scan）的成本計算公式入下：

（*）IRS COST=I/O COST+CPU COST

（*）I/O COST=INDEX ACCESS I/O COST+TABLE ACCESS I/O COST

（*）index access i/o cost=blevel_celt(#leaf_blocks*ix_sel)

（*）table access i/o cost=celt(clustering_factor*ix_sel_with_filters)

從這個公式可以推斷出走索引範圍掃描的成本可以近似看作是與聚簇因子成正比。因此，聚簇因子值得大小實際對CBO判斷是否走相關索引起著至關重要的作用。 3、列的統計資訊

oracle裡列的統計資訊用於描述oracle資料庫裡列的詳細資料，包括列的distinct值（num_distinct）、列的null值（num_nulls）得數量、列的最小值（low_value）、列的最大值（high_value）等一些典型維度。可以通過資料字典dba_tab_col_statistics、dba_part_col_statistics和dba_subpart_col_statistics分別查看錶、分區表的分區、分區表的子分區的列的統計資訊。

（1）列的distinct值（上述資料字典中欄位num_distinct表示distinct值數量），cbo用num_distinct值來計算目標列做等值查詢時的可選擇率。

（2）上述字典中的欄位num_nulls儲存的就是目標列的null值數量，cbo用num_null值來評估對目標列施加“is null”或“is not null”條件後的返回結果集cardinality。另外cbo還用num_nulls值來調整對有null值得目標列做等值查詢時的可選擇率selectivity。

對目標列進行等值查詢時可選擇率計算公式：selectivity=(1/num_distinct)*((num_rows-num_nulls)/num_rows)

（3）上述字典的列low_value和high_value值就是目標列的最小值和最大值，cbo通過low_value和high_value來計算目標列進行範圍查詢時可選擇率selectivity的值。

沒有長條圖，目標列範圍查詢可選擇率計算公式（略）。 3.1長條圖（histogram） 1）長條圖含義

在oracle資料庫中，CBO會預設認為目標列的資料在最小值low_value和最大值high_value之間是均勻分布的，並且會根據這個均勻分布的原則來計算對目標列施加查詢條件後的可選擇率以及結果集的cardinality，進而據此來計算成本值並且選擇執行計畫。但是目標列的資料是均勻分布的這個原則並不總是準確的，在實際的系統中，我們很容易看到一些目標列的資料分布式不均勻的，甚至是極度傾斜的，分布極不均勻。對這樣的列如果還按照均勻分布的原則去計算可選擇率和結果集，並據此來計算成本，選擇執行計畫，那麼CBO所選擇的執行計畫就可能是不合理的，甚至是錯誤的。

對於上述問題，oracle引入了長條圖。長條圖是一種特殊的列的統計資訊，它描述了目標列的資料分布情況。可以通過資料字典視圖DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS來分別查看錶、分區表的分區和分區表的子分區的長條圖統計資訊。

如果對目標列收集了長條圖，則意味著CBO將不再認為該列上的資料是分布不均勻的，CBO就會用該目標列上的長條圖統計資訊來計算對該列施加查詢條件後的可選擇率和返回的結果集的cardinility，進而據此計算成本並選擇相應的執行計畫。即長條圖就是oracle為了專門準確評估這種目標列分布不均勻的可選擇率和結果集cardinility的方法。 2）長條圖類型

oracle資料庫裡的長條圖使用了一種稱為bucket的方式來描述目標列的資料分布。根據bucket的數量，長條圖分為2類：

（1）frequency類型長條圖：儲存在資料字典裡用於描述目標列長條圖的bucket數量等於目標列的distinct數量。

oracle長條圖針對於文本類型的列收集長條圖統計資訊，則oracle只會將該文字欄位頭32位字元取出來，並將其轉換成一個浮點數。然後將這個浮點數作為長條圖統計資訊儲存在資料字典裡。

（2）heigh balanced類型長條圖：儲存在資料字典裡用於描述目標列長條圖的bucket數量小於目標列的distinct數量。

oracle裡表、索引、列的統計資訊