Before most customers can take advantage of Database In-Memory they will need to navigate the tricky terrain of a database upgrade. One of the most challenging aspects of an upgrade is figuring out how to minimize performance regressions due to execution plan changes.

And if that wasn’t enough to handle, the introduction of Database In-Memory into a 12c environment has the potential to change even more of the execution plans. So, what should you do?

First and foremost, you need to focus on getting to Oracle Database 12c with the same or better performing execution plans before introducing Database In-Memory into the mix. In order to achieve this goal you will need to arm yourself with two things; a copy of your existing optimizer statistics and a set of your existing (hopefully performant) execution plans.

Capturing existing optimizer statistics

You want to change as little as possible during the upgrade in order to make it easier to diagnose any changes that may occur. Since statistics have the biggest impact on the optimizer it is advisable for them to stay constant during the upgrade. You should continue use to use your 11g statistics until your 12c system performance is stable.

The best way to ensure you have your 11g statistics is to keep a backup. So before the upgrade export a complete set of Optimizer statistics into a statistics or stats table using DBMS_STATS package.

Step 1 Create the stats table

BEGIN
dbms_stats.Create_stat_table(‘SYS’, ‘MY_STATS_TAB’);
END;
/

Step 2 Export the statistics for your critical schemas and select a stats_id to make it easy to identify your 11g statistics

BEGIN
dbms_stats.Export_schema_stats(‘sh’, ‘MY_STATS_TAB’, ’11g_stats’);
END;
/

Note: If you’re not planning on doing an in-place upgrade, you will have to move the statistics to the Oracle Database 12c system by exporting the stats table and importing it into the 12c database. Then use the DBMS_STATS.EXPORT_SCHEMA_STATS procedure to populate your 11g statistics into the data dictionary.

Capturing existing execution plans

As we discussed in our previous post, SQL Plan Management (SPM) is your best safety net to ensure execution plans do not change and it should definitely be employed during an upgrade to help maintain stable performance.

There a number of different ways you can capture your existing 11g plans in SPM, all of which are described in detail in capture section of the SPM whitepaper.

Note: If you are not planning on doing an in-place upgrade you will have to move the SQL plan baselines to the Oracle Database 12c system by exporting them via a staging table using the DBMS_SPM package. Again you will find details on how to do this in the SPM whitepaper.

After the upgrade

Immediate after the upgrade you should be using your 11g plans and statistics to achieve the same executions plans you had before. Any new plans found by the 12c optimizer will be automatically captured in your existing SQL plan baselines but won’t be used until they have been verified to perform better than the 11g plan.

But what should we do about gathering statistics and introducing Database In-Memory?

You should start with statistics, since the optimizer needs a good, representative set of statistics in order to determine the correct execution, including in-memory plans.

Since we have SPM in place, gathering a new set of statistics poses no threat to our system performance. Any new plans found after the statistics gather, won’t be used until they are verified to perform better than the 11g plan. We strongly recommend you follow Oracle’s best practices for gathering optimizer statistics.

We also recommend that you run an SPM evolve task at this point, to ensure you are using the best execution plan for every statement.

Once you are comfortable you have stable and acceptable performance in your 12c environment, you can introduce Database In-Memory by following the guidelines outlined in our previous post.

Hopefully this approach will make you feel more comfortable about upgrading to Oracle Database 12c to take advantage of  Database In-Memory.

Original publish date: July 24, 2015