Oracle Histogram Investigation

这段时间有客户的10gR2数据库经常遭遇执行计划不稳定的现象,应该是直方图信息(Histogram)+绑定变量窥视(Bind Variable Peeking)造成的问题,藉此分享一下直方图的使用经验,但是我非常希望能听到针对本文的不同声音。测试环境Oracle 11.2.0.1 for Windows X64。

创建一个测试表。

[sourcecode language=”sql”]DROP TABLE t;
CREATE TABLE t AS
SELECT rownum n1,
ROUND(rownum/100) n2,
‘xxxx’ n3
FROM dba_objects
WHERE rownum<=10000;[/sourcecode]

这样我们生成了一张表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表中的三列数据分布都是均匀的,并不应该收集任何列上的直方图信息,实际呢?

[sourcecode language=”sql”]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
[/sourcecode]

可以看到在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的。

[sourcecode language=”sql”]
–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
[/sourcecode]

因此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将删除列上的直方图信息,同样也意味着不收集。

[sourcecode language=”sql”]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[/sourcecode]

method_opt=>’for all columns size 30′
size自定义大于1的数值,最大为254,如例表示要求收集30个bucket的直方图信息。

[sourcecode language=”sql”]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[/sourcecode]

那么现在到了最重要的部分,对于一个数据库系统,我们到底应该如何收集直方图信息呢?

