Recommended Oracle 19c Optimizer Settings for RSA Identity Governance & Lifecycle
3 years ago
Originally Published: 2020-06-22
Article Number
000064709
Applies To
RSA Product Set: RSA Identity Governance & Lifecycle
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.2.0
Database: Oracle 19c
 
Issue
RSA 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:
 
Tasks
These 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.
 
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 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.

Related Articles

Trending Articles

Don't see what you're looking for?