优化器(CBO)的统计信息

 

优化器统计信息就是一个更加详细描述数据库和数据库对象的集合，这些统计信息被用于查询优化器，让其为每条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包同样提供了过程来维护统计信息。
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识别过期的统计信息。

SQL> execute dbms_stats.gather_table_stats(-

　　>ownname => 'test',-

　　>tabname => 'PARTTAB',-

　　>partname => null,- --> 收集所有分区状态

　　>estimate_percent => null,- --> 计算模式

　　>block_sample => false,- --> 默认值，计算模式下无意义

　　>method_opt => 'FOR ALL COLUMNS SIZE 1',- --> 表和列统计，不生成直方图

　　>degree => null,- --> 基于PARTTAB表上的DOP设置的默认并行度

　　>granularity => 'default',- -->收集全局和分区统计数据

　　>cascade => true ,- --> 产生所有统计数据

　　>stattab => null,- -->统计数据将被存储在字典中

　　>statid => null,-

　　>statown => null);
DBMS_STATS.GATHER_TABLE_STATS详解
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息.

DBMS_STATS.GATHER_TABLE_STATS的语法如下:

DBMS_STATS.GATHER_TABLE_STATS (   ownname      VARCHAR2,   
                                                                              tabname      VARCHAR2,   
                                                                              partname     VARCHAR2,  
                                                                              estimate_percent  NUMBER,   
                                                                              block_sample     BOOLEAN,  
                                                                              method_opt       VARCHAR2,   
                                                                              degree           NUMBER,   
                                                                              granularity      VARCHAR2,  
                                                                              cascade          BOOLEAN,  
                                                                              stattab          VARCHAR2,   
                                                                              statid           VARCHAR2,  
                                                                              statown          VARCHAR2,  
                                                                              no_invalidate    BOOLEAN,  
                                                                              force            BOOLEAN);
参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:决定并行度.默认值为null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascace:是收集索引的信息.默认为false.

stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.

例子:

execute dbms_stats.gather_table_stats(ownname => 'owner',
                                                                          tabname => 'table_name' ,
                                                                          estimate_percent => null ,
                                                                          method_opt => 'for all indexed columns auto' ,
                                                                          cascade => true);