000038082 - Recommended Oracle or Proactive Bundle Patch October 2017 or later Optimizer Settings in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Oct 28, 2019Last modified by RSA Customer Support Employee on Aug 27, 2020
Version 17Show Document
  • 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 or Oracle with the October 2017 or later Proactive Bundle Patch (BP)
IssueRSA Identity Governance & Lifecycle 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.

Starting in Oracle and the Oracle 2017 Proactive Bundle Patch (BP) for Oracle, optimizer_adaptive_features has been deprecated and replaced with two new optimizer settings:
  • optimizer_adaptive_plans
  • optimizer_adaptive_statistics
The purpose of this RSA Knowledge Base Article is to explain how to configure these optimizer settings to assist the new optimizer in choosing an optimal SQL execution plan and prevent performance degradation due to a poorly constructed execution plan. This RSA Knowledge Base Article is written specifically for Oracle database versions and October 2017 Proactive Bundle Patch (BP) or later.


  • RSA Identity Governance & Lifecycle does not distribute Oracle 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.
  • Although Oracle is supported for use with RSA Identity Governance & Lifecycle, it has not been qualified (certified) for use with RSA Identity Governance & Lifecycle 7.1.1 or 7.2.0.
  • This RSA Knowledge Base Article is specific to Oracle and not (Oracle 18c).

TasksThese settings apply to Oracle version or Oracle with the October 2017 Proactive BP or later 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 RSA Identity Governance & Lifecycle Platform Datasheet and Support Matrix for your version of RSA Identity Governance & Lifecycle.

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=''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 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

NotesIf you are planning to upgrade to Oracle 19c, please see related RSA Knowledge Base Article 000039034 -- Recommended Oracle 19c Optimizer Settings for RSA Identity Governance & Lifecycle.