In my previous post I have explained how to capture different statistics from the same table into temporary stattab’s for further analysis (Or to provide to support). In 10.2.0.4 and 11g, dbms_stats has some procedures that can help you get a report on the difference between the two sets of statistics.
DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB is one such procedure. Continuing from my last example where we created two separate stats tables stattab_old and stattab_new, you can now check the difference between the two sets of stats by running the following sql statement
set long 500000 longchunksize 500000
select report, maxdiffpct from
table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(null,’DEPT’,’STATTAB_OLD’,’STATTAB_NEW’));
You will see an output similar to the one below (The stats are not really what you will see on a dept table, but you get the general idea)
############################################################################### STATISTICS DIFFERENCE REPORT FOR: ................................. TABLE : DEPT OWNER : SCOTT SOURCE A : User statistics table STATTAB_OLD : Statid : : Owner : SCOTT SOURCE B : User statistics table STATTAB_NEW : Statid : : Owner : SCOTT PCTTHRESHOLD : 10 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ COLUMN STATISTICS DIFFERENCE: ............................. COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ ............................................................................... DEPTNO A 1 .000000203 YES 0 3 C105 C105 9363 B 9 .000000341 YES 0 3 C102 C10A 1465265 DNAME A 21 .000000203 YES 0 6 C4032 C4032 9363 B 315 .003967048 YES 0 6 C4032 C4032 1465265 LOC A 25110 .000039824 NO 0 5 C2061 C32D1 47114 B 161368 .000006197 NO 0 5 C114 C33C3 1465265 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS ###############################################################################
NDV – Number of distinct values
Density – 1/NDV
