11g新特性--檢視錶的歷史統計資訊差異

11g新增了一個函式DIFF_TABLE_STATS_IN_HISTORY ，可以對比兩個時間點的統計資訊差異，測試如下：

FUNCTION DIFF_TABLE_STATS_IN_HISTORY RETURNS DIFFREPTAB

Argument Name Type In/Out Default?

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

OWNNAME VARCHAR2 IN

TABNAME VARCHAR2 IN

TIME1 TIMESTAMP WITH TIME ZONE IN

TIME2 TIMESTAMP WITH TIME ZONE IN DEFAULT

PCTTHRESHOLD NUMBER IN DEFAULT

SQL> create table opt_test as select rownum id from dual connect by level<=10000;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'OPT_TEST');

PL/SQL procedure successfully completed.

SQL> select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2013-07-02 10:11:32

--之後修改表opt_test結構，增加一列，並插入新的記錄

SQL> alter table opt_test add(name varchar(10));

Table altered.

SQL> insert into opt_test select rownum,lpad(rownum,10,'x') from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'OPT_TEST');

PL/SQL procedure successfully completed.

--檢視前後統計資訊差異

SQL> select report, maxdiffpct from table(dbms_stats.diff_table_stats_in_history('SCOTT','OPT_TEST',to_timestamp('2013-07-02 10:11:32','YYYY-MM-DD hh24:mi:ss')));

REPORT MAXDIFFPCT

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

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:

.................................

TABLE : OPT_TEST

OWNER : SCOTT

SOURCE A : Statistics as of 02-JUL-13 10.11.32.000000 AM -04:00

SOURCE B : Current Statistics in dictionary

PCTTHRESHOLD : 10

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:

.............................................

OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE

...............................................................................

OPT_TEST T A 10000 20 4 10000

B 20000 54 10 20000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:

.............................

COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ

...............................................................................

NAME A NO_STATS

B 10000 .0001 NO 10000 6 78787 78787 10000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS

###############################################################################

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-765472/，如需轉載，請註明出處，否則將追究法律責任。

11g新特性--檢視錶的歷史統計資訊差異

相關文章