With release 11g Oracle made many feature enhancements to the database and some of them are now downported into the soon-to-be-released patchset 10.2.0.4.
One new feature that I just found by 'accident' is the easy comparision of CBO statistics for tables for different points in time.
As most of you know, Oracle (from release 10g onwards) keeps a history of the CBO stats for all tables and indexes that get their statistics via the DBMS_STATS package.
From here it is a small step to get the idea 'Why not compare the statistics of any two dates to see what changed?'
This is especially useful when a once top-performing query 'suddenly' becomes slow.
Of course, until now one was able to restore old statistics values, store them into a intermediate table, restore the current statistics and finally compare both datasets - but, that would have meant much manual work.
Now, it's just a single-liner in sqlplus, since the function DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY is available in Oracle 10.2.0.4.
I have a table called "/BIC/FTEST" that I use for BW related tests and trainings.
It's a small table but it's enough for the sake of this demonstration.
I truncated the table and re-gathered statistics via DBMS_STATS.
Now let's see what has changed:
SQL> select * from
2 table(dbms_stats.DIFF_TABLE_STATS_IN_HISTORY('SAPR3', '"/BIC/FTEST"', sysdate -4, NULL, NULL));
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : /BIC/FTEST
OWNER : SAPR3
SOURCE A : Statistics as of 07-JUL-08 12.00.10.000000 PM +02:00
SOURCE B : Current Statistics in dictionary
PCTTHRESHOLD :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
/BIC/FTEST T A 11892 151 86 11892
B 0 0 0 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
CREATED A 1804 .000554323 NO 0 8 78690 786B0 11892
B 0 0 NO 0 0 78690 786B0 NULL
DATA_OBJECT_ID A 3004 .000332889 NO 8852 2 C103 C30A1 3040
B 0 0 NO 0 0 C103 C30A1 NULL
GENERATED A 2 .5 NO 0 2 4E 59 11892
B 0 0 NO 0 0 4E 59 NULL
LAST_DDL_TIME A 2216 .000451263 NO 0 8 78690 786B0 11892
B 0 0 NO 0 0 78690 786B0 NULL
OBJECT_ID A 11892 .000084090 NO 0 5 C103 C30A1 11892
B 0 0 NO 0 0 C103 C30A1 NULL
OBJECT_NAME A 9295 .000107584 NO 0 19 2F424 5F757 11892
B 0 0 NO 0 0 2F424 5F757 NULL
OBJECT_TYPE A 34 .029411764 NO 0 7 434C5 57494 11892
B 0 0 NO 0 0 434C5 57494 NULL
OWNER A 12 .083333333 NO 0 6 44425 574D5 11892
B 0 0 NO 0 0 44425 574D5 NULL
SECONDARY A 1 1 NO 0 2 4E 4E 11892
B 0 0 NO 0 0 4E 4E NULL
STATUS A 2 .5 NO 0 7 494E5 56414 11892
B 0 0 NO 0 0 494E5 56414 NULL
SUBOBJECT_NAME A 109 .009174311 NO 11634 2 24565 57524 258
B 0 0 NO 0 0 24565 57524 NULL
TEMPORARY A 2 .5 NO 0 2 4E 59 11892
B 0 0 NO 0 0 4E 59 NULL
TIMESTAMP A 2716 .000368188 NO 0 20 31393 32303 11892
B 0 0 NO 0 0 31393 32303 NULL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................
INDEX: /BIC/FTEST~0
...................
/BIC/FTEST~0 I A 12 1 12 1 1 12 0 12
B 0 0 0 0 0 0 0 0
INDEX: /BIC/FTEST~1
...................
/BIC/FTEST~1 I A 34 1 34 1 1 34 0 34
B 0 0 0 0 0 0 0 0
###############################################################################
|
Amazing, isn't it?
Ok, the max/min values are still in the HEX-format we will also find in
DBA_TAB_HISTOGRAMS or
DBA_TAB_COL_STATISTICS view, but most of the values are immediately usable.
Even indexes and partitions (if there are any of them) will be automatically compared.
Some words to the function definition:
FUNCTION DIFF_TABLE_STATS_IN_HISTORY RETURNS DBMS_STATS
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
|
If
TIME2 is set to NULL than the CURRENT statistics are compared with the statistics as of
TIME1.
PCTTHRESHOLD defines a lower threshold that will prevent the report from displaying differences that are 'too small' to be interesting. The DEFAULT value for this threshold is 10.
So what I did was to compare the current statistics against the statistics as of 4 days ago (sysdate-4).
Since the changes had been rather large, the 10 percent threshold was easily fullfiled and all differences are reported.
I hope you like this one and maybe it proves to be useful in your performance problem analyis.
Best regards,
Lars
