Performance Tuning Guide > Tuning Siebel EIM for Performance > Troubleshooting EIM Performance >

Using the SQLPROFILE Parameter

The inclusion of this parameter greatly simplifies the task of identifying the most time-intensive SQL statements. By inserting the following statement in the header section of the .IFB file, the most time-intensive SQL statements will be placed in the file:

SQLPROFILE = c:\temp\eimsql.sql

Below is an example of the file eimsql.sql.

Start of the file - list of most time-intensive queries:

EIM: Integration Manager v6.0.1.2 [2943] ENU SQL profile dump (pid 430).

*************************************************************************

Top 34 SQL statements (of 170) by total time:

Batch Step Pass Total Rows Per Row What

-------- -------- -------- -------- -------- -------- -----------------

106 10 401 1334.48 5000 0.27 update implicit primaries to child

106 9 114 242.56 5000 0.05 copy

...list of queries continues

Statistics by step and by pass

*************************************************************************

Statements per step by total time:

Step Stmts Total Min Max Avg %

-------- -------- -------- -------- -------- -------- --------

10 15 2627.27 0.00 1334.48 175.15 83.73

9 11 329.52 0.00 242.56 29.96 10.50

...list of statistics continues...

SQL statements:

*************************************************************************

batch 106, step 10, pass 401: "update implicit primaries to child":

(total time 22:14m (1334s), 5000 rows affected, time/row 0.27s)

UPDATE siebel.S_CONTACT BT

SET PR_BL_PER_ADDR_ID =

(SELECT VALUE(MIN(ROW_ID), 'No Match Row Id')

FROM siebel.S_ADDR_PER CT

WHERE (CT.PER_ID = BT.ROW_ID)),

LAST_UPD = ?,

LAST_UPD_BY = ?,

MODIFICATION_NUM = MODIFICATION_NUM + 1

WHERE (ROW_ID IN (

SELECT T_ADDR_PER_PER_ID C1

FROM siebel.EIM_CONTACT

WHERE(

T_ADDR_PER_PER_ID IS NOT NULL AND

IF_ROW_BATCH_NUM = 106 AND

T_ADDR_PER__STA = 0 AND

T_ADDR_PER__EXS = 'N' AND

T_ADDR_PER__UNQ = 'Y' AND

T_ADDR_PER__RID IS NOT NULL)

GROUP BY T_ADDR_PER_PER_ID)

AND

(PR_BL_PER_ADDR_ID IS NULL OR PR_BL_PER_ADDR_ID = 'No Match Row Id'))

************************************************************

...list of SQL statements continues...