10g统计信息增加恢复功能（三）

 

从10g开始，Oracle可以恢复错误的统计信息。

这篇描述恢复统计信息的限制。

 

恢复统计信息操作也存在一些限制，比如无法恢复用户自定义的统计信息。除此之外由于统计信息是表的属性，因此如果表被删除，则对应的统计备份也被清除，而TRUNCATE操作则并不会影响统计备份信息：

SQL> CREATE TABLE T (ID NUMBER);

Table created.

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;

96 rows created.

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

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T                                      96          5 25-10月-10

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME

  2  FROM USER_TAB_STATS_HISTORY

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME

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

T                              25-10月-1010.55.10.109647下午+08:00

SQL> TRUNCATE TABLE T;

Table truncated.

SQL> INSERT INTO T SELECT ROWNUM FROM USER_OBJECTS;

242 rows created.

SQL> COMMIT;

Commit complete.

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

PL/SQL procedure successfully completed.

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

Session altered.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T                                     242          5 2010-10-25 22:56:39

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME

  2  FROM USER_TAB_STATS_HISTORY

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME

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

T                              25-10月-1010.55.10.109647下午+08:00

T                              25-10月-10 10.56.39.079646下午+08:00

SQL> DROP TABLE T PURGE;

Table dropped.

SQL> CREATE TABLE T (ID NUMBER);

Table created.

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;

96 rows created.

SQL> COMMIT;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME

  2  FROM USER_TAB_STATS_HISTORY

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME

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

T                              25-10月-1010.58.01.280423下午+08:00

对于DROP表会清除统计备份信息这很容易理解，还有一点需要注意，就是ANALYZE语句对于统计信息备份的影响：

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

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T                                      96          5 2010-10-25 23:06:00

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME

  2  FROM USER_TAB_STATS_HISTORY

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME

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

T                              25-10月-1010.58.01.280423下午+08:00

T                              25-10月-10 11.06.00.779685下午+08:00

SQL> ANALYZE TABLE T COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T                                      96          5 2010-10-25 23:06:50

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME

  2  FROM USER_TAB_STATS_HISTORY

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME

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

T                              25-10月-1010.58.01.280423下午+08:00

T                              25-10月-10 11.06.00.779685下午+08:00

SQL> BEGIN                            

  2  DBMS_STATS.RESTORE_TABLE_STATS(

  3  USER,

  4  'T',

  5  TO_TIMESTAMP_TZ('2010-10-25 23:06:00.779685 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T                                      96          5 2010-10-25 23:06:50

SQL> BEGIN

  2  DBMS_STATS.RESTORE_TABLE_STATS(

  3  USER,

  4  'T',

  5  TO_TIMESTAMP_TZ('2010-10-25 22:58:01.280423 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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

T                                      96          5 2010-10-25 22:58:01

使用ANALYZE语句生成的统计信息是无法进行恢复的，从USER_TAB_STATS_HISTORY视图中也可以看到，里面并不包含ANALYZE语句对应的统计信息。

而ANALYZE语法带来的影响不仅如此，还使得ANALYZE语句执行之前的统计信息无法进行恢复。可以看到，虽然从USER_TAB_STATS_HISTORY视图中可以查询到23:06:00的统计信息，但是利用RESTORE_TABLE_STATS过程无法恢复这个时间点的统计信息。Oracle调用DBMS_STATS包收集统计信息时，在将最新的统计信息写入到数据字典之前，会先将现有的统计信息备份，而ANALYZE语句显然不会进行这个操作，因此导致了ANALYZE执行之前的统计信息丢失。

 

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）