Performance Tuning Guide for Siebel CRM Application on Oracle

Part I - Cost-Based Optimization Guide

An Oracle Technical White Paper March 2010

Authors: James Qiu, Paul Blokhin, Mehdi Gerami

Table of Contents
Document Overview .......................................................................................................... 3 Siebel CRM Versions and Supported Oracle Database Versions...................... 4 Transitioning from RBO to CBO ............................................................................ 5 Transitioning from Oracle CBO 9i to Oracle CBO 10g.................................. 5 Cost Based Optimizer Statistics.................................................................................... 6 Collecting Optimizer Statistics .............................................................................. 6 Deleting Optimizer Statistics ................................................................................. 7 Locking Optimizer Statistics ................................................................................... 7 System Optimizer Statistics ................................................................................... 8 Know When to Create a Stored Outline .................................................................... 8 Fix the root cause, not the symptom! ............................................................... 8 Dropping Outlines ...................................................................................................... 9 Peeking of User Binds in Siebel CRM .......................................................................... 9 Double Hard Parse in Siebel 7.7/7.8 ................................................................ 10 No Bind Peeking in Siebel 8.0/8.1 ..................................................................... 10 Single Hard Parse in Siebel 8.1.1 ...................................................................... 10 Environment Variable SIEBEL_ORA_BIND_PEEK ........................................ 11 Recommendations for Customers Affected by Issues Related to Peeking of User Binds ........................................................................................................................... 11 Hidden parameter _optim_peek_user_binds ................................................ 12 Preparing and Administrating the Siebel Database for CBO ........................... 13 Default Database Parameters for Oracle 9i and 10g ................................. 13 Recommended Database Parameters for Oracle 9i and 10g .................. 13 Hidden Parameters in Oracle 10G ..................................................................... 16 Parameter OPTIMIZER_INDEX_COST_ADJ .................................................... 17 Recommended Database Parameters for Oracle 11g ................................ 18 Hard-coded Session Parameters for Siebel OM Sessions ......................... 18 Bibliography and Useful References ......................................................................... 19 Oracle Corporation Documentation ................................................................... 19 Siebel Product Defects Affecting Performance on Oracle RDBMS ................. 20 Oracle Product Defects Affecting Siebel .................................................................. 21 Oracle 9i Product Defects...................................................................................... 21 Oracle 10g Product Defects .................................................................................. 22 Oracle 11g Product Defects .................................................................................. 22

2
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Document Overview
The purpose of this document is to publish a formal recommendation for Siebel CRM configuration and performance tuning on the Oracle database platform. Siebel CRM performance tuning is a complex area. There is no one size fits all solution. A well-tuned out-of-box Siebel CRM configuration may need to be further enhanced for most implementations given the different data volume, the data shape, the transaction mix, and the customizations, etc. Multiple documents and recommendations exist on this subject from different sources, which are either too general or too specific. Many times those documents contradict each other, and even deviate from what Siebel CRM performance experts recommend. This document is an attempt to reconcile all documents published in the past. It provides concise guidelines on key areas that affect performance of Siebel CRM application deployed on the Oracle database platform. It defines a set of basic rules to implement for Siebel CRM configuration and performance tuning on the Oracle database platform. If such rules are meticulously deployed, it should lay down a solid foundation for further performance tuning. This document should be used in conjunction with Siebel CRM Troubleshooting Guide, which is currently being developed.

3
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Siebel CRM Versions and Supported Oracle Database Versions

The table below represents supported combinations of Siebel CRM and Oracle RDBMS versions.
Siebel CRM Version Oracle RDBMS Product Oracle RDBMS Version Optimizer Type INIT.ORA parameter OPTIMIZER_MODE RULE

Siebel 7.7

Oracle 8i Enterprise Server

Siebel 7.7

Oracle 9i Enterprise Server Oracle 10g Enterprise/Standard Server Oracle 11g Enterprise/Standard Server Oracle 9i Enterprise Server Oracle 10g Enterprise/Standard Server Oracle 11g Enterprise/Standard Server Oracle 10g Enterprise/Standard Server Oracle 11g Enterprise/Standard Server Oracle 10g Enterprise/Standard Server Oracle 11g Enterprise/Standard Server

Siebel 7.7

8.1.7.4 + p1744093 or above for Unix 8.1.7.4.6 or above for Windows 9.2.0.4 or above and within the 9.2.0.x series 10.1.0.3 or above, Including 10gR2 10.1.0.3 or above, Including 10gR2 9.2.0.4 or above and within the 9.2.0.x series 10.1.0.3 or above, Including 10gR2 11.1.0.6 or above

RBO

CBO

CHOOSE

CBO

ALL_ROWS

Siebel 7.7

CBO

ALL_ROWS

Siebel 7.8

CBO

