Oracle 11g 数据统计量Pending处理

从10g开始，Oracle引入了自动收集统计量的作业，以保证数据字典中统计量正确反映数据对象状态。这在很大程度上，缓解了由于数据变

在CBO时代，SQL语句的执行计划完全依赖于在数据字典中保存的统计量信息和优化器Optimizer的计算公式参数。从9i开始到现在的11gR2，我们说CBO优化器已经很成熟和完善。在通常情况下，我们的SQL都是可以获取到较好的执行计划以及执行效率的。 

在实际工作中，我们经常会遇到执行计划低效的情况。但是这种故障根源中，绝大多数的原因在于统计量的错误或者失效。错误的统计量连带生成的就是不恰当的执行计划，以至于低效的执行过程。在9i时代，RBO和CBO混合使用，让我们经常需要自定义的统计量收集过程。 

从10g开始，Oracle引入了自动收集统计量的作业，以保证数据字典中统计量正确反映数据对象状态。这在很大程度上，缓解了由于数据变化导致的统计量过期问题。但是，我们在实际工作中，还是会发现执行计划的突然变化。究其原因，就是某个时间点收集的统计量，也许不能反映数据的全貌（如中间表）。

1、统计量Pending

在系统运维中，我们常常希望维持SQL执行计划的稳定。很多DBA和开发人员对于hint的依赖，很大程度上也是源于对CBO情况下，执行计划对于统计量过于依赖，容易形成不稳定执行计划。

那么，我们SQL语句执行计划的稳定性，就变成统计量的稳定性问题。更进一步，就是新的统计量更新，无论是否手动收集还是自动收集，能否促进SQL语句生成更高效的执行计划。

所以，一种思路是：在新的统计量收集生成时，暂时不要生效投入执行计划生成。等待最后确认统计量正确之后，再投入生产环境。

在Oracle 11g中，推出了统计量管理的一种新技术——Pending Statistic技术，提供了这种功能。

简单的说，我们可以对一系列的数据表设置pending属性。设置pending属性之后，数据的统计量在数据字典中相当于已经锁定Lock住。但新统计量生成之后，不是直接替换原有的数据，而是存放在pending数据字典中。

在pending字典中的统计量，默认情况下是不会参与SQL执行计划的生产的。只有在进行SQL测试通过的时候，经过用户手工的确定，才会将其Publish出来，替换原有的统计量信息。

这样，就给我们运维DBA一种维持执行计划稳定的思路。通过固定统计量，将新统计量pending的方式将原有的统计量固定，从而稳定执行计划。进而，对pending的统计量进行测试，只有在更好执行计划的情况下，才会替换原有的方案。

下面，我们通过实验来验证pending统计量的使用。

2、实验环境构建

我们选择11gR2进行实验。

SQL> select * from v$version;

BANNER

-----------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE   11.2.0.1.0 Production

构建数据表T，以及对应的索引。注意，我们首先在数据表中不保存任何数据。

SQL> create table t as select * from dba_objects where 1=0;

Table created

 

SQL> create index idx_t_owner on t(owner);

Index created

 

SQL> create index idx_t_id on t(object_id);

Index created

在不显式的收集统计量的情况下，，是没有对应的数据表统计量的。

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

 NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

---------- ------------ ---------- ---------- -----------

 

SQL> select count(*) from user_tab_col_statistics where table_name='T';

 COUNT(*)

----------

        0

 

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR  NUM_ROWS from user_ind_statistics where index_name='IDX_T_OWNER';

   BLEVEL LEAF_BLOCKS DISTINCT_KEYS  NUM_ROWS

---------- ----------- ------------- ----------

        0          0            0         0

收集统计量，获取最新的数据分布状况。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

当我们修改数据内容，没有收集统计量，会存在新旧差异。

SQL> insert into t select * from dba_objects;

72202 rows inserted

 

SQL> commit;

Commit complete

 

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

 

 NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

---------- ------------ ---------- ---------- -----------

        0           0         0         0          0