通过锁定表的统计信息来稳定sql的执行计划

问题提出：
两个表的关联查询，一个表数据量在千万，一个表数据量只有几条（参数表），大表是按时间进行的分区表，而且时间字段上还建有分区索引。刚开始，执行计划是正确的，但是后来却发现两个表的关联不是走分区表的索引，而是直接扫描整个分区，有点费解。

了解业务后发现，参数表中有两个字段，一个是起始时间，一个是终止时间，这两个时间间隔一般在10分钟。每过一段时间，将拿参数表中的时间出来，然后根据时间段去大表中查询这段时间内进行的业务。大表每天新增数据在20万左右，10天一个分区，小表数据量不变，变的只是里面的开始和结束时间，但结束－开始基本都在10分钟左右。
因此应该是oracle不知道小表中的开始和结束时间的具体值，所以它不能使用这个值去进行成本估算，因此觉得扫描索引再从表读取数据还没有直接全扫描分区来的快。于是对表的column值进行分析，让oracle能知道其中的值的分布。analyze table tab compute statistics for column，执行上面分析后，再来查看执行计划，分区扫描变成了读取索引了，而且查询速度也提升不少。可是小表的数据是随时间频繁变化的，而且oracle会自动收集表的统计信息，导致执行计划再次出现问题。看了半天STORED OUTLINES也没整大明白怎么来固定一个sql的执行计划，而且动作比较大。
采用锁定统计信息的方法，调用dbms_stats.lock_table_stats包，把小表统计信息锁定，问题得到解决。

总结：在10G中，oracle会自动收集表的统计信息，大部分情况下，这种行为是有利的，不需要对某个表做收集的时候，可以采用锁定统计信息的方法，把不需要收集的表排除在外，使得此表上的sql的执行计划得到稳定

22．    锁定统计数据
   Oracle 10g 的表或者方案的统计数据可以被锁定。锁定之后，搜集统计数据的操作将
   不再更新这些表或者方案的统计数据。这样可以避免执行计划因为统计数据的改变
   而改变的情况。可以使用 DBMS_STATS.LOCK_TABLE_STATS  来锁定表的统计数
   据，通过 LOCK_SCHEMA_STATS            来锁定方案的统计数据。解锁可以通过
   UNLOCK_TABLE_STATS 和UNLOCK_SCHEMA_STATS 过程。
23．    管理统计数据历史
   在Oracle 10g 中，统计数据历史会写入AWR，然后再更新成新的统计数据。这样一
   旦新的统计数据有问题，则可以进行恢复。旧的统计信息会定期从AWR 中被清除，
   默认是 31      天。这个默认保留天数的值可以通过 DBMS_STATS                            的函数
   SET_STATS_HISTORY_RETENTION 来设置。DBMS_STATS 的下列过程可用来恢复
   各种类型的统计数据：
    RESTORE_DATABASE_STATS
    RESTORE_DICTIONARY_STATS
    RESTORE_FIXED_OBJECTS_STATS
    RESTORE_SCHEMA_STATS
    RESTORE_SYSTEM_STATS
    RESTORE_TABLE_STATS

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

转载于:http://blog.itpub.net/730796/viewspace-594728/