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

[#1]

Article Number

000038082

Applies To

RSA 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)
 

Issue

RSA 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 12.2.0.1 and the Oracle 2017 Proactive Bundle Patch (BP) for Oracle 12.1.0.2, 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 12.2.0.1 and 12.1.0.2 October 2017 Proactive Bundle Patch (BP) or later.

NOTES:

• 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.
• Although Oracle 12.2.0.1 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 12.2.0.1 and not 12.2.0.2 (Oracle 18c).

 

Task

These settings apply to Oracle version 12.2.0.1 or Oracle 12.1.0.2 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:

Image description

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.

 

Resolution

As 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.0.2 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

 

Notes

If 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.