oracle 11g新特性:Pending Statistics

转过来学习：http://daizj.iteye.com/blog/1716867

从11g开始，表与索引的统计信息收集完毕后，可以选择收集的统信息立即发布，也可以选择使新收集的统计信息处于pending状态，待确定处于pending状态的统计信息是安全的，再使处于pending状态的统计信息发布，这样就会避免一些因为收集统计信息立即发布而导致SQL执行计划走错的灾难。 

在 11g 之前的版本中，DBMS_STATS 自动统计收集(Automatic Statistics Gathering)默认的阀值是 10%, 这个 10% 是不可以修改的。这对千变万化的企业数据库来说环境来说，有些死板，如果是个超大的表，默认的 10% 数据也是海量了，会把整个资源占死。Oracle 11g 中，这个属性可以通过修改 STALE_PERCENT 属性来修改, 有全局（DBMS_STATS.SET_GLOBAL_PREFS ）和表级别（DBMS_STATS.SET_TABLE_PREFS）两种。 

1 如何判断是否有pending的统计信息需要生效？ 
SQL> Select dbms_stats.get_prefs('PUBLISH') publish from dual; 
PUBLISH 
-------------------------- 
TRUE 
dbms_stats的get_prefs函数返回true，表示对象的统计信息收集后立即生效，如果返回flase，收集的统计信息将处于pending状态。 
2 如果查看相关的视图 
A 立即生效的统计信息可以通过以下字典可以查看 
user_tab_stats 
user_ind_stats 
B pending状态的统计信息可以通过以下字典可以查看 
user_tab_pending_stats 
user_ind_pending_stats 
3 如何设置表或schema的统计信息的publish状态 
用dbms_stats的set_table_prefs或者set_schema_prefs过程可以在表级或schema表设置它们的统计信息是否立即生效，当我们设置tmp_test表的统计信息收集后处于pending状态，那该表收集统计信息后，将存放于user_tab_pending_stats字典中。 
SQL> Exec dbms_stats.set_table_prefs('yekai','tmp_test','publish','false'); 
PL/SQL procedure successfully completed. 
SQL> select count(*) from user_tab_pending_stats; 
COUNT(*) 
---------- 
0 
SQL> exec dbms_stats.gather_table_stats('yekai','tmp_test'); 
PL/SQL procedure successfully completed. 
SQL> select count(*) from user_tab_pending_stats; 
COUNT(*) 
---------- 
1 
4 如何测试并使用处于pending状态的统计信息 
在11g，新的参数optimizer_pending_statistics将可以来解决这个问题，当我们在session级设置optimizer_pending_statistics为true时，我们就可以使用存放在user_*_pending_stats字典中的统计信息啦，当我们确保该处于pending状态的统计信息是正确时，我们就可以决定是否使它们立即生效。 
SQL> alter session set optimizer_pending_statistics = TRUE; 
5 如何发布处于pending状态的统计信息 
当测试过统计信息有效后，我们可以选择发布pending状态的统计信息 
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test'); 
如果我们不需要该处于pending状态的统计信息，可以选择删除这个pending的统计信息 
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test'); 

在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 



3、Pending Statistics设置 

在11g环境中，数据表、Schema都存在一个统计量相关参数PUBLISH，表示当有新统计量的时候，新统计量是否立即被publish出来，作为最新的统计信息使用。 

该参数的默认值为TRUE。 


