oracle 柱狀圖(histogram)
oracle中的柱狀圖是用于記錄表中的數(shù)據(jù)分布質(zhì)量情況的描述，當(dāng)每次使用analyze或者dbms_stat包分析數(shù)據(jù)表及列后，該表的分布情況會(huì)唄保存在統(tǒng)計(jì)表
(user_tab_columns/user_histograms)里面，當(dāng)多表連接時(shí),CBO優(yōu)化器會(huì)根據(jù)柱狀圖提供的信息評(píng)估多表連接時(shí)將產(chǎn)生的成本(cost)或技術(shù)(cardinality)，決定是否使用該列的索引，當(dāng)然，導(dǎo)致CBO不能選擇最優(yōu)執(zhí)行計(jì)劃的因素有多種情況，而柱狀圖只是協(xié)助CBO優(yōu)化器選擇最優(yōu)的執(zhí)行計(jì)劃，在一個(gè)數(shù)據(jù)分布不均勻的表列上建立柱狀圖將有力地保證優(yōu)化器做出正確合理的選擇。
其他因素后面在進(jìn)行探討。 (直方圖的使用不受索引的限制，可以在表的任何列上構(gòu)建直方圖)
1. 搜集柱狀圖
SQL> conn scott/tiger
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'dept',estimate_percent => null,method_opt => 'for all indexed columns',cascade => true);

PL/SQL procedure successfully completed

SQL> select column_name,density,num_buckets,histogram from user_tab_col_statistics where table_name='DEPT';

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
DEPTNO 0.125 4 HEIGHT BALANCED
DNAME 0.25 1 NONE
LOC 0.25 1 NONE
SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from user_tab_histograms where table_name='DEPT';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ -------------------------------------------- --------------- -----------------------------
DEPT DEPTNO 1 10
DEPT DEPTNO 2 20
DEPT DEPTNO 3 30
DEPT DEPTNO 4 40
DEPT DNAME 0 3.388635500875
DEPT LOC 0 3.443005050520
DEPT DNAME 1 4.322850386777
DEPT LOC 1 4.064055440899

8 rows selected
柱狀圖的搜集有三個(gè)參數(shù)，for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:統(tǒng)計(jì)指定列的histograms.N的取值范圍[1,254]; REPEAT上次統(tǒng)計(jì)過(guò)的histograms;AUTO由oracle決定
N的大小;SKEWONLY
在上面柱狀圖搜集中，histogram字段有三個(gè)值，NONE，F(xiàn)REQUENCY或者HEIGHT BALANCED
a. NONE：就是沒(méi)有直方圖
b. FREQUENCY: 當(dāng)該列的distinct值數(shù)量<=bucket數(shù)量時(shí)，為此類型。在user_tab_histograms表中記錄有相關(guān)的值
c. HEIGHT BALANCED：當(dāng)該列的distinct值數(shù)量>bucket數(shù)量時(shí)，為此類型。
d. density字段值的含義 Density的含義是“密度”。DENSITY值是會(huì)影響CBO判斷執(zhí)行計(jì)劃的

2. 并不是所有柱狀圖信息都有存在的必要，產(chǎn)生直方圖的成本是很高的，頻繁分析一個(gè)表，若該表數(shù)據(jù)量非常大，做一次分析可能導(dǎo)致嚴(yán)重的性能問(wèn)題，但是那些列的柱狀圖應(yīng)該存在呢，建
議如下：
a. 第一次收集統(tǒng)計(jì)信息時(shí)，設(shè)置method_opt=>FOR ALL COLUMNS SIZE 1，這意味刪除所有列上的直方圖。
b. 在測(cè)試階段或者在真實(shí)生產(chǎn)環(huán)境中，在調(diào)優(yōu)SQL的過(guò)程中，DBA將會(huì)逐漸得知每個(gè)需要直方圖信息的字段，在這些字段上人工收集統(tǒng)計(jì)信息，method_opt=>FOR COLUMNS SIZE AUTO
[COLUMN_NAME]
c. 在每次數(shù)據(jù)分布有所變化的時(shí)候，更新統(tǒng)計(jì)信息，使用method_opt=>FOR ALL COLUMNS SIZE REPEAT，這樣只會(huì)收集已經(jīng)存在了直方圖信息的字段。
重復(fù)2，3步驟，直到系統(tǒng)穩(wěn)定。
3. 柱狀圖是如何影響執(zhí)行計(jì)劃的,下面通過(guò)示例來(lái)查看
SQL> show user
User is "colin"

