000038082 - Recommended optimizer_adaptive_* database parameter settings in RSA Identity Governance & Lifecycle 7.1.1

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

Article Content

Article Number000038082
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.1.1
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. Please click here for access to the Database Setup and Management Guide for your RSA Identity Governance & Lifecycle version. 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.


These 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 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 BP.



ResolutionAs user SYS, execute the following SQL to define these settings for optimal use with RSA Identity Governance & Lifecycle.
 

alter system set optimizer_adaptive_statistics=FALSE scope=spfile;
alter system set optimizer_adaptive_plans=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. 
 

Attachments

    Outcomes