SQL> select dbms_stats.get_prefs(pname => 'PUBLISH',ownname => 'SYS',tabname => 'T') from dual; 
DBMS_STATS.GET_PREFS(PNAME=>'P 
------------------------------------------------------- 
TRUE 

--设置数据表的publish参数取值； 
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','false'); 
PL/SQL procedure successfully completed 

SQL> select dbms_stats.get_prefs('PUBLISH',ownname => 'SYS',tabname => 'T') from dual; 
DBMS_STATS.GET_PREFS('PUBLISH' 
-------------------------------------- 
FALSE 


此时，数据表中已经包括了七万余条数据，重新收集统计量。 


SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); 
PL/SQL procedure successfully completed 


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 


当我们将数据表T的PUBLISH参数修改为false之后，我们重新收集统计量，发现原有统计信息并没有连带的更新。

新统计量不是没有收集，而是被记录在了pending信息中。我们可以通过user_ind_pending_stats和user_tab_pending_stats两个视图查看被pending的统计量信息。 


SQL> select NUM_ROWS, BLOCKS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED from user_tab_pending_stats where table_name='T'; 

NUM_ROWS    BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED 
---------- ---------- ----------- ----------- ------------- 
    72202      1028         97      72202 2012/6/20 20: 

SQL> select index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR,LAST_ANALYZED from user_ind_pending_stats where table_name='T'; 

INDEX_NAME                    LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED 
------------------------------ ----------- ------------- ----------------- ------------- 
IDX_T_OWNER                           293           23             1884 2012/6/20 20: 
IDX_T_ID                              256        72202             1665 2012/6/20 20: 


4、Pending和SQL执行计划 

新的统计量没有被publish出来。那么，在一般情况下，我们的SQL执行计划还是依据正式被publish的统计量生成。 


SQL> explain plan for select * from t where wner='SYS'; 
Explained 

SQL> select * from table(dbms_xplan.display); 
PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------ 
Plan hash value: 1516787156 
------------------------------------------------------------------------------ 
| Id | Operation                  | Name       | Rows | Bytes | Cost (%CPU)| 
------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT           |            |    1 |  207 |    1  (0)| 
|  1 | TABLE ACCESS BY INDEX ROWID| T          |    1 |  207 |    1  (0)| 
|* 2 |  INDEX RANGE SCAN         | IDX_T_OWNER |    1 |      |    1  (0)| 
-------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  2 - access("OWNER"='SYS') 

14 rows selected 


实际执行情况； 

SQL> select * from t where wner='SYS'; 
已选择58799行。 

已用时间: 00: 00: 06.19 

执行计划 
---------------------------------------------------------- 
Plan hash value: 1516787156 
------------------------------------------------------------------------------- 
| Id | Operation                  | Name       | Rows | Bytes | Cost (%CPU)| Time    | 
--------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT           |            |    1 |  207 |    1  (0)| 00:00:01 | 
|  1 | TABLE ACCESS BY INDEX ROWID| T          |    1 |  207 |    1  (0)| 00:00:01 | 
|* 2 |  INDEX RANGE SCAN         | IDX_T_OWNER |    1 |      |    1  (0)| 00:00:01 | 
------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  2 - access("OWNER"='SYS') 

统计信息 
---------------------------------------------------------- 
       528 recursive calls 
         0 db block gets 
      8962 consistent gets 
      1108 physical reads 
         0 redo size 
   6291375 bytes sent via SQL*Net to client 
     43520 bytes received via SQL*Net from client 
      3921 SQL*Net roundtrips to/from client 
         4 sorts (memory) 
         0 sorts (disk) 
     58799 rows processed 

SQL> 


在sys用户下，行数比例超过了数据表T的绝大多数。按照CBO的原则，走全表扫描可能是较好的方法。但是，由于统计量还是在空表的状态下，所以，Oracle CBO认为Index路径会更好。 

在Oracle中，存在一个参数optimizer_use_pending_statistics，用来控制当前是否使用pending的统计量来生成执行计划。作为运维DBA，可以通过这个参数暂时性的启用pending统计量，观察一下性能状况。再决定是否启用publish这些统计量。 

默认情况下，该参数取值为false。我们可以在session级别设置下该参数为true。 


SQL> show parameter optimizer_use_pending 
NAME                                TYPE       VALUE 
------------------------------------ ----------- ------------------------------ 
optimizer_use_pending_statistics    boolean    FALSE 


修改参数为true之后，Oracle CBO在生成执行计划的时候就会使用Pending的统计量。 


SQL> alter session set optimizer_use_pending_statistics=true; 
Session altered 

SQL> select value from v$parameter where name='optimizer_use_pending_statistics'; 
VALUE 
------------------------------------------ 
TRUE 

SQL> explain plan for select * from t where wner='SYS'; 
Explained 

SQL> select * from table(dbms_xplan.display); 
PLAN_TABLE_OUTPUT 
-------------------------------------------------------------------------- 
Plan hash value: 1601196873 
-------------------------------------------------------------------------- 
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    | 
-------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT |     | 58274 | 5463K|  281  (1)| 00:00:04 | 
|* 1 | TABLE ACCESS FULL| T   | 58274 | 5463K|  281  (1)| 00:00:04 | 
-------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  1 - filter("OWNER"='SYS') 
13 rows selected 

SQL> select * from t where wner='SYS'; 
已选择58799行。 

已用时间: 00: 00: 04.68 

执行计划 
---------------------------------------------------------- 
Plan hash value: 1601196873 
-------------------------------------------------------------------------- 
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    | 
-------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT |     | 58274 | 5463K|  281  (1)| 00:00:04 | 
|* 1 | TABLE ACCESS FULL| T   | 58274 | 5463K|  281  (1)| 00:00:04 | 
-------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  1 - filter("OWNER"='SYS') 
统计信息 
---------------------------------------------------------- 
      7511 recursive calls 
        50 db block gets 
      6599 consistent gets 
      1118 physical reads 
         0 redo size 
   2392962 bytes sent via SQL*Net to client 
     43520 bytes received via SQL*Net from client 
      3921 SQL*Net roundtrips to/from client 
       211 sorts (memory) 
         0 sorts (disk) 
     58799 rows processed 


果然，设置参数后，Oracle生成了FTS路径，说明更新的统计量起了作用。同时，执行时间减少了近2秒钟，说明结果上也确实是生成了更好的执行计划。 

5、Pending统计量的后续处理 

在对pending统计量进行合理评估之后，DBA是可以做出删除还是发布统计量的决定的。具体操作如下： 

--删除pending信息 
SQL> exec dbms_stats.delete_pending_stats(user,'T'); 
PL/SQL procedure successfully completed 

SQL> select count(*) from user_tab_pending_stats; 
COUNT(*) 
---------- 
        0 

--重新收集pending统计量 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); 
PL/SQL procedure successfully completed 

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 

