Skip to Content

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Stefan Koehler
    Hello Lars,
    great blog article with a really useful information.

    I like this feature in my Non-SAP Oracle 11g databases and now it is available with the Patchset 10.2.0.4.

    Thank you for finding this 🙂

    Now i am just looking forward to the Patchset 10.2.0.4 … i hope SAP will release it soon.

    Regards
    Stefan

    (0) 

Leave a Reply