Oracle Parameters for Siebel on Oracle Database

Oracle Parameters on Siebel Deployments

Siebel performance and scalability recent benchmarks show that setting of certain parameters previously recommended are no longer needed. These benchmarks are done on Siebel out-the-box applications; however, it is possible that due to certain customizations and data shape the Siebel performance might not be optimal. For this reason and other factors, Oracle highly recommends the use of SPM to maintain Siebel customers’ applications performance in production.

The following are the recommended Oracle parameters on Siebel deployments:

Oracle Parameter	Recommended Value for 12c and previous versions	Recommended Value for 19c Database and future releases	Comment
OPTIMIZER_FEATURES_ENABLE	12.x.x.x	19.x.x.x	See the remark below
OPTIMIZER_ADAPTIVE_PLANS	FALSE	FALSE	Important particularly if SPM is not used
OPTIMIZER_ADAPTIVE_REPORTING_ONLY	TRUE	TRUE	Recommended
_OPTIMIZER_ADAPTIVE_CURSOR_SHARING	FALSE	FALSE	Important
_OPTIMIZER_EXTENDED_CURSOR_SHARING	NONE	NONE	Important
_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL	NONE	NONE	Important
_SQL_PLAN_DIRECTIVE_MGMT_CONTROL	0	0	Important
_OPTIMIZER_DSDIR_USAGE_CONTROL	0	0	Important
OPTIMIZER_INDEX_COST_ADJ	1	1	Important
OPTIMIZER_DYNAMIC_SAMPLING	1	1	or => 2 if SPM is used
OPEN_CURSORS	> 2000 - 5000	> 2000 - 5000	Important This parameter defines the limit, so setting it to recommended high value of 5,000 will not consume resources if not used. However, this high limit will prevent errors in Siebel deployments if more than 2000 are needed.
STATISTICS_LEVEL	TYPICAL	TYPICAL	Important
SESSION_CACHED_CURSORS	>= 50 (D) - 200	>= 50 (D) - 200	Important
_GC_DEFER_TIME	0 (for RAC implementation)	0 (for RAC implementation)	Important for RAC deployment
_LIKE_WITH_BIND_AS_EQUALITY	TRUE	TRUE	Optional
SGA_TARGET			See AWR SGA Target Advisory
SHARED_POOL_SIZE			See AWR Shared Pool Advisory
PGA_AGGREGATE_TARGET			See AWR PGA Memory Advisory

Oracle Setting for Siebel Upgrades

During Siebel Upgrades, there are scenarios where a massive number of updates or inserts must be performed on Siebel tables. This is particularly relevant when upgrading from pre-Siebel Innovation Pack 2016 to Innovation Pack Siebel 2016 or later.

Enabling Parallel DML for Siebel Upgrades

For these specific cases, the Parallel DML feature can be enabled to improve upgrade processing time:

ALTER SESSION FORCE PARALLEL DML;

Using Parallel DML can significantly reduce processing time.

Considerations and Limitations

Oracle enforces specific rules for executing Parallel DML, meaning that only a limited set of upgrade scenarios are eligible for parallel execution. Hence, very few cases are candidate for parallelism of DML statements for current Siebel Updates.

Database administrators should carefully evaluate when to apply Parallel DML based on the specific Siebel Upgrade workload and Oracle’s parallel execution requirements.

The Optimizer Features Parameter

It is recommended that this parameter be set to the Oracle version in use to benefit from the new or enhanced Optimizer features.

In special cases when SPM SQL Baseline isn'tused and upgrade to most recent Oracle DB version is causing poor performance and can't be remedied with SQL Profile of other tuning options, the OPTIMIZER_FEATURES_ENABLE can temporarily be set to 11.2.0.4 to potentially resolve the performance issues.

However, it's highly recommended to set this parameter back to the Oracle 19c Database and future releases version in use once the issues are addressed with tuning options presented in this document.

These performance issues arise when the upgrade to most recent Oracle DB version hasn't been thoroughly tested in performance testing environment with full production database copy. It is strongly recommended that performance testing is done before going live. To simulate the production workload, Oracle Real Application Testing (RAT) can be used to ensure that the potential upgrade issues are addressed in a performance test environment before the final Oracle latest version upgrade of the Siebel production. RAT high-level features:

Providing the capability of testing the current production load on the new Oracle release including the new features, new HW platform, etc.
Enabling the Database Replay’s Workload Capture traces all the current production Oracle database activities including the batch and user transactions including the ad hoc queries writing them in a capture file.

The Optimizer Adaptive Feature (Adaptive Query Optimization)

Starting with Oracle 12.2 in Enterprise Edition, the Optimizer Adaptive feature is managed by 2 parameters:

optimizer_adaptive_plans is set to TRUE by default
optimizer_adaptive_statistics is set to FALSE by default

Note: Siebel deployments have shown to perform better and remain more stable when both parameters are set to FALSE.

To adhere to this recommendation and have full control over the SQL execution plans, it's highly recommended to use SQL Plan Management.

To benefit from enhancements in 19c and future versions if SPM is used (recommended), the optimizer_adaptive_plans can be set to TRUE (D), so Optimizer can generate SQL plans that could be superior to the current one (please see the SPM section above).

However, the recommended option is to start with OPTIMIZER_ADAPTIVE_REPORTING_ONLY setting it to TRUE to review and evaluate the new Optimizer generated plans.

Adaptive Cursor Sharing (ACS)

In order to achieve more SQL execution plan stability, number of Siebel customers disable the ACS. Our recent investigations of several customers running into SQL plans stabilities confirm as well that these settings will provide important improvements and bring overall consistence performance to Siebel deployments. Therefore, it's highly recommended to test if the following settings help your Siebel deployment to attain more plan stability.

_optimizer_adaptive_cursor_sharing = FALSE
_optimizer_extended_cursor_sharing = NONE
_optimizer_extended_cursor_sharing_rel = NONE

Oracle Optional Parameters for Siebel on Oracle Database

The marking of parameters as "Optional" is based on our performance and scalability testing. However, they can be kept if your performance testing shows that they're necessary for your Siebel application due to data shape and customizations.

Oracle Parameter	Recommended Value for 12c and previous versions	Recommended Value for 19c Database and future releases	Comment
OPTIMIZER_INDEX_CACHING	0 (D)	0 (D)	Optional
OPTIMIZER_MODE	ALL_ROWS (D)	ALL_ROWS (D)	Optional
QUERY_REWRITE_INTEGRITY	ENFORCED (D)	ENFORCED (D)	Optional
STAR_TRANSFORMATION_ENABLED	FALSE	FALSE	Optional
CURSOR_SHARING	EXACT (D)	EXACT (D)	Optional
QUERY_REWRITE_ENABLED	FALSE	FALSE	Optional
_ALWAYS_SEMI_JOIN	OFF	OFF	Optional
_B_TREE_BITMAP_PLANS	FALSE	FALSE	Optional
_PARTITION_VIEW_ENABLED	FALSE	FALSE	Optional
_NO_OR_EXPANSION	FALSE	FALSE	Optional
_OPTIMIZER_MAX_PERMUTATIONS	100	100	Optional

Oracle Parameters for Siebel on Oracle Autonomous Database

Siebel is certified on Oracle Autonomous Transaction Processing – Dedicated Infrastructure (ADB-D), starting with Siebel 2021.1 release requiring Oracle ADB-D 19.9.

The autonomous features of Oracle ADB automate many database tuning and maintenance tasks, Hence, none of the parameters above will be used for Siebel deployments on ADB-D allowing ADB to take the actions autonomously to best support the workload and maintenance tasks.

Exadata Recommended Database Parameters

All the database parameters mentioned above are also recommended (including RAC parameters) for Exadata. There are no additional parameters required.

Important: For Siebel deployments, EXADATA option must not be used for gathering system statistics. If this option has been used the system statistics must be deleted and regenerated without this option:

exec dbms_stats.gather_system_stats();

For more information, refer: Gathering Optimizer Statistics

For configuring Oracle on Exadata as Siebel database, it is highly recommended that you review the following technical briefs:

Note: Running Oracle Database on Exadata can't replace all existing Siebel database indexes. Siebel database indexes can be made invisible for the purpose of testing; however, they can't be dropped unless it has been approved by Oracle ACS through an official review process, and this should only be done in very specific circumstances, and very sparingly.