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
STATISTICS DIFFERENCE REPORT FOR:
TABLE : /BIC/FTEST
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
/BIC/FTEST T A 11892 151 86 11892
COLUMN STATISTICS DIFFERENCE:
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
CREATED A 1804 .000554323 NO 0 8 78690 786B0 11892
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
/BIC/FTEST~0 I A 12 1 12 1 1 12 0 12
/BIC/FTEST~1 I A 34 1 34 1 1 34 0 34
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
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.