SQL> drop table tmp_liuhc_1;

Table dropped
SQL> create table tmp_liuhc_1 as select * from dba_objects;

Table created

SQL> desc tmp_liuhc_1;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y

SQL> select owner,count(*) from tmp_liuhc_1 group by owner;

OWNER COUNT(*)
------------------------------ ----------
MDSYS 885
TSMSYS 3
DMSYS 189
LINK 3
PUBLIC 19987
OUTLN 8
CTXSYS 339
OLAPSYS 720
HR 34
SYSTEM 454
EXFSYS 281
SCOTT 6
DBSNMP 46
ORDSYS 1669
ORDPLUGINS 10
SYSMAN 1321
OE 127
PM 26
SH 306
XDB 682

OWNER COUNT(*)
------------------------------ ----------
IX 53
BI 8
SYS 22912
WMSYS 242
SI_INFORMTN_SCHEMA 8
COLIN 6

26 rows selected
SQL> create index idx_tmp_liuhc on tmp_liuhc_1(owner);

Index created

SQL> select sysdate from dual;

SYSDATE
-----------
2011-10-30
刪除柱狀圖信息，bucket為1時(shí)，相當(dāng)于一個(gè)普通分析，沒(méi)有柱狀圖信息，執(zhí)行計(jì)劃按絕大多數(shù)ID字段內(nèi)容來(lái)選擇走索引，刪除之后刷新shared_pool
SQL> exec dbms_stats.gather_table_stats(ownname => 'colin',tabname => 'tmp_liuhc_1',estimate_percent => null ,method_opt => 'for all columns size 1' ,cascade => true);

PL/SQL procedure successfully completed

SQL> alter system flush shared_pool;

System altered

SQL> select * from user_tab_col_statistics where column_name='OWNER';

TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE
DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN HISTOGRAM
------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- --------------------------
-------------------------------------- ---------- ---------- ----------- ------------- ----------- ------------ ---------- ----------- ---------------
TMP_LIUHC_1 OWNER 26 4249 584442
0.03846153 0 1 2011-10-30 9: 50325 YES NO 6 NONE

SQL> explain plan for select * from tmp_liuhc_1 where owner='COLIN';

Explained

