A comment on my previous post indicated that it might help if I clarify the behavior of the adaptive optimizer settings in the Oracle 220.127.116.11 bundle patches (from October 2017 onwards).
MOS Note 2312911.1 and Mike Dietrich's blog tell you what you need to know to get the changes up and running. Nevertheless, from the questions I receive, I know that some of you like to dig a bit deeper and take a closer look at what's going on under the hood. If that's you, then I'm going to go over what you can expect to see. If you just want to know what optimizer adaptive parameters to set (with or without the new optimizer behavior enabled) then you can jump to the bullet list at the end of this post.
The Oracle 18.104.22.168 October 2017 and January 2017 BPs include some changes to the adaptive optimizer. It is worth knowing that Oracle has a strict policy precluding inclusion of most optimizer fixes from BPs. The policy allows you to apply BPs without changing SQL execution plans, and it is why the adaptive changes are not active by default (although there are exceptions covered shortly). Check out MOS Note: 1962125.1 – Overview of Database Patch Delivery Methods if you want to know more about patching policies. Note that the policy for patch inclusion and activation for Windows is different, so I'm covering activation for non-Windows systems in this post. The difference is covered in the MOS note and another blog from Mike.
Here I'm going to concentrate on patch for bug 22652097. It is included in the BP and allows you to control adaptive features in the same way as Oracle Database 12c Release 2 (by adding the database parameters optimizer_adaptive_plans and optimizer_adaptive_statistics). The adaptive optimizer changes included in in the BP are not active by default unless you've previously applied a matching patch. For example, if you applied 22652097 before the October BP, the BP will detect this and keep the changes active. In this way, you can continue to use the new parameters post-BP without interruption.
From this point on, I will assume that patch 22652097 was not applied before the BP. This means that the adaptive changes will not be active by default. Note also that I'm going to ignore the optimizer_adaptive_reporting_only parameter here because it is not relevant to this discussion.
Once the BP is applied, the new adaptive optimizer behavior can be enabled using ‘fix control’ 26664361 (Mike Dietrich’s blog post shows you how). The first thing I want to clarify is that you will see two new database parameters even if you have not enabled the adaptive feature changes with fix control 26664361:
SQL> show parameter optimizer_adaptive NAME TYPE VALUE ------------------------------------ ----------- ------- ... optimizer_adaptive_plans boolean TRUE optimizer_adaptive_statistics boolean TRUE ...
These parameters are intended to replace optimizer_adaptive_features but only if you choose to use them. They will be TRUE or FALSE depending on whether optimizer_adaptive_features is TRUE or FALSE, but they are benign if you don’t enable them and don't set them explicitly. In other words, it is safe to apply the BP, leave the fix control unset and then continue to use optimizer_adaptive_features (OAF) in the same was as before. The behavior remains the same and we obey the BP policy.
If you set fix control 26664361 to activate patch 22652097, the database will acquire the Oracle Database 12c Release 2 defaults. This is what you will see if you have not set any optimizer_adaptive_* parameters:
SQL> show parameter optimizer_adaptive NAME TYPE VALUE ------------------------------------ ----------- ------- ... optimizer_adaptive_plans boolean TRUE optimizer_adaptive_statistics boolean FALSE ...
You should be aware of a potentially confusing issue here. The parameter values will be reported incorrectly if you start a database instance and use the same session to view them. Here is an example of what you will see if you have not set any optimizer_adaptive_* parameters:
SQL> startup ...don't disconnect here... SQL> show parameter optimizer_adaptive NAME TYPE VALUE --------------------------------- ----------- ------- ... optimizer_adaptive_plans boolean TRUE optimizer_adaptive_statistics boolean TRUE [instead of FALSE] ...
The correct values are shown if you exit the startup session, reconnect, and then use show parameter.
What happens if you have not activated the adaptive changes with _fix_control but go ahead and set the new parameters anyway? The database will not stop you from doing this but it is not recommended. Setting the new parameters explicitly will override both optimizer_adaptive_features and the fix control settings. For example, if optimizer_adaptive_feature is FALSE and optimizer_adaptive_statistics is set to TRUE, the database will assume that you want to use adaptive statistics even if you have not set the fix control. Combining the old and the new approach is confusing and it is even possible to configure illogical combinations of functionality. So, assuming that you did not install 22652097 before the BP, this is the advice:
If you did install 22652097 before the BP, then I’m assuming here that you will be using the new parameters already.
**Note - For both the October and January BP the intention was to allow you to leave optimizer_adaptive_statistics unset and it would take the default value FALSE. However, leaving this parameter unset enables adaptive statistics in some deployments even though the parameter will show the default value of FALSE. This issue only occurs if the _fix_control is set to enable the new 12.2 parameter settings. This is unpublished bug 27626925 and our intention is to include it in BPs ASAP. You can avoid the issue by explicitly setting optimizer_adaptive_statistics in the pfile/spfile rather than leaving it to default.