Oracle優化統計數據之直方圖(histograms)

直方圖是一種按數據出現的頻率來進行分類存儲的方法.在oracle中直方圖是用來描述表中列數據的分布情況.每一個sql在被執行前都要經

直方圖是一種按數據出現的頻率來進行分類存儲的方法.在Oracle中直方圖是用來描述表中列數據的分布情況.每一個sql在被執行前都要經過優化這一步驟那么在優化器給出一個最優執行計劃之優化器應該要知道sql語句中所引用的底層對象的詳細信息.

直方圖描述的對象包括列中不同值的數量和它們出現的頻率.現在存儲每一個不同值和它出現的頻率是不可行的,特別是對于大表來說列中有上萬個不同值,oracle使用直方圖來存儲關于列中數據分布的有用信息而且oracle的CBO使用直方圖信息來計算出一個最優的執行計劃.

CBO與直方圖histograms
從一個行源中評估返回行數所占的比例這就是選擇率,選擇率在CBO的查詢優化中起著重要作用.選擇率的取值范圍是0到1之間.粗略的講,如果滿足謂詞條件的只有少量的行記錄那么CBO將更喜歡使用索引掃描,如果謂詞條件要從表中獲取大量數據那么CBO將更喜歡使用全表掃描.比如下面的查詢獲取deptno等于10的所有雇員信息如果返回少量的記錄查詢將會更傾向于使用索引掃描:
select * from emp where deptno=10;

為了評估選擇率(或者換句話說計算出最優執行計劃),CBO會使用各種形式的統計信息,配置參數等.以表中列的角度來說,CBO會收集以下統計信息:
列中不同值的數量也就是NDV
列中的最小值/最大值
列中null值的數量
數據分布或直方圖信息

在沒有直方圖時優化器使用基表中記錄的列中不同值的數量,列中最小值/最大值和列中null值的數量來計算統計信息.使用這些信息優化器假設數據在列中的最小值和最大值之間是均勻分布的或者說列中每一個不同值的出現次數是相同的.
下面舉列說明.創建一個測試表t1它有10000行記錄,有兩個列,列all_distinct包含不同值的范圍從1到10000.列skew對于前10行記錄的值從1到10,余下的9990行記錄都是10000.
[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 4 06:05:14 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1 as select rownum all_distinct,10000 skew from dual connect by level < =10000; Table created. SQL> update t1 set skew=all_distinct where rownum< =10; 10 rows updated. SQL> commit;

Commit complete.

SQL> select skew,count(*) from t1 group by skew order by skew;

SKEW COUNT(*)
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
10000 9990

11 rows selected.

使用dbms_stata.gather_table_stats來收集統計信息是生成直方圖是由參數method_opt來控制的method_opt參數的語法是由多個部分組成的.前兩個部分是強制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column...]

method_opt語法中的主要部分控制哪此列將收集列的統計信息(min,max,ndv,nulls).缺省是for all columns,它將會對表中所有的列(包括隱藏列)收集基本的列統計信息.

for all indexed columns將只對哪些包含索引的列進收集列統計信息.

for all hidden columns將只會對哪些虛擬列收集列統計信息.這意味著在對表收集統計時真實列是不會生成列統計信息的.這個值不能用于通常的統計信息收集.它只能用在當基表列的統計信息精確收集后在表中創建新的虛擬列.然后對新的虛擬列收集列統計信息時才使用它.

注意如果列不在統計信息收集列表中那么只會收集列的平均長度.

size用來指定直方圖的桶數SIZE {integer | REPEAT | AUTO | SKEWONLY}
auto:基于列的使用信息(sys.col_usage$)和是否存在數據傾斜來收集直方圖
integer:人為的指定創建直方圖的桶數范圍是1到254,如果size 1意味著不創建直方圖
repeat:只會對已經存在直方圖的列重新生成直方圖.如果是一個分區表,repeat會確保對在全局級別存在直方圖的列重新生成直方圖.這是不被推薦的設置的.當前直方圖的桶數將會作為重新生成直方圖所使用的桶數的最大值.比如,當前直方圖的桶數是5,那么生成的直方圖最大桶數就是5,說的直白點就是刷新現有直方圖的列上的統計信息.
skewonly:對任何數據分布出現傾斜列的自動創建直方圖

現在來對表t1收集統計信息但不創建直方圖
SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,density from user_tab_col_statistics where
2 table_name='T1';

COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ALL_DISTINCT 10000 .0001
SKEW 11 .090909091

如果沒有直方圖,列的density統計信息代表了它的選擇率它是通過去時1/num_distinct=1/11=0.09090901來計算出來的.在有直方圖的情況下,density的計算依賴于直方圖的類型和oracle的版本.density值的范圍是0到1之間.當查詢使用這個列作謂詞條件時優化器將會使用這個列的density統計信息來評估將要返回的行數.所以 cardinality(基數)=selectivity(選擇率)* number of rows(表的行數)

下面來檢查一下在謂詞條件中列的數據分布存在傾斜而沒有直方圖的情況下其基數評估的情況:
SQL> explain plan for select * from t1 where skew=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com