Reasons Behind Collecting SQL Baselines Before an 11G Upgrade
Jeff Smith, via Twitter, asked a very good question about my previous post regarding baseline plans when upgrading from 10g to 11g, (or for previous versions, which I can also go into the steps in how to ensure more consistent performance during upgrades from earlier versions as well to 11g…)
“…this assumes that new 11g plans wouldn’t be ‘better’..right? Are people gun-shy b/c 9-10 upgrade issues?”
Why would we want to go back to 10g performance when we have just upgraded to a beautiful new version of 11g?
Many responded in the Twitter conversation in regards to insurance and this is a solid reason, as we do like to know, as DBA’s, that we have a fallback plan. To go into the scenario deeper though, let’s investigate some of the first steps of an 11g upgrade.
Identified parameter steps in upgrade process:
1. Identify non-default parameters.
2. Identify undocumented parameters.
3. REMOVE both and return to the DEFAULTS.
4. During upgrade, 11g database wide parameters are implemented.
As most DBA’s and Developers are aware, a parameter change is a database WIDE change. This is opposing a hint, outline or SQL Profile which only influences the one statement at hand or statistics that will impact the statements involving the objects updated.
Noting what the 11g upgrade requests of the DBA, (which is quite different from previous upgrades of Oracle)- RETURN to the defaults. Where as we may have documented what parameters we possessed in a database that were not default, but would also (hopefully) justify why they had been changed or implemented in the first place.
Reverting the changes and/or removing any changes previous to the environment can impact the performance greatly. To have the opportunity to retain the performance baselines, available to the DBA if required to ensure we have one less thing to address post the upgrade is a life-saver of a feature.
This really doesn’t go into the realm of “Are 10g plans better than 11g?” but granting the DBA a way to return to similar performance post an upgrade due to the changes that are part of the upgrade process at the database parameter level.
Thanks to Jeff for asking this valuable question!