溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
1 直方圖的含義
在Oracle數(shù)據(jù)庫中，CBO會默認認為目標(biāo)列的數(shù)據(jù)在其最小值LOW_VALUE和最大值HIGH_VALUE之間均勻分布的，并且會按照這個均勻分布原則來計算對目標(biāo)列施加查詢條件后的可選擇率以及結(jié)果集的Cardinality，進而據(jù)此來計算成本值并選擇執(zhí)行計劃。但目標(biāo)列的數(shù)據(jù)是均勻分布這個原則并不總是正確的，在實際的系統(tǒng)中，我們很容易就能看到一些目標(biāo)列的數(shù)據(jù)分布是不均勻的，甚至是極度傾斜、分布極度不均衡的。對這樣的列如果還按照均勻分布的原則去計算可選擇率與Cardinality，并據(jù)此來計算成本、選擇執(zhí)行計劃，那么CBO所選擇的執(zhí)行計劃就可能是不合理的，甚至是錯誤的。
看一個由于數(shù)據(jù)分布極不均衡而導(dǎo)致CBO選錯執(zhí)行計劃的例子：
zx@ORCL>create table t1 (a number(5),b varchar2(5)); Table created. zx@ORCL>declare cnt number(5) := 1; 2 begin 3 loop 4 insert into t1 values(1,'1'); 5 if cnt=10000 then 6 exit; 7 end if; 8 cnt:=cnt+1; 9 end loop; 10 insert into t1 values(2,'2'); 11 commit; 12 end; 13 / PL/SQL procedure successfully completed. zx@ORCL>select b,count(*) from t1 group by b; B COUNT(*) --------------- ---------- 1 10000 2 1 zx@ORCL>create index t1_ix_b on t1(b); Index created.
對表T1不收集直方圖統(tǒng)計信息的方式收集一下統(tǒng)計信息：
zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T1',estimate_percent=>100,method_opt=>'for all columns size 1'); PL/SQL procedure successfully completed. zx@ORCL>select * from t1 where b='2'; A B ---------- --------------- 2 2 zx@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5p7b772tpcvm4, child number 0 ------------------------------------- select * from t1 where b='2' Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 5001 | 25005 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- .....省略部分輸出
從執(zhí)行計劃可以看出執(zhí)行計劃走的是全表掃描，但是很顯然應(yīng)該走索引T1_IX_B。這是因為CBO默認認為列B的數(shù)據(jù)是均勻分布的，而列B上的distinct值只有1和2這兩值，所以CBO評估出來的對列B施加等值查詢條件的可選擇率就是1/2,進而評估出來對列B施加等值查詢條件的結(jié)果集的Cardinality就是5001：
zx@ORCL>select round(10001*(1/2)) from dual; ROUND(10001*(1/2)) ------------------ 5001
正因為CBO評估出上述等值查詢要返回結(jié)果集的Cardinality是5001，已經(jīng)占了表T1總記錄數(shù)的一半，所以CBO認為此時再走列B上的索引T1_IX_B就已經(jīng)不合適了，進而就選擇了對列T1的全表掃描。但實際上，CBO對上述等值查詢要返回結(jié)果集的Cardinality的評估已經(jīng)與事實嚴重不符，評估出來的值是5001，其實卻只有1，差了好幾個數(shù)量級。
CBO這里選擇了執(zhí)行計劃，正確的執(zhí)行計劃應(yīng)該是走索引T1_IX_B。CBO選錯執(zhí)行計劃的根本原因是表T1的列B的分布實際上是極度不均衡的(列B一共就兩值，其中10000個1，只有1個2)，CBO在評估的一開始所用的原則就錯了，當(dāng)然結(jié)果也就錯了。
為了解決上述問題，Oracle引入了直方圖(Histogram)。直方圖是一種特殊的列統(tǒng)計信息，它詳細描述了目標(biāo)列的數(shù)據(jù)分布情況。直方圖實際上存儲在數(shù)據(jù)字典基表HISTGRM$中，可以通過數(shù)據(jù)字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS來分別查看表、分區(qū)表的分區(qū)和分區(qū)表的子分區(qū)的直方圖統(tǒng)計信息。
如果對目標(biāo)列收集了直方圖，則意味著CBO將不再認為該目標(biāo)列上的數(shù)據(jù)是均勻分布的了，CBO就會用該目標(biāo)列上的直方圖統(tǒng)計信息來計算對該列施加查詢條件后的可選擇率和返回結(jié)果集的Cardinality，進而據(jù)此計算成本并選擇相應(yīng)的執(zhí)行計劃。
還用上面的例子，對表T1的列B收集了直方圖統(tǒng)計信息后，CBO正確地評估出了返回結(jié)果集的Cardinality不是5001而是1，進而就正確地選擇了走索引T1_IX_B的執(zhí)行計劃：
zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T1',estimate_percent=>100,method_opt=>'for all columns size auto',cascade=>true); PL/SQL procedure successfully completed. #清空shared_pool,生產(chǎn)系統(tǒng)不要隨便執(zhí)行 zx@ORCL>alter system flush shared_pool; System altered. zx@ORCL>select * from t1 where b='2'; A B ---------- --------------- 2 2 zx@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5p7b772tpcvm4, child number 0 ------------------------------------- select * from t1 where b='2' Plan hash value: 3579362925 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_IX_B | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- .....省略部分輸出
所以，直方圖就是專門為了準確評估這種分布不均勻的目標(biāo)列的可選擇率、結(jié)果集的Cardinality而被Oracle引入的，它詳細描述了目標(biāo)列的數(shù)據(jù)分布情況，并將這些分布情況記錄在數(shù)據(jù)字典里，相當(dāng)于直觀地告訴了CBO這些列的數(shù)據(jù)分布情況，于是CBO就能據(jù)此來做出相對準確的判斷。
2 直方圖的類型
Oracle數(shù)據(jù)庫里的直方圖使用了一種稱為Bucket(桶)的方式來描述目標(biāo)列的數(shù)據(jù)分布。這有點類似哈希算法的Bucket，它實際上是一個邏輯上的概念，相當(dāng)于分組，每個Bucket就是一組，每個Bucket里會存儲一個或多個目標(biāo)列上的數(shù)據(jù)。Oracle會用兩個維度來描述一個Bucket，這兩個維度分別是ENDPOINT NUMBER和ENDPOINT VALUE。Oracle會將每個Bucket的維度ENDPOIONTNUMBER和ENDPOINT VALUE記錄在數(shù)據(jù)字典基表HISTGRM$中，這樣就達到了目標(biāo)列的直方圖統(tǒng)計信息記錄在數(shù)據(jù)字典中的目的。維度ENDPOINT NUMBER和ENDPOINT VALUE分別對應(yīng)于數(shù)據(jù)字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS及DBA_SUBPART_HISTOGRAMS中的字段ENDPOINT_NUMBER/BUCKET_NUMBER和ENDPOINT_VALUE。同時，Oracle還會記錄目標(biāo)列的直方圖統(tǒng)計信息所占用的Bucket的總數(shù)，可以通過數(shù)據(jù)字典DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS及DBA_SUBPART_COL_STATISTICS中字段NUM_BUCKETS來查看目標(biāo)列對應(yīng)直方圖的Bucket的總數(shù)。
在Oracle 12c之前，Oracle數(shù)據(jù)庫里的直方圖分為兩種類型，分別是Frequency和HeightBalanced(Oracle 12c中還存在名為Top-Frequency和Hybrid類型的直方圖)。在Oracle 12以之前，如果存儲在數(shù)據(jù)字典里描述目標(biāo)列直方圖的Buckt的數(shù)量等于目標(biāo)列的distinct值的數(shù)量，則這種類型的直方圖就是Frequency類型的直方圖。如果存儲在數(shù)據(jù)字典里描述目標(biāo)列直方圖的Bucket的數(shù)量小于目標(biāo)列的distinct值的數(shù)量，則這種類型的直方圖就是Height Balanced類型的直方圖。
2.1 Frequency類型的直方圖
對于Frequency類型的直方圖而言，目標(biāo)列直方圖的Bucket的數(shù)量就等于目標(biāo)列的distinct的數(shù)量，此時目標(biāo)列有多個個distinct值，Oracle在數(shù)據(jù)字典DBA_TAB_HISTOGRAMS、DBA-PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中就會存儲多少條記錄，每一條記錄不代表了對其中的一個Bucket的描述，上述數(shù)據(jù)字典中的字段ENDPOINT_VALUE記錄了這些distinct值，而字段ENDPOINT_NUMBER是一個累加值，實際上，我們可以用一條記錄的ENDPOINT_NUMBER值減去它的上一條記錄的ENDPOINT_NUMBER值來得到這條記錄本身所對應(yīng)的ENDPOINT_VALUE值的記錄數(shù)。
實際上，Frequency類型的直方圖就是把目標(biāo)列的每一個distinct值都記錄在數(shù)據(jù)字典里，同時在數(shù)據(jù)字典里記錄記錄每個distinct值在目標(biāo)表里一共有多少條記錄，這樣CBO就能非常清楚地知道目標(biāo)列在目標(biāo)表里的實際數(shù)據(jù)分布情況了。這種Frequency類型的直方圖所對應(yīng)的收集方法并不適用于目標(biāo)列的distinct值非常多的情形，所以Oracle對Frequence類型的直方圖有如下限制：Frequency類型的直方圖所對應(yīng)的Bucket的數(shù)量不能超過254(注意，Oracle 12c 中將不再有這一限制，在Oracle 12c中Frequency類型的直方圖所對應(yīng)的Bucket的數(shù)量可以超過254)，即Frequency類型的直方圖只適用于那些目標(biāo)列的distinct值數(shù)量小于或等于254的情形。
zx@ORCL>create table h (x number); Table created. zx@ORCL>declare i number; begin for i in 1..3296 loop insert into h values(1); 5 6 end loop; 7 for i in 1..100 loop 8 insert into h values(3); 9 end loop; 10 for i in 1..798 loop 11 insert into h values(5); 12 end loop; 13 for i in 1..3970 loop 14 insert into h values(7); 15 end loop; 16 for i in 1..16293 loop 17 insert into h values(10); 18 end loop; 19 for i in 1..3399 loop 20 insert into h values(16); 21 end loop; 22 for i in 1..3651 loop 23 insert into h values(27); 24 end loop; 25 for i in 1..3892 loop 26 insert into h values(32); 27 end loop; 28 for i in 1..3521 loop 29 insert into h values(39); 30 end loop; 31 for i in 1..1080 loop 32 insert into h values(49); 33 end loop; 34 commit; 35 end; 36 / PL/SQL procedure successfully completed. zx@ORCL>select count(*) from h; COUNT(*) ---------- 40000
按照Frequency類型直方圖的定義，如果對列X收集Frequency類型的直方圖，則DBA_TAB_HISTOGRAMS中應(yīng)該有10條記錄，而且這10條記錄的ENDPOINT_VALUE記錄的就是這10個distinct值，對應(yīng)的ENDPOINT_NUMBER就是到此distinct值為止累加的行記錄數(shù)。這10條記錄的ENDPOINT_VALUE和ENDPOINT_NUMBER實際上可以用如下SQL的顯示結(jié)果來模擬：
zx@ORCL>select x as x,count(*) as cardinality,sum(count(*)) over(order by x range unbounded preceding) as cum_cardinality from h group by x; X CARDINALITY CUM_CARDINALITY ---------- ----------- --------------- 1 3296 3296 3 100 3396 5 798 4194 7 3970 8164 10 16293 24457 16 3399 27856 27 3651 31507 32 3892 35399 39 3521 38920 49 1080 40000 10 rows selected.
上述查詢結(jié)果中的列X就模擬了DBA_TAB_HISTOGRAMS中那10條記錄的ENDPOINT_VALUE，列CUM_CARDINALITY就模擬了DBA_TAB_HISTOGRAMS中那10條記錄的ENDPOINT_NUMBER。
對表h的列x來實際收集一下直方圖統(tǒng)計信息
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'H',method_opt=>'for columns size auto X',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed.
收集完統(tǒng)計信息后發(fā)現(xiàn)DBA_TAB_COL_STATISTICS中列x所對應(yīng)的字段HISTOGRAM的值為NONE，這表明現(xiàn)在列x上依然沒有直方圖統(tǒng)計信息：
zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='H'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- H X 10 .1 1 NONE
這種現(xiàn)象是正常的。因為Oracle在自動收集直方圖統(tǒng)計信息時會秉承一個原則，那就是只對那些用過的列(即在SQL語句where條件中出現(xiàn)過的列)收集直方圖統(tǒng)計信息。Oracle會在表SYS.COL_USAGE$中記錄各表中各列的使用情況，在自動收集直方圖統(tǒng)計信息時Oracle會查詢SYS.COL_USAGE$，如果發(fā)現(xiàn)其中沒有目標(biāo)列的使用記錄，那就不會對目標(biāo)列收集直方圖統(tǒng)計信息。表H剛剛建立，還沒有在SQL語句的where條件中使用過列X，所以這里不會對列X收集直方圖統(tǒng)計信息。
收集直方圖的前提條件是：1.列上的數(shù)據(jù)分布不均勻，2.列在sql的where條件中被使用過
zx@ORCL>select name,intcol# from sys.col$ where obj# = (select object_id from dba_objects where object_name='H'); NAME INTCOL# ------------------------------------------------------------------------------------------ ---------- X 1 zx@ORCL>select obj#,intcol#,equality_preds from sys.col_usage$ where obj# = (select object_id from dba_objects where object_name='H'); no rows selected zx@ORCL>select count(*) from h where x=10; COUNT(*) ---------- 16293 zx@ORCL>select obj#,intcol#,equality_preds from sys.col_usage$ where obj# = (select object_id from dba_objects where object_name='H'); OBJ# INTCOL# EQUALITY_PREDS ---------- ---------- -------------- 88766 1 1
再次對表H的列X自動收集直方圖統(tǒng)計信息：
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'H',method_opt=>'for columns size auto X',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='H'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- H X 10 .0000125 10 FREQUENCY
另外DBA_TAB_COL_STATISTICS中列x所對應(yīng)的字段HISTORAM的值已經(jīng)由NONE變成了RREQUENCY，這說明現(xiàn)在列X上已經(jīng)有了Frequency類型的直方圖
可以從DBA_TAB_HISTOGRAMS中看到列x的Frequence類型的直方圖的具體信息：
zx@ORCL>select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='H'; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- H X 3296 1 H X 3396 3 H X 4194 5 H X 8164 7 H X 24457 10 H X 27856 16 H X 31507 27 H X 35399 32 H X 38920 39 H X 40000 49 10 rows selected.
從結(jié)果中可以看出，DBA_TAB_HISTOGRAMS中的10條記錄與之前模擬出來的結(jié)果一模一樣。
介紹完Frequency類型的直方圖的含義，現(xiàn)在來討論Oracle數(shù)據(jù)庫里針對文本類型字段的直方圖統(tǒng)計的先天缺陷了。
在Oracle數(shù)據(jù)庫，如果針對文本開的字段收集直方圖統(tǒng)計信息，則Oracle只會將該文本字段的文本值的頭32個字節(jié)(Byte)給取出來(實際上只取頭15個字節(jié))，并將其轉(zhuǎn)換成一個浮點數(shù)，然后就將這個浮點數(shù)作為其直方圖統(tǒng)計信息存儲在上述數(shù)據(jù)字典里。這種處理機制的先天身陷就在于，對于那些超過32個字節(jié)的文本型字段，只要其對應(yīng)記錄的文本值的頭32個字節(jié)相同，Oracle在收集直方圖統(tǒng)計信息時就會認為這引起記錄在該字段的文本值是相同的，即使實際上它們并不相同。這種先天性缺陷會直接影響CBO對相關(guān)文本類型字段的可選擇率及返回結(jié)果集的Cardinality的評估。
使用之前的測試表T1，其中列B為文本型字段
zx@ORCL>select b,count(*) from t1 group by b; B COUNT(*) --------------- ---------- 1 10000 2 1 zx@ORCL>select count(*) from t1 where b='1'; COUNT(*) ---------- 10000 zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T1',estimate_percent=>100,method_opt=>'for columns size auto B'); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T1'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T1 B 2 .000049995 2 FREQUENCY T1 A 2 .5 1 NONE
從DBA_TAB_HISTOGRAMS中查看列B的直方圖具體信息
zx@ORCL>select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='T1'; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- T1 B 10000 2.5442E+35 T1 B 10001 2.5961E+35 T1 A 0 1 T1 A 1 2
從結(jié)果可以看到，由文本型的'1'和'2'轉(zhuǎn)換而來的浮點數(shù)。
轉(zhuǎn)換方法：
select dump('1',16)from dual;
將0x31右邊補0一直補到15個字節(jié)的長度，再將其轉(zhuǎn)換為十進制數(shù)：
zx@ORCL>select dump('1',16)from dual;
DUMP('1',16)
------------------------------------------------
Typ=96 Len=1: 31
zx@ORCL>select to_number('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')from dual;
TO_NUMBER('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
----------------------------------------------------------------------------
2.5442E+35轉(zhuǎn)換出的值與數(shù)據(jù)字典的數(shù)據(jù)一致。
再創(chuàng)建一個測試表T2，有一個長度為33字節(jié)的文本型字段B：
zx@ORCL>create table t2(b varchar2(33));
Table created.
zx@ORCL>insert into t2 values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1');
1 row created.
zx@ORCL>insert into t2 values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2');
1 row created.
zx@ORCL>insert into t2 values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2');
1 row created.
zx@ORCL>commit;
Commit complete.這三條記錄的頭32個字節(jié)均相同，均為32個a，但distinct值有兩個
zx@ORCL>select b,length(b)from t2; B LENGTH(B) --------------------------------------------------------------------------------------------------- ---------- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1 33 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2 33 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2 33 zx@ORCL>select count(distinct(b)) from t2; COUNT(DISTINCT(B)) ------------------ 2
使用一下列B，以讓SYS.COL_USAGE$中有列B的使用記錄：
select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2';
對列B以自動方式收集直方圖：
zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T2',estimate_percent=>100,method_opt=>'for columns size auto B'); PL/SQL procedure successfully completed.
現(xiàn)在DBA_TAB_COL_STATISTICS中列B所對應(yīng)的字段HISTOGRAM的值為FREQUENCY(注意：10.2.0.4和11.2.0.1為FREQUENCY,11.2.0.4為HEIGHT BALANCED)，說明現(xiàn)在列B上已經(jīng)有了Frequency類型的直方圖統(tǒng)計信息：
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T2'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T2 B 1 .166666667 1 FREQUENCY
注意，上述查詢結(jié)果中文本型字段B的不同distinct的值只有1個，Frequency類型的直方圖所在的Bucket數(shù)量也只有1個，這明顯和事實不符。其實這已經(jīng)說明了對那些超過32字節(jié)的文本型字段而言，只要對應(yīng)記錄的文本值的頭32個字節(jié)相同，Oracle在收集直方圖統(tǒng)計信息時就會認為這些記錄在該字段的文本值是相同的，即使實際上它們并不相同。
從DBA_TAB_HISTOGRAMS中看到列B的Frequency類型的直方圖統(tǒng)計信息的具體內(nèi)容，這進一步證實了上述結(jié)論：
SQL> select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='T2';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
T2 B 3 5.0563E+35
SQL> select dump('a','16') from dual;
DUMP('A','16')
------------------------------------------------
Typ=96 Len=1: 61
SQL> select to_number('616161616161616161616161616161','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;
TO_NUMBER('616161616161616161616161616161','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
----------------------------------------------------------------------------
5.0563E+35通過計算相互符合。
對表T2執(zhí)行如下sql
select count(*) fromt2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1';
實際返回結(jié)果集的Cardinality為1
但從執(zhí)行計劃的結(jié)果可以看出CBO錯誤地評估出上述SQL返回結(jié)果集的Cardinality為3：
SQL> select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'; COUNT(*) ---------- 1 SQL> select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 3n69wfhjuj4sg, child number 0 ------------------------------------- select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1' Plan hash value: 3321871023 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 34 | | | |* 2 | TABLE ACCESS FULL| T2 | 3 | 102 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
這是因為DBA_TAB_HISTOGRAMS中列B的Frequency類型的直方圖只有1個Bucket，這會使Oracle認為表T2中只有一個distinct文本值32個'a'，所以對于上述SQL而言，Oracle會認為該SQL要訪問的就是表T2的所有數(shù)據(jù)。
2.2 Height Balanced類型的直方圖
前面介紹到Oracle 12c之前，Frequence類型的直方圖對應(yīng)的Bucket的數(shù)量不能超過254，那如果目標(biāo)列的distinct值的數(shù)量大于254呢？此時Oracle會對目標(biāo)列收集Height Balanced類型的直方圖。
zx@ORCL>create table t1(id number); Table created. zx@ORCL>begin 2 for i in 1..254 loop 3 for j in 1..i loop 4 insert into t1 values(i); 5 end loop; 6 end loop; 7 commit; 8 end; 9 / PL/SQL procedure successfully completed. #distinct值的數(shù)量為254 zx@ORCL>select count(distinct(id)) from t1; COUNT(DISTINCT(ID)) ------------------- 254 #執(zhí)行一個查詢使id列在where條件中 zx@ORCL>select * from t1 where id=1; ID ---------- 1 #收集直方圖信息 zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',method_opt=>'for columns size auto id',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>col table_name for a10 zx@ORCL>col column_name for a10 zx@ORCL>set linesize 200 zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T1'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T1 ID 254 .000015372 251 FREQUENCY zx@ORCL>select endpoint_value,endpoint_number from dba_tab_histograms where owner=user and table_name='T1'; ENDPOINT_VALUE ENDPOINT_NUMBER -------------- --------------- 1 1 2 3 3 6 4 10 5 15 .... 252 31878 253 32131 254 32385 254 rows selected.
從輸出的結(jié)果可以看出ID列上已經(jīng)有了Frequency類型的直方圖。
現(xiàn)在對表T1再插入一條包含不同ID值的記錄，然后刪除列ID上的直方圖信息，再列ID列重新收集直方圖信息，然后查詢ID列直方圖的類型。
zx@ORCL>insert into t1 values(255); 1 row created. zx@ORCL>commit; Commit complete. zx@ORCL>select count(distinct id) from t1; COUNT(DISTINCTID) ----------------- 255 zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',method_opt=>'for columns size 1 id',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',method_opt=>'for columns size auto id',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T1'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- T1 ID 255 .004243247 254 HEIGHT BALANCED
從輸出的結(jié)果看現(xiàn)在ID列上的直方圖類型已經(jīng)從之前的Frequency變?yōu)榱薍eight Balanced。
對于Height Balanced類型的直方圖而言，即當(dāng)目標(biāo)列直方圖的Bucket的數(shù)量小于目標(biāo)列的distinct值的數(shù)量時，Oracle首先會根據(jù)目標(biāo)列對目標(biāo)表的所有記錄按從小到大的順序排序，然后用目標(biāo)表總的記錄數(shù)除以需要使用的Bucket的數(shù)量，來決定每個Bucket里需要描述的已經(jīng)排好序的記錄數(shù)。假設(shè)目標(biāo)表的總記錄數(shù)為M，需要使用的Bucket數(shù)量為N，每個Bucket里需要描述的已經(jīng)排好序的記錄數(shù)為O，則O=M/N；
然后Oracle會用DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中的每一條記錄的ENDPOINT_NUMBER來記錄Bucket號，Bucket號從0開始，一直到N。其中0號Bucket里存儲的是目標(biāo)列的最小值，所以0號Bucket所在記錄的ENDPOINT_NUMBER值為0，其余Bucket所在記錄的ENDPOINT_NUMBER從1一直遞增到N，這些記錄除了0號Bucket所在記錄的ENDPOINT_VALUE值是目標(biāo)列的最小值外，其他所有記錄的ENDPOINT_VALUE值實際上存儲的是到此記錄所描述述Bucket為此之前所有Bucket描述的記錄里目標(biāo)列的最大值。即除了0號Bucket之外，其他所有記錄的ENDPOINT_VALUE值都是用如下公式來計算的：
最后，Oracle在將這些ENDPOINT_NUMBER和ENDPOINT_VALUE存儲在數(shù)據(jù)字典里時使用了一個節(jié)省存儲空間的技巧：對那些相鄰的公ENDPOINT_NUMBER值不同，但ENDPOINT_VALUE值相同的記錄合并存儲，并且只在數(shù)據(jù)字典中存儲合并后的記錄。比如2號桶的ENDPOINT_NUMBER是2，它的ENDPOINT_VALUE是P，3號桶的ENDPOINT_NUMBER是3，它的ENDPOINT_VALUE也是P，則Oracle就會將上述相鄰的記錄合并且只在數(shù)據(jù)字典中存儲合并后的值。此時合并后的記錄的ENDPOINT_NUMBER是3，ENDPOINT_VALUE是P也就是說DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中Height Balanced類型的直方圖所在記錄的ENDPOINT_NUMBER值可能是不連續(xù)的，這種記錄在數(shù)據(jù)字典里的合并后的記錄所在的ENDPOINT_VALUE，Oracle稱之為popular value。顯然，popular value所在記錄的ENDPOINT_NUMBER值和它上一條記錄的ENDPOINT_NUMBER值之間的差值越大，則意味著該popular value在目標(biāo)表中所占的比例也就越大，它所對應(yīng)的Cardinality也就越大。
我們再來使用之前的H表來說明Height Balanced類型的直方圖
先刪除表H中已存在的Frequency類型的直方圖
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'H',method_opt=>'for columns size 1 X',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed.
對于Height Balanced類型的直方圖而言，目標(biāo)列直方圖的Bucket的數(shù)量會小于目標(biāo)列的distinct值的數(shù)量。這里表H有10個distinct值，如果在收集直方圖統(tǒng)計信息的時候指定Bucket數(shù)量為5，則Oracle就應(yīng)該收集Height Balanced類型的直方圖了。這里收集直方圖統(tǒng)計信息時指定method_opt的值為'for columns size 5 X',這里表示在對列X收集直方圖時已經(jīng)指定所用Bucket的數(shù)量為5(注意，這里的Bucket數(shù)量不含0號Bucket)：
zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'H',method_opt=>'for columns size 5 X',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@ORCL>select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='H'; TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM ---------- ---------- ------------ ---------- ----------- --------------------------------------------- H X 10 .085276318 5 HEIGHT BALANCED
從輸出來看X列所對應(yīng)的字段HISTOGRAM的值為HEIGHT BALANCED，這說明X列上已經(jīng)有Height Balanced類型的直方圖。
現(xiàn)在按照剛才介紹的算法算一下DBA_TAB_HISTOGRAM中存儲的Height Balanced類型的直方圖統(tǒng)計信息的詳情。
現(xiàn)在需要使用的Bucket數(shù)量為5(不含0號Bucket)表H中總的記錄數(shù)為40000，所以每個Bucket里所需要描述的記錄數(shù)為40000/5=8000。
0號Bucket所在記錄的ENDPOINT_NUMBER是0，ENDPOINT_VALUE是表H中10個distinct值中最小值1。
使用如下公式計算出每個Bucket所在記錄的ENDPOINT_VALUE值：
#Bucket1 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000; MAX(X) ---------- 7 #Bucket2 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*2; MAX(X) ---------- 10 #Bucket3 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*3; MAX(X) ---------- 10 #Bucket4 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*4; MAX(X) ---------- 32 #Bucket5 zx@ORCL>select max(x) from (select x from h order by x) where rownum<8000*5; MAX(X) ---------- 49
從上述結(jié)果可以看到2號Bucket和3號Bucket所對應(yīng)記錄的ENDPOINT_VALUE值都是10，所以O(shè)racle會將2號和3號Bucket合并存儲，合并后的記錄ENDPOINT_NUMBER值是3，ENDPOINT_VALUE值是10。這里10就是一個popular value。經(jīng)過上述分析，我們可知DBA_TAB_HISTOGRAMS中的存儲的Height Balanced類型的直方圖統(tǒng)計信息的詳細應(yīng)為如下所示：
ENDPOINT_NUMBER ENDPOINT_VALUE 0 1 1 7 3 10 4 32 5 49
我們查詢DBA_TAB_HISTOGRAMS中列X的Height Balanced類型的直方圖統(tǒng)計信息：
zx@ORCL>select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='H'; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- H X 1 7 H X 3 10 H X 4 32 H X 5 49 H X 0 1
可以看到實際查詢結(jié)果與我們分析的一致。
3 直方圖的收集方法
在Oracle數(shù)據(jù)庫里收集直方圖統(tǒng)計信息，通常是在調(diào)用DBMS_STATS包中的存儲過程GATHER_DATABASE_STATS/GATHER_DICTIONARY_STATS/GATHER_SCHEMA_STAS/GATHER_TABLE_STATS收集統(tǒng)計信息時通過指定輸入?yún)?shù)METHOD_OPT來實現(xiàn)。當(dāng)然也可以使用ANALYZE命令來收集直方圖統(tǒng)計信息，比如使用命令“analyze table h compute statistics forcolumns X”來收集表H的列X的直方圖統(tǒng)計信息。因為ANALYZE命令在收集統(tǒng)計信息方面有先天的缺陷，所以這里只討論用DBMS_STATS包來收集直方圖統(tǒng)計信息。
DBMS_STATS包中上述存儲過程的輸入?yún)?shù)METHOD_OPT可以接受如下的輸入值：
FOR ALL[INDEXES|HIDDEN] COLUMNS [size_clause]
FOR COLUMNS[size_clause] column|attribute [size_clause] [,column|attribute[size_clause]...]
其中的size_clause必須符合如下的格式：
SIZE {integer|REPEAT|AUTO|SKEWONLY}
size_clause子名中各選項的含義如下所述：
Integer：直方圖的Bucket的數(shù)量，必須是在1~254的范圍內(nèi)，1表示刪除該目標(biāo)列上直方圖統(tǒng)計信息。
REPEAT：只對已經(jīng)有直方圖統(tǒng)計信息的列收集直集直方圖統(tǒng)計信息。
AUTO：讓Oracle自行決定是否對目標(biāo)列收集直方圖統(tǒng)計信息，以及使用哪種類型的直方圖。
SKEWONLY：只對數(shù)據(jù)分布不均衡的列收集直方圖統(tǒng)計信息。
使用SCOTT用戶下的表EMP為例來說明：
scott@ORCL> desc emp Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
1)對表EMP所有有索引的列以自動收集的方式收集直方圖統(tǒng)計信息：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all indexed columns size auto');
2)對表EMP上的列EMPNO和DEPTNO以自動收集的方式收集直方圖統(tǒng)計信息：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns size auto EMPNO DEPTNO');
3)對表EMP上的列EMPNO和DEPTNO收集直方圖統(tǒng)計信息，同時指定Bucket數(shù)量均為10：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns size 10 EMPNO DEPTNO');
4)對表EMP上的列EMPNO和DEPTNO收集直方圖統(tǒng)計信息，同時指定列EMPNO的Bucket數(shù)量為10，列DEPTNO的Bucket數(shù)量為5：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns EMPNO size 10 DEPTNO size 5');
5)只刪除表EMP上列EMPNO的直方圖統(tǒng)計信息：
execdbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns EMPNO size 1');
6)刪除表EMP上所有列的直方圖統(tǒng)計信息：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all columns size 1');
參考《基于Oracle的SQL優(yōu)化》
官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30103
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