Optimizer Adaptive Features Requirements for Enterprise Manager Installation

As a general rule, Oracle recommends that when you do a fresh install you also apply all Enterprise Manager one-off patches available via auto-download as well as the latest Release Updates available. Patch 30912308 is one such patch that addresses some Optimizer Adaptive Features fixes. Note that patch 30912308 is available with automatic updates and it is also part of Enterprise Manager 13.4 Release Update 1 (13.4.0.1). For a better experience, Oracle recommends applying this patch during the installation process using one of the methods below:

• Download the patch automatically during the installation process as part of the Enterprise Manager Cloud Control Installation Wizard using your My Oracle Support (MOS) credentials.

• If you are not connected to My Oracle Support, manually download and apply the Release Update1 during the installation process.

Before proceeding with either method you must first perform the following:

• If the Management Repository is using Oracle Database 12.1.0.2.0 and database patch 22652097 has not been applied, ensure that you disable the optimizer adaptive features by connecting to the database as SYSDBA and running the following:

  alter system set optimizer_adaptive_features=false scope=both;

  To verify that the changes have taken effect, run the following command:

  show parameter adaptive;

  You should see the following output:

  NAME                          TYPE         VALUE
  ---------------------------------------------------------------------
  optimizer_adaptive_features   boolean      FALSE 

• If the Management Repository is using Oracle Database 12.1.0.2 with database patch 22652097 applied, connect to the database as SYSDBA and run the following:

  alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid='*'; 
  alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='*'; 
  alter system set "_px_adaptive_dist_method" = OFF scope=both sid='*'; 
  alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='*'; 
  alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid='*'; 
  alter system set "_optimizer_use_feedback" = FALSE scope=both sid='*'; 
  alter system set "_optimizer_gather_feedback" = FALSE scope=both sid='*'; 
  alter system set "_optimizer_performance_feedback" = OFF scope=both sid='*'; 

• If the Management Repository is using Oracle Database 12.2, 18.x or 19.x, there's no need to set any parameters since they will be addressed when applying the patch 30912308 during the installation process.

  Otherwise, if you plan not to follow any of the above recommended methods to apply the patch 30912308 during the installation process then you must to connect to the database as SYSDBA and run the following:

  alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid='*';
  alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='*';
  alter system set "_px_adaptive_dist_method" = OFF scope=both sid='*';
  alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='*';
  alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid='*';
  alter system set "_optimizer_use_feedback" = FALSE scope=both sid='*';
  alter system set "_optimizer_gather_feedback" = FALSE scope=both sid='*';
  alter system set "_optimizer_performance_feedback" = OFF scope=both sid='*';

Then, perform the Enterprise Manager installation process using one of the methods described below to apply the patch 30912308:

• Let the Enterprise Manager Cloud Control Installation Wizard (Oracle Installer) download automatically the patch 30912308 using your My Oracle Support (MOS) credentials during the Enterprise Manager installation process. This is an auto download patch which requires Internet connection to MOS. For more details about this method, see step 2 from Installing an Enterprise Manager System for a Production Site (Advanced Configuration).

• Manually download and apply the bundle patch along with the Enterprise Manager installation process. It doesn't require Internet connection to MOS. For more details, see Apply Bundle Patch under step 30 from Installing an Enterprise Manager System Using Software Only Install Along With Plugins and Configuring Later.

After installing the Enterprise Manager 13c Release 4, if the Management Repository is using Oracle Database 12.2, 18.x or 19.x and you have not applied patch 30912308 via any of the two recommended methods described above during the installation process then Oracle recommends to reset the optimizer adaptive features parameters by connecting to the database as SYSDBA and running the following:

alter system reset "_optimizer_nlj_hj_adaptive_join" scope=both sid='*';
alter system reset "_optimizer_strans_adaptive_pruning" scope=both sid='*';
alter system reset "_px_adaptive_dist_method" scope=both sid='*';
alter system reset "_sql_plan_directive_mgmt_control" scope=both sid='*';
alter system reset "_optimizer_dsdir_usage_control" scope=both sid='*';
alter system reset "_optimizer_use_feedback" scope=both sid='*';
alter system reset "_optimizer_gather_feedback" scope=both sid='*';
alter system reset "_optimizer_performance_feedback" scope=both sid='*';

(Bug 25679612, 30912308)