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>