5 统计信息--优化主题系列

请记住，高级DBA 一定要收集统计信息，而且数据库自带的JOB 一定要关闭

并且要自己定制统计信息收集策略

 

在OLTP系统中，一定要收集统计信息

很多的 OLAP 系统都不收集，直接用HINT固定我们现在就不收集

因为OLAP系统I/O压力大，表也非常大，收集统计信息很耗费资源

并且OLAP系统表的数据随时都在变化(因为实时在入库),那么就需要经常收集统计信息

所以OLAP系统收集统计信息不现实。

 

如何判断一个系统是OLAP 还是OLTP？？

典型的归档是否开启 OLTP一般都开启 OLAP不开

 

为什么OLTP系统一定要收集统计信息？？

OLTP系统业务非常复杂

不同查询条件很可能访问路径就会发生变化

走错了执行计划很可能数据库会崩溃

 

在OLTP系统中要优化一个SQL语句，首先要保证SQL语句中所有的表统计信息是准确的。如果统计信息都不准确，那么SQL优化就无从谈起。在OLAP系统中，如果数据量特别大，一般不会去收集统计信息，会直接利用HINT把执行计划给固定住。

 

对非分区表收集统计信息：

BEGIN

 DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',

                               tabname          => 'DEPT',

                               estimate_percent => 30,

                               method_opt       => 'for all columns sizerepeat',

                               no_invalidate    => FALSE,

                               degree           => 8,

                               cascade          => TRUE);

END;

/

 

对分区表收集统计信息

BEGIN

 DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',

                               tabname          => 'P_TEST',

                               estimate_percent => 30,

                               method_opt       => 'for all columns sizerepeat',

                               no_invalidate    => FALSE,

                               degree           => 8,

                               granularity      => 'ALL',

                               cascade          => TRUE);

END;

/

 

estimate_percent表示采样率，采样率设置太大，也没必要，如果表非常大，采样率过高会导致收集统计信息跑很长，增加了系统压力。采样率设置过小，统计的信息就不能很完整的体现表中数据的分布，这样CBO在进行执行计划的选择上，很可能选择错误的执行计划。

根据我的工作经验：

表小于1GB 采样率可以设置50%-100%

表大于1GB小于5GB可以设置30%

表大于5GB 这类表都应该进行分区，采样率可以设置为30%

 

提问：为什么要根据segment_size来计算一个表是大表还是小表？？

提问：统计信息最佳采样率多大？？

原厂最佳实践 30%是最佳的采样率

 

method_opt表示收集的方法，一个稳定的系统收集统计信息的时候推荐使用

method_opt=> 'for all columns size repeat'

repeat表示以前收集过直方图，现在收集统计信息的时候就收集直方图，如果以前没收集过直方图，现在收集统计信息的时候就不收集。

method_opt => 'for all columns size auto',

auto表示Oracle根据谓词过滤信息(前文讲解直方图的时候提到过的where条件过滤)，自动判断该列是否收集直方图。一个稳定的系统，不应该让Oracle去自动判断，自动判断很可能就会出事，比如某列不该收集直方图，设置auto过后它自己去收集直方图了，从而导致系统不稳定。

degree表示收集统计信息的时候并行度，并行度根据你系统配置以及当前系统可用资源自行设置。一般degree设置4--8。

一般来说设置为4

 

cascade表示收集表的统计信息时候同时收集索引的统计信息。

no_invalidate表示收集统计信息之后在共享池中引用了相关表的SQL游标是否失效。这个一定要设置为FALSE，不然你可能在做SQL优化的时候，你发现明明更新了统计信息，但是执行计划还是没改变。

FALSE 表示说立即失效否则游标15分钟再失效

 

granularity 分区的粒度默认是'ALL'对所有分区都收集可设置为partition

method_opt => 'for all columns size skewonly' 表示让ORACLE自动判断哪个列去收集统计信息

下面做个试验：

 

grant dba to scott;

conn scott/tiger

drop table test purge;

create table test as select * from dba_objects;

BEGIN