Best Practices to Tune EIM Jobs.

Hello Everyone,

One of the common problem reported by Data load teams -"My EIM job is running very slow due to database slow response!". Following are the common mistake made by users which lead to these problems-
1- Inefficient database Stats.
2- Improper maintenence of Base Tables.
3- Choice of inappropriate indexes.
4- Unoptimized IFB file.
5- Running huge volumes of data loads without considering hardware and software capabilities.

If you check all the above reasons before initiating EIM jobs, you have eliminated the risk of gettting into performance issues.

After working on different environment from development to production, I have seen these issues and gathered a set of best practices which can help you to run your EIM job faster.
I have categorized the best practices from Siebel and Oracle Database perspective. These will help you to achieve better results.

Best Practices from Siebel side -

1- If there are any WF policies on any of the entity that you are loading it is ideal if you can drop the triggers with the help of the DBA and complete your load. Once the load is completed u can regenerate them again.

2- Run independent EIM jobs in parallel. EIM jobs that have no interface or base tables in common can be run in parallel.

3- Delete batches from interface tables on completion. Leaving old batches in the EIM interface table will waste space and could adversely affect performance.

4- Try different batch sizes. Often, large batch sizes are not the most efficient. Siebel Systems recommends that customers use a batch size no more than 5000 rows, preferably less than 2000 rows in a single batch.

5- Consider switching off Siebel transaction logging during the EIM run. This will definitely improve performance; however, it must be balanced with the need for mobile users to re-extract afterwards.

6- Check out if the IFB file is optimized is to check the status of the records being processed in the EIM tables. This will give an indication if there are tables or columns that are being processed that may not be necessary. The following query can be used to check the status of records in the EIM table:

select count(*), IF_ROW_STAT from where IF_ROW_BATCH_NUM = ?group by IF_ROW_STAT;

7- Fine tune the IFB file using the configuration parameters like ONLY BASE TABLES, IGNORE BASE TABLES, ONLY BASE COLUMNS, IGNORE BASE COLUMNS.

8- Disable transaction logging during EIM process run.

Best Practices from Oracle side -

1- Check that database stats are updated after data load.

2- Delete batches from interface tables on completion. Leaving old batches in the EIM interface table will waste space and could adversely affect performance.

3- Check for the table fragmentation.Prior to running EIM it is important to clean up fragmented objects, especially those that will be used during the EIM processing. The following SQL statement can be used to identify objects with greater than 10 extents:

SELECT segment_name,segment_type,tablespace_name,extentsFROM dba_segmentsWHERE 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 defaults, triggers, etc. on the objects

4- When purging data from the EIM table, use the TRUNCATE command as opposed to DELETE. The TRUNCATE command will release the data blocks and reset the high water mark while DELETE will not, thus causing additional blocks to be read during processing. Also, be sure to drop and re-create the indexes on the EIM table to release the empty blocks.

5- When dealing with huge volume of data upload, we can have index strategy in place to make the process faster. This will impact the other part of application who are accessing the involved object. So we need to be careful before we think about it. Example of Index strategy is mentioned below-

Target Table Indexing Strategy: For the target base table (e.g. S_ORG_EXT) we would only require the Primary Index ("Px" for example P1) and the Unique Indexes ("Ux" for example U1) and drop the remaining indexes for the duration of the EIM import. It has often been found that the "Fx" and "Mx" indexes can be dropped after an in depth SQL analysis of sample EIM runs.
Non-target Table indexing Strategy: For the child tables (e.g. S_ADDR_ORG) we would only require the Primary Index ("Px"), the Unique Indexes ("Ux") and the Foreign Key Indexes (needed for setting Primary Foreign keys in the parent). It has often been found that the "Fx" and "Mx" indexes can be dropped after an in depth SQL analysis of sample EIM runs.

Thanks