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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-765472/