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
Originally Published: 2019-10-25
Article Number
Applies To
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
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
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).
Tasks
select * from product_component_version;
The output will look similar to the following:
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
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
