【IT168技术评论】
    摘要：本文描述了11g的新特性中，有关收集统计信息方面功能的增强。本文围绕统计信息收集，分别对收集统计信息时可以设置的选项、对合并列收集统计信息，对表达式和函数收集统计信息以及延迟发布统计信息这四个方面做了阐述。

1. 设置收集统计信息时的选项
    我们知道，数据库里的对象的统计信息（statistics）对于优化器得到正确的执行计划来说起着至关重要的作用。因此从10g R1开始，只要使用DBCA安装的数据库，都会自动创建一个job，该job缺省周一到周五每天晚上10点到第二天早上6点（周末则为全天）负责收集数据库所有对象的统计信息。不过，可能存在某些情况，你需要用自己的脚本来收集某些特殊对象的统计信息。但是由于你采用了自动收集统计信息，oracle就会对所有对象使用相同的选项来收集统计信息，这样你就失去了对某个对象的控制权。当你发现缺省的统计信息收集方式对某个对象不是很合适时，你必须锁定该对象的统计信息，并使用一个特殊的选项值对该对象来收集统计信息。

    比如，某个表的列的数据倾斜（列为某种值的记录行数非常多，而某种值的记录行数又非常少）的非常严重，这时如果采用标准的采样率：ESTIMATE_PERCCENT=AUTO_SAMPLE_SIZE可能就不适合了。这时你就需要单独指定该对象的采样率。我们知道，在11g之前的收集统计信息方面，oracle提供的类似的其他选项还包括：CASCADE、DEGREE、METHOD_OPT、NO_INVALIDATE、GRANULARITY。

   到了11g里，则提供了更大的灵活性，从而使得你可以很简单的处理上面所说的这种情况。在11g里，上面说的这些选项可以在不同的级别上分别设置，级别由高到低分别为：global级别、数据库级别、schema级别、表级别。其中，低级别的选项覆盖高级别的选项。

    比如，对于上面所举的例子来说，如果要对你的一个特殊的、列上的值倾斜的很严重的表收集统计信息时，你只需要简单的调用如下的存储过程来设置该表级别上的的ESTIMATE_PERCCENT=100即可，如下所示：
SQL> exec dbms_stats.set_table_prefs('Schema_name','Table_name','ESTIMATE_PERCCENT','100');

    这样设置以后，当数据库在自动收集统计信息时，对于其他没有单独设置采样率的表来说，采样率会采用AUTO_SAMPLE_SIZE，而对于你单独设置的Table_name表，则会使用100的采样率来收集统计信息。
类似的，如果需要设置global级别上的选项，则调用dbms_stats.set_global_prefs；如果要设置数据库级别上的选项，则调用dbms_stats.set_database_prefs；如果要设置schema级别上的选项，则调用dbms_stats.set_schema_prefs即可。
同时到了11g里，除了上面提到的这些选项以外，还添加了另外三种新的选项：PUBLISH、INCREMENTAL、STALE_PERCENT。其中：

1) PUBLISH：收集完统计信息以后是否立即将统计信息发布到数据字典里，还是将它们存放在私有区域里。TRUE表示立即发布，FALSE表示存放到私有区域里。

2) STALE_PERCENT：确定某个对象的统计信息过时的上限，如果过时就需要重新收集统计信息，缺省为10。计算某个表的统计信息是否过时，oracle会计算自从上一次收集该表的统计信息以来，该表中被修改的数据行数占该表的总行数的百分比。然后用得出的百分比值与该选项配置的值（如果缺省，就是10）进行比较，大于10，则说明该表的统计信息过时了，需要重新收集统计信息；否则就认为该表的统计信息不过时，不用再次收集。

3) INCREMENTAL：在分区表上收集global的统计信息时（将GRANULARITY设置为GLOBAL），采用增量方式完成。使用该选项是因为对于某些分区表来说，比如按照月份进行范围分区的分区表来说，除了代表当前月的分区里的数据会经常变化以外，其他分区里的数据不会变动。因此在收集该分区表上的global的统计信息时，就没有必要再次扫描那些非当前月的分区了。如果你将INCREMENTAL设置为TRUE时，则在收集统计信息时，就不会扫描那些非当前月的分区里的数据，而只会扫描当前月的分区里的数据。最后将非当前月的分区上已经存在的统计信息加上当前月新算出来的统计信息合并就得出了分区表的global的统计信息。

  可以从视图：DBA_TAB_STAT_PREFS里看到所有的收集统计信息时的各个选项的值。

2. 对合并列收集统计信息
    对于where条件里具有两个列以上的情况，比如where c1=’A’ and c2=’B’来说，11g以前优化器评估其selectivity时，总是将每个列的selectivity相乘，从而得到整个where条件的selectiviey。但是如果两个列具有很强的依赖关系，比如汽车制造商与汽车型号这两个列来说，我们知道每个汽车制造商所生产的汽车型号几乎都不会重复，也就是说当你发出where 汽车制造商列=’XXX’ and 汽车型号列=’XXX’时，与发出where汽车型号列=’XXX’时返回的记录行数可能几乎一样。这时如果在计算where条件的selectivity时仍然采用将汽车制造商列的selectivity乘以汽车型号列的selectivity时，就会导致总的selectivity过低，从而导致优化器估计返回的记录行数过少，从而可能导致不正确的执行计划。

    为了弥补这样的问题，11g以后可以让你将多个依赖程度很高列合并成一个组，然后对该组收集统计信息。具体如何实现，则可以看下面的例子。
