oracle直方圖

直方圖概述：

直方圖是一種統(tǒng)計(jì)學(xué)上的工具，并非Oracle專(zhuān)有，通常情況下它會(huì)表現(xiàn)為一種幾何圖形表，這個(gè)圖形表是根據(jù)從實(shí)際環(huán)境中所收集來(lái)的被管理對(duì)象某個(gè)方面的質(zhì)量分布情況的數(shù)據(jù)所繪制成的，通常會(huì)畫(huà)成以數(shù)量為底邊，以頻度為高度的一系列連接起來(lái)的矩形圖，因此直方圖在統(tǒng)計(jì)學(xué)上也稱為質(zhì)量分布圖。
在Oracle中直方圖是一種對(duì)數(shù)據(jù)分布質(zhì)量情況進(jìn)行描述的工具。它會(huì)按照某一列不同值出現(xiàn)數(shù)量多少，以及出現(xiàn)的頻率高低來(lái)繪制數(shù)據(jù)的分布情況，以便能夠指導(dǎo)優(yōu)化器根據(jù)數(shù)據(jù)的分布做出正確的選擇。在某些情況下，表的列中的數(shù)值分布將會(huì)影響優(yōu)化器使用索引還是執(zhí)行全表掃描的決策。當(dāng)where 子句的值具有不成比例數(shù)量的數(shù)值時(shí)，將出現(xiàn)這種情況，使得全表掃描比索引訪問(wèn)的成本更低。這種情況下如果where 子句的過(guò)濾謂詞列之上有一個(gè)合理的正確的直方圖，將會(huì)對(duì)優(yōu)化器做出正確的選擇發(fā)揮巨大的作用，使得SQL語(yǔ)句執(zhí)行成本最低從而提升性能。
在分析表或索引時(shí)，直方圖用于記錄數(shù)據(jù)的分布。通過(guò)獲得該信息，基于成本的優(yōu)化器就可以決定使用將返回少量行的索引，而避免使用基于限制條件返回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上構(gòu)建直方圖。
構(gòu)造直方圖最主要的原因就是幫助優(yōu)化器在表中數(shù)據(jù)嚴(yán)重偏斜時(shí)做出更好的規(guī)劃 。例如，如果一到兩個(gè)值構(gòu)成了表中的大部分?jǐn)?shù)據(jù)(數(shù)據(jù)偏斜)，相關(guān)的索引就可能無(wú)法幫助減少滿足查詢所需的I/O數(shù)量。創(chuàng)建直方圖可以讓基于成本的優(yōu)化器知道何時(shí)使用索引才最合適，或何時(shí)應(yīng)該根據(jù)WHERE子句中的值返回表中80％的記錄。
通常情況下在以下場(chǎng)合中建議使用直方圖：
1、當(dāng)Where子句引用了列值分布存在明顯偏差的列時(shí)：當(dāng)這種偏差相當(dāng)明顯時(shí)，以至于WHERE 子句中的值將會(huì)使優(yōu)化器選擇不同的執(zhí)行計(jì)劃。這時(shí)應(yīng)該使用直方圖來(lái)幫助優(yōu)化器來(lái)修正執(zhí)行路徑。（注意：如果查詢不引用該列，則創(chuàng)建直方圖沒(méi)有意義。這種錯(cuò)誤很常見(jiàn)，許多 DBA 會(huì)在偏差列上創(chuàng)建柱狀圖，即使沒(méi)有任何查詢引用該列。）
2、當(dāng)列值導(dǎo)致不正確的判斷時(shí)：這種情況通常會(huì)發(fā)生在多表連接時(shí)，例如，假設(shè)我們有一個(gè)五項(xiàng)的表聯(lián)接，其結(jié)果集只有10行。Oracle 將會(huì)以一種使第一個(gè)聯(lián)接的結(jié)果集（集合基數(shù)）盡可能小的方式將表聯(lián)接起來(lái)。通過(guò)在中間結(jié)果集中攜帶更少的負(fù)載，查詢將會(huì)運(yùn)行得更快。為了使中間結(jié)果最小化，優(yōu)化器嘗試在SQL執(zhí)行的分析階段評(píng)估每個(gè)結(jié)果集的集合基數(shù)。在偏差的列上擁有直方圖將會(huì)極大地幫助優(yōu)化器作出正確的決策。如優(yōu)化器對(duì)中間結(jié)果集的大小作出不正確的判斷，它可能會(huì)選擇一種未達(dá)到最優(yōu)化的表聯(lián)接方法。因此向該列添加直方圖經(jīng)常會(huì)向優(yōu)化器提供使用最佳聯(lián)接方法所需的信息。

如何使用直方圖：

為表的所有索引字段建立直方圖：

exec dbms_stats.gather_table_stats('HDC_DAG','EB_JBXX',method_opt => 'for all indexed columns size 254',cascade => true);

為指定字段建立直方圖：

exec dbms_stats.gather_table_stats('HDC_DAG','EB_JBXX',method_opt => 'for columns size 100 xb',cascade => true);

exec dbms_stats.gather_table_stats('HDC_DAG','DA_GRJBXX', estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for columns xm size auto ',cascade => true);

刪除直方圖：

exec dbms_stats.gather_table_stats('用戶', '表',cascade=>false, method_opt=>'for columns 列size 1');

exec dbms_stats.delete_column_stats('HDC_DAG','EB_JBXX','JLLYXTBH',col_stat_type => 'HISTOGRAM');

查看列的直方圖存儲(chǔ)桶(bucket)信息：

select * from user_histograms  t where t.TABLE_NAME  ='EB_JBXX' and t.COLUMN_NAME ='XM';

直方圖的分析是建立在索引的分析上

創(chuàng)建直方圖的考慮因素：

其中size 10指定的是直方圖所需的存儲(chǔ)桶(bucket)數(shù)，所謂存儲(chǔ)桶可以理解為存儲(chǔ)數(shù)據(jù)的容器，這個(gè)容器會(huì)按照數(shù)據(jù)的分布將數(shù)據(jù)盡量平均到各個(gè)桶里，如一張表如果有6000條記錄，那么每個(gè)桶中平均就會(huì)有600條記錄，但這只是一個(gè)平均數(shù)，每個(gè)桶中的記錄數(shù)并不會(huì)相等，它會(huì)將高頻出現(xiàn)記錄集中在某一些桶中，低頻記錄會(huì)存放在少量桶中，因此如果存儲(chǔ)桶(bucket)數(shù)合適的增加就會(huì)減少高頻記錄使用的桶數(shù)，統(tǒng)計(jì)結(jié)果也會(huì)更加準(zhǔn)確（可以避免被迫將低頻記錄存入高頻桶中，影響優(yōu)化器生成準(zhǔn)確的執(zhí)行計(jì)劃）。所以我們最后得到的直方圖信息的準(zhǔn)確性就由兩個(gè)數(shù)值決定，一個(gè)是BUCTET的個(gè)數(shù)，一個(gè)NUM_DISTINCT的個(gè)數(shù)。所以創(chuàng)建直方圖時(shí)首先要正確地估計(jì)存儲(chǔ)桶(bucket)數(shù)。默認(rèn)情況時(shí)，Oracle的直方圖會(huì)產(chǎn)生75個(gè)存儲(chǔ)桶?？梢园?span lang="en-us" xml:lang="en-us">SIZE的值指定在1~254之間。