Oracle 收集对象统计信息

      一. 概述

        Oracle9i之前，由DBA负责收集对象统计信息。默认情况下，数据库不提供对象统计信息。到了oracle10g，在创建数据库的时候，就创建了一个定期收集对象统计信息的作业并进行调度。以便拥有最新的对象统计信息，因为表时刻都在变化，假如某一时刻插入了数据，对象统计信息也需要更新的。

        二. 收集对象

        oracle是用工具包dbms_stats来收集统计信息，根据不同的用途，收集对象也不相同。可以针对整个数据库，数据字典，用户或者单个表，索引进行收集。如下所示：

        gather_database_stats：收集数据库对象统计信息

        gather_dictionary_stats：收集数据字典对象统计信息

        gather_schema_stats：收集整个模式的所有对象统计信息

        gather_table_stats：收集表的对象统计信息

        gather_index_stats：收集索引的对象统计信息

        

        三. 参数说明

        针对不同的收集对象，有不同的参数，如下图：

        参数可以被分为3类：指明目标对象，指明收集选项和指明覆盖当前统计信息之前是否备份

        1. 目标对象

        用来指定要收集统计信息的对象。

        ownname：模式名称（也就是用户名）

        indname：索引名称

        tabname：表名称

        partname：分区或者子分区名称，不指定的话收集所有分区，默认为null

        comp_id：组件ID

        granularity：分区对象的统计级别，可接受值如下图，到oracle9i为止，默认值为default，从oracle10g开始默认值为auto

        cascade：是否收集索引的统计信息，可接受true，false和dbms_stats.auto_cascade(一个常量，取值为null，意思是让数据库引擎决定是否收集)。oracle9i之前默认值为false，从oracle10g开始默认值为dbms_stats.auto_cascade。

        gather_sys：是否收集sys用户的统计信息，可接受true，false。默认值为false。

        gather_temp：是否收集临时表的统计信息，可接受true，false。默认值为false。

        options：处理对象以及处理方式

        objlist：根据参数options的不同值，返回被处理对象的列表。

        force：是否覆盖锁定对象信息

        obj_filter_list：根据条件进行统计对象信息，比如只统计以C打头的表的统计信息。

        2. 收集选项

        指明收集统计信息的方式，收集哪类列的统计信息，以及与之相关的SQL游标是否失效。

        estimate_percent：是否采样收集统计信息，可用值从0.000001到100的一个百分比值。100等同于NULL，表示不采样收集。需要理解一点，这个参数仅指定采样的最小百分比，如果数据库觉得这个取值太小，会自动增大该值。oracle9i之前默认值为NULL，oracle10g默认为dbms_stats.auto_sample_size(一个常量，为0，表示由存储过程决定采样大小)，要加速统计过程，可取一个较小的采样值，比如0.5%或者0.1%。

        block_sample：是否采用数据块采样（非数据块采样就是数据行采样），数据块采样更快速，数据行采样更准确。只有确信数据是随机均匀分布的，才可以使用数据块采样。TRUE：数据块采样，FALSE：数据行采样，默认FALSE。

        method_opt ：是否收集直方图统计信息，如果收集，采样的最大桶数。可取值如下：

        NULL或者空字符串：只收集列统计信息，不收集直方图统计信息。

       收集列统计信息和所有列的直方图统计信息，比如：for all columns size 200，表示对每一列最多采样200个桶。

       只收集某一些列的直方图统计信息，比如：for columns size 200 col1，col2，col3，col4 size 1，col5 size 1。只对这5列收集直方图信息，而且只对前3列最多采样200个桶。

       oracle9i默认值为 for all columns size 1，oracle10g后默认值为 for all columns size auto。

       degree：对象统计时所用的从属进程数量，默认值为null

       no_invalidate：与收集的对象相关的游标是否失效，可用值为true，false和dbms_stats.auto_invalidate。oracle9i默认值为false(相关游标立即失效)，oracle10g之后默认为dbms_stats.auto_invalidate（游标过一段时间失效，避免集中重新解析游标）

       四. 参数设置

       10G方式：（不支持分级别设置，比如数据库级别，用户级别）

       在oracle10g中可以修改如下参数：cascade, estimate_percent, degree, method_opt, no_invalidate和granularity

       查看某一个参数的值：

execute dbms_output.put_line(dbms_stats.get_param(pname => 'cascade'))

       修改某一个参数的值：

 execute dbms_stats.set_param(pname => 'cascade', pval => 'true')

       全部重新恢复默认值：

execute dbms_stats.reset_param_defaults

       11G方式： （支持分级别设置）
       除开可以修改10g可以修改的参数外，还加3个参数：publish, incremental, stale_precent。由于在每个级别都可以为同一参数设值，比如数据库级别设置cascade为true，用户级别设置为false，表级别设置成auto。那么应该参考哪一个值呢？这里采用的是明细优先，先找表级别，再找用户级别，最后找数据库级别。

       比如：

       

begin
 dbms_stats.set_database_prefs(pname => 'cascade', pvalue => 'dbms_stats.auto_cascade');
 dbms_stats.set_schema_prefs(ownname => 'scott', pname => 'cascade', pvalue => 'false');
 dbms_stats.set_table_prefs(ownname => 'scott', tabname => 'emp', pname => 'cascade', pvalue => 'true');
end

select dbms_stats.get_prefs(pname => 'cacade') as global,
       dbms_stats.get_prefs(pname => 'cascade',
                            ownname => 'scott',
                            tabname => 'emp') as emp,
       dbms_stats.get_prefs(pname => 'cascade',
                            ownname => 'scott',
                            tabname => 'dept') as dept 
from dual;

      结果如下：