前两篇介绍PENDING统计信息，这篇继续利用上一篇文章中的例子，来介绍11g新增的统计信息比较功能。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T                                      26 2010-10-31 06:42:55

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ----------------------------------------------------------
T                              31-10月-10 06.42.55.047571 上午 +08:00

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
  2  FROM USER_TAB_PENDING_STATS
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T                                   69446 2010-11-01 04:59:49

除了前一篇介绍的验证PENDING统计信息的方法，还可以利用11g提供的DIFF_TABLE_STATS_IN_PENDING函数来返回PENDING统计信息与数据字典统计信息的差异。

SQL> SET LONG 100000
SQL> SELECT * FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING(USER, 'T'));

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T
OWNER         : TEST
SOURCE A      : Current Statistics in dictionary
SOURCE B      : Pending Statistics
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

T                           T   A   26         5          3          26
                                B   69446      54         5          69446
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

ID              A   26      .038461538 NO   0       3    C102  C11B  26
                B   69446   .000014399 NO   0       5    C102  C3075 69446
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX: IND_T_ID
                                 ...............

IND_T_ID        I   A   26      1       26      1     1     1       0   26
                    B   69446   69      69446   1     1     53      NUL 69446
###############################################################################

函数返回的结果很友好，二者的差别一目了然。这个函数的输入函数可以指定时间戳，如果采用默认值，则使用数据字典中使用的统计信息，如果指定时间戳，则可以利用HISTORY中的统计信息与PENDING统计信息进行比较。

下面看一下HISTORY统计信息与当前统计信息进行比较：

SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
T                              31-10月-10 06.42.55.047571 上午 +08:00
T                              01-11月-10 05.19.35.035969 上午 +08:00

SQL> SELECT * FROM TABLE(                                                 
  2  DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY(     
  3  USER,
  4  'T',
  5  TO_TIMESTAMP_TZ('2010-10-31 06:42:55.047571 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF +TZH:TZM')));

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T
OWNER         : TEST
SOURCE A      : Statistics as of 31-10月-10 06.42.55.047571 上午 +08:00
SOURCE B      : Current Statistics in dictionary
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

T                           T   A   26         5          3          26
                                B   69446      54         5          69446
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

ID              A   NO_STATS
                B   69446   .000014399 NO   0       5    C102  C3075 69446
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX: IND_T_ID
                                 ...............

IND_T_ID        I   A   26      1       26      1     1     1       0   26
                    B   69446   69      69446   1     1     53      1   69446
###############################################################################

这个函数有两个时间戳输入参数，如果仅指定一个时间戳，则是指定时间点的统计信息与当前数据字典中的统计信息进行比较，如果输入两个时间戳，则是HISTORY中两个时间点的统计信息进行比较。

除了与PENDING统计信息、历史统计信息比较之外，还可以与用户导出、导入的统计信息进行比较：

SQL> SELECT COUNT(*) FROM T_STAT;

  COUNT(*)
----------
         3

SQL> SELECT * FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(USER, 'T', 'T_STAT'));

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T
OWNER         : TEST
SOURCE A      : User statistics table T_STAT
              : Statid     :
              : Owner      : TEST
SOURCE B      : Current Statistics in dictionary
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN COLUMN STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX: IND_T_ID
                                 ...............

IND_T_ID        I   A   69446   69      69446   1     1     53      NUL 69446
                    B   69446   69      69446   1     1     53      1   69446
###############################################################################

当第二个STATTAB输入参数为空时，统计表内的统计信息于数据字典中的统计信息进行比较，这也是默认设置。利用这个函数，还可以比较两张统计表中的统计信息，或者一张统计表中不同统计ID对应的统计，方法大同小异，这里就不再赘述了。