程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle性能分析12：對象統計信息

Oracle性能分析12：對象統計信息

編輯：Oracle教程

Oracle性能分析12：對象統計信息

對象統計信息描述數據是如何在數據庫中存儲的，查詢優化器使用這些統計信息來做出正確的決定。Oracle中有三種類型的對象統計信息：表統計、列統計和索引統計。而在每種類型中，有細分為：表或索引級別的統計、分區級別統計和子分區級別的統計，後面兩種只有在對象被分區和具有子分區的情況下才可用。

統計信息相關視圖

表統計信息

表/索引級別的統計

user_tab_statistics
user_tables

分區級別的統計

user_tab_statistics
user_tab_partitions

子分區級別統計

user_tab_statistics
user_tab_subpartitions

列統計信息

表/索引級別的統計

user_tab_col_statistics
user_tab_histograms

分區級別的統計

user_part_col_statistics
user_part_histograms

子分區級別統計

user_subpart_col_statistics
user_subpart_histograms

索引統計信息

表/索引級別的統計

user_ind_statistics
user_indexes

分區級別的統計

user_ind_statistics
user_ind_partitions

子分區級別統計

user_ind_statistics
user_ind_subpartitions

創建測試表

這裡將創建測試表T用於後面對統計信息的說明。

創建測試表

create table test as 
select rownum as id,
       round(dbms_random.normal * 1000) as val1,
       100 + round(ln(rownum / 3.25 + 2)) as val2,
       100 + round(ln(rownum / 3.25 + 2)) as val3,
       dbms_random.string('p', 250) as pad
  from all_objects
 where rownum <= 1000
 order by dbms_random.value

上面的語句創建了一個1000行的表，然後我們將val1列中的負值清空：

update test set val1 = null where val1 < 0;

為測試表添加主鍵和索引

alter table test add constraint test_pk primary key (id);
create index test_val1 on test (val1);
create index test_val2 on test (val2);

為測試表收集統計信息

begin
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                cascade          => TRUE);
end;

表統計信息

下面是表統計信息中的關鍵字段：

select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
  from user_tab_statistics
 where table_name = 'TEST';
 
NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN
----------------------------------------------------------------------------------
1000		39		0				0			0			265

下面是對字段含義的解釋：

1）num_rows：表中數據的行數；
2）blocks：高水位線以下的數據塊個數（高水位線詳見“Oracle性能分析4：數據訪問方法之全掃描”http://blog.csdn.net/tomato__/article/details/38981425）；
3）empty_blocks：高水位線以上的數據塊個數，由於dbms_stats不計算該值，因此為0；
4）avg_space：表裡數據塊的平均空閒空間（字節），由於dbms_stats不計算該值，因此為0；
5）chain_cnt：涉及行鏈接和行遷移的總行數，由於dbms_stats不計算該值，因此為0（詳見“Oracle行遷移和行鏈接”http://blog.csdn.net/tomato__/article/details/40146573）；
6）avg_row_len：表中平均每個記錄的長度（字節）。

列統計信息

下面是列統計信息的最重要的統計信息字段：

select column_name,
       num_distinct,
       low_value,
       high_value,
       density,
       num_nulls,
       avg_col_len,
       histogram,
       num_buckets
  from user_tab_col_statistics
 where table_name = 'TEST';

下面是對這些字段的解釋：
1）num_distinct：該列中不同值的數量；
2）low_value：該列的最小值。顯示為內部存儲的格式，對於字符串列只存儲前32字節；
3）high_value：該列的最大值。顯示為內部存儲的格式，對於字符串列只存儲前32字節；
4）density：0到1之間的一個小數。接近0表示對於列的過濾操作能去掉大多數行。接近1表示對於該列的過濾操作起不到什麼作用。
如果沒有直方圖，該值的計算方法為：density=1/num_distinct。
如果有直方圖，則根據不同的直方圖類型有不同的計算方法。
5）num_nulls：該列中存儲的NULL的總數；
6）avg_col_len：平均列大小，以字節表示；
7）histogram：表明是否有直方圖統計信息，值包括：NONE（沒有）、FREQUENCY（頻率類型）和HEIGHT BALANCED（平均分布類型）；
8）num_buckets：直方圖裡的bucket的數量，最小為1，最大為254。
注：low_value和high_value表示為內部存儲的格式，下面的存儲過程可以得到test表的所有列的最大最小值：

declare
  l_val1 test.val1%type;
