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!

When involved in an 11g upgrade of a 10g database, one of the important goals for a DBA is to maintain the same performance in the newly upgraded environment as the previous one.

One of the best ways to accomplish this is through baseline collection of the execution plans that then can be imported and loaded directly into the SQL Plan Baseline once the database is upgraded.

The disclaimer needs to be added, you must have a license for the tuning pack, so always check your DBA_FEATURE_USAGE_STATISTICS view and ensure you are in compliance.

The steps to complete this are as follows:

1. Create your SQL set in the 10g database:

SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ’11G_PLANS_UPG’
);
END;
/

2. Collect the execution plans for a time interval that makes sense for the database usage, (i.e. OLTP vs. OLAP intervals, etc.):

SQL> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
sqlset_name => ’11G_PLANS_UPG’, -
time_limit => 240, -
repeat_interval => 5);

**Note– forgot how long this takes to run- about 5 min or so, do not be alarmed, it’s simply collecting data.

3. Once complete, check the Plan count:

SQL> select owner,name, STATEMENT_COUNT from DBA_SQLSET;

OWNER NAME STATEMENT_COUNT
—————————— —————————— —————
SYS 11G_PLANS_UPG 162

SQL> select SQL_ID,SQL_TEXT from DBA_SQLSET_STATEMENTS;

4. Create a table to hold the plans for the import post the upgrade:

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => ‘S10G_211G_TBL’
);
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => ’11G_PLANS_UPG’
,sqlset_owner => ‘SYS’
,staging_table_name => ‘S10G_211G_T’
,staging_schema_owner => ‘SYS’
);
END;
/

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => ’10G_TO_11G’
,sqlset_owner => ‘SYS’
,staging_table_name => ‘S10G_211G_TBL’
,staging_schema_owner => ‘SYS’
);
END;
/

PL/SQL procedure successfully completed.

This Completes the steps for the pre-upgrade colletion steps.

5. Post the 11g upgrade, you can implement the SQL Baselines immediately to return to the execution plans utilized in the pre-upgrade database.

The execution plans will be imported into the SQL Plan Baseline via the next step:

DECLAREmy_plans pls_integer;
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => ’10G_TO_11G’
,sqlset_owner => ‘%’
,replace => TRUE
,staging_table_name => ‘S10G_211G_TBL’);

my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => ’10G_TO_11G’
,sqlset_owner => ‘SYS’,basic_filter => ‘sql_text like ”%”’
,fixed => ‘YES’,enabled => ‘YES’
,commit_rows => 1);
END;
/

You now have the ability to guarantee more consistent performance post an upgrade from 10g to 11g without many of the worries we once had as DBA’s.