|
原文链接：http://blog.itpub.net/2317695/viewspace-1226138/ 今天网上看到一篇关于收集统计信息的文章，还不错，特转载下来。 1. 理解什么是统计信息
优化器统计信息就是一个更加详细描述
数据库和数据库对象的集合，这些统计信息被用于查询优化器，让其为每条SQL语句选择最佳的执行计划。优化器统计信息包括: · 表的统计信息
o 行数
o Block数 o 行平均长度
· 列的统计信息
o 列中不同值的数量
o 列中null的数量 o 数据分布(柱状图/直方图) · 索引的统计信息
o 叶子块的数量
o 索引的高度
o 聚簇因子(clustering factor) · 系统的统计信息
o I/O性能和利用 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点以及整个周末来收集统计信息。 可以查看DBA_SCHEDULER_JOBS， DBA_SCHEDULER_PROGRAMS,DBA_SCHEDULER_WINDOWS，DBA_SCHEDULER_JOB_RUN_DETAILS等视图来查看JOB设置以及运行信息。
自动收集过期的统计信息依赖于表监控特征，在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过程来收集。固定的对象反映了当前数据库的活动。当数据库活动处于具有代表性的时候，就应该收集这类统计信息。 5. 锁住/解锁统计信息
· LOCK_SCHEMA_STATS · LOCK_TABLE_STATS · UNLOCK_SCHEMA_STATS · UNLOCK_TABLE_STATS
6. 手动收集统计信息
· 如果你选择手动收集统计信息，那么你需要手动的收集所有用户的统计信息，包括系统用户。如果你数据库中的数据是有规律的变化的，那么你可以有规律的收集统计信息，以便统计信息能够准确的反映数据库中的对象的特征。
· 可以利用DBMS_STATS包，来收集表，索引，列，以及分区表的统计信息，DBMS_STATS不能收集CLUSTER 的统计信息，不过可以收集单个表来代替收集整个CLUSTER的统计信息。
· 当你收集表，列，索引的统计信息的时候，如果ORACLE在数据字典中发现这个对象已经收集了统计信息，那么ORACLE会更新已经存在的统计信息，旧的统计信息会被保存下来，如果你愿意还能还原旧的统计信息。
· 你可以使用DBMS_STATS.GATHER_DICTIONARY_STATS来收集系统用户的统计信息，这个过程收集所有的系统用户的统计信息，包括SYS和SYSTEM，以及其他用户，比如CTXSYS,DRSYS。
· 当数据库对象的统计信息被更新之后，ORACLE会使已经解析的SQL语句作废，当再次运行该SQL语句的时候，ORACLE会重新解析该SQL，优化器会自动的根据新的统计信息选择一条新的执行计划。对于分布式的数据库，不会作废。
· 收集统计信息的过程
o GATHER_INDEX_STATS --收集索引统计信息 o GATHER_TABLE_STATS --收集表，列，索引统计信息 o GATHER_ |