CHOOSE

Siebel 7.8

CBO

ALL_ROWS

Siebel 7.8

CBO

ALL_ROWS

Siebel 8.0

10.2.0.2 or above

CBO

ALL_ROWS

Siebel 8.0

11.1.0.6 or above

CBO

ALL_ROWS

Siebel 8.1

Siebel 8.1

10.2.0.2 or above (with Oracle 11g client) 11.1.0.6 or above

CBO

ALL_ROWS

CBO

ALL_ROWS

With the exception of Siebel CRM version 7.7 on Oracle 8i Enterprise Server, all the above listed Siebel CRM versions can only be configured to use the Cost Based Optimizer (CBO). This differs from the recommended configuration for pre-Siebel 7.7 CRM versions, which utilized the Rule Based Optimizer (RBO) on Oracle 8i Enterprise Server. In the vast majority of cases, performance of the Siebel application with properly implemented and configured CBO will meet or exceed RBO performance, ceteris paribus (i.e. under similar architecture/network topologies, hardware configurations, data

4
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

volumes/distributions, storage characteristics, etc.). Detailed discussion of the RuleBased Optimizer (RBO) is beyond the scope of this document. The following provides general guidelines for RBO to CBO migration.

Transitioning from RBO to CBO

At the time of transitioning from RBO to CBO a few problems must be addressed to avoid Siebel CRM application performance degradation. This primarily pertains to specific tuning due to customizations implemented in addition to Siebel OOTB configuration and tuning: RBO-specific tuning must be withdrawn. The list below shows some examples of such RBO-specific tuning: o Customized single-column indices that worked best for the RBO need to be dropped for CBO to avoid generation of sub-optimal query plans in CBO context o Hard-coded predicates embedded into the SQL for the purpose of forcing RBO to use a certain index need to be removed Environment needs to be re-configured with the parameters and settings recommended for CBO (More information about recommendation on environment configuration and methods of statistics collection can be found below in this document) CBO-specific tuning may need to be put into place as necessary. Primary focus should be on the customer extensions and a limited number of cases when Siebel CRM default configuration may require additional tuning with respect to customer environment specifics. Transition from RBO to CBO is a one-time effort that may take from few days to few weeks, depending on the level of application customization, data volume, data shape, etc.

Transitioning from Oracle CBO 9i to Oracle CBO 10g

Under normal circumstances, migration from Oracle CBO version 9i to later versions of Oracle CBO (i.e. 10g or 11g) should not cause Siebel CRM application performance degradation if the following recommendations are deployed. There is a possibility of encountering some isolated cases of performance regression, but all such occurrences must be studied and resolved on a case-by-case basis. Otherwise, almost any major degradation caused by migration from Oracle 9i CBO to later versions falls in one or more of the following categories: Siebel CRM application has not been tuned to work well with Oracle 9i CBO in the first place. Level of Siebel CRM application customization is very high, and additional tuning may be required to address customer-specific configurations In general, transition from Oracle CBO version 9i to later versions of Oracle CBO is a one-time effort of much lesser scope than the migration from Oracle RBO to the CBO. However, customers who upgrade from Oracle 9i CBO to a later version of Oracle CBO in

5
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

parallel with deployment of a customer application release may need to allocate some additional development time for performance tuning.

Cost Based Optimizer Statistics

It is important to understand that Oracle CBO bases its decision on data statistics, and if statistics have not been collected for a given data object, Oracle CBO reverts to rulebased logic to determine the best path for access. Proper collection of statistics for the data objects is a key element in generation of optimal query execution plans.

Collecting Optimizer Statistics

