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