SQL> select * From table(Dbms_Xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774022813
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1936 | 175K| 57 (0
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_LIUHC_1 | 1936 | 175K| 57 (0
|* 2 | INDEX RANGE SCAN | IDX_TMP_LIUHC | 1936 | | 5 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='COLIN')

14 rows selected

SQL> explain plan for select * from tmp_liuhc_1 where owner='SYS';

Explained

SQL> select * From table(Dbms_Xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774022813
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1936 | 175K| 57 (0
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_LIUHC_1 | 1936 | 175K| 57 (0
|* 2 | INDEX RANGE SCAN | IDX_TMP_LIUHC | 1936 | | 5 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')

14 rows selected

SQL> exec dbms_stats.gather_table_stats(ownname => 'colin',tabname => 'tmp_liuhc_1',estimate_percent => null ,method_opt => 'for all columns size auto' ,cascade =>
true);

PL/SQL procedure successfully completed
SQL> select * from user_tab_col_statistics where column_name='OWNER';

TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE
DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN HISTOGRAM
------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- --------------------------
-------------------------------------- ---------- ---------- ----------- ------------- ----------- ------------ ---------- ----------- ---------------
TMP_LIUHC_1 OWNER 26 4249 584442
9.93541977 0 26 2011-10-30 9: 50325 YES NO 6 FREQUENCY

SQL>
SQL> explain plan for select * from tmp_liuhc_1 where owner='COLIN';

Explained

SQL> select * From table(Dbms_Xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774022813
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 558 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_LIUHC_1 | 6 | 558 | 2 (0
|* 2 | INDEX RANGE SCAN | IDX_TMP_LIUHC | 6 | | 1 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='COLIN')

14 rows selected

SQL> explain plan for select * from tmp_liuhc_1 where owner='SYS';

Explained

SQL> select * From table(Dbms_Xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1961695573
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22912 | 2080K| 160 (2)| 00:00:02
|* 1 | TABLE ACCESS FULL| TMP_LIUHC_1 | 22912 | 2080K| 160 (2)| 00:00:02
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')

13 rows selected
從以上可以看出，當(dāng)刪除柱狀圖時(shí)，查詢SYS用戶時(shí)，CBO按大多數(shù)ID字段的內(nèi)容，選擇走索引；當(dāng)搜集柱狀圖后，CBO選擇了正確的執(zhí)行計(jì)劃，走全表掃描，因?yàn)榍懊嬉呀?jīng)查詢了，SYS用戶下的表占用了決大部分。

附錄：附帶兩張表的解釋信息，此處的表DBA_TAB_COLUMNS和表user_tab_col_statistics是同樣效果
DBA_TAB_HISTOGRAMS
該視圖列出了所有表上列的柱狀圖信息。

字段名稱 描述
---------------------------------------------------------
OWNER 表的所有者
TABLE_NAME 表名
COLUMN_NAME 列名(字段名)
ENDPOINT_NUMBER 終點(diǎn)數(shù)值，也就是bucket的編號(hào)值
ENDPOINT_VALUE 根據(jù)該bucket的編號(hào)值，規(guī)格化的終點(diǎn)字段值，
如果字段類型是數(shù)字，則顯示該字段值，
如果是其他類型的，則是被規(guī)格化的數(shù)值。

DBA_TAB_COLUMNS
該視圖包含了所有表上列的描述信息。
(注意：雖然視圖和簇的信息也包含在該視圖中，但沒(méi)有相關(guān)的柱狀圖信息)

字段名稱 描述
----------------------------------------------------------
OWNER 表的所有者
TABLE_NAME 表名
COLUMN_NAME 列名(字段名)
DATA_TYPE 字段的數(shù)據(jù)類型
DATA_LENGTH 該列定義的長(zhǎng)度
DATA_PRECISION NUMBER或FLOAT數(shù)值類型的精度
DATA_SCALE 定義的小數(shù)位數(shù)
NULLABLE 是否允許NULL值
COLUMN_ID 該列在表中的順序的位置，表中第幾個(gè)字段
DEFAULT_LENGTH 字段默認(rèn)值的長(zhǎng)度
DATA_DEFAULT 字段默認(rèn)值
NUM_DISTINCT 該字段不同值的數(shù)量
LOW_VALUE 該字段的最小值，
該值是內(nèi)部按16進(jìn)制存儲(chǔ)的該值的前32 bytes內(nèi)容
HIGH_VALUE 該字段的最大值，
該值是內(nèi)部按16進(jìn)制存儲(chǔ)的該值的前32 bytes內(nèi)容
DENSITY 該字段的密度(不同值的比例值，
比如一個(gè)字段只有2個(gè)不同值，那么該字段密度為0.5)
NUM_NULLS 該字段NULL值的數(shù)量
NUM_BUCKETS 該字段柱狀圖中bucket的數(shù)量
LAST_ANALYZED 最近一次分析表的時(shí)間
SAMPLE_SIZE 采樣數(shù)據(jù)的數(shù)量(根據(jù)分析時(shí)的百分比得出的數(shù)量，
例如100%分析，該采樣值就是行的數(shù)量)