Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > Troubleshooting EIM Performance >

Using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS Parameters

Perform testing with the .IFB file parameters USE INDEX HINTS and USE ESSENTIAL INDEX HINTS, trying both settings (TRUE and FALSE).

The default value for USE INDEX HINTS is FALSE. The default value for USE ESSENTIAL INDEX HINTS is TRUE.

NOTE:  If your configuration file has more than one process section, you must specify USE INDEX HINTS within each one.

If these parameters are set to FALSE, EIM does not generate hints during processing. By setting the value to FALSE, you may realize performance gains if the TRUE setting means that hints are being generated that direct the database optimizer to use less than optimal indexes. EIM processing should be tested with both the TRUE and FALSE settings to determine which one provides better performance for each of the respective EIM jobs.

NOTE:  The USE INDEX HINTS parameter is only applicable for Oracle database platforms. The USE ESSENTIAL INDEX HINTS parameter is only applicable for Microsoft SQL Server and Oracle database platforms.

These two parameters work for different queries, so you need to enable both to get all of the index hints on Oracle.

The following example illustrates the results achieved for an SQL statement with index hints and without index hints. This example was performed on the MS SQL Server platform.

SQL User Name

CPU

Reads

Writes

Duration

Connection ID

SPID

SADMIN

549625

38844200

141321

626235

516980

9

UPDATE dbo.S_ASSET5_FN_IF

SET T_APPLDCVRG__RID =

(SELECT MIN(BT.ROW_ID)

FROM dbo.S_APPLD_CVRG BT (INDEX = S_APPLD_CVRG_U2)

WHERE (BT.COVERAGE_CD = IT.CVRG_COVERAGE_CD AND

BT.TYPE = IT.CVRG_TYPE AND

BT.ASSET_ID = IT.T_APPLDCVRG_ASSETI AND

(BT.ASSET_CON_ID = IT.T_APPLDCVRG_ASSETC OR

(BT.ASSET_CON_ID IS NULL AND IT.T_APPLDCVRG_ASSETC IS NULL)) AND

(BT.INSITEM_ID = IT.T_APPLDCVRG_INSITE OR

(BT.INSITEM_ID IS NULL AND IT.T_APPLDCVRG_INSITE IS NULL))))

FROM dbo.S_ASSET5_FN_IF IT

WHERE (CVRG_COVERAGE_CD IS NOT NULL AND

CVRG_TYPE IS NOT NULL AND

T_APPLDCVRG_ASSETI IS NOT NULL AND

IF_ROW_BATCH_NUM = 10710001 AND

IF_ROW_STAT_NUM = 0 AND

T_APPLDCVRG__STA = 0)

SET STATISTICS PROFILE ON

GO

SET STATISTICS IO ON

GO

select

(SELECT MIN(BT.ROW_ID)

FROM dbo.S_APPLD_CVRG BT (INDEX = S_APPLD_CVRG_U2)

WHERE (BT.COVERAGE_CD = IT.CVRG_COVERAGE_CD AND

BT.TYPE = IT.CVRG_TYPE AND

BT.ASSET_ID = IT.T_APPLDCVRG_ASSETI AND

(BT.ASSET_CON_ID = IT.T_APPLDCVRG_ASSETC OR

(BT.ASSET_CON_ID IS NULL AND IT.T_APPLDCVRG_ASSETC IS NULL)) AND

(BT.INSITEM_ID = IT.T_APPLDCVRG_INSITE OR

(BT.INSITEM_ID IS NULL AND IT.T_APPLDCVRG_INSITE IS NULL))))

FROM dbo.S_ASSET5_FN_IF IT

WHERE (CVRG_COVERAGE_CD IS NOT NULL AND

CVRG_TYPE IS NOT NULL AND

T_APPLDCVRG_ASSETI IS NOT NULL AND

IF_ROW_BATCH_NUM = 10710001 AND

IF_ROW_STAT_NUM = 0 AND

T_APPLDCVRG__STA = 0)

With hints:

Table 'S_APPLD_CVRG'. Scan count 1, logical reads 394774, physical reads 0, read-ahead reads 280810.

Table 'S_ASSET5_FN_IF'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 0.

Without hints:

Table 'S_APPLD_CVRG'. Scan count 1268, logical reads 10203, physical reads 697, read-ahead reads 0.

Table 'S_ASSET5_FN_IF'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 0.


 Siebel Enterprise Integration Manager Administration Guide 
 Published: 05 January 2004