000038082 - Recommended optimizer_adaptive_* database parameter settings in versions 7.1.1 and 7.2.0 of RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Oct 28, 2019•Last modified by RSA Customer Support Employee on May 22, 2020
Version 12Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000038082
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.1.1, 7.2.0
Database: Oracle 12.2.0.1 or Oracle 12.1.0.2 with the October 2017 or later Proactive Bundle Patch (BP)
 
IssueThe RSA Identity Governance & Lifecycle product requires certain optimizer settings be set in the Oracle database for optimal query performance. These optimizer settings are documented in the RSA Identity Governance & Lifecycle Database Setup and Management Guide. One of these required settings is optimizer_adaptive_features, a setting RSA Identity Governance & Lifecycle requires be disabled.

Oracle 12.2 has introduced two new optimizer settings that replace the optimizer_adaptive_features parameter. In addition, Oracle has bundled these new parameters into their Oracle 2017 Proactive Bundle Patch (PB) for Oracle 12.1.0.2.

The information in this RSA Knowledge Base Article applies to those customers on Oracle 12.2.0.1 and those customers who are on 12.1.0.2 and have installed the October 2017 Proactive Bundle Patch (BP).

Please note that RSA Identity Governance & Lifecycle does not distribute Oracle 12.2.0.1 nor does it distribute Oracle Proactive BPs as part of the Appliance Updater. The Appliance Updater has Oracle Critical Patch Updates (CPUs) and Patch Set Updates (PSUs) only. Therefore, if you are on an RSA Supplied database, you will not have these settings and this article does not apply to your environment. 


 The new optimizer settings are:

  • optimizer_adaptive_plans
  • optimizer_adaptive_statistics
Defining these changes as described in the below Resolution section will assist the new optimizer in choosing an optimal SQL execution plan and prevent performance degradation due to a poorly constructed execution plan.
 
TasksThese settings apply to Oracle version 12.2 or Oracle 12.1 with the October 2017 Proactive BP installed. To validate the database version that you are on, as avuser, run the following SQL query.

select * from product_component_version;


The output will look similar to the following:


User-added image



For more information on supported database versions, please see the product matrix for your version.




WARNING:
RSA Identity Governance & Lifecycle 7.1.1 and 7.2.0 is certified on Oracle 12.2.0.1 only. RSA Identity Governance & Lifecycle is not currently certified on Oracle 12.2.0.2 (18c) or Oracle 12.2.0.3 (19c). Therefore the parameter requirements discussed in this article are specific to RSA Identity Governance & Lifecycle 7.1.1 on Oracle 12.2.0.1 or Oracle 12.1.0.2 with the Oracle 2017 Proactive Bundle Pack (BP).



To determine the current settings for the new optimizer parameters, execute the following as user SYS:


show parameter optimizer_adaptive_plans;
show parameter optimizer_adaptive_statistics;
show parameter optimizer_features_enable;


The output should look like this:

show parameter optimizer_adaptive_plans;
NAME                             TYPE             VALUE
-----------------------------------------------------------
optimizer_adaptive_plans         boolean          FALSE

show parameter optimizer_adaptive_statistics;
NAME                             TYPE             VALUE
-----------------------------------------------------------
optimizer_adaptive_statistics    boolean          FALSE

show parameter optimizer_features_enable;
NAME                             TYPE              VALUE
-----------------------------------------------------------
optimizer_features_enable         string            12.1.0.2


If the output differs from the above, please follow the steps in the Resolution section to set these values as per RSA recommendations.
 
ResolutionAs user SYS, execute the following SQL to define these settings for optimal use with RSA Identity Governance & Lifecycle.
 

alter system set optimizer_adaptive_plans=FALSE scope=spfile;
alter system set optimizer_adaptive_statistics=FALSE scope=spfile;
alter system set optimizer_features_enable='12.1.0.2'scope=both;



The last parameter will take effect immediately (scope-both) but the first two will require a database restart to go into effect.


NOTE: If you are still on Oracle 12.1 with no Oracle Proactive Bundle Patches installed, then you do not have these new parameters. In this case, we require the optimizer_adaptive_features parameter be set to false as per the Database Setup and Management Guide for your RSA Identity Governance & Lifecycle version. To determine the current value for this feature:

As user SYS:




show parameter optimizer_adaptive_features;


If this returns a value of TRUE, then:




alter system set optimizer_adaptive_features=FALSE scope=both;

 The output should like this:


show parameter optimizer_adaptive_features;
NAME                             TYPE             VALUE
-----------------------------------------------------------
optimizer_adaptive_features      boolean          FALSE


 

Attachments

    Outcomes