What Should You Do With SQL Plan Directive Changes in 12.1?

Last week we looked at SQL Plan Directives, what they are, when do they get created, how they are used and maintained. Need an intro to SQL Plan Directives? Check out last week’s post: What Are SQL Plan Directives?

In theory, the idea of using/having SQL Plan Directives is great! The story is different in real life!

Adaptive Features were introduced in 12c. The two components that make up Adaptive Features are : Adaptive Statistics (SQL Plan Directives) and Adaptive Plans.
In 12.1 both these features were controlled by the init.ora parameter OPTIMIZER_ADAPTIVE_FEATURES, and were turned on by default.
In 12.2, the two features are controlled by separate parameters:

• Adaptive Stats are controlled by the init.ora parameter OPTIMIZER_ADAPTIVE_STATISTICS, and is FALSE/OFF by default.
• Adaptive Plans are controlled by the init.ora parameter OPTIMIZER_ADAPTIVE_PLAN, and is TRUE/ON by default.
• The original parameter, introduced in 12.1, is obsoleted: OPTIMIZER_ADAPTIVE_FEATURES.

Why Do SQL Plan Directives cause problems in 12.1?

As we have seen above, and also from your experience with 12c (if you have any), in 12.1, SQL Plan directives where enabled by default.
This feature caused lots of execution plan instability, with plans constantly changing.

What does this mean to performance in 12.1?

With each run of SQL Statements, SQL Plan Directives are created (if applicable, and most likely they are applicable). Most of these directives have instructions to gather dynamic stats the next time the statement runs, or the next time a similar statement runs.

Why is this not a good idea?

At the next execution of the statement, during parsing, before the final execution plan is determined, the optimizer will gather dynamic stats, based on the instructions of the SQL Plan Directives.

This should help with a choosing a better execution plan, right? Not really, this is why:

• first the parse time for the SQL Statement is increasing.
• second, there is lots of CPU time spent on gathering dynamic stats. Out of curiosity, have a look at how much dynamic sampling is going on in your database.
• third, the execution plans keep changing, therefore there is no plan stability, and performance of your system is not stable. You (the DBA) get called a lot.

What should you do, if you are on 12.1?

If you are on 12.1 already, and have no performance problems, then you are in the minority! Keep reading on, and by the time you finish the Oracle docs, you will be convinced that even if no performance problems exist, you still should apply the patch.

If you do have performance problems, (and let’s be honest, who doesn’t), then you should keep reading and check out Oracle’s recommendations.

What does Oracle recommend we do?

Oracle made 12.1 behave like 12.2, when it comes to Adaptive Features.

This behavioral change was introduced in the October 2017 Bundle Patch, and any new Bundle Patches that have been released since then. At the writing of this post, the January 2018 is the latest one.

As per Oracle, the adaptive feature changes in 12.2 are available in 12.1 through a pair of fixes:

Bug 22652097 splits the parameter OPTIMIZER_ADAPTIVE_FEATURES into two (OPTIMIZER_ADAPTIVE_STATISTICS and OPTIMIZER_ADAPTIVE_PLANS) disables adaptive statistics.
Bug 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STAT_EXTENSIONS is set to ON.

These fixes provided in the bundle patch, are not enabled by default. The Read-Me file and the documents I am referring to below, include detailed steps on how to enable these fixes.

What should you do now that you are aware of this problem and fix?

• 1. Head to Oracle’s support site, and review the following Doc ID 2289719.1: ALERT: Oracle Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0 (Doc ID 2289719.1)
• 2. This Note will send you to the following document, which explains in detail all you need to know, and how to apply the patch and enable the fixes:
  Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance)(Doc ID 2312911.1)
• 3. Deploy the Bundle Patch in your test environment, and run performance test to confirm it is all good.
• 4. If you think you are done testing, TEST some more, review execution plan changes, elapsed times and so on.
• 5. If all is good, then and only then, proceed to production deployment.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana