Siebel Performance Tuning Guide > Tuning Siebel EIM > Database Guidelines for Optimizing Siebel EIM >

Oracle Database and Siebel EIM

This topic is part of Database Guidelines for Optimizing Siebel EIM.

The information that follows provides Siebel EIM tuning tips for Oracle Database.

Avoiding Excessive Table Fragmentation

Before running Siebel EIM, consult with an experienced DBA in order to evaluate the amount of space necessary to store the data to be inserted in the EIM tables and the Siebel base tables. Also, for example with Oracle Database, you can make sure that the extent sizes of those tables and indexes are defined accordingly.

Avoiding excessive extensions and keeping a small number of extents for tables and indexes is important because extent allocation and disallocation activities (such as truncate or drop commands) can be demanding on CPU resources.

To check if segment extension is occurring in Oracle Database

• Use the SQL statement that follows to identify objects with greater than 10 extents.

  NOTE:  Ten extents is not a target number for segment extensions.

  SELECT segment_name,segment_type,tablespace_name,extents
  FROM dba_segments
  WHERE owner = (Siebel table_owner)
  and extents > 10;

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

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.

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 Siebel EIM processes can be executed against an EIM table provided they all use different batches or batch ranges. The concern is that you might experience contention for locks on common objects. To run multiple jobs in parallel against the same EIM table, make sure that the FREELIST parameter is set appropriately for the tables and indexes used in the Siebel EIM processing.

NOTE:  If you are using Auto Segment Space Mgmt (ASSM) as part of defining table spaces, then the PCTUSED and FREELIST parameters (and FREELIST groups) are ignored.

Applicable database objects include EIM tables and indexes, as well as base tables and indexes. The value of the FREELIST parameter specifies the number of block IDs that will be stored in memory which are available for record insertion. Generally, you set the value 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 permits 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 makes sure 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 Database updates the wrong data due to hash keys overflow. This issue is specific to Oracle Database. To avoid this problem, use less than 255 NVL functions in the update statement.