Although Oracle provides alternative ways for collecting optimizer statistics, the recommended approach is to use a PL/SQL package DBMS_STATS to collect the optimizer statistics for each Siebel CRM data table, including indexed columns, indexes, and histograms for all indexed columns:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ( ownname => '<table owner>' , tabname => '<table name>' , method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254' , granularity => 'ALL' , cascade => TRUE , degree => DBMS_STATS.DEFAULT_DEGREE );
It is critical to note that we do not recommend collecting histograms statistics for all columns. Such histograms statistics can be created only for tables with highly skewed data where some values have a disproportional number of rows. Unnecessary introduction of histograms in the statistics collection is one of the most common mistakes. We strongly recommend that the optimizer statistics histograms be created using 'for all indexed columns size 254' method (please see the syntax in the above example). However, there are a few exceptions to this rule. Some tables in Siebel CRM schema are known to have skewed data, and collection of all column statistics histograms may lead to generation of better query plans. Below is the list of such tables, along with the recommended method and syntax to create the optimizer statistics:
Siebel Table Name S_POSTN_CON S_ORG_BU S_ORG_GROUP Oracle Optimizer Statistics Collection Method FOR ALL COLUMNS SIZE 254 FOR ALL COLUMNS SIZE 254 FOR ALL COLUMNS SIZE 254

6
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

One needs to benchmark the application performance to make sure that full column statistics histograms do help in their environment.

Deleting Optimizer Statistics

PL/SQL package DBMS_STATS provides functionality to delete the optimizer statistics collected for a given index, or a table (including statistics and histograms for all columns and indexes associated with the table). The following example illustrates how to delete the optimizer statistics collected for tables:

EXECUTE DBMS_STATS.DELETE_TABLE_STATS ( ownname => '<table owner>' , tabname => '<table name>' );
There are a few cases when we recommend deleting optimizer statistics collected for Siebel CRM data tables: We recommend using 'for all indexed columns size 254' to generate statistics before any query tuning. If the statistics were created differently, it is necessary to delete them1 and recreate a new set of the optimizer statistics using 'for all indexed columns size 254' method before starting the query tuning.

Locking Optimizer Statistics

Beginning with Oracle 10gR2, PL/SQL package DBMS_STATS is providing functionality to lock statistics on a table or schema. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. We only recommend locking statistics for tables with 15 rows or less, and only after statistics for such tables have been dropped. Thus, the DBA will not have to explicitly drop statistics for such tables every time they do statistics collection. (Note that on Oracle 9i statistics for tables that have only 15 rows or less must be dropped explicitly every time).

Regenerating statistics using 'for all indexed columns size 254' may not clean-up entirely the previous statistics. Therefore, the statistics must be deleted first to make sure there are no residuals.

7
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

System Optimizer Statistics

In 9i, Oracle introduced cpu_costing, a mechanism that allows the CPU cost of an operation to be included as part of the overall estimate. This feature is enabled in 9i only if you collect system statistics. This feature in 10g is enabled by default. Oracle Database 10g gathers two types of system statistics - statistics captured without a workload (noworkload) and statistics captured with a workload. In addition to CPU cost, disk I/O and memory utilization are also captured, to ensure that CBO picks the plan with the lowest estimated cost relative to other plans. Many Oracle DBAs attempt to leverage system optimizer statistics for further tuning of Siebel CRM application. Such actions may cause unpredictable degradation in performance of certain Siebel CRM application views and queries, and are strongly discouraged. We recommend that DBAs refrain from collecting system optimizer statistics, or altering any Oracle default settings pertaining to systems statistics.

Know When to Create a Stored Outline

A Stored Outline allows preserving and maintaining an execution plan (captured with certain conditions/hints) for a query performing slower than expected without such an outline. At runtime, CBO is forced to use this plan2, regardless of changes in the environment configuration, or associated statistics. Detailed discussion on usage of a Stored Outline is beyond the scope of this document. Our main goal is to provide guidance on when to use a Stored Outline while tuning Siebel CRM application performance.

Fix the root cause, not the symptom!

As a general rule, we do not consider Stored Outlines to be a viable solution for Siebel CRM application performance issues. We insist that Stored Outlines not to be used during Siebel CRM application development and performance testing o tuning. If such Stored Outlines exist, they should be dropped to expose all performance problems that need to be properly fixed. In rare cases, when a product defect is confirmed or implementing a configuration fix to address a performance problem in a production system is not feasible, Oracle/Siebel Tech Support Engineer may instruct you to use a Stored Outline. However, such a Stored Outline is temporary and must be dropped as soon as a permanent fix is available.

In certain cases, CBO ignores the Outline if it cannot use it. For example, if an index that the Outline is supposed to use is dropped the CBO will ignore the Outline and generates a new query plan.

8
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Stored Outlines may cause performance degradations after the Oracle database upgrade since each database version may retire a set of database parameters that affect the query plan preserved by the Stored Outlines. It is also important to understand that although in Oracle 11g release the Stored Outlines continue to function as in past releases, support for Stored Outlines will be deprecated in future releases of Oracle database. Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines. However, the recommendation remains the same no matter what tool is used: Fix the root cause, not the symptom!

Dropping Outlines
To comply with the above recommendation, it may be necessary to identify the Stored Outlines that exist in the environment, check if the outlines have been used, and understand their intended usage. [USER|ALL|DBA]_OUTLINES view should be used to display information about existing outlines:

SELECT name, category, used FROM user_outlines;

Stored Outlines that were not created upon instruction from Oracle/Siebel Tech Support must be removed through meticulous implementation of guidelines in this document. However, there are complex cases that might need assistance from Oracle/Siebel Application Expert Services (ES) for more advanced CBO tuning. The Stored Outlines can be dropped by using a PL/SQL package DBMS_OUTLN. For example:

BEGIN DBMS_OUTLN.drop_by_cat (cat => '< category >'); END /

Peeking of User Binds in Siebel CRM

In Siebel CRM application, Siebel Object Manager (OM) may generate a SQL statement with the syntax of :n in the WHERE clause. The :n is placeholder for values to be replaced at run-time. At run-time, Siebel OM substitutes the real values for the placeholder(s) to seek a better query plan. This process is called peeking of user binds or simply bind peek.

select LAST_NAME from S_CONTACT where TIMEZONE_ID=:n;

On a hard parse, Oracle will peek the value provided for :n, and optimize the query as if the same query was submitted with this particular literal value. This allows the CBO to

9
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

leverage optimizer statistics and generate the most efficient query plan for given value. Disabling of bind peeking, or inaccurate statistics, or excessive histogram statistics may equally result in generation of suboptimal query execution plans, and noticeable degradation in Siebel CRM application views response times.

Double Hard Parse in Siebel 7.7/7.8

In Siebel CRM application server prior to version 8.0, bind peeking was implemented through a hard-coded call to Oracle Call Interface (OCI), issued by Siebel Object Manager (OM). However, it was discovered that such implementation was causing double hard parsing, both during describe (expected behavior), and during execute (unexpected behavior).

No Bind Peeking in Siebel 8.0/8.1

Double hard parsing caused long cold time in Oracle CBO. To address the issue, bind peeking has been removed completely in Siebel CRM version 8.0. This implementation was back ported to Siebel 7.7/7.8 via QuickFix and FixPack delivery vehicles, as shown in the matrix below:
Siebel CRM Version Siebel 7.7 Siebel 7.8 Siebel 8.0 Siebel 8.1 Bind Peek with Double Hard Parse All 7.7.x versions prior to 7.7.2.4 QF All 7.8.x versions prior to 7.8.2.3 QF None None No Bind Peek 7.7.2.4 QF and after, prior to 7.7.2.12 FP 7.8.2.3 QF and after, prior to 7.8.2.12 FP All versions prior to 8.0.0.6 FP All versions

However, no-bind-peek implementation caused performance degradation in some Siebel CRM application views for customers who already tuned their application using bind peeking with double hard parse.

Single Hard Parse in Siebel 8.1.1

In Siebel 8.1.1 a solution was devised to enable bind variable peeking using single hard parse. This solution was intended to resolve all issues related to peeking of user binds in Siebel CRM application, and it was ported to earlier versions of Siebel as part of 8.0.0.6 FP, 7.8.2.12 FP, and 7.7.2.12 FP. But after this solution was released, it has been established that it tends to cause query execution issues, which resulted in severe performance regression for some customers.

10
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Environment Variable SIEBEL_ORA_BIND_PEEK

As mentioned earlier, removal of bind peeking in Siebel 8.0 caused application performance issues for customers who tuned their application using bind peeking with double hard parse. To address such issues, a switch was implemented by the means of OS environment variable SIEBEL_ORA_BIND_PEEK. This variable allows enabling or disabling bind peeking at customer discretion. 3 Default value for SIEBEL_ORA_BIND_PEEK is FALSE, and must be set to TRUE to turn on the bind peeking.

Recommendations for Customers Affected by Issues Related to Peeking of User Binds

Prior to the release of this document, our recommendation was to install a corresponding FP or QF (depending on the base Siebel CRM version), which implements bind peeking using single hard parse. However, due to recent findings of performance regression issues in this implementation, this recommendation has been withdrawn. Our current recommendation for Siebel CRM customers affected by issues related to peeking of user binds is to deploy a generally available FixPack or QuickFix which corresponds to their base application version, and leverage the environment variable SIEBEL_ORA_BIND_PEEK to enable/disable bind peeking. Customers who tuned their Siebel CRM application with double hard parse and bind peeking should set SIEBEL_ORA_BIND_PEEK to TRUE. Customers who tuned their Siebel CRM application without bind peeking must verify that SIEBEL_ORA_BIND_PEEK variable is set to FALSE. Below table contains the list of Siebel versions that support SIEBEL_ORA_BIND_PEEK functionality:

It is important to understand that variable SIEBEL_ORA_BIND_PEEK does not address the original issue of double hard parse with bind peeking. It is designed to eliminate performance issues for Siebel CRM customers who have already tuned their application with the double hard parse.

11
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Base Siebel CRM Version

Siebel CRM Versions with SIEBEL_ORA_BIND_PEEK support

Siebel 7.7 Siebel 7.8

7.7.2.12 Fix Pack Build2 [18391] 7.8.2.10[19241]QF0A52 7.8.2.11[19244]QF0B09 7.8.2.13 Fix Pack 8.0.0.6 [20423]QF0637 8.0.0.6 Quick Fix 26xx Branch 8.0.0.7 [20426]QF0707 8.0.0.7 [20426]QF1703 8.0.0.7 Quick Fix 17xx Branch 8.0.0.8 Fix Pack Build2[20428] 8.0.0.9 Fix Pack 8.1.1 [21112] QF0023 8.1.1.2 Fix Pack BLD_DEN_11_APPS_SIA

Siebel 8.0

Siebel 8.1 Siebel 8.2

Hidden parameter _optim_peek_user_binds

This parameter was introduced in Oracle 9.2.0, and is maintained in all consequent versions of Oracle 9i, 10g and 11g Enterprise/Standard server. As name suggests, this parameter enables peeking of user binds. This is an internal Oracle parameter; it is not documented, and should not be used unless instructed to do so. There are some articles in the knowledge base (e.g., Doc ID 532787.1), which suggest that setting _optim_peek_user_binds to FALSE may improve performance of Siebel CRM upgrade process. We do not have enough data either to prove or to disapprove this recommendation. However, it is certain that setting of _optim_peek_user_binds parameter has no effect on Siebel CRM application server runtime. Internal OCI calls issued by Siebel application server cannot be altered, and are not governed by the _optim_peek_user_binds parameter settings. Our recommendation regarding _optim_peek_user_binds is NOT to alter the default settings for this parameter in environments running Siebel CRM applications.

12
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Preparing and Administrating the Siebel Database for CBO

For Oracle 10g and 11g, OPTIMIZER_MODE should be set to ALL_ROWS which is the default value. For Oracle 9i, OPTIMIZER_MODE should be set to CHOOSE unless you are performing an upgrade. Besides setting the OPTIMIZER_MODE to CHOOSE or ALL_ROWS, about 60 other parameters affect the generation of the query plan. The most important ones are listed and explained below. The others, including hidden parameters that are not documented, should not be modified unless instructed by Oracle/Siebel Tech Support or Engineering.

Default Database Parameters for Oracle 9i and 10g

The following visible parameters must be set to their default values where applicable: Oracle Parameter HASH_JOIN_ENABLED OPTIMIZER_FEATURES_ENABLE OPTIMIZER_INDEX_CACHING OPTIMIZER_MODE PARTITION_VIEW_ENABLED QUERY_REWRITE_INTEGRITY STAR_TRANSFORMATION_ENABLED 9i Default Value True 9.2.0 0 CHOOSE False Enforced False 10g Default Value ** Obsolete 10.1.0 * 0 ALL_ROWS ** Obsolete Enforced False

It is always a good idea to set the OPTIMIZER_FEATURES_ENABLE = <Oracle DB patch version being used> to get full benefit of a series of optimizer feature supported by Oracle DB version. Hence, if your db version is already 10.2.x, then you can set the OPTIMIZER_FEATURES_ENABLE=10.2.x.

Recommended Database Parameters for Oracle 9i and 10g

In addition, for the Siebel application to work effectively and efficiently, the following parameters need to be set to the recommended values other than default values. Oracle Parameter Description 9i 10g Recomm Default Default ended Value Value Value 1 2 1

OPTIMIZER_DYNAMIC_SAMPLING This parameter controls the level of dynamic sampling performed by the optimizer. OPTIMIZER_MAX_PERMUTATIONS This parameter restricts the number of permutations of the tables the optimizer will consider in queries with joins. A higher value result in
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

2000

** Obsolete

100

13

higher parse time, a lower value carries the risk of overlooking a good plan. NOTE: Starting with Oracle Database 10g, this parameter has been made obsolete. Refer to the Oracle documentation for further information. OPTIMIZER_INDEX_COST_ADJ Controls the access path 100 100 1 selection to be more or less index friendly. QUERY_REWRITE_ENABLED Enable or disable query False True False rewriting globally for the database (**) Do not automatically alter the values of these parameters, which have been deprecated to hidden parameters in 10g, based on the recommendations for visible parameters in version 9i. The parameters below should be set according to the information documented in the following Siebel Bookshelf references: Siebel Installation Guide for Microsoft Windows Version 8.0 > Configuring the RDBMS > Configuring an Oracle Database for Siebel Applications > Guidelines for Configuring Settings in the init.ora File. Siebel Installation Guide for UNIX Version 8.0 > Configuring the RDBMS > Configuring an Oracle Database for Siebel Applications > Guidelines for Configuring Settings in the init.ora File. Siebel Installation Guide for Microsoft Windows: Servers, Mobile Web Clients, Tools Version 7.8 > Configuring the RDBMS > Configuring Oracle for Siebel Applications > Guidelines for Configuring Settings in the init.ora File. Siebel Installation Guide for UNIX: Servers, Mobile Web Clients, Tools Version 7.8 > Configuring the RDBMS > Configuring Oracle for Siebel Applications > Guidelines for Configuring Settings in the init.ora File. Siebel Installation Guide for Microsoft Windows: Servers, Mobile Web Clients, Tools Version 7.7 > Guidelines for Configuring the RDBMS > Configuring Oracle for Siebel Applications. Siebel Installation Guide for UNIX: Servers, Mobile Web Clients, Tools Version 7.7 > Guidelines for Configuring the RDBMS > Configuring Oracle for Siebel Applications. Recommended Value DB_FILE_MULTIBLOCK_READ_COUNT The database buffer cache For most implementations, parameter dictates the this value should be set number of data blocks read between 16 and 32 blocks in a single Oracle I/O and adjusted as necessary. operation during a table You may want to set an scan. initial value of 32. SORT_AREA_SIZE This value is specified in Set bytes, and may be adjusted PGA_AGGREGATE_TARGET according to the number of instead. When not setting users, the amount of RAM PGA_AGGREGATE_TARGET available, and the size of - You should start with an Oracle Parameter Description

14
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

sorted queries.

SORT_AREA_RETAINED_SIZE

Specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory.

initial value of 1,000,000 (1 MB). Optimal performance can be achieved for the Dictionary Managed TEMP tablespaces if the extents are uniform and a multiple of the SORT_AREA_SIZE. Set PGA_AGGREGATE_TARGET instead. When not setting PGA_AGGREGATE_TARGET - Derived from SORT_AREA_SIZE.

The following related parameters should be set by a trained DBA and according to the information in the applicable Oracle database documentation and requirements for your implementation. General guidance on these related settings is provided below: Oracle Parameter PGA_AGGREGATE_TARGET Description Specifies the target aggregate PGA memory available to all server processes attached to the instance. General Guidance For most production implementations with a substantial number of users, this value should be set to 1 GB or greater. Monitor PGA target advisory views for additional guidance. All the advisories in Oracle9i TYPICAL in production and Release 2 including the ALL in TEST environments. Buffer Cache Advisor are controlled by a newly introduced parameter STATISTICS_LEVEL. Relevant to parallel Set execution operations and to PGA_AGGREGATE_TARGET the query portion of DML or instead. When not setting DDL statements. It specifies PGA_AGGREGATE_TARGET the maximum amount of - Derived: 2 * memory, in bytes, to be SORT_AREA_SIZE used for hash joins. Specifies the policy for If sizing work areas. This PGA_AGGREGATE_TARGET parameter controls the is set, then AUTO. If mode in which working PGA_AGGREGATE_TARGET areas are tuned. is not set, then MANUAL.

STATISTICS_LEVEL

HASH_AREA_SIZE

WORKAREA_SIZE_POLICY

15
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Hidden Parameters in Oracle 10G

Hidden parameters are internal Oracle database parameters, which should not be modified unless instructed to do so by Oracle Support. Below table represents the list of hidden Oracle database parameters which need to be set to benefit Siebel CRM application performance Oracle Parameter _always_semi_join Description Recommended Value

Always use this OFF method for semi- In some cases, setting _always_semi_join to OFF join when possible renders better performance. This is due to Siebel OMs FIRST_ROWS_10 optimization strategy. However, it is recommended that a customer run a thorough performance testing to verify whether this setting improves performance in the Siebel environment Enable the use of bitmap plans for tables with only Btree indexes Determine how long to defer down converts for hot buffers FALSE For Siebel OLTP environment, it is recommended to set _b_tree_bitmap_plans to FALSE and _partition_view_enabled to FALSE 0 For a Siebel RAC implementation, it is recommended to set _gc_defer_time to 0. This is applicable to Oracle 9i and 10g environments

_b_tree_bitmap_plans

_gc_defer_time

_no_or_expansion

OR expansion FALSE during optimization Some customers report setting this parameter to is disabled TRUE is providing better performance. However, Siebel does not perform better consistently with this setting. It is recommended to set this parameter to FALSE 100 Starting with Oracle Database 10g, optimizer_max_permutations is an obsolete parameter. It is recommended to leave this hidden parameter setting to its default value FALSE For Siebel OLTP environment, it is recommended to set _partition_view_enabled to FALSE and _b_tree_bitmap_plans to FALSE

_optimizer_max_permuta Limit the optimizer tions maximum join permutations per query block

_partition_view_enabled

Enable/disable partitioned views

16
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Parameter OPTIMIZER_INDEX_COST_ADJ
This parameter is of critical importance for Siebel CRM application performance. Incorrect setting may result in severe performance degradation of Siebel CRM application. For Oracle 9i CBO setting OPTIMIZER_INDEX_COST_ADJ = 1 is imperative. This will set the optimizer goal for best response time (versus best throughput). Incorrect setting may cause the optimizer to favor full-table scans instead of index access. For Oracle 10g/11g CBO setting OPTIMIZER_INDEX_COST_ADJ = 1 is also recommended, although the default setting of 100 will deliver good results in most cases.

It is important to understand that in-house tuning of Siebel CRM application was performed with OPTIMIZER_INDEX_COST_ADJ = 1 setting. This means that customers who want to implement OPTIMIZER_INDEX_COST_ADJ = 100 on Oracle 10g/11g will need to allocate extra development time for additional tuning that may be necessary. The need for such tuning is not considered to be an optimizer defect or a regression issue. Oracle Engineering will only review cases when it is confirmed that a specific query performance is equally unacceptable both under OPTIMIZER_INDEX_COST_ADJ = 1 and OPTIMIZER_INDEX_COST_ADJ = 100 settings. In all other cases, additional tuning should be provided on-site, or OPTIMIZER_INDEX_COST_ADJ setting should be reverted to 1. Under no circumstances OPTIMIZER_INDEX_COST_ADJ parameter should be set to values other than 1 (Siebel recommended) or 100 (Oracle default on 10g/11g).

17
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Recommended Database Parameters for Oracle 11g

Oracle 11g introduces Automatic Memory Management (AMM) feature, which allows managing both the SGA memory and the instance PGA memory completely automatically. Detailed discussion on the AMM feature is beyond the scope of this document. We will only provide our generic guideline to Siebel CRM customers on Oracle 11g. Our recommendation to Siebel CRM customers on Oracle 11g is to switch to the Automatic Memory Management. There are two new initialization parameters introduced in Oracle 11g: MEMORY_TARGET and MEMORY_MAX_TARGET. We recommend setting the MEMORY_TARGET parameter to the value which equals 60% of total physical memory on the database server. We do not recommend explicitly setting MEMORY_MAX_TARGET. This parameter is optional, and if not set, Oracle will automatically set it to the value of MEMORY_TARGET. In addition to switching to the Automatic Memory Management, Siebel CRM customers on Oracle 11g should follow all recommendations provided in this document for Oracle 10g.

Hard-coded Session Parameters for Siebel OM Sessions

In Siebel 7.7/7.8 on Oracle 9i platform, Siebel database connector has been modified to make a few alter session statements for OLTP operations. Same functionality has later been built into Siebel database connectors for Oracle 10g and 11g. Thus, in all current Siebel CRM versions (7.7, 7.8, 8.0, 8.1 and 8.1.1) each Siebel OM session will automatically set up the following session parameters: ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10 When optimizer_mode is set to first_rows_n, the optimizer will in all cases use cost-based optimization, and set the optimizer goal for best response time (versus best throughput). ALTER SESSION SET HASH_JOIN_ENABLED = FALSE (In Oracle 10g, this parameter is deprecated, Siebel uses _hash_join_enabled instead) ALTER SESSION SET _OPTIMIZER_SORTMERGE_JOIN_ENABLED = FALSE ALTER SESSION SET _OPTIMIZER_JOIN_SEL_SANITY_CHECK = TRUE This is done to enable sanity checks for join using two columns.

The above session parameters should not be altered to other values by any means.

18
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Bibliography and Useful References

This document is partially based on Siebel/Oracle bulletins and white papers published in the past, with our special thanks to the authors and contributors: Oracle CBO and Siebel Business Applications (Doc ID 478028.1) by Selma Takara, Principal Technical Support Engineer, Oracle Corp Siebel CRM with Oracle Cost-Based Optimizer (CBO) by Dillip Kumar Praharaj, CMTS, Oracle Corp Anna Leyderman, Development Manager, Oracle Corp Kavitha Raghunathan, SMTS, Oracle Corp Tuning Oracle Cost Based Optimizer (CBO) for Siebel OLTP CRM Applications by Dominic Stewart Principal Sales Consultant, Oracle Corp Troubleshooting Guide for Siebel Performance after Upgrade to Oracle 10G, by Caroline Wanyonyi, Principal Architecture Specialist, Oracle Corp However, it is important to mention that the current document should be considered as the ultimate reference. The recommendations in this document take precedence over all other documents on this subject if any other document is advising otherwise.

Oracle Corporation Documentation

Oracle 9i Database Performance Tuning Guide and Reference, Release 2 (9.2). Oracle9i Supplied PL/SQL Packages and Types Reference. Oracle 10g Database Performance Tuning Guide and Reference, Release 2 (10.2) Oracle10g Supplied PL/SQL Packages and Types Reference

19
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Siebel Product Defects Affecting Performance on Oracle RDBMS

Siebel CR 12-R5WXCB Description Siebel 8.0 client sets session parameter HASH_JOIN_ENABLED, which is deprecated in Oracle 11g. Note: This problem was corrected for the Siebel 8.0 client on the Oracle 10g platform. Note: This problem does not exist for the Siebel 8.1.1 client on Oracle 11g platform. There is a bug in the DBConn, which does not recognize Oracle 11g banner and will cause performance problem. Resolution/FR Targets Fixed in: 8008 FixPack 8005 QuickFix

12-1TK6KQ9

12-1TK6KQU (Aspen) 12-1TK9A1L (8.0.0.8 FP) 12-1UI7Y8T (7.7.2.10 QF) 12-1UIA88O (7.7.2.12 FP) 12-1UIA89R (7.8.2.x FP) 12-1UIA8AQ (8.1.1.2 FP) 12-1V14HVD (7.7.2.8 QF)

20
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Oracle Product Defects Affecting Siebel

Oracle 9i Product Defects
Oracle Bug/Note Bug 3756797 Bug 3239873 Bug 3737955 Bug 3406977 Description High CPU may be used during parse of certain query forms High CPU during parse of statements with many identical predicates Long parse times for long inlists / many AND/OR terms. HIGH VERSION COUNT DUE TO BINDS MARKED AS NONDATA WITH CURSOR_SHARING=FORCE. EXCESSIVE HIGH VERSION COUNT WHEN CURSOR_SHARING=SIMILAR. HIGH PARSE TIME WITH USE_CONCAT AND INLISTS AT EMPTY TABLE. CBO may produce a suboptimal plan in FIRST_ROWS_N mode due to incorrect cardinalities being used. Incorrect cardinality estimates may occur for LIKE predicates resulting in poor execution plans. Statistics on empty tables (probably in conjunction with FIRST_ROWS_N). Source of Alert 1162. Suboptimal plan possible for FIRST_ROWS for SQL with ORDER BY and indexes. ORDER BY sort may not be eliminated in First_rows_NNN. Suboptimal plan possible in FIRST_ROWS_N mode. Long parse times for many ORed predicates with FIRST_ROW_XX optimization. Optimizer may not choose best index. Ora-00600 [Kkojcio] on a query Fixed in 9.2.0.7 (or patch) Fixed in 9.2.0.6 Resolution

Bug 3321724 Bug 3898338 Bug 3075139

Not fixed Fixed in 10.2 Fixed in 9.2.0.5

Bug 2991526

Fixed in 9.2.0.5

Bug 4275247

Internal only.

Bug 3380026 Bug 3220073 Bug 3075139 Bug 3018146 Bug 3566843 Bug 4335559

Fixed in 9.2.0.6 Fixed in 9.2.0.5 Fixed in 9.2.0.5. Fixed in 9.2.0.5 Fixed in 9.2.0.7 Fixed in 9.2.0.6

21
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Oracle 10g Product Defects

Oracle Bug/Note 4878299 Description Bad plan from join with FIRST_ROWS_N or a ROWNUM predicate ORDER BY elimination may not find best plan FIRST_ROWS_K recost for ORDER BY elimination may not find best plan Suboptimal plan possible as bind peeking not used during CBQT CBO does not prorates chained_cnt under first_rows_k INTERMITENT WRONG RESULTS ON 9iR2,10gR1 and 10gR2 Increased Parse Time Resolution Patch in 10.2.0.4 (Server Patch) Fixed in 11.1.0.6 (Base Release) Fixed in 11.1.0.8 Related to Bug 5288623 Fixed in 11.1.0.8 Fixed in 11.1.0.8 Fixed in 10.2.0.4 Fixed in 10.2.0.4

7430474

6990305 7236148 Note 406966.1 Note 4724074.8 Bug 6455161 Bug 5240607 Note 4878299.8 Bug 7891471

Higher "cache buffer chains" latch gets / higher "consistent gets" after truncate/Rebuild FIRST_K_ROWS may choose inefficient NESTED LOOPS join Bad plan from join with FIRST_ROWS_N or a ROWNUM predicate Query with ORDER BY has a bad plan with FIRST_ROWS_10 optimization in 10.2.0.4 "cache buffer chains" latch contention from concurrent index range scans in extreme situations, excessive CPU consumption without any user-SQL activity.

Fixed in 10.2.0.4 Fixed in 10.2.0.4 Fixed in 10.2.0.4

Fixed in 10.2.0.5

Bug 4742607

Fixed in 10.2.0.3

Oracle 11g Product Defects

Oracle Bug/Note 8839301 Description On Oracle 11.2.0.0.2 only: ALTER TABLE command to add a CHAR (1 char) column creates such column with length 4. Resolution To be fixed in the next distributed version

22
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide

Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide December 2008 Authors: James Qiu, Paul Blokhin, Mehdi Gerami Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. Worldwide Inquiries: Phone: +1.650.506.7000 Fax: +1.650.506.7200 oracle.com Copyright 2008, Oracle. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

23
Performance Tuning Guide for Siebel CRM Application on Oracle Part I - Cost-Based Optimization Guide