New Slides – and a discussion about Dictionary Statistics

First of all we have just upoaded a new version of the Upgrade and Migration Workshop slides with some added information. So please feel free to download them from here.The slides have one new interesting information which lead to a discussion I’ve had in the past days with a very large customer regarding their upgrades – and internally on the mailing list targeting an EBS database upgrade from Oracle 10.2 to Oracle 11.2.

Why are we creating dictionary statistics during upgrade?

I’d believe this forced dictionary statistics creation got introduced with the desupport of the Rule Based Optimizer in Oracle 10g. The goal: as RBO is not supported anymore we have to make sure that the data dictionary has fresh and non-stale statistics. Actually that would have led in Oracle 9i to strange behaviour in some databases – so in Oracle 9i this was strongly disrecommended.

The upgrade scripts got hardcoded to create these stats. But during tests we had the following findings:

It’s important to create dictionary statistics the night before the upgrade. Not two weeks before, not 60 minutes before your downtime begins. But very close to the upgrade. From Oracle 10g onwards you’d just say:

$ execute DBMS_STATS.GATHER_DICTIONARY_STATS;

This is important to make sure you have fresh dictionary statistics during upgrade for performance reasons. Tests have shown that running an upgrade without valid dictionary statistics might slow down the whole upgrade by factors of 2x-3x.

And it would be also a great idea post upgrade to create again fresh dictionary statistics when you’ve did suppress the stats creation during the upgrade process. Suppress? Yes, you could set this underscore parameter in the init.ora:

_optim_dict_stats_at_db_cr_upg=FALSE

to suppress the forced dictionary statistics collection during an upgrade. We believe strongly that (a) people using the default statistics creation process which will create dictionary statistics by default and (b) create fresh stats before upgrade on the dictionary. Therefore we find it save once you have followed our advice to use the underscore during upgrade. And we’ve taken out that forced statistics collection during upgrade in the next release of the database. Please note: If you are using the DBUA for the upgrade it will remove underscore parameters for the upgrade run to improve performance – which is generally a good idea. So you’ll have to start the DBUA with that call:

$ dbua -initParam “_optim_dict_stats_at_cb_cr_upg”=FALSE

-Mike

5 thoughts on “New Slides – and a discussion about Dictionary Statistics

  1. Sorry but I’ll have to disagree – both links work.
    Could you please try it again?

    Thx and sorry for any inconvenience
    Mike

  2. Mike,

    Does this mean that running the DBUA with _optim_dict_stats_at_db_cr_upg=FALSE will reduce the upgrade time as we are collecting the dict stats as part of the upgrade ?

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *