With release 11g Oracle made many feature enhancements to the database and some of them are now down-ported into the soon-to-be-released patchset 10.2.0.4.
One new feature that I just found by ‘accident’ is the easy comparison 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 an 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 : ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <pre>TABLE / (SUB)PARTITION STATISTICS DIFFERENCE: 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 then 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 fulfilled and all differences are reported.
I hope you like this one and maybe it proves to be useful in your performance problem analysis.
Best regards,
Lars