Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > 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>
|