X

Enabling Real-Time Analytics With Database In-Memory

What to do with optimizer statistics when upgrading to 12c to take advantage of Database In-Memory

Maria Colgan
Master Product Manager

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.

Join the discussion

Comments ( 6 )
  • guest Monday, July 27, 2015

    Hello Maria,

    As this is not explicit, can you tell us if the tuning pack and/or the diagnostics pack are required in this exercice? (including what is described inside the SPM white-paper).

    Regards


  • Maria Colgan Monday, July 27, 2015

    You don't need either the tuning pack and/or the diagnostics pack for any of the steps described in this blog post or our easily post.

    No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g.

    I believe the confusion arises from the wording in the Oracle Database Licensing documentation that says the Tuning Pack includes "Automatic Plan Evolution of SQL Plan Management". What this is actually referring to is the interaction between the SQL Tune Advisor's nightly tuning task and SPM. This interaction does require you to have a license to use the Tuning pack.

    In both Oracle Database 11g and 12c, the SQL Tuning Advisor, runs automatically during the maintenance window. This automatic SQL tuning task targets high-load SQL statements. These statements are identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots and not from SPM. If the SQL Tuning Advisor finds a better execution plan for a SQL statement it will recommend a SQL profile. Some of these high-load SQL statements may already have SQL plan baselines created for them. If a SQL profile recommendation is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted plan to the existing SQL plan baseline.

    Hope this helps,

    Maria


  • guest Tuesday, July 28, 2015

    Thank you Maria, this is very helpful!


  • Foued Wednesday, July 29, 2015

    Thank you for sharing this post


  • Roberto Tuesday, September 1, 2015

    The call to dbms_stats is wrong.

    It should be dbms_stats.create_stat_table rather than dbms_stats.create_stats_table


  • guest Tuesday, September 8, 2015

    Thanks Roberto, I've corrected the typo in the post.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.