Consistent Performance for 10g Upgrades to 11g

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.