分区表用哪个级别的统计信息

  这几天有几个分区表上的SQL执行计划不正常, 感觉上不应当, 已经设置了几个容易引起优化器选错执行计划的参数了.

ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=0;
ALTER SYSTEM SET "_optim_peek_user_binds"=false;

    按照现行的表分析原则, 只统计了表的全局索引, 不统计表分区上的索引, 因为分区会有增减操作, 不是每个分区都有数据, 也没有定下来要经常分析表, 而是分析后如果不出问题就不再分析. 现在SQL走错了, 可能的原因有什么呢? 重新分析一下不行, 看了一下执行计划, 发现全表扫描的COST值很底, 估计是用了分区上的统计信息了, 于是手动将全局的统计信息复制到每一个分区上得以解决.

    为了得到原因, 回顾了一下当时的SQL, 发现有一个特征, 就是出问题的SQL都能定位到某个分区或某些分区, 今天就模拟了一下情况, 先只有全局统计信息. 首先用分区列范围查询, 去试了一下, 发现用的是分区级的统计信息.

SQL> select * from database_perf_statistics
  2  where day > sysdate - 100 and day < sysdate + 300;

----------------------------------------------------------------
| Id  | Operation                 | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------
|   0 | SELECT STATEMENT          |     3   (0)|       |       |
|*  1 |  FILTER                   |            |       |       |
|   2 |   PARTITION RANGE ITERATOR|     3   (0)|   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL      |     3   (0)|   KEY |   KEY |
----------------------------------------------------------------

    然后全表扫描, 涉及所有的分区时, 用的是全局的统计信息.

SQL> select * from database_perf_statistics;
             
----------------------------------------------------------
| Id  | Operation           | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------
|   0 | SELECT STATEMENT    |  2196   (1)|       |       |
|   1 |  PARTITION RANGE ALL|  2196   (1)|     1 |     4 |
|   2 |   TABLE ACCESS FULL |  2196   (1)|     1 |     4 |
----------------------------------------------------------

    再测试一个开包的查询条件, 发现用的也是分区的统计信息.

SQL> select * from database_perf_statistics where day > sysdate - 100;

---------------------------------------------------------------
| Id  | Operation                | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------
|   0 | SELECT STATEMENT         |     3   (0)|       |       |
|   1 |  PARTITION RANGE ITERATOR|     3   (0)|   KEY |     4 |
|*  2 |   TABLE ACCESS FULL      |     3   (0)|   KEY |     4 |
---------------------------------------------------------------

    重新设置了一下各分区的统计信息, 再验证一下上面的SQL的COST值, 就确定了是这个原因引起的, 需要更改一下我们的分析策略了.