Oracle Patchset 10.2.0.4 is released ‚Äď finally.

So after a loooong wait time, it’s finally available to be downloaded by the SAP customers.
What a relieve!

Over is the time where tens of patches needed to be installed to run the database on a supported patch level and to avoid trapping into a myriad of nasty optimizer bugs that could render the system unusable.
Hooray to the developers!

Well, ok perhaps this ‘event’ is not that fantastic.

From the support perspective, it’s just another patch.
And we already know that there will be a Patchset 10.2.0.5 in which the already available merge fixes for 10.2.0.4 will be included.
So far nothing new.

Having witnessed the advent of many Patchsets, Patches and Releases now, one customer behaviour pattern really sticks out.
During all the usually quite long (from several months to sometimes years) there are only few support messages in which customers seek to know the planned release date for the ‘new version’.
Anyhow, as soon as the expected release date gets closer, more and more requests are opened to get a definitive date.
“We’re in an upgrade/migration right now and want to include the most current patchset.”
or “Should I wait with installing the required patches until the Patchset will be available?”
are just two typical questions here.

As a support person, I can of course only deliver the officially released software versions and information.
There wouldn’t be any benefit to a customer if I would hand out some unsupported piece of software.
The same is true for unreleased information.

Let’s assume we would have an internal fixed date (believe me: there isn’t a fixed date!) on which we plan to release a piece of software.
Now, I hand this information to you (the customer or partner) and you base your planning on that information.

What can happen now are three things:

  1. The release date is matched and your plans won’t break.
    That is the wanted outcome.
  2. The release date is earlier than assumed.
    This would usually be no problem with your plans but it may turn out that there are new bugs in that released software until the date you planned to use it.
    Would you then still want to install this patchset?
  3. The release date is actually later than expected.
    Now what?
    You don’t have any official information that you can use to argue with SAP.
    Neither have you got a good explanation for your customer why you cannot apply the planned patchset as it was planned

Basically, option 1 would be the only option that would not lead to any problems for you.
And would you like to have to handle the other two outcomes?

Another aspect is that you want to be sure that the newly released piece of software is working fine and that SAP has taken all measures to ensure it is.
Now, what if a release date is announced and one day before that date a severe problem occurs?
Should SAP still deliver and hope that a fix is found for that problem before anyone notices?
That’s clearly not an option at all.

That leaves one way of handling the release of new software for the benefit of both SAP and the customers:
“Release the software when it’s ready (so far as we can now)”.

Does that mean bug-free software?
Of course not.
But it means that the software is not released while we actually know that there are bugs in it for which we don’t can tell you how to handle them.

Finally, it does not seem to make sense to try to incorporate the latest patch into your already tight schedules.
You’ll need to have a plan to patch your system anyhow – so why not perform the planning with the patches that are actually released and postpone any upcoming patches to the next maintenance window?

Ok – that wasn’t meant to be a ‘customer bashing’ blog post – so don’t be offended!
Of course, I’m happy if I can boost my productivity record by answering request for release dates ūüôā

best regards,
Lars

Oracle Patchset 10.2.0.4 ‚Äď neat DBMS_STATS enhancement

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:
………………………………………</pre>
<pre>OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
…………………………………………………………………….</pre>
<pre>/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 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