关于dbms_stats.gather_table_stats

这段时间有客户的10gR2数据库经常遭遇执行计划不稳定的现象，应该是直方图信息（Histogram）+绑定变量窥视（Bind Variable Peeking）造成的问题。

DROP TABLE t;
CREATE TABLE t AS
SELECT rownum n1,
ROUND(rownum/100) n2,
'xxxx' n3
FROM dba_objects
WHERE rownum<=10000;

对于这三种类型的字段，我们都不应该去收集直方图信息，因为没有意义。那么直方图信息之所以需要存在的根本意义在哪里？

一. 我们何时该做
直方图究其根本实际上就是一个数据分布的图示，这个图示是为了在生成SQL执行计划的时候给Oracle的CBO更多的信息，换句话说，就是当在where条件中的某些列可能由于列值的不同而希望CBO制定出不同的执行计划时，我们需要直方图。

反过来说，什么情况下我们不需要直方图呢？或者说直方图的存在是没有意义的呢？
1. 当此列不用于查询时，也就是这个字段永远不会出现在where条件中（注意：即使是用于表连接那也算是出现在where条件中）。
2. 当此列无论给予什么比较值，我们都希望永远是一种执行计划时。上面例子中的3个字段都属于这种情况。
对于N1或者N2出现在where条件中，我们希望永远是用该列上的索引扫描（当然需要在该列上先创建索引），对于N3出现在where条件中，我们希望永远是全表扫描。这样的执行计划的制定，只要有表级别的统计信息就足够了，直方图信息的出现不但不会对制定正确的执行计划有帮助，甚至会出现奇怪的现象导致执行计划不稳定。

exec dbms_stats.gather_table_stats(ownname=>'zk_sit',tabname=>'t',estimate_percent=>20,degree=>4,cascade=>TRUE);

select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';

三. 我们应该怎样做
首先我们要明确直方图信息是有存在的必要的，但是只应该存在在那些应该要存在的列上，Oracle知道哪些列上应该存在吗？Oracle一直在致力于想知道，可惜的是现在做的仍然不够好，全部交给Oracle去做的话（SIZE SKEWONLY或者SIZE AUTO），可能得到的结果就是该收集的没收集，不该收集的收集了一堆。这实际上已经远远不仅仅是技术问题了，而是一个业务问题，应该知道自己的应用的数据分布特点，应该知道哪些列会常被用在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值数量<=bucket数量时，为此类型。对于此类型而言，在dba_tab_histograms视图中的会存在相当于distinct值数量的记录，该视图的ENDPOINT_VALUE字段记录了这些distinct值，而ENDPOINT_NUMBER字段则记录了到此distinct值为止总共有多少条记录，注意这是一个累加值，实际上我们可以用一条记录的ENDPOINT_NUMBER减去上一条记录的ENDPOINT_NUMBER来获知对应于本记录的ENDPOINT_VALUE值有多少条记录。我知道这听上去很拗口，可能也有些难于理解，但是请尽量去理解吧。

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的返回值。

第二个ENDPOINT_VALUE的值相当于如下SQL的返回值。

第三个ENDPOINT_VALUE的值相当于如下SQL的返回值。

依次类推。正是这样的信息告诉了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的判断产生多大影响（至少我还没有研究到），但是至少我们可以知道直方图只应该存在在必须存在的列上，因为除了不必要的收集会消耗不必要的资源，它有更多不可预知的影响会导致性能问题。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24214296/viewspace-1044993/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24214296/viewspace-1044993/