Oracle Histogram Investigation

这段时间有客户的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值数量<=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的返回值。
[sourcecode language=”sql” light=”true”]select max(n2) from (select n2 from t order by n2) where rownum<=333[/sourcecode]
第二个ENDPOINT_VALUE的值相当于如下SQL的返回值。
[sourcecode language=”sql” light=”true”]select max(n2) from (select n2 from t order by n2) where rownum<=3332[/sourcecode]
第三个ENDPOINT_VALUE的值相当于如下SQL的返回值。
[sourcecode language=”sql” light=”true”]select max(n2) from (select n2 from t order by n2) where rownum<=333
3[/sourcecode]

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

[备注3] 参阅其它的文章
Choosing An Optimal Stats Gathering Strategy
DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS

11 Comments Add yours

  1. dbsnake says:

    非常好!补充一点——col_usage$里会记录某个列的使用情况(也就是你文中提到的workload),对skew的列指定size auto并采集统计信息的时候,如果col_usage$里没有skew列的使用信息,那么oracle就不会对上述skew列产生histogram——这个是和文中的测试结果一致。

  2. kamus says:

    @dbsnake
    Thanks for your comment. 🙂

  3. ztg says:

    好文!没有完全读懂!
    “我们应该怎样做”
    设置method_opt=>FOR ALL COLUMNS SIZE 1
    我觉得可以先分析索引列的直方图,在根据应用分析其他列的histogram,某些不是用的列,或者数据分布均匀的列,不分析直方图。
    method_opt=>FOR ALL index COLUMNS
    有不清楚的地方就是blob字段的表,blob不会产生直方图吧?这个问题,有点弱!

  4. kamus says:

    @ztg
    并不是所有的被索引列都需要直方图信息,比如主键索引字段完全不需要histogram。
    至于LOBs,那可能比较复杂,LOBs上的索引通常应该跟Text有关吧,我并没有很多经验。

  5. gengmao says:

    11G有adaptive cursor sharing加上cursor_sharing=force,数据均匀分布的列上即使有直方图也不会有太坏的影响,是不是?

    有点倾向于让oracle自动管理直方图。

  6. Kamus says:

    @gengmao
    当没有碰到问题的时候,自动管理是省事儿的。比如很多客户都在使用sga_target,甚至memory_target,但是也有很多客户不敢用。

  7. Alberto Dell’Era 对CBO stats的研究颇深, 关于(new)Density的计算, 可以参考这篇文章,
    http://www.adellera.it/blog/2009/10/23/cbo-newdensity-for-frequency-histograms11g-10204-densities-part-iv/

    NewDensity with the “half the least popular” rule active

    By default the rule is active, and in this case,

    NewDensity is set to
    NewDensity = 0.5 * bkt(least_popular_value) / num_rows

    and hence, for non-existent values:

    E[card] = (0.5 * bkt(least_popular_value) / num_rows) * num_rows
    = 0.5 * bkt(least_popular_value)

  8. http://www.adellera.it/investigations/11g_newdensity/index.html

    这篇文章详细的介绍了11g new density的计算方法.

    “where value=”, The formula for NewDensity is:

    NewDensity = [(BktCnt – PopBktCnt) / BktCnt] / (NDV – PopValCnt) = = [(5 – 4) / 5] / (6-2) = .05 (as required)

  9. orion says:

    更喜清爽的版本

  10. hiihbd says:

    one mini comment:

    ENDPOINT_VALUE 很多时候都是empty的,看不到也就不能了解数据如何分布的,dba_tab_histograms里面有一个ENDPOINT_ACTUAL_VALUE又是一串数字,以前看到过一个PL/SQL写的ENDPOINT_ACTUAL_VALUE到 ENDPOINT_VALUE 的转换…

  11. Kamus says:

    @hiihbd
    我想你应该说的是Greg写的DISPLAY_RAW

Leave a Reply to orion Cancel reply

Your email address will not be published. Required fields are marked *