oracle统计信息(一)

1.     理解什么是统计信息
    优化器统计信息就是一个更加详细描述数据库和数据库对象的集合，这些统计信息被用于查询优化器，让其为每条SQL语句选择最佳的执行计划。优化器统计信息包括:

 

表的统计信息

行数
Block数

行平均长度
列的统计信息

列中不同值的数量
列中null的数量

数据分布(柱状图/直方图)

索引的统计信息

叶子块的数量
索引的高度

聚簇因子(clustering factor)

系统的统计信息

I/O性能和利用

CPU性能和利用

 

优化器统计信息存储在下列数据字典中
·         DBA_TABLES

·         DBA_OBJECT_TABLES

·         DBA_TAB_STATISTICS

·         DBA_TAB_COL_STATISTICS

·         DBA_TAB_HISTOGRAMS

·         DBA_INDEXES

·         DBA_IND_STATISTICS

·         DBA_CLUSTERS

·         DBA_TAB_PARTITIONS

·         DBA_TAB_SUBPARTITIONS

·         DBA_IND_PARTITIONS

·         DBA_IND_SUBPARTITIONS

·         DBA_PART_COL_STATISTICS

·         DBA_PART_HISTOGRAMS

·         DBA_SUBPART_COL_STATISTICS

·         DBA_SUBPART_HISTOGRAMS

·         INDEX_STATS              存储ANALYZE ..VALIDATE STRUCTURE统计信息

·         AUX_STATS$               存储CPU统计信息

·         X$KCFIO                  存储I/O统计信息

 

因为数据库中的对象会经常的变化，所以统计信息必须有规律的更新以便更加准确的描述这些数据库对象。统计信息默认是由ORACLE自动维护的，不过我们也可以用DBMS_STATS包手动收集统计信息。DBMS_STATS包同样提供了过程来维护统计信息。关于DBMS_STATS包更详细的描述请参阅官方文档PL/SQL Packages and Types Reference部分。
2. 自动收集统计信息
    Oracle10g中，在安装Oracle的时候，就默认创建了一个名为GATHER_STATS_JOB的job来自动收集优化器统计信息。这个job收集数据库中所有对象的统计信息。默认的情况下这个job是周一到周五每天晚上10点到第二天早上6点以及整个周末来收集统计信息。

Job信息记录在下列视图中：

DBA_SCHEDULER_JOBS DBA_SCHEDULER_PROGRAMS

DBA_SCHEDULER_WINDOWS

DBA_SCHEDULER_JOB_RUN_DETAILS

自动收集过期的统计信息依赖于表监控特征，在Oracle10g中表监控默认是开启的，同时它也依赖STATISTICS_LEVEL参数的值，10g中默认为typical,只有将STATISTICS_LEVEL参数设置为ALL或者TYPICAL才能让ORACLE识别过期的统计信息。

3. 关闭自动收集统计信息
在某些情况下，我们想关闭自动收集统计信息那么我们可以利用如下方法:

   BEGIN

      DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

   END;

   /

4. 何时该手动收集统计信息

有时候自动收集统计并不合适，因为自动收集统计信息是在午夜运行的，然而由于对象是在白天被修改了，导致导致的统计信息变得陈旧

 

这里有2种这类对象:

白天经常被delete,或者truncated之后又rebuild的表(经常变化的表)

批量操作之后有10%或者以上的数据被更改的表(批量处理的表)

对于经常变化的表，可以将其统计信息设置为null，当ORACLE遇到一个表没有统计信息，ORACLE会动态采样以便为查询优化器收集必要的统计信息。

 

动态采样这个特征受到参数optimizer_dynamic_sampling的控制，它的默认值为2，同时呢optimizer_mode也能控制动态采样，可将其设置为all.

 

以SCOTT用户下的DEPT表为例，将一个表的统计信息设置为null的方法如下：

BEGIN 

DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT'); 

DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');

END;

/

 

我们也可以在表具有典型的，代表性的时候收集统计信息，并且锁住其统计信息，因为在夜晚自动收集的统计信息未必适用于白天的负载，而典型的统计信息具有代表意义，所以这个时候采取lock其典型的统计信息更能让CBO选择更优的执行计划。

至于上面的两种方法用哪种，这个还需要根据业务，实际情况分析之。

对于批量处理的表 ，应该在批量处理完成的时候立即对其收集统计信息，可以将收集统计信息的脚本绑定到批量处理的脚本中。

对于外部表，只能通过gather_table_stats过程来收集统计信息，并且外部表不支持取样，所以需要把gather_table_stats中的estimate_percent设置为null。

系统的统计信息也需要手动收集，因为这些信息是不会自动收集的。

对于固定对象，比如说动态性能表，需要手动的执行gather_fixed_objects_stats过程来收集。固定的对象反映了当前数据库的活动。当数据库活动处于具有代表性的时候，就应该收集这类统计信息。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13177610/viewspace-688095/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13177610/viewspace-688095/