000039034 - Recommended Oracle 19c Optimizer Settings for RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Jun 23, 2020Last modified by RSA Customer Support Employee on Aug 27, 2020
Version 5Show Document
  • View in full screen mode

Article Content

Article Number000039034
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.2.0
Database: Oracle 19c
 
IssueRSA Identity Governance & Lifecycle requires certain optimizer settings be set in the Oracle database for optimal query performance. The required optimizer settings differ per Oracle database version. The purpose of this RSA Knowledge Base Article is to explain how to configure the Oracle optimizer settings for Oracle 19c to assist the Oracle 19c optimizer in choosing an optimal SQL execution plan and prevent performance degradation due to a poorly constructed execution plan. The database parameters that need to be set in Oracle 19c are:
  • optimizer_adaptive_plans
  • optimizer_adaptive_statistics
  • cursor_sharing
  • optimizer_index_cost_adj
  • optimizer_index_caching
The database parameters that need to be reset to their default values are:

  • optimizer_features_enable
  • compatible
NOTES:


 
TasksThese settings apply to Oracle version 19c. To validate the database version that you are on, as avuser, run the following SQL query.

select * from product_component_version;

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 sid='*';
alter system set optimizer_adaptive_statistics=FALSE scope=spfile sid='*';
alter system set optimizer_index_cost_adj=30 scope=both sid='*';
alter system set optimizer_index_caching=50 scope=both sid='*';
alter system set cursor_sharing=force scope=spfile sid='*';
alter system reset optimizer_features_enable scope=spfile sid='*';
alter system reset compatible scope=spfile sid='*';


NOTES:

  • The recommended Oracle 12.2.0.1 optimizer_features_enable parameter and the compatible parameter (if set) need to be reset to their default values.
  • Parameter settings with scope=both will take effect immediately. All other parameters require a database restart to go into effect.

Attachments

    Outcomes