Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > Database Optimization Tips for EIM >

Oracle Databases

This section provides EIM tuning tips for the Oracle database platform.

Fixing Table Fragmentation

Before running EIM, you should clean up fragmented objects, especially those that will be used during EIM processing. The following SQL statement can be used to identify objects with greater than 10 extents:

SELECT segment_name,segment_type,tablespace_name,extents

FROM dba_segments

WHERE owner = (Siebel table_owner)

and extents > 9;

To fix fragmentation, the objects will need to be rebuilt with appropriate storage parameters. Always be careful when rebuilding objects because of issues such as defaults or triggers on the objects.

Using the Oracle Optimizer Mode

The Oracle optimization mode can also affect EIM performance. Typically, the Siebel application has been found to perform better under rule-based optimization. While there have been cases where cost-based optimization has improved EIM performance, you should only attempt this as a last resort and you must switch back to rule-based optimization for online usage.

NOTE:  Be aware that only rule-based optimization is supported.

Optimization mode can be verified by running the following query:

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'OPTIMIZER_MODE';

NOTE:  To verify the optimization mode, you must have database administrator (DBA) privilege when using this query.

Purging an EIM Table

When purging data from an EIM table, use the TRUNCATE command as opposed to the DELETE command. The TRUNCATE command releases the data blocks and resets the high water mark while the DELETE command does not, which causes additional blocks to be read during processing. Also, be sure to drop and recreate the indexes on the EIM table to release the empty blocks.

Creating Indexes

When working with large volumes of data in EIM tables, index build time can be costly when refreshing an EIM table with a new data set. To improve the performance of the index build use the UNRECOVERABLE option (Oracle 7.3) or NOLOGGING (Oracle 8) option. This prevents the Oracle database from writing to the REDO LOG files. You can also improve index build time by creating multiple SQL scripts to create the indexes, and then by running these scripts in parallel through SQLPlus. The following section provides a sample SQL statement that demonstrates the syntax for using the UNRECOVERABLE or NOLOGGING options:

CREATE INDEX S_SAMPLE_M1 ON

S_SAMPLE (SAMPLE_ID)

TABLESPACE TS_INDX

STORAGE (INITIAL 10M NEXT 5M PCTINCREASE 0)

UNRECOVERABLE/NOLOGGING;

NOTE:  The option you choose (UNRECOVERABLE or NOLOGGING) depends on the version of the Oracle database you are using.

Disabling Archive Logging

It is recommended that Archive Logging be disabled during initial data loads. You can enable this feature to provide for point-in-time recovery after completing the data loads.

FREELIST Parameter

Multiple EIM processes can be executed against an EIM table provided they all use different batches or batch ranges. The concern is that you may experience contention for locks on common objects. To run multiple jobs in parallel against the same EIM table, you should make sure that the FREELIST parameter is set appropriately for the tables and indexes used in the EIM processing.

This includes EIM tables and indexes, as well as base tables and indexes. The value of this parameter specifies the number of block IDs that will be stored in memory which are available for record insertion. Generally, you should set this to at least half of the intended number of parallel jobs to be run against the same EIM table (for example, a FREELIST setting of 10 should permit up to 20 parallel jobs against the same EIM table).

This parameter is set at the time of object creation and the default for this parameter is 1. To check the value of this parameter for a particular object, the following query can be used:

SELECT SEGMENT_NAME, SEGMENT_TYPE, FREELISTS

FROM DBA_SEGMENTS

WHERE SEGMENT_NAME='<OBJECT NAME TO BE CHECKED>';

To change this parameter, the object must be rebuilt. Again, be careful when rebuilding objects because of issues such as defaults or triggers on the objects.

To rebuild an object

  1. Export the data from the table with the grants.
  2. Drop the table.
  3. Recreate the table with the desired FREELIST parameter.
  4. Import the data back into the table.
  5. Rebuild the indexes with the desired FREELIST parameter.

Caching Tables

Another method to improve performance is to put small tables that are frequently accessed in cache. The value of BUFFER_POOL_KEEP determines the portion of the buffer cache that will not be flushed by the LRU algorithm. This allows you to put certain tables in memory, which improves performance when accessing those tables. This also ensures that after accessing a table for the first time, it will always be kept in the memory. Otherwise, it is possible that the table will get pushed out of memory and will require disk access the next time used. Be aware that the amount of memory allocated to the keep area is subtracted from the overall buffer cache memory (defined by DB_BLOCK_BUFFERS). A good candidate for this type of operation is the S_LST_OF_VAL table. The syntax for keeping a table in the cache is as follows:

ALTER TABLE S_LST_OF_VAL CACHE;

Updating Tables

When there are 255 or more NVL functions in an update statement, Oracle updates the wrong data due to hash keys overflow. This is an Oracle-specific issue. To avoid this problem, use less than 255 NVL functions in the update statement.


 Siebel Enterprise Integration Manager Administration Guide 
 Published: 05 January 2004