三. 我们应该怎样做
首先我们要明确直方图信息是有存在的必要的,但是只应该存在在那些应该要存在的列上,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<=333*2[/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

Using Safari5 Reader for Reading Technical Posts

最新Release的Mac下的浏览器Safari5除了全面支持HTML5之外,还有一个着重提到的功能就是Reader。

关于Reader的赞美之辞可以参看《改变阅读习惯的Safari 5阅读器》

那么实际上Reader对于我个人的用处是在浏览DBAsupport.com站点的文章,此站点文章质量较高而且还时常出现不少新奇有趣的文章。但是这个站点恼人的地方在于:1.在Google Reader中无法看到全文 2.在站点上直接浏览文章的话要承受大量的广告。

比如原站点中的文章大概看上去是这样五颜六色的。

使用了Safari5 Reader以后,变成了一篇白底黑字的清爽文章。

但是目前这个版本的Reader仍然有需要改进的地方,特别是对于可能会有大量代码的技术文章,那些代码在原文中有rss格式化,但是Reader转换以后格式就不正确了,这样会导致较长的代码有一部分无法看到。

仍然是上面这片技术文章。原文中的格式如下图,虽然也不尽美观,但是注意那些小字体的部分,至少可以看到全部文字。

而在Reader中最后超出页面的那些文字都无法看到了,而且似乎是苹果太在意用户阅读体验了,甚至连横向的滚动栏也没有。是的,我知道Reader中的字体可以缩小,但是即使缩到最小也仍然有一部分在页面之外。

Hope will be fixed soon.

ADRCI Purge is NOT What You Think It Should Be

ADRCI是在Oracle11g以后提供的实用程序,用来更加灵活的检查和分析各个ORACLE_HOME的告警文件,跟踪文件。

根据文档和帮助,ADRCI是具有purge功能的。

adrci> help purge

  Usage: PURGE [[-i  |  ] |
               [-age  [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:

  Purpose: Purge the diagnostic data in the current ADR home. If no
           option is specified, the default purging policy will be used.

  Options:
    [-i id1 | id1 id2]: Users can input a single incident ID, or a
    range of incidents to purge.

    [-age ]: Users can specify the purging policy either to all
    the diagnostic data or the specified type. The data older than 
    ago will be purged

    [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
    data to be purged.

  Examples:
    purge
    purge -i 123 456
    purge -age 60 -type incident

仅仅看这个帮助,我们认为的ADRCI Purge功能是怎样的呢?比如我们执行了下面这个命令,那么Oracle应该将我们的告警日志中在距现在这个时刻1440分钟(也就是1天)以前的所有日志内容删除,是这样吧?可是确实是这样吗?

adrci> PURGE -age 1440 -type ALERT

我们测试一下。

adrci> show home
ADR Homes: 
diag/rdbms/orcl/orcl
diag/tnslsnr/dbserver/listener
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail
2010-05-02 20:04:56.447000 +08:00
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2010-05-02 20:04:58.182000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2010-05-02 20:04:59.607000 +08:00
Starting background process QMNC
QMNC started with pid=18, OS id=1405 
2010-05-02 20:05:00.653000 +08:00
Completed: ALTER DATABASE OPEN
2010-05-02 20:05:07.033000 +08:00
Starting background process CJQ0
CJQ0 started with pid=19, OS id=1426 
2010-05-02 20:10:00.609000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=1587 
2010-05-02 20:11:47.241000 +08:00
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: /app/oracle/oradata/orcl/redo01.log

现在告警日志中存在的某两次记录时间是2010-05-02 20:04:56和2010-05-02 20:11:47。我们准备删除掉20:04前后的告警信息,保留20:11的。

[oracle@dbserver alert]$ date
Sun May  2 20:14:20 CST 2010
[oracle@dbserver alert]$ pwd
/app/oracle/diag/rdbms/orcl/orcl/alert
[oracle@dbserver alert]$ ls -l
total 24
-rw-r----- 1 oracle dba 20925 May  2 20:11 log.xml

当前的时间是2010-05-02 20:14。检查了Oracle11g中告警日志的默认存储位置,最后的一次更新时间是20:11。

[oracle@dbserver alert]$ date
Sun May  2 20:15:51 CST 2010

adrci> PURGE -age 5 -type ALERT
adrci> show alert -tail
2010-05-02 20:04:56.447000 +08:00
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2010-05-02 20:04:58.182000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2010-05-02 20:04:59.607000 +08:00
Starting background process QMNC
QMNC started with pid=18, OS id=1405 
2010-05-02 20:05:00.653000 +08:00
Completed: ALTER DATABASE OPEN
2010-05-02 20:05:07.033000 +08:00
Starting background process CJQ0
CJQ0 started with pid=19, OS id=1426 
2010-05-02 20:10:00.609000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=1587 
2010-05-02 20:11:47.241000 +08:00
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: /app/oracle/oradata/orcl/redo01.log

[oracle@dbserver alert]$ ls -l
total 24
-rw-r----- 1 oracle dba 20925 May  2 20:11 log.xml

我们执行的命令成功了,但是很明显我们希望的目的没有达到,实际上,就好似没有执行这条命令一样,我们想要删除5分钟以前的内容,执行purge命令的时刻是20:15,那么理应在20:10之前的告警日志内容都被删除掉,但是实际上告警日志中20:04的内容依然还在,并且我们也可以看出log.xml文件的最后更新时间仍然是20:11。

那么purge命令到底有没有效果呢?

[oracle@dbserver alert]$ date
Sun May  2 20:28:34 CST 2010

adrci> PURGE -age 5 -type ALERT
adrci> show alert -tail
adrci> 

[oracle@dbserver alert]$ ls -l
total 0

在20:28的时候我们再次执行了purge命令,仍然是尝试删除5分钟以前的内容,这次的结果是所有的告警日志内容都被清空了,告警日志文件log.xml也被删除了。这条命令实际上有效果的,但是为什么不是我们期望的那样灵敏呢?为什么第一次没有成功,而第二次却成功了呢?

原因在于,ADRCI Purge命令的操作单元是文件而不是文件内容,只有整个文件的最后更新时间在我们指定的purge命令条件之前,该文件才会被删除,也就是说,要不整个文件都删除,要不一点儿也不删除。对于我们后一次执行的命令,因为文件的最后更新时间是20:11,这个时间点在我们执行purge命令时间点的5分钟以前,因此整个告警日志都被删除。而对于我们第一次执行的命令,因为告警日志中不但包含了执行purge命令时间点5分钟之前的内容,还包括了5分钟之前到执行purge命令时间点之间的内容(20:11的内容位于20:10和20:15之间),因此文件无法被删除。

无论是purge的什么TYPE,或者说purge -i 命令也同样是这样,对于INCIDENT来说,每个Incident ID在INCIDENT目录中都是一个子目录,因此很容易做到整个子目录的删除。

不得不说这样的purge命令并不是我们期望的,我也希望看到在今后的版本升级中ADRCI Purge可以变得如我们一开始期望的那样,直接删除符合条件的文件内容,而不是现在这样删除文件。

我们可以预见到对于特别繁忙的系统,假设每分钟都有告警日志内容生成,那么我们始终不可能执行成功purge,我们只能使用purge -age 0 -type ALERT这样的命令来清除所有的log内容。那么现在这样的purge命令我们该如何精确使用呢?

实际上仍然需要另外的客户化脚本,比如在crontab中定义每天晚上的某个时刻将现在的log.xml文件更名为log_`date`.xml,然后统一使用purge命令删除符合条件的告警日志文件备份。特别需要注意的是,更名规则要符合purge命令的检查规范,purge命令只会对相应目录中以log开头以xml结尾的文件进行检查。

PS:purge命令只会清除xml文件,对于trace目录中的和Oracle11g之前版本兼容的alert_.log文件并不会有任何改动。

[oracle@dbserver trace]$ pwd
/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@dbserver trace]$ ls -l alert*
-rw-r----- 1 oracle dba 41444 May  2 20:20 alert_orcl.log