--发布pending统计量 
SQL> exec dbms_stats.publish_pending_stats(user,'T'); 
PL/SQL procedure successfully completed 

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 
---------- ------------ ---------- ---------- ----------- 
    72202        1028         0         0         96 


单发布完统计量之后，就可以在正常的情况下使用统计量生成执行计划了。 


SQL> show parameter optimizer_use_pen 
NAME                                TYPE       VALUE 
------------------------------------ ----------- ------------------------------ 
optimizer_use_pending_statistics    boolean    FALSE 

SQL> alter session set optimizer_use_pending_statistics=false; 
会话已更改。 

已用时间: 00: 00: 00.01 
SQL> select * from t where wner='SYS'; 
已选择58799行。 

已用时间: 00: 00: 04.33 
执行计划 
---------------------------------------------------------- 
Plan hash value: 1601196873 
-------------------------------------------------------------------------- 
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    | 
-------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT |     | 58794 | 5511K|  281  (1)| 00:00:04 | 
|* 1 | TABLE ACCESS FULL| T   | 58794 | 5511K|  281  (1)| 00:00:04 | 
-------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  1 - filter("OWNER"='SYS') 
统计信息 
---------------------------------------------------------- 
       426 recursive calls 
         0 db block gets 
      4975 consistent gets 
         0 physical reads 
         0 redo size 
   2392962 bytes sent via SQL*Net to client 
     43520 bytes received via SQL*Net from client 
      3921 SQL*Net roundtrips to/from client 
         4 sorts (memory) 
         0 sorts (disk) 
     58799 rows processed 


6、结论 

在11g中提出的pending statistic的方法，可以在生产运维和稳定优化执行计划方面，给我们提供帮助。