Histogram總結[@more@]

總結

Histogram主要是提供資料的分佈情況，以便Oracle的CBO能夠做出最優的執行計劃。

直方圖的型別：

基於寬度平衡Histogram

將欄位的屬性分成相等的寬度，稱作bucket。並計算行數同樣的值被放置在同一個bucket中，如果幾個行是同樣的值，會放置為同一個bucket中，增機bucket的高度。

基於高度平衡Histogram

每個bucket有幾乎相同數量的同樣數量的行。如果幾行是相同的值，將被放置到同一個或者分散到幾個bucket上，也就是說同樣值得行被放置在幾個bucket中，而每一個bucket的高度，就是存放的行數幾乎相同。高度平衡Histogram更適合於計算選擇度的估計。

Popular值和nonpopular值

在新增Histogram時候，Oracle 9i區分Popular值和nonpopular值。

Popular值意思是這個值是多次作為bucket的結束點的值；而只是顯示為一次或者根本就沒有作為bucket的結束點的值的成為nonpopular值。

DENSITY統計

在新增一個Histogram地時候，DENSITY統計被計算。這個值為NUM_DISTINCE加1。

在最佳化器使用Histogram來估計選擇度的時候，範圍掃描的選擇度是由這個值得bucket分佈的數目來決定。當為等值得選擇度則是由考慮兩種情況：

一、如果這個值是Popular值的話，將根據bucket的數目來確定。

二、如果是nonpopular值的話，這是根據DENSITY統計來確定。

Histogram的收集

可以有兩種方式收集Histogram統計資料：analyze和dbms_stats包。

在這兩種收集的時候，可以有指定bucket數目或者使用預設的bucket數目。

什麼時間使用Histogram

主要是在欄位上的資料有高度的資料分佈的不均勻，即有部分資料佔整個資料量的比例很大。這樣特徵的表應該建立Histogram。

避免使用Histogram的情況：

1) 這部分欄位不會在where字句中使用；

2) 欄位是唯一的，只是在等值使用；

3) 所有的在此欄位中的使用條件是用於繫結變數；

4) 欄位中的資料是被均勻分配的

這裡的第三點，說明在使用繫結變數的時候，Oracle的最佳化器並不知道這個欄位上將繫結的變數的確切的值。所以並不能用到這個Histogram統計資料。而欄位的值是唯一的話，說明在這個欄位上所有的值將是均勻分佈的。而在條件是等值使用的時候，Histogram統計資料是沒有任何意義的。

需要bucket的數目

預設的bucket的數量是75。可以根據自己的需要指定bucket的數量。最大的bucket數量是255。但是決定bucket數量的多少以達到最優的結果，是需要依靠經驗來確定。同時在包dbms_stats中可以指定自動決定bucket的數量。

如果在這個欄位上的值相對比較少的話，建議設定bucket的數量大於頻繁使用的distinct值。

另外在一些資料上說，如果指定的bucket的數量小於欄位的distinct值的話，Oracle將採用基於高度的直方圖來反映資料的分佈。

檢視histogram的統計資料

User/all_histogram

Histogram的使用提示

n 可以使用for all indexed columns選項

n 如果資料分佈可能動態改變得話，可以定期收集histogram統計資料；

n 如果是使用繫結變數，histogram資料將不會被使用。

n Oracle推薦如果不需要充分提高效能的話，不需要使用histogram統計

關於histograms的使用的一個問題：

在正常的資料分佈而是沒有資料的某種分佈偏差的情況下，如果收集histograms統計資訊，在cursor_sharing這個引數設定為similar的時候容易出現cursor的子版本過多的問題。所以在使用histograms的時候需要注意這個問題。

詳細請參考：

http://blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx

之所以產生這樣的原因是這樣的：在使用收集histogram統計資料之後，如果使用使用繫結變數，histogram資料將不會被使用。在我們設定引數cursor_sharing有三種選擇：如果是force的時候，這時候，Oracle會將SQL文的變數無論三七二十一就變成需要的繫結變數的形式，這樣我們採集的histogram資料將不會被使用。也就達不到我們所需要的最佳化效果了。如果設定為exact的時候，就會被不使用繫結變數，如果自己本身沒有繫結變數的時候，如果設定為similar的時候，每一次最佳化器會根據histogram統計資料解析SQL，以達到最優的結果。但是如果這個資料的分佈並不是有分佈不均得現象，就不要使用histogram統計資料，這或許是為什麼在Oracle的文件中說：

Do not use histograms unless they substantially improve performance.

的其中一個原因之一了。

作個試驗：

SQL> create table t as select rownum id from all_objects;

Table created

SQL> create index t_idx on t (id);

Index created

SQL>

SQL> begin

2 dbms_stats.gather_table_stats

3 ( ownname => USER,

4 tabname => 'T',

5 method_opt => 'for all indexed columns size 254',

6 cascade => TRUE

7 );

8 end;

9 /

PL/SQL procedure successfully completed

SQL>

SQL> alter session set cursor_sharing=similar;

Session altered

SQL> select * from t CS_SIMILAR where id = 1;

ID

----------

1

SQL> select * from t CS_SIMILAR where id = 50;

ID

----------

50

SQL> select * from t CS_SIMILAR where id = 99;

ID

----------

99

SQL> select * from t CS_SIMILAR where id = 1;

ID

----------

1

SQL> select * from t CS_SIMILAR where id = 50;

ID

----------

50

SQL> select * from t CS_SIMILAR where id = 99;

ID

----------

99

SQL> select sql_text from v$sql where sql_text like 'select * from t CS% where id = %' order by sql_text;

SQL_TEXT

--------------------------------------------------------------------------------

select * from t CS_SIMILAR where id = :"SYS_B_0"

這時候就可以看到在設定cursor_sharing=similar時候，會有不同的sql出現

SQL> select * from v$sql_shared_cursor

2 a where a.kglhdpar='78E80FF0';

ADDRESS KGLHDPAR UNBOUND_CURSOR SQL_TYPE_MISMATCH OPTIMIZER_MISMATCH OUTLINE_MISMATCH STATS_ROW_MISMATCH LITERAL_MISMATCH SEC_DEPTH_MISMATCH EXPLAIN_PLAN_CURSOR BUFFERED_DML_MISMATCH PDML_ENV_MISMATCH INST_DRTLD_MISMATCH SLAVE_QC_MISMATCH TYPECHECK_MISMATCH AUTH_CHECK_MISMATCH BIND_MISMATCH DESCRIBE_MISMATCH LANGUAGE_MISMATCH TRANSLATION_MISMATCH ROW_LEVEL_SEC_MISMATCH INSUFF_PRIVS INSUFF_PRIVS_REM REMOTE_TRANS_MISMATCH LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH OVERLAP_TIME_MISMATCH SQL_REDIRECT_MISMATCH MV_QUERY_GEN_MISMATCH USER_BIND_PEEK_MISMATCH TYPCHK_DEP_MISMATCH NO_TRIGGER_MISMATCH FLASHBACK_CURSOR

-------- -------- -------------- ----------------- ------------------ ---------------- ------------------ ---------------- ------------------ ------------------- --------------------- ----------------- ------------------- ----------------- ------------------ ------------------- ------------- ----------------- ----------------- -------------------- ---------------------- ------------ ---------------- --------------------- ------------------------- -------------------- --------------------- --------------------- --------------------- ----------------------- ------------------- ------------------- ----------------

78E7B484 78E80FF0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

78E81888 78E80FF0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

78E6D4D8 78E80FF0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

SQL>

再次檢視v$sql_shared_cursor會發現這三個sql是獨立的，並沒有什麼不同的地方。

TOM 的這篇文章也是講的這個話題：

Histogram總結

總結

相關文章