select dbms_stats.create_extended_stats('Schema_name','Table_name','(C1,C2)') from dual;

通过调用函数dbms_stats.create_extended_stats将两个或多个列合并，并返回一个虚拟的隐藏列的列名，其名字类似于：SYS_STUW_5RHLX443AN1ZCLPE_GLE4。
然后，我们可以开始对表收集统计信息，收集完以后，你可以使用ALL|DBA|USER_STAT_EXTIONSIONS视图来查看列组合的统计信息。

exec dbms_stats.gather_table_stats('Schema_name','Table_name');
如果你要对组合列收集直方图，则可以如下所示：
exec dbms_stats.gather_table_stats('Schema_name','Table_name',
method_opt=>'for columns (C1,C2) size AUTO');

3. 对函数以及表达式收集统计信息
    如果where条件类似于function_name(table_name.column_name)=’XXX’时，则优化器在估计这样的where条件的selectivity时，总是会假设其selectivity为1％，也就是该where条件将返回table_name里总记录行数的1％的记录行数。很明显的，这种假设肯定是错误的，从而可能导致优化器产生了不够优化的执行计划。

    从11g开始，我们可以对函数或者表达式收集统计信息了。该特性依赖于虚拟列，也就是说你需要先用dbms_stats.create_extended_stats函数为function_name(table_name.column_name)创建一个虚拟列，然后对该虚拟列收集统计信息。比如下面的例子。

    select dbms_stats.create_extended_stats('Schema_name','Table_name','length(C1)') from dual;
下面则显示的是对表达式来收集统计信息。

    select dbms_stats.create_extended_stats('Schema_name','Table_name','C1*C2') from dual;
然后你可以对表收集统计信息时，就会为函数length(C1)对应的虚拟列收集统计信息了。如果你要对该虚拟列收集直方图，则可以如下所示：

exec dbms_stats.gather_table_stats('Schema_name','Table_name',
method_opt=>'for columns (length(C1)) size AUTO');

4. 延迟发布统计信息
    缺省情况下，11g之前，数据库每次在收集统计信息以后，都会将统计信息存放在数据字典里。只要它们写入了数据字典，优化器就会看到它们，从而在下次生成执行计划的时候就会参考当前最新的统计信息。但是有时候，你可能并不希望收集完最新的统计信息以后立即发布。而是先对最新的统计信息进行测试，如果测试下来发现性能没有损失，才正式发布出去，从而让优化器看到。11g以后，你可以这么去做了。

    在11g里，你可以将发布统计信息这一步从收集统计信息的过程中剥离出来。也就是先收集统计信息，然后再发布出去。这样就可以让DBA有机会先在测试环境里验证新的统计信息对性能所带来的影响。

    如果你想延迟发布统计信息，则将收集统计信息的选项PUBLISH设置为FALSE，然后收集统计信息，这时统计信息就会存放在私有数据字典表里，可以从ALL|DBA|USER}_{TAB|COL|IND|TAB_HISTGRM}_PRIVATE_STATS里看到这些私有的统计信息。

    为了测试这些私有统计信息，你可以有两种方法：
    1) 第一种方式使用DBMS_STAT.EXPORT_PRIVATE_STATS存储过程将私有统计信息转移到你自己的统计信息表（可以使用存储过程DBMS_STATS.CREATE_STAT_TABLE来创建你自己的统计信息表）里。然后可以使用expdp导出你的统计信息表，然后再使用impdp将导出文件导入到测试环境中，再使用DBMS_STAT.IMPORT_TABLE_STATS将其导入到测试环境中进行测试。

   2) 第二种方式不导出私有的统计信息，而是直接在产品库的session级别，将11g引入的新的初始化参数：  OPTIMIZER_PRIVATE_STATISTICS设置为TRUE（缺省情况下该参数为FALSE）。这时你执行SQL时，优化器就会参考私有统计信息来解析SQL语句并生成执行计划了。

    最后，测试完毕，发现最新的统计信息没有问题的话，你就可以使用DBMS_STAT.PUBLISH_PRIVATE_STATS在产品库上将私用统计信息发布出去，从而让优化器能够看到它们。

    下面列举一个例子来简单说明这个过程。首先设置表级别的publish选项为false：
    exec dbms_stats.set_table_prefs('Schema_name','Table_name','PUBLISH','false');

    然后，收集表的统计信息：
    exec dbms_stats.gather_table_stats('Schema_name','Table_name');

    第三，设置相关初始化参数：
    alter session set optimizer_use_private_statistics = true;

    第四，进行测试，运行相关的SQL语句，并检查产生的执行计划。
最后，把该表的统计信息发布出去：
exec dbms_stats.publish_private_stats('Schema_name','Table_name');