Oracle優(yōu)化統(tǒng)計數(shù)據(jù)之直方圖(histograms)

直方圖是一種按數(shù)據(jù)出現(xiàn)的頻率來進行分類存儲的方法.在oracle中直方圖是用來描述表中列數(shù)據(jù)的分布情況.每一個sql在被執(zhí)行前都要經(jīng)

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

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

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

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

在沒有直方圖時優(yōu)化器使用基表中記錄的列中不同值的數(shù)量,列中最小值/最大值和列中null值的數(shù)量來計算統(tǒng)計信息.使用這些信息優(yōu)化器假設(shè)數(shù)據(jù)在列中的最小值和最大值之間是均勻分布的或者說列中每一個不同值的出現(xiàn)次數(shù)是相同的.
下面舉列說明.創(chuàng)建一個測試表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來收集統(tǒng)計信息是生成直方圖是由參數(shù)method_opt來控制的method_opt參數(shù)的語法是由多個部分組成的.前兩個部分是強制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column...]

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

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

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

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

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

現(xiàn)在來對表t1收集統(tǒng)計信息但不創(chuàng)建直方圖
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統(tǒng)計信息代表了它的選擇率它是通過去時1/num_distinct=1/11=0.09090901來計算出來的.在有直方圖的情況下,density的計算依賴于直方圖的類型和oracle的版本.density值的范圍是0到1之間.當查詢使用這個列作謂詞條件時優(yōu)化器將會使用這個列的density統(tǒng)計信息來評估將要返回的行數(shù).所以 cardinality(基數(shù))=selectivity(選擇率)* number of rows(表的行數(shù))

下面來檢查一下在謂詞條件中列的數(shù)據(jù)分布存在傾斜而沒有直方圖的情況下其基數(shù)評估的情況:
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

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