SIEBEL EIM optimizer mode

Author Ella Zhong  Sr. technical consultant

In order to tune the performance of SIEBEL EIM loading, we always consider the following steps:

1. Maintenance of EIM tables: leaving old batches in EIM tables wastes space and can adversely affect performance.
2. Using Batch Ranges: BATCH = x-y( the maximum number of batches that you can run in an EIM process is 1000)
3. Parallel Processing
4. Transaction logging: consider disabling the enable transaction of logging system performance in the Administration – Siebel remote screen during the EIM run.
5. Trace Flag Setting
6. Indexes
7. Configuration the Parameters in IFB: ONLY BASE TABLES / ONLY BASE COLUMNS/UPDATE ROWS = XX,FALUSE
8. Database Server Optimization: Database log/Archive files/Temporary workspace used by the databases
9. For initial data loading, you can disable transaction logging for improved performanc

However, we met one issue before the above solutions cannot fix the performance issue at all. The IFB session was keeping in running but had no progress. The related ORACEL query execution plan showed it’s using the index which was not the one we expected.

E. G.

[Siebel Interface Manager]

                        PROCESS = Import Asset

[Import Asset]

                        TYPE = IMPORT

BATCH = $cbatch

                        TABLE = EIM_ASSET

                        INSERT ROWS = S_ASSET, FALSE

                UPDATE ROWS = S_ASSET, TRUE

                        ONLY BASE TABLES = S_ASSET

                        ONLY BASE COLUMNS = S_ASSET.ASSET_NUM,\

                        S_ASSET.BU_ID,\

                        S_ASSET.OWNER_ACCNT_ID,\

                        S_ASSET.PROD_ID,\

                        S_ASSET.NAME,\

                S_ASSET.X_SEID,\

                        S_ASSET.X_SUFFIX,\

                        S_ASSET.X_SE_CD,\

                        S_ASSET.X_SUMMARY_EQUIPMENT_NUM

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

The unique index of table S_ASSET is INDEX_1 (ASSET_NUM). It also has another non-index: INDEX_2 (PROD_ID, X_SUFFIX).

But the query took INDEX_2 and the performance was very bad.

The root cause is EIM uses the ORCALE rule-based optimizer (RBO) mode not CBO mode.

So sometime, its execution plan was not optimal.

SIEBEL EIM has a parameter called [USE INDEX HITS]. The default value is false, which means the IFB loading will use RBO optimizer by default.

If set to TRUE, then the optimizer knows it has to use the index specified. It makes EIM uses the following selection criteria in choosing indexes:

a. unique indexes have priority over non-unique indexes

b. required columns have priority over non- required indexes

;;;;;;;;;;;;;;;;;;;;;;;;;;;; Change Log ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

; Performance issue:Added ‘USE INDEX HINTS = TRUE’  

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[Siebel Interface Manager]

                        PROCESS = Import Asset

[Import Asset]

                        TYPE = IMPORT

BATCH = $cbatch

                        TABLE = EIM_ASSET

      USE INDEX HINTS = TRUE

                        INSERT ROWS = S_ASSET, FALSE

                UPDATE ROWS = S_ASSET, TRUE

                        ONLY BASE TABLES = S_ASSET

                        ONLY BASE COLUMNS = S_ASSET.ASSET_NUM,\

                        S_ASSET.BU_ID,\

                        S_ASSET.OWNER_ACCNT_ID,\

                        S_ASSET.PROD_ID,\

                        S_ASSET.NAME,\

                S_ASSET.X_SEID,\

                        S_ASSET.X_SUFFIX,\

                        S_ASSET.X_SE_CD,\

                        S_ASSET.X_SUMMARY_EQUIPMENT_NUM

In this case, we added the parameter define (USE INDEX HINTS = TRUE) and the query took index INDEX_1 instead. The performance issue’s resolved.