begin
  for v in (select low_value, high_value
              from user_tab_col_statistics
             where table_name = 'TEST') loop
    dbms_stats.convert_raw_value(v.low_value, l_val1);
    dbms_output.put_line('low value : ' || l_val1);
    dbms_stats.convert_raw_value(v.high_value, l_val1);
    dbms_output.put_line('low value : ' || l_val1);
  end loop;
end;

直方圖

查詢優化器需要找到滿足條件的數據行數，如果列的數據均勻分布的，則很容易根據最小值、最大值和唯一值總數就可以計算得到，這些信息在列統計信息中就可以得到。但如果數據不是均勻分布的，查詢優化器則需要額外的信息才能做出正確估算。
這些查詢優化器需要的關於數據不均勻分布的額外信息叫做直方圖，存在兩種類型的直方圖：頻度直方圖（frequency histogram）和等高直方圖（height-balanced histogram）。

頻度直方圖

頻度直方圖的本質特性如下：
1）桶數（即分類數）等於唯一值總數。對於每個桶來說，視圖user_tab_histograms有一行數據與之對應；
2）列endpoint_value提供該值本身。該列為number類型，應此非數字類型的列必須要進行轉換，只取前六個字節。這意味著直方圖中存儲的值的分布是基於列的前面部分，因而，固定前綴的字符串會使直方圖的分布嚴重不均衡；
3）列endpoint_number是取值的累積出現次數，當前的endpoint_number減去上一個endpoint_number，就是當前行這個值的出現次數。
通過下面的方式就可以得到列val2的頻次：

select column_name,
       endpoint_value,
       endpoint_number,
       endpoint_number - lag(endpoint_number, 1, 0) over(order by endpoint_number) as frequency
  from user_tab_histograms
 where table_name = 'TEST'
   and column_name = 'VAL2'
 order by endpoint_number

COLUMN_NAME	ENDPOINT_VALUE	ENDPOINT_NUMBER	FREQUENCY
-------------------------------------------------------
VAL2				101				8			8
VAL2				102				33			25
VAL2				103				101			68
VAL2				104				286			185
VAL2				105				788			502
VAL2				106				1000		212

下面用test表作為一個例子說明優化器怎樣利用頻度直方圖精確估算查詢返回的行數：

explain plan set statement_id '101' for select * from test where val2 = 101;
explain plan set statement_id '102' for select * from test where val2 = 102;
explain plan set statement_id '103' for select * from test where val2 = 103;
explain plan set statement_id '104' for select * from test where val2 = 104;
explain plan set statement_id '105' for select * from test where val2 = 105;
explain plan set statement_id '106' for select * from test where val2 = 106;

然後我們查看執行計劃對返回行數的估算：

select statement_id,cardinality from plan_table where id = 0;

STATEMENT_ID	CARDINALITY
----------------------------------
	101			8
	102			25
	103			68
	104			185
	105			502
	106			212

等高直方圖

當一列的唯一值數量總是大於桶的允許最大數量（254）時，就不能使用頻度直方圖了，這是就只能使用等高直方圖了。
等高直方圖的主要特征如下：
1）桶數少於唯一值總數。除非被壓縮，否則對應於每個桶，視圖user_tab_histograms裡都有一個包含端點號（endpoint number）的行與之對應，端點號0表明最小取值；
2）端點值（endpoint_value）就是列的數值。因為該列是number類型，非數字類型必須進行轉換，此值僅取前六個字節；
3）endpoint_number列給出了桶號；
4）直方圖不存儲一個取值的頻度。
等高直方圖只存儲列值屬於某一個桶，如果有兩個列值位於同一個桶，則其中一個將被忽略（壓縮），這樣的統計就可能導致估算不准確。在實踐中，等高直方圖不但可能導致錯誤的估算，還可能引起查詢優化器估值的不穩定。

索引統計信息

下面的查詢可以得到索引統計信息：

select index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       num_rows,
       clustering_factor,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key
  from user_ind_statistics
 where table_name = 'TEST';

主要字段的含義如下：
1）blevel：為了訪問葉子塊而需要讀取的分支塊的數量，包括根塊；
2）leaf_blocks：索引中的葉子塊數；
3）distinct_keys：索引中的唯一鍵值總數；
4）num_rows：索引中的鍵值數；
5）clustering_factor：見“Oracle性能分析8：使用索引”http://blog.csdn.net/tomato__/article/details/39294655；
6）avg_leaf_blocks_per_key：存放一個鍵值的平均葉子塊數，公式如下；
avg_leaf_blocks_per_key = leaf_blocks/distinct_keys
7）avg_data_blocks_per_key：表中單個鍵引用的平均數據塊數，公式如下：
avg_data_blocks_per_key = clustering_factor/distinct_keys