这段时间有客户的10gR2数据库经常遭遇执行计划不稳定的现象，应该是直方图信息（Histogram）+绑定变量窥视（Bind Variable Peeking）造成的问题，藉此分享一下直方图的使用经验，但是我非常希望能听到针对本文的不同声音。测试环境Oracle 11.2.0.1 for Windows X64。
创建一个测试表。
DROP TABLE t;
CREATE TABLE t AS
SELECT rownum n1,
ROUND(rownum/100) n2,
'xxxx' n3
FROM dba_objects
WHERE rownum<=10000;
这样我们生成了一张表T，其中有一万行记录。
N1字段可以认为是主键，有10000个distinct值（1-10000），这是一个数据分布均匀的字段；
N2字段有101个distinct值，除了第一个值0和最后一个值101之外，其它的值都有100行记录，我们可以认为这是一个数据分布均匀的字段；
N3字段有10000个值都是xxxx，实际上这也仍然是一个分布均匀的字段。
对于这三种类型的字段，我们都不应该去收集直方图信息，因为没有意义。那么直方图信息之所以需要存在的根本意义在哪里？
一. 我们何时该做
直方图究其根本实际上就是一个数据分布的图示，这个图示是为了在生成SQL执行计划的时候给Oracle的CBO更多的信息，换句话说，就是当在where条件中的某些列可能由于列值的不同而希望CBO制定出不同的执行计划时，我们需要直方图。
反过来说，什么情况下我们不需要直方图呢？或者说直方图的存在是没有意义的呢？
1. 当此列不用于查询时，也就是这个字段永远不会出现在where条件中（注意：即使是用于表连接那也算是出现在where条件中）。
2. 当此列无论给予什么比较值，我们都希望永远是一种执行计划时。上面例子中的3个字段都属于这种情况。
对于N1或者N2出现在where条件中，我们希望永远是用该列上的索引扫描（当然需要在该列上先创建索引），对于N3出现在where条件中，我们希望永远是全表扫描。这样的执行计划的制定，只要有表级别的统计信息就足够了，直方图信息的出现不但不会对制定正确的执行计划有帮助，甚至会出现奇怪的现象导致执行计划不稳定。
二. 我们可以怎样做
那么我们继续看一下收集直方图信息的方法，众所周知，是需要使用DBMS_STATS.GATHER_TABLE_STATS存储过程的，并且是由这个存储过程中的method_opt参数决定的。可以由以下几种选择。
method_opt=>’for columns size skewonly [column_name]‘
什么是SKEWONLY？先看一下Oracle官方文档中的定义。
Oracle determines the columns to collect histograms based on the data distribution of the columns.
也就是要看列上的数据分布，那么按照我们前面提到的，T表中的三列数据分布都是均匀的，并不应该收集任何列上的直方图信息，实际呢？
EXEC dbms_stats.gather_table_stats(ownname=>'KAMUS',tabname=>'T',estimate_percent=>NULL,method_opt=>'for all columns size skewonly',CASCADE=>true);
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
N3 .00005 1 FREQUENCY
N2 .00005 101 FREQUENCY
N1 .0001 1 NONE
可以看到在N2上收集了直方图信息，收集了101个bucket，而N3上则收集了1个bucket的直方图信息（实际上也只能收集一个，因为只有一个distinct值xxxx）。收集直方图信息是耗费资源的，因此SKEWONLY不推荐使用。实际上经历了这么多版本，SKEWONLY的算法一直很奇怪，我没有找到一份文档描述Oracle到底是如何定义SKEWONLY的收集凭据的。
method_opt=>’for columns size auto [column_name]‘
什么是AUTO？再看一下Oracle官方文档中的定义。
Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
也就是在SKEWONLY的基础上额外增加了workload限制，何谓workload？实际上就是我们前面提到的是否在where条件中出现。如果一个列从来没有出现在where条件中，Oracle就认为是没有workload的，那么即使此列上的数据被认为是SKEW的，也不会收集直方图信息，只有出现过至少一次，Oracle才会收集。[via Comment by Dbsnake] 该workload信息可以从数据字典COL_USAGE$中获得，在数据字典中存在的列才是有workload的。
--T表是新创建的，创建完毕以后还没有进行过任何select，因此AUTO选项不会收集任何直方图信息。
EXEC dbms_stats.gather_table_stats(ownname=>'KAMUS',tabname=>'T',estimate_percent=>NULL,method_opt=>'for all columns size auto',CASCADE=>true);
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
N3 1 1 NONE
N2 .00990099 1 NONE
N1 .0001 1 NONE
--执行一次select，再次收集，可以看到N2字段上的直方图信息了。
SQL> SELECT COUNT(*) FROM t WHERE n2=10;
COUNT(*)
----------
100
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'KAMUS',tabname=>'T',estimate_percent=>NULL,method_opt=>'for all columns size auto',CASCADE=>true);
PL/SQL procedure successfully completed.
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
N3 1 1 NONE
N2 .00005 101 FREQUENCY
N1 .0001 1 NONE
因此AUTO也是有问题的，N2字段上的直方图信息仍然会收集，这是我们不需要的。注意，在Oracle10g之后的自动统计信息收集任务中，默认的是FOR ALL COLUMNS SIZE AUTO，这是很有问题的，因此即使是不禁用自动统计信息收集，也应该通过DBMS_STATS.SET_PARAM存储过程（在11gR2版本中被SET_GLOBAL_PREFS存储过程替代）来修改该默认值。通常我们经历的执行计划莫名其妙改变，很多场合都是因为Oracle10g之后的这个统计信息自动收集任务导致的。关于自动统计信息收集，可以参看：Automatic Statistics Gathering
method_opt=>’for columns size repeat [column_name]‘
只有在已有直方图信息的列上再次收集直方图信息。这是我们推荐设置为默认值的方式。具体的执行方法见后文。
method_opt=>’for columns size 1 [column_name]‘
size 1将删除列上的直方图信息，同样也意味着不收集。
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'KAMUS',tabname=>'T',estimate_percent=>NULL,method_opt=>'for all columns size 1',CASCADE=>true);
PL/SQL procedure successfully completed.
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
N3 1 1 NONE
N2 .00990099 1 NONE
N1 .0001 1 NONE
method_opt=>’for all columns size 30′
size自定义大于1的数值，最大为254，如例表示要求收集30个bucket的直方图信息。
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'KAMUS',tabname=>'T',estimate_percent=>NULL,method_opt=>'for all columns size 30',CASCADE=>true);
PL/SQL procedure successfully completed.
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
N3 .00005 1 FREQUENCY
N2 .00995002 30 HEIGHT BALANCED
N1 .0001 30 HEIGHT BALANCED
那么现在到了最重要的部分，对于一个数据库系统，我们到底应该如何收集直方图信息呢？
三. 我们应该怎样做
首先我们要明确直方图信息是有存在的必要的，但是只应该存在在那些应该要存在的列上，Oracle知道哪些列上应该存在吗？Oracle一直在致力于想知道，可惜的是现在做的仍然不够好，全部交给Oracle去做的话（SIZE SKEWONLY或者SIZE AUTO），可能得到的结果就是该收集的没收集，不该收集的收集了一堆。只有我们的DBA才最知道哪些列上应该收集直方图，这实际上已经远远不仅仅是技术问题了，而是一个业务问题，因此DBA应该去熟悉业务，DBA应该知道自己的应用的数据分布特点，应该知道哪些列会常被用在where条件中。
直方图信息收集是一个漫长而持续的过程，没有一蹴而就的方法，也不可能一劳永逸。
我们推荐的方法是：
1. 第一次收集统计信息时，设置method_opt=>FOR ALL COLUMNS SIZE 1，这意味删除所有列上的直方图。
2. 在测试阶段或者在真实生产环境中，在调优SQL的过程中，DBA将会逐渐得知每个需要直方图信息的字段，在这些字段上人工收集统计信息，method_opt=>FOR COLUMNS SIZE AUTO [COLUMN_NAME]，如果你能够明确知道应该收集多少个bucket而手工指定SIZE值那更好。保留收集所有这些字段的脚本，以备数据库系统升级或者迁移时候使用。
3. 在每次数据分布有所变化的时候，更新统计信息，使用method_opt=>FOR ALL COLUMNS SIZE REPEAT，这样只会收集已经存在了直方图信息的字段。
重复2，3步骤，直到系统稳定。这是一个可控的步骤，只有可控，才可能避免不可预知的错误。
[备注1] dba_tab_col_statistics.histogram字段值的含义。
该字段可能包含三个值：NONE，FREQUENCY或者HEIGHT BALANCED。
NONE：就是没有直方图
FREQUENCY：当该列的distinct值数量
HEIGHT BALANCED：当该列的distinct值数量>bucket数量时，为此类型。比如我们上面的例子收集了SIZE 30的直方图信息，对于N1，N2字段来说都超过了30个distinct值，因此为HEIGHT BALANCED类型。此时dba_tab_histograms视图中的ENDPOINT_NUMBER字段就不再是表示有多少条记录了，而仅仅表示bucket编号，SIZE 30的话，就是简单的0-30（需要31个bucket才可以表示SIZE 30）。主要是在于ENDPOINT_VALUE字段，实际上是这样分的，分了30个bucket，现在T表总共是10000条记录，那么每个bucket里面大概会是333条记录。
第一个ENDPOINT_VALUE的值相当于如下SQL的返回值。
select max(n2) from (select n2 from t order by n2) where rownum<=333
第二个ENDPOINT_VALUE的值相当于如下SQL的返回值。
select max(n2) from (select n2 from t order by n2) where rownum<=333*2
第三个ENDPOINT_VALUE的值相当于如下SQL的返回值。
select max(n2) from (select n2 from t order by n2) where rownum<=333*3
依次类推。正是这样的信息告诉了Oracle数据的分布情况，试想一下，如果连续3个bucket的ENDPOINT_VALUE值都10，那么也就是说至少有2个bucket中的记录N2字段都是10，也就是说N2=10的记录至少有666条，越多的bucket有越多相同的ENDPOINT_VALUE值，就表明数据分布越不均匀。
有一点需要额外注意的是：如果某几个bucket的ENDPOINT_VALUE值相同，那么在视图中只会记录最后一个bucket的信息。
[备注2] dba_tab_col_statistics.density字段值的含义。
官方文档中的解释“Density of the column”就跟没说一样。Density的含义是“密度”。
还是看上面的例子，当HISTOGRAM=NONE的时候，N1字段的密度是.0001，一万分之一，1万条记录在某字段上有1万个distinct值，那么该字段的密度就是一万分之一；N3字段的密度是1，1万条记录在某字段上都是相同的，只有1个distinct值，那么该字段的密度就是1。到这里应该可以比较形象的理解Density了。那么对于N2字段来说呢，密度是.00990099，很简单了，1万条记录里面有101个distinct值，10000/101/10000=.00990099。
DENSITY值是会影响CBO判断执行计划的，而回到前文的例子，我们比较一下有直方图和没有直方图时候的同一列的DENSITY值，就会发现很要命的事情，直方图很大地影响到了密度值，目前还没有更科学的方法去研究直方图是如何影响密度的，从而又会对CBO的判断产生多大影响（至少我还没有研究到），但是至少我们可以知道直方图只应该存在在必须存在的列上，因为除了不必要的收集会消耗不必要的资源，它有更多不可预知的影响会导致性能问题。
Blog
(67 unread)
~依米岛~ (67 unread)
Google 黑板报 -- Google 中国的博客网志
(1063 unread)
疯狂的设计 (980 unread)
Oracle Professional (201 unread)
Oracle BI Publisher Blog (508 unread)
王建硕 (375 unread)
DBA's notes (1024 unread)
Chanel [K] (475 unread)
BusinessWeek Online -- Tech Beat (1829 unread)
UCDChina.com (143 unread)
8:10
