Using the SQLPROFILE Parameter

This topic is part of Troubleshooting Siebel EIM Performance.

The inclusion of the SQLPROFILE 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

An example of the eimsql.sql file follows.

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

EIM: Integration Manager v8.2.2.3 [23021] LANG_INDEPENDENT SQL profile dump (pid 
9096).
*******************************************************************************
Top 100 SQL statements (of 24564) by total time:
Batch Step Pass Total Rows Per Row What
-------- -------- -------- -------- -------- -------- ------------------------
1000 4 106 124.07 0 virtual NULL key
1003 4 706 101.68 0 virtual NULL key
1002 4 506 93.15 0 virtual NULL key
1000 2 101 89.36 10000 0.01 default/fixed column

…list of queries continues

Statistics by step and by pass:

*******************************************************************************
Statements per step by total time:
Step Stmts Total Min Max Avg %
-------- -------- -------- -------- -------- -------- --------
9 3405 14727.44 0.00 75.80 4.33 58.10
4 12011 2854.16 0.01 124.07 0.24 11.26
2 454 2165.35 0.50 89.36 4.77 8.54

...list of statistics continues...