Siebel Server Installation Guide for UNIX > Creating the Oracle Database >

Oracle Database Configuration Guidelines

This section contains guidelines for obtaining optimum performance from an Oracle database. Refer to your Oracle technical documentation for additional information.

The init.ora file contains parameters that have a major impact on the performance of Siebel applications.

NOTE:  Use the following settings only as guidelines for your initial configuration. Your final settings will vary based on the server hardware configuration, the number of users, and the type of workload.

In the init.ora file, Oracle provides default parameter values for small, medium, and large database configurations. Unless the configuration parameters are specified in the following settings, set them to the large database values. Refer to your Oracle documentation for detailed descriptions of each of the parameters and their effects on database performance and system resource utilization.

Brief descriptions of these parameters follow:

OPTIMIZER_MODE. Select the rule-based optimizer by setting OPTIMIZER_MODE to RULE.

NOTE:  Siebel optimizes and certifies Siebel applications on a standard Oracle installation using Rule-Based Optimizer (RBO). Using advanced features like Cost-Based Optimizer (CBO) and table partitioning are not supported and could have a detrimental effect on query performance.

If there is a reason to believe that the issues are caused by CBO, table partitioning, or other database features that are not currently supported, Siebel Support will request that you reproduce issues against a standard installation using RBO.

DB_BLOCK_BUFFERS. The minimum recommended value is 10,000 blocks (assuming a block size of 8 KB). This yields 80 MB of block buffers. If significant
I/O activity occurs, you can increase this value, provided that enough RAM is available.

In a production system, it is recommended that you assign this parameter a minimum value of 256 MB. Also, your system should have a minimum of 512 MB RAM.

SHARED_POOL_SIZE. Start with a minimum value of 100 MB in your production environment. A DBA should adjust this value upward based on the available physical memory of the hardware and performance, whether connections are dedicated, or run Multi-Threaded Server (MTS) and the application type.

Siebel eBusiness Applications make heavy demands on the dictionary cache for columns. In Oracle, you cannot explicitly set the size of the column cache. Instead, column cache is set as a fixed percentage of the shared pool size. By setting a large shared pool size, you set a large column cache size.

The number of repositories active in your Siebel schema also adds to dictionary overhead since Siebel eBusiness Applications maintains a record for each column in each table for each repository. As a result, if you have six active repositories, the Siebel dictionary will be six times larger than it needs to be.

DB_FILE_MULTIBLOCK_READ_COUNT. The database buffer cache parameter dictates the number of data blocks read in a single Oracle I/O operation during a table scan.

For most implementations, this value should be set between 16 and 32 blocks and adjusted as necessary. You may want to set an initial value of 32. To reduce I/O overhead, this parameter should ideally be between 64 and 256 KB, depending on the tables undergoing scanning, the number of disk drives/devices, the tablespace structure, and other variables.

To optimize performance during upgrades, this value should be set higher than for installation or daily operation and the parameter ARCHIVE_LOGGING should be switched off.

SORT_AREA_SIZE. This value is specified in bytes, and may be adjusted according to the number of users, the amount of RAM available, and the size of sorted queries. You should start with an initial value of 2,000,000 (2 MB).

SORT_AREA_RETAINED_SIZE. When a sort operation is complete, Oracle releases the memory it used in SORT_AREA_SIZE and retains only the memory defined in this parameter. This value is specified in bytes, and determines how much sort area is retained for each user process.

Start by setting this parameter equal to SORT_AREA_SIZE. If the Siebel Database Server is running low on memory, deallocate all the sort memory after the sort by setting it to zero.

If users regularly execute implicit sort operations, such as order by, this memory does not need to be deallocated. Therefore, if the Siebel Database Server is not using virtual memory, set SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE.

On the other hand, if the Siebel Database Server is running short on memory, set SORT_AREA_RETAINED_SIZE = 0. This completely deallocates all sort memory after sort operations.

Should the sort exceed the value of SORT_AREA_SIZE, it will spill over onto the disk. For this reason, you must consider the TEMP tablespace default storage size. Allocate uniform and equal extents (set default PCTINCREAE to ZERO) in the TEMP tablespace that are the same size as SORT_AREA_SIZE, and assign the default MINEXTENTS 2. These parameter settings are recommended because if a sort operation spills over to disk, it will then use more than the SORT_AREA_SIZE in temporary space. Since Oracle will then need to allocate more extents to contain the sort operation, and Oracle performs this serially using enqueues, an already suboptimal system performance will be magnified several-fold.

CURSOR_SHARING. This parameter is set to EXACT by default and you should not change it unless directed by Siebel Technical Support.

OPEN_CURSORS. This parameter controls the amount of spaces that should be reserved for the maximum number of cursors (a cursor being the same as an open query). The minimum open cursor requirement for Oracle support is 1000 and the maximum is 2000. This parameter may be adjusted according to observed usage patterns.

NOTE:  Setting this number higher than 2000 commits more memory for the database server, thereby affecting performance. Setting it lower than 1000 can cause an error that prevents you from continuing.

DB_BLOCK_SIZE. Small block size leads to high levels of row chaining and large numbers of levels in B*tree indexes, creating serious performance problems. It is recommended that you set the block size to a minimum of 8 KB to prevent excessive row chaining and performance degradation with EIM.

NLS_SORT. The collating sequence, also called the sort order, is specified during the initial installation of your database and defines the way in which the database will sort character data.

For detailed information on Siebel sort order recommendations, see Sort Order.

You should specify the same sort order at the database client level, so that output there does not need to be resorted.

NOTE:  Customers are responsible for ensuring that their data is exported and imported correctly.

NLS_DATE_FORMAT. Set the NLS_DATE_FORMAT parameter as needed. (The default setting is DD-MON-YY.)

For information about the formats supported, consult your Oracle documentation.


 Siebel Server Installation Guide for UNIX 
 Published: 24 June 2003