Pending Statistics

   Oracle11.2开始，可以使用下面方式搜集统计信息：

    1、搜集统计信息完成后，自动发布(默认方式PUBLISH)
    2、保存新的统计信息，并且待定发布
   以待定方式保存新的统计信息，允许你检验新的统计信息，如果统计信息满足需求，再将其发布。
   为了检查统计信息搜集完成后是否自动发布，使用DBMS_STATS，如下：
   SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISHFROM DUAL;
   查询结果返回TRUE或FALSE。TRUE表明搜集统计信息完成后自动发布，而FALSE表明搜集统计信息完成后将待定。

提示：
数据库存储发布的统计信息到数据字典视图比如USER_TAB_STATISTICS 和USER_IND_STATISTICS。数据库存储待定的统计信息到视图比如USER_TAB_PENDING_STATS和USER_IND_PENDING_STATS。

   你可以在用户或者表级修改PUBLISH设置。例如，修改用户SH的customers表的PUBLISH设置：
    execdbms_stats.set_table_prefs('SH', 'CUSTOMERS', 'PUBLISH','false');
   随后，当搜集customers表的统计信息，搜集完成后，统计信息将不会自动发布，取而代之的是，数据库存储新搜集的统计信息到USER_TAB_PENDING_STATS。
   缺省情况下，优化器使用存在在数据字典视图中已发布的统计信息。如果希望优化器使用新搜集的待定统计信息，设置初始化参数OPTIMIZER_USE_PENDING_STATISTICS的值为TRUE(缺省值为FALSE)，并基于表或者用户运行一个工作负载。
    altersession set optimizer_use_pending_statistics = TRUE;
   当编译sql语句，优化器将使用待定的统计信息替代发布的统计信息。如果待定的统计性能是有效的，可以使用执行下面的语句将它发布：
    Execdbms_stats.publish_pending_stats(null, null);
   你可以为一个特定的数据对象发布待定统计信息。例如，使用下面的语句：
    Execdbms_stats.publish_pending_stats('SH','CUSTOMERS');
   如果你不想发布待定的统计信息，执行下面的语句：
    Execdbms_stats.delete_pending_stats('SH','CUSTOMERS');

   你可以使用函数dbms_stats.export_pending_stats导出待定的统计，导出待定的统计到测试系统，使你可以运行一个完整的工作负责测试新的统计信息。

简单实验如下：
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bitProduction
PL/SQL Release 11.2.0.3.0 - Production
CORE   11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISHFROM DUAL;

PUBLISH
----------------------
TRUE

SQL> show parameteroptimizer_use_pending_statistics

NAME                                TYPE       VALUE
------------------------------------ -----------------------------------------
optimizer_use_pending_statistics    boolean    FALSE     
SQL>
SQL> create table wxlun_obj as select * fromdba_objects where rownum<10;

Table created.

SQL> create index ind_obj_type onwxlun_obj(object_type);

Index created.

SQL> execdbms_stats.gather_table_stats('wxlun','wxlun_obj');

PL/SQL procedure successfully completed.

SQL>  select table_name,NUM_ROWSfrom user_tab_statistics where table_name='WXLUN_OBJ';

TABLE_NAME                      NUM_ROWS
------------------------------ ----------
WXLUN_OBJ                              9

SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;

no rows selected

SQL>

SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';


Execution Plan
----------------------------------------------------------
Plan hash value: 4094614133

--------------------------------------------------------------------------------------------
| Id  |Operation                  |Name        | Rows  | Bytes | Cost (%CPU)|Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT           |             |    3 |    30|    2   (0)| 00:00:01 |
|   1 |  TABLEACCESS BY INDEX ROWID|WXLUN_OBJ   |    3 |    30|    2   (0)| 00:00:01 |
|*  2 |   INDEXRANGESCAN         | IND_OBJ_TYPE|    3|      |    1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 -access("OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         4  consistent gets
         0  physical reads
         0  redo size
       597  bytes sent via SQL*Net to client
       519  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         4  rows processed

SQL>
多次执行：insert into wxlun_obj select * from dba_objects;

SQL> execdbms_stats.set_table_prefs('wxlun','wxlun_obj','PUBLISH','false');

PL/SQL procedure successfully completed.

SQL> execdbms_stats.gather_table_stats('wxlun','wxlun_obj');

PL/SQL procedure successfully completed.

SQL> select table_name,NUM_ROWS fromuser_tab_statistics where table_name='WXLUN_OBJ';

TABLE_NAME                      NUM_ROWS
------------------------------ ----------
WXLUN_OBJ                              9   ------user_tab_statistics的统计信息未变

SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;

TABLE_NAME                      NUM_ROWS
------------------------------ ----------
WXLUN_OBJ                        1085609   ------WXLUN_OBJ的统计信息放到了USER_TAB_PENDING_STATS
SQL> select count(*) from wxlun_obj;

  COUNT(*)
----------
   1085609

SQL> select count(*) from wxlun_obj whereobject_type='TABLE';

  COUNT(*)
----------
    90964

SQL>
因为wxlun_obj中object_type值为TABLE的数据量太多，按照分析下面的查询应该走全表扫描才合理，但是由于前面的设置,事与愿违:
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';

90964 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4094614133

--------------------------------------------------------------------------------------------
| Id  |Operation                  |Name        | Rows  | Bytes | Cost (%CPU)|Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT           |             |    3 |    30|    2   (0)| 00:00:01 |
|   1 |  TABLEACCESS BY INDEX ROWID|WXLUN_OBJ   |    3 |    30|    2   (0)| 00:00:01 |
|*  2 |  INDEX RANGESCAN         | IND_OBJ_TYPE|    3|      |    1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 -access("OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
     18283  consistent gets
         0  physical reads
         0  redo size
   1592229  bytes sent via SQL*Net to client
     67223  bytes received via SQL*Net fromclient
      6066  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     90964  rows processed

SQL>

SQL> alter session setoptimizer_use_pending_statistics = TRUE;

Session altered.

SQL> set linesize 132
SQL> set autotrace traceonly
SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';

90964 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 381774511

-------------------------------------------------------------------------------
| Id  |Operation        |Name     | Rows  | Bytes | Cost (%CPU)|Time    |
-------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT |          | 90024 |  1142K| 4148   (1)| 00:00:50 |
|*  1 |  TABLE ACCESS FULL| WXLUN_OBJ | 90024|  1142K| 4148   (1)| 00:00:50 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 -filter("OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
        52  recursive calls
         1  db block gets
     20656  consistent gets
         0  physical reads
         0  redo size
   1592229  bytes sent via SQL*Net to client
     67223  bytes received via SQL*Net fromclient
      6066  SQL*Net roundtrips to/from client
         3  sorts (memory)
         0  sorts (disk)
     90964  rows processed

SQL>

SQL> Execdbms_stats.publish_pending_stats('wxlun','wxlun_obj');

PL/SQL procedure successfully completed.

SQL> select table_name,NUM_ROWS fromuser_tab_statistics where table_name='WXLUN_OBJ';

TABLE_NAME                      NUM_ROWS
------------------------------ ----------
WXLUN_OBJ                        1085609

SQL> select table_name,NUM_ROWS fromUSER_TAB_PENDING_STATS;

no rows selected

SQL>
SQL> alter session setoptimizer_use_pending_statistics = false;

Session altered.

SQL> SELECT OBJECT_ID FROM wxlun_obj whereobject_type='TABLE';

90964 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 381774511

-------------------------------------------------------------------------------
| Id  |Operation        |Name     | Rows  | Bytes | Cost (%CPU)|Time    |
-------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT |          | 90024 |  1142K| 4148   (1)| 00:00:50 |
|*  1 |  TABLE ACCESS FULL| WXLUN_OBJ | 90024|  1142K| 4148   (1)| 00:00:50 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 -filter("OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
         1  recursive calls
         1  db block gets
     20625  consistent gets
         0  physical reads
         0  redo size
   1592229  bytes sent via SQL*Net to client
     67223  bytes received via SQL*Net fromclient
      6066  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     90964  rows processed

SQL>