Oracle@H_502_2@数据库里的统计信息是这样的一组数据：它存储在数据字典里，且从多个维度描述了@H_502_2@Oracle@H_502_2@数据库里对象的详细信息。@H_502_2@CBO@H_502_2@会利用这些统计信息来计算目标@H_502_2@sql@H_502_2@各种可能的、不同的执行路径的成本，并从中选择一条成本值最小的执行路径来作为目标@H_502_2@sql@H_502_2@的执行计划。@H_502_2@
Oracle@H_502_2@数据库里的统计信息可以分为如下@H_502_2@6@H_502_2@种类型：@H_502_2@
表的统计信息用于描述@H_502_2@Oracle@H_502_2@数据库里表的详细信息，它包含了一些典型的维度，如记录数、表块@H_502_2@(@H_502_2@表里的数据块@H_502_2@)@H_502_2@数量、平均行长度等。@H_502_2@
索引的统计信息于描述@H_502_2@Oracle@H_502_2@数据库里索引的详细信息，它包含了一些典型的维度，如索引的层级、叶子块的数量、聚簇因子等。@H_502_2@
列的统计信息于描述@H_502_2@Oracle@H_502_2@数据库里列的详细信息，它包含了一些典型的维度，如列的@H_502_2@distinct@H_502_2@值的数量、列的@H_502_2@NULL@H_502_2@值的数量、列的最小值、列的最大值以及直方图等。@H_502_2@
系统统计信息于描述@H_502_2@Oracle@H_502_2@数据库所在的数据库服务器的系统处理能力，它包含了@H_502_2@cpu@H_502_2@和@H_502_2@I/O@H_502_2@这两个维度，借助于系统统计信息，@H_502_2@Oracle@H_502_2@可以更清楚地知道目标数据库服务器的实际处理能力。@H_502_2@
数据字典统计信息用于热核@H_502_2@Oracle@H_502_2@数据库里数据字典基表@H_502_2@(@H_502_2@如@H_502_2@TAB$@H_502_2@、@H_502_2@IND$@H_502_2@等@H_502_2@)@H_502_2@、数据字典基表上的索引，以及这些数据字典的列的详细信息，描述上述数据字典基表的统计信息与描述普通表、索引、列的统计信息没有本质区别。@H_502_2@
内部对象统计信息用于描述@H_502_2@Oracle@H_502_2@数据库里的一些内部表@H_502_2@(@H_502_2@如@H_502_2@X$@H_502_2@系列表@H_502_2@)@H_502_2@的详细信息，它的维度和普通表的统计信息的维度类似，只不过其表块的数量为@H_502_2@0@H_502_2@，因为@H_502_2@X$@H_502_2@系统表实际上只是@H_502_2@Oracle@H_502_2@自定义的内存结构，并不占用实际的物理存储空间。@H_502_2@
在@H_502_2@Oracle@H_502_2@数据库里，通常有两种方法可以用来收集统计信息：一种是使用@H_502_2@ANALYZE@H_502_2@命令；另一种是使用@H_502_2@DBMS_STATS@H_502_2@包。表、索引、列的统计信息和数据字典统计信息用@H_502_2@ANALYZE@H_502_2@命令或者@H_502_2@DBMS_STATS@H_502_2@包收集均可，但系统统计信息和系统内部对象统计信息只能使用@H_502_2@DBMS_STATS@H_502_2@包来收集。@H_502_2@
对系统内部表若使用@H_502_2@ANALYZE@H_502_2@命令来收集统计信息，会报错@H_502_2@ORA-02030@H_502_2@
1.1 @H_502_2@用@H_502_2@ANALYZE@H_502_2@命令收集统计信息@H_502_2@
从@H_502_2@Oracle7@H_502_2@开始，@H_502_2@ANALYZE@H_502_2@命令就可以用来收集表、索引、列的统计信息，以及系统统计信息。@H_502_2@
典型用法如下：
zx@ORCL>createtablet2asselect*fromdba_objects; Tablecreated. zx@ORCL>createindexidx_t2ont2(object_id); Indexcreated. zx@ORCL>analyzeindexidx_t2deletestatistics; Indexanalyzed.
从@H_502_2@Oracle 10g@H_502_2@开始，创建索引后@H_502_2@Oracle@H_502_2@会怎么收集目标索引的统计信息，出现演示的目的，这里删除索引@H_502_2@IDX_T2@H_502_2@的统计信息：@H_502_2@
执行@H_502_2@sosi@H_502_2@脚本，从输出内容可以看到表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的列和索引@H_502_2@IDX_T2@H_502_2@均没有相关的统计信息@H_502_2@
zx@ORCL>selectcount(*)fromt2; COUNT(*) ---------- 86852
只对表@H_502_2@T2@H_502_2@收集统计信息，并且以估算模式，采样的比例为@H_502_2@15%@H_502_2@：@H_502_2@
zx@ORCL>analyzetablet2estimatestatisticssample15percentfortable; Tableanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，可以看出现在只用表@H_502_2@T2@H_502_2@有统计信息，表@H_502_2@T2@H_502_2@的列和索引@H_502_2@IDX_T2@H_502_2@均没有相关的统计信息。而且因为采用的是估算模式所以估算结果和实际结果并不一定会完全匹配，比如表@H_502_2@T2@H_502_2@的实际数量与估算出的数量不一致。@H_502_2@
只对表@H_502_2@T2@H_502_2@收集统计信息，并且以计算模式：@H_502_2@
zx@ORCL>analyzetablet2computestatisticsfortable; Tableanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，可以看出现在只用表@H_502_2@T2@H_502_2@有统计信息，表@H_502_2@T2@H_502_2@的列和索引@H_502_2@IDX_T2@H_502_2@均没有相关的统计信息。而且因为采用的是计算模式，计算模式会扫描目标对象的所有数据，所以统计结果和实际结果是匹配的。@H_502_2@
对表@H_502_2@T2@H_502_2@收集完统计信息后，现在对表@H_502_2@T2@H_502_2@的列@H_502_2@OBJECT_NAME@H_502_2@和@H_502_2@OBJECT_ID@H_502_2@以计算模式收集统计信息：@H_502_2@
zx@ORCL>analyzetablet2computestatisticsforcolumnsobject_name,object_id; Tableanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，可以看出，现在列@H_502_2@OBJECT_NAME@H_502_2@和@H_502_2@OBJECT_ID@H_502_2@确实已经有统计信息了@H_502_2@
注：在崔华老师的《基于Oracle的sql优化》一书中提到@H_502_2@T2@H_502_2@原有的统计信息已经被抹掉了，也就是说对同一个对象而言，新执行的@H_502_2@ANALYZE@H_502_2@命令会抹掉之前@H_502_2@的结果。但是在我@H_502_2@实际的执行结果是表@H_502_2@原有的统计信息没有被抹掉。我用到的环境是10.2.0.4和11.2.0.4，暂时没有11.2.0.1的环境。@H_502_2@
可以使用如下的命令同时以计算模式对表@H_502_2@T2@H_502_2@和列@H_502_2@OBJECT_NAME@H_502_2@、@H_502_2@OBJECT_ID@H_502_2@收集统计信息：@H_502_2@
zx@ORCL>analyzetablet2computestatisticsfortableforcolumnsobject_name,object_id; Tableanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，可以看到表@H_502_2@T2@H_502_2@和列@H_502_2@OBJECT_NAME@H_502_2@、@H_502_2@OBJECT_ID@H_502_2@上都有统计信息了。@H_502_2@
使用如下命令可以以计算模式收集索引@H_502_2@IDX_T2@H_502_2@的统计信息@H_502_2@
zx@ORCL>analyzeindexidx_t2computestatistics; Indexanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，从输出可以看到，现在索引@H_502_2@IDX_T2@H_502_2@已经有了统计信息，并且之前收集的表@H_502_2@T2@H_502_2@和列@H_502_2@OBJECT_NAME@H_502_2@、@H_502_2@OBJECT_ID@H_502_2@上的统计信息并没有被抹掉，这是因为我们刚才执行的@H_502_2@ANALYZE@H_502_2@命令和之前执行的@H_502_2@ANALYZE@H_502_2@命令针对的不是同一个对象。@H_502_2@
使用如下命令可以删除表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的所有列及表@H_502_2@T2@H_502_2@的所有索引的统计信息：@H_502_2@
zx@ORCL>analyzetablet2deletestatistics; Tableanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，从输出可以看到，刚才收集的表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的列@H_502_2@OBJECT_NAME@H_502_2@、@H_502_2@OBJECT_ID@H_502_2@以及索引@H_502_2@IDX_T2@H_502_2@的统计信息已经全部被删除了。@H_502_2@
如果想一次性以计算模式收集表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的所有列和表@H_502_2@T2@H_502_2@上的所有索引的统计信息，执行如下的语句就可以了：@H_502_2@
zx@ORCL>analyzetablet2computestatistics; Tableanalyzed.
再次执行@H_502_2@sosi@H_502_2@脚本，从输出可以看到，现在表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的所有列和索引@H_502_2@IDX_T2@H_502_2@的统计信息都有了。@H_502_2@
1.2 @H_502_2@用@H_502_2@DBMS_STATS@H_502_2@包收集统计信息@H_502_2@
从@H_502_2@Oracle 8.1.5@H_502_2@开始，@H_502_2@DBMS_STATS@H_502_2@包被广泛用于统计信息的收集，用@H_502_2@DMBS_STATS@H_502_2@包收集统计信息也是@H_502_2@Oracle@H_502_2@官方推荐的方式。在收集@H_502_2@CBO@H_502_2@所需要的统计信息方面，可以简单的将@H_502_2@DBMS_STATS@H_502_2@包理解成是@H_502_2@ANALYZE@H_502_2@命令的增加版。@H_502_2@
DBMS_STATS@H_502_2@包里最常用的就是如下@H_502_2@4@H_502_2@个存储过程：@H_502_2@
GATHER_TABLE_STATS@H_502_2@：用于收集目标表、目标表的列和目标表上的索引的统计信息。@H_502_2@
GATHER_SCHEMA_STATS@H_502_2@：用于收集指定@H_502_2@schema@H_502_2@下所有对象的统计信息。@H_502_2@
现在介绍@H_502_2@DBMS_STATS@H_502_2@包在收集统计信息时的常见用法，还是针对上面的测试表@H_502_2@T2@H_502_2@，这里使用@H_502_2@DBMS_STATS@H_502_2@包实现了和@H_502_2@ANALYZE@H_502_2@命令一模一样的效果。@H_502_2@
先删除表@H_502_2@T2@H_502_2@上的所有统计信息@H_502_2@
analyze table t2 delete statistics;
只对表@H_502_2@T2@H_502_2@收集统计信息，并且以估算模式，采用的比例同样为@H_502_2@15%@H_502_2@：@H_502_2@
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>15,method_opt=>'FORTABLE',cascade=>false); PL/sqlproceduresuccessfullycompleted.
执行@H_502_2@sosi@H_502_2@脚本，从输出内容可以看出，现在只有表@H_502_2@T2@H_502_2@有统计信息，表@H_502_2@T2@H_502_2@的列和索引@H_502_2@IDX_T2@H_502_2@均没有相关的统计信息。而且因为采用的估算模式，所以估算结果和实际结果并不一定会完全匹配。@H_502_2@
需要注意的是，这里@H_502_2@Oracle@H_502_2@数据库的版本是@H_502_2@11.2.0.4@H_502_2@，我们在调用@H_502_2@DMBS_STATS.GATHER_TABLE_STATS@H_502_2@时指定参数@H_502_2@METHOD_OPT@H_502_2@的值为@H_502_2@'FOR TABLE',@H_502_2@这表示只收集表@H_502_2@T2@H_502_2@的统计信息。这种收集表统计信息的方法并不适用于@H_502_2@Oracle@H_502_2@数据库所有的版本。例如这种方法就不适用于@H_502_2@Oracle10.2.0.4@H_502_2@和@H_502_2@Oracle10.2.0.5@H_502_2@，在这两个版本里，即使指定了@H_502_2@'FOR TABLE'@H_502_2@，@H_502_2@Oracle@H_502_2@除了收集表统计信息之外还会对所有的列收集统计信息。@H_502_2@
如果公对表@H_502_2@T2@H_502_2@收集统计信息，并且是以计算模式收集，用@H_502_2@DBMS_STATS@H_502_2@包实现的方法就是将估算模式的采样比例@H_502_2@(@H_502_2@即参数@H_502_2@ESTIMATE_PERCENT)@H_502_2@设置为@H_502_2@100%@H_502_2@或@H_502_2@NULL@H_502_2@；@H_502_2@
exec dbms_stats.gather_table_stats(ownname=>'ZX',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);
exec dbms_stats.gather_table_stats(ownname=>'ZX',estimate_percent=>NULL,cascade=>false);
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>'ZX',cascade=>false); PL/sqlproceduresuccessfullycompleted.
执行@H_502_2@sosi@H_502_2@脚本，从输出内容可以看出，现在只有表@H_502_2@T2@H_502_2@的统计信息，表@H_502_2@T2@H_502_2@的列和索引@H_502_2@IDX_T2@H_502_2@均没有相关的统计信息。而且因为采用的是计算模式，计算模式会扫描目标对象的所有数据，所以统计结果和实际结果是匹配的。@H_502_2@
对表@H_502_2@T2@H_502_2@收集完统计信息后，现在我们来对表@H_502_2@T2@H_502_2@的列@H_502_2@OBJECT_NAME@H_502_2@、@H_502_2@OBJECT_ID@H_502_2@以计算模式收集统计信息@H_502_2@(@H_502_2@不收集直方图@H_502_2@)@H_502_2@：@H_502_2@
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>'ZX',method_opt=>'forcolumnssize1object_name,object_id',cascade=>false); PL/sqlproceduresuccessfullycompleted.
执行@H_502_2@sosi@H_502_2@脚本，从输出内容可以看出，现在表@H_502_2@T2@H_502_2@的列@H_502_2@OBJECT_NAME@H_502_2@、@H_502_2@OBJECT_ID@H_502_2@上都有统计信息了，并且@H_502_2@Oracle@H_502_2@还会同时收集表@H_502_2@T2@H_502_2@上的统计信息@H_502_2@(@H_502_2@注意，这和@H_502_2@ANALYZE@H_502_2@命令有所区别@H_502_2@)@H_502_2@。@H_502_2@
使用如下命令可以以计算模式收集索引@H_502_2@IDX_T2@H_502_2@的统计信息@H_502_2@
zx@ORCL>execdbms_stats.gather_index_stats(ownname=>'ZX',indname=>'IDX_T2',estimate_percent=>100); PL/sqlproceduresuccessfullycompleted.
执行@H_502_2@sosi@H_502_2@脚本，从输出内容可以看出，现在索引@H_502_2@IDX_T2@H_502_2@已经有了统计信息。@H_502_2@
使用如下命令可以删除表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的所有列及表@H_502_2@T2@H_502_2@的所有索引的统计信息：@H_502_2@
zx@ORCL>execdbms_stats.delete_table_stats(ownname=>'ZX',tabname=>'T2'); PL/sqlproceduresuccessfullycompleted.
执行@H_502_2@sosi@H_502_2@脚本，从输出内容可以看出，表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的所有列及表@H_502_2@T2@H_502_2@的所有索引的统计信息已经全部被删除了。@H_502_2@
如果想一次性以计算模式收集表@H_502_2@T2@H_502_2@、表@H_502_2@T2@H_502_2@的所有列及表@H_502_2@T2@H_502_2@的所有索引的统计信息，执行如下语句就可以了@H_502_2@
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>'ZX',cascade=>true); PL/sqlproceduresuccessfullycompleted.
1.3 ANALYZE@H_502_2@和@H_502_2@DBMS_STATS@H_502_2@的区别@H_502_2@
从上面的演示中可以看出@H_502_2@ANALYZE@H_502_2@命令和@H_502_2@DBMS_STATS@H_502_2@包都可以用来收集表、索引和列的统计信息，看起来它们在收集统计信息方面的效果是一模一样的，为什么@H_502_2@Oracle@H_502_2@会推荐使用@H_502_2@DBMS_STATS@H_502_2@包来收集统计信息呢？@H_502_2@
因为@H_502_2@ANALYZE@H_502_2@命令和@H_502_2@DMBS_STATS@H_502_2@包相比，存在如下缺陷：@H_502_2@
ANALYZE@H_502_2@命令不能正确地收集分区表的统计信息，而@H_502_2@DBMS_STATS@H_502_2@包却可以。@H_502_2@ANALYZE@H_502_2@命令只会收集最低层次对象的统计信息，然后推导和汇总出高一级的统计信息，比如对于有子分区的分区表而言，它只会先收集子分区统计信息，然后再汇总，推导出分区或表级的统计信息。有的统计信息是可以从当前对象的下一级对象进行汇总后得到的，比如表的总行数，可以由各分区的行数相加得到。但有的统计信息则不能从下一级对象得到，比如列上的@H_502_2@distinct@H_502_2@值数量@H_502_2@NUM_DISTINCT@H_502_2@以及@H_502_2@DESNSITY@H_502_2@等。@H_502_2@
ANALYZE@H_502_2@命令不能并行收集统计信息，而@H_502_2@DBMS_STATS@H_502_2@包却可以。并行收集统计信息对数据量很大的表表而言，是非常有用的特性。对于数据量很大的表，如果不能并行收集统计信息，则意味着如果想精确地收集目标对象的统计信息，那么耗费的时间可能会非常长，这有可能是不能接受的。在@H_502_2@Oracle@H_502_2@数据库里，@H_502_2@DBMS_STATS@H_502_2@包收集统计信息可以并行执行，这在一定程度上缓解了对大表的统计信息收集过长所带来的一系列问题。@H_502_2@
DBMS_STATS@H_502_2@包的并行收集是通过手工指定输入参数@H_502_2@DEGREE@H_502_2@来实现的，比如对表@H_502_2@T1@H_502_2@进行收集统计信息，同时指定并行度为@H_502_2@4@H_502_2@：@H_502_2@
execdbms_stats.gahter_table_stats(ownname=>'SCOTT',tabname=>'T1',cascade=>true,degree=>4);
当然，@H_502_2@DBMS_STATS@H_502_2@包也不是完美的，它与@H_502_2@ANALYZE@H_502_2@命令相比，其缺陷在于@H_502_2@DBMS_STATS@H_502_2@包只能收集与@H_502_2@CBO@H_502_2@相关的统计信息，而与@H_502_2@CBO@H_502_2@无关的一些额外信息，比如行迁移@H_502_2@/@H_502_2@行链接的数量@H_502_2@(CHAIN_CNT)@H_502_2@、校验表和索引的结构信息等，@H_502_2@DBMS_STATS@H_502_2@包就无能为力了。而@H_502_2@ANALYZE@H_502_2@命令可以用来分析和收集上述额外的信息，比如@H_502_2@analyze table xxx list chained rows intoyyy @H_502_2@可以用来分析和收集行迁移@H_502_2@/@H_502_2@行链接的数量，@H_502_2@analyzeindex xxx validate structure@H_502_2@可以用来分析索引的结构。@H_502_2@
前面介绍了如何收集统计信息，那如何查看这些统计信息呢？@H_502_2@Oracle@H_502_2@数据库的统计信息会存储在数据字典里，我们只需要去查询相关的数据字典就好了。如果有充裕的时间，现写@H_502_2@sql@H_502_2@去查询数据字典里的统计信息也没有什么，但当我们真正碰到有性能问题的@H_502_2@sql@H_502_2@时，通常会希望能在第一时间就收集到与目标@H_502_2@sql@H_502_2@相关的各种统计信息，以便于在第一时间定位问题所在，这时候写@H_502_2@sql@H_502_2@去查询数据字典就已经来不及了，所以我们需要事先准备好通用的查询统计信息的脚本，出问题的时候只需要运行一下脚本，就能在第一时间获取目标对象的所有统计信息了。@H_502_2@
sosi@H_502_2@脚本@H_502_2@(Show Optimizer Statistics Information)@H_502_2@就是这样一种脚本，国内的@H_502_2@Oracle@H_502_2@数据库专家也一直在用这个脚本，它源于@H_502_2@MOS@H_502_2@上的文章：@H_502_2@SCRIPT - Select to show OptimizerStatistics for CBO (@H_502_2@文档@H_502_2@ ID 31412.1)@H_502_2@，用法很简单，只需要运行一下@H_502_2@sosi@H_502_2@脚本，并指定要查看统计信息的表名就可以了。它支持分区表，显示分为三部分，分别是表级别的统计信息，分区级别的统计信息和子分区级别的统计信息。前面做实验用到的也是这个脚本。@H_502_2@
附件是sosi脚本可以下载使用。
