Enabling Real-Time Analytics With Database In-Memory

SQL Plan Management

Andy Rivenes
Product Manager

Have you ever upgraded something to a new version, like your phone or your laptop, and found that while most of it worked great there were just a few things that were worse or even broken? You might have decided to restore back to the previous version because the problem(s) were just too annoying or catastrophic to ignore. Well this was one of the motivations behind the release of SQL Plan Management back in Oracle Database 11g. SQL Plan Management (SPM) provides a framework for completely transparent, controlled execution plan evolution. The main idea being that your application should see no performance regressions due to execution plan changes when you upgrade or change something in your database environment.

So what does this have to with Oracle Database In-Memory? Just like a database upgrade, the addition of Oracle Database In-Memory is a big change and can introduce SQL plan changes. We've talked before on this blog about how the Optimizer in Oracle Database has been enhanced to make it aware of Oracle Database In-Memory. But let's face it, there have been a lot of changes and no software is perfect. To minimize the risk of database upgrades we routinely recommend using SPM to prevent any regressions in SQL performance and an upgrade to Oracle Database In-Memory is no different. SPM provides the ability to help insure that all of your SQL will run at least as fast as it did prior to the upgrade.

For Oracle Database In-Memory implementations we recommend that SQL plan baselines be captured prior to implementing Oracle Database In-Memory. This should be considered the application "baseline" and the performance of any SQL executions should not regress from this baseline. Ideally we would expect it to improve. The following process can be used to capture the initial baseline:

Once these SQL plan baselines have been captured, Oracle Database In-Memory can be implemented. The same workload used to create the baseline should be executed again with all objects populated into the In-Memory column store. If the optimizer finds new execution plans that take advantage of the In-Memory column store, they will be automatically added to the existing SQL plan baselines but they will not be used. The original plans captured during the baseline run will still be used. A set of evolve tasks can be run and only the new execution plans that perform better then the original baselines will be accepted.

The following shows an example of running a set of evolve tasks:

The workload can then be run a final time and should now use the best possible execution plans with no regressions. You can find additional information about SQL Plan Management in this white paper or in the Oracle Database 12c documentation.

Be the first to comment

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