EIM Performance Tuning for Dummies

A quick look at the various strategies available for tuning EIM on Oracle.

EIM is the mechanism used to load large data volumes into a Siebel database.

EIM can also be used for ongoing data loads where data is processed (typically from an external system) to create, update, merge or delete records.

Data quality

EIM can load data fast. However, if the data is missing key fields from the source system, the entire data load will have to be repeated. Exhaustive, thorough checking, testing and validation of all data will have significant benefits. EIM performance degrades in direct proportion to the number of rejected records.

Consider using a staging table with multiple check constraints to validate the data and enforce integrity as much as possible.

Testing

Test, test, check and double check the load procedure. Check that data is actually visible in the Siebel application. Failure to populate a single primary column may mean data will not be visible to the end user.

Profiling

Analyse the SQL statements issued by EIM Process and the performance profile. The IFB parameter SQLPROFILE may be specified to generate a detailed breakdown of each EIM step together with the most resource intensive SQL statements.

Gathering statspack snapshots at the start/end of EIM processing may be used to report on general Oracle performance metrics.

Disable Transaction Logging

Siebel supports mobile web clients (remote users who work in a discinnected mode and synchronise with the Siebel server). For large scale data loads,the Siebel System Preference 'Docking: Transaction Logging' should be set to 'FALSE'. When the data load is complete, mobile web clients should be extracted.

Tune the IFB

ONLY BASE COLUMNS
The parameters 'ONLY BASE TABLES and, in particular, 'ONLY BASE COLUMNS' limit the amount of processing done by EIM. In addition, the resulting IFB documents the data mappings.

DEFAULT/FIXED COLUMNS
The EIM parameter 'DEFAULT COLUMN' and 'FIXED COLUMN' are relatively inefficient. EIM generates an UPFDATE statement for each such column for each batch processed. It is preferable to populate constant values prior to the EIM load either using SQL*Loader or updating the data.

Index hints
Two IFB parameters USE ESSENTIAL INDEX HINTS (default=TRUE) and USE INDEX HINTS (default=FALSE) should usually both be set to TRUE on Oracle. The parameters issue additional index hints to choose the appropriate indexes.

Direct path inserts
EIM also supports direct path inserts. The IFB parameter 'ORACLE INSERT APPEND MODE = TRUE' should be specified. This will add the /*+ APPEND */ hint to all insert statements.

Direct path load locks the target table. However, this isn't an issue here as the insert operation is the last part of EIM processing and is followed by a commit.

Tune the batch size
EIM processes data in batches. The size of each batch is determined by the IF_ROW_BATCH_NUM column in the EIM interface table. It is worth experimenting with batch size between 2,500 and 25,000 to determine the optimal value. There isn't a massive difference and 5,000 or 10,000 is normally a decent choice (and helps makes the sums easier).

Index monitoring
Maintaining all those indexes is expensive. Oracle provides a feature to monitor index usage. This technique can be used to enable index monitoring, execute an EIM data load. Any non-unique indexes that were not used during EIM processing may be dropped.

The necessary DDL to re-create the indexes should be captured. This allows the indexes to be reinstated when data loading is complete. Parallel index creation may be used to create the indexes quickly. The Siebel DDLSYNC utility will also recreate any missing indexes but is sequential (and relatively slow).

Tune Oracle
Siebel is an OLTP application with users connecting using a web browser. EIM is a batch application. Consequently, there is scope to reconfigure the SGA. The size of the shared pool may be reduced and the memory allocated to the buffer cache.

Pre-allocate tablespace
For large data loads, it is recommended to pre-allocate the necessary tablespace. Dynamic extent allocation is expensive and should be avoided.

Redo logs(Undo Tablespaces)
EIM generates large volumes of redo information. In addition to the actual insert's of new records into the target tables, EIM performs updates on the EIM interface tables to set and modify status values. In addition, some primary columns will also result in update statements.

Parallelism
The final step of EIM tuning should be to run multiple EIM processes in parallel. EIM is database intensive and consumes relatively few resources on the Siebel application server. With modern disk storage technologies, the limiting factor for EIM throughput should be the CPU resources on the database server.