Oracle 11g的統計資訊功能增強

在11g中，Oracle在統計資訊方面進行了進一步的增強。

這篇介紹PENDING統計資訊。

在10g中，Oracle提供了恢複統計資訊的方法，這使得統計資訊收集後造成的問題可以通過恢複原始統計資訊的方法快速解決。

在11g中，Oracle更進了一步，使用者收集的統計可以設定為懸而未決的，等到統計資訊驗證過然後在發布到資料字典中。

SQL> SELECT * FROM V$VERSION;

BANNER

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

Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

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

DBMS_STATS.GET_PREFS('PUBLISH')

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

TRUE

這個查詢用於檢查收集的統計資訊是否即時生效，預設情況下，用DBMS_STATS包收集的統計資訊會馬上更新到資料字典中，並對最佳化器生效。

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;    

已建立24行。

SQL> COMMIT;

提交完成。

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

PL/SQL過程已成功完成。

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

會話已更改。

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                                      24          5 2010-10-28 08:04:50

這時預設的情況，如果修改全域設定，使得PENDING方式生效：

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'T', 'PUBLISH', 'FALSE')

PL/SQL過程已成功完成。

SQL> DELETE T;

已刪除24行。

SQL> INSERT INTO T SELECT ROWNUM FROM ALL_OBJECTS;

已建立69442行。

SQL> COMMIT;

提交完成。

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

PL/SQL過程已成功完成。

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                                      24          5 2010-10-28 08:04:50

可以看到，新收集的統計資訊並沒有覆蓋資料字典中原始的統計資訊，這是因為表T的全域屬性被修改，此時收集的統計資訊並不會馬上發布，而是至於PENDING狀態，可以通過查詢資料字典USER_TAB_PENDING_STATS擷取PENDING統計資訊：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED

2  FROM USER_TAB_PENDING_STATS

3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED

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