Testing Siebel eBusiness Applications > Execute Performance Tests >

Performing SQL Trace

Since many performance issues are caused by poorly formed SQL or sub optimal database tuning, the first step to improve performance is to perform SQL trace. SQL trace creates a log file that records the statements generated in the Siebel object manager and that are executed on the database. The time required to execute and fetch on an SQL statement has a significant impact on both the response time seen by end users of a system and on the system's resource utilization on the database tier. It is important to discover slow SQL statements and root cause, and fix issues before attempting scalability or load tests, as excessive resource utilization on the database server will invalidate the results of the test or cause it to fail.

To obtain an SQL trace

1. Set a breakpoint in the script at the end of each action and execute the script for two iterations.
2. Enable EvtLogLvl (ObjMgrSqlLog=5) to obtain SQL traces for the component on the application server that has this user session or task running.
3. Continue executing the script for the third iteration and wait for the breakpoint at the end of action.
4. Turn OFF SQL tracing on the application server (reset it to its original value, or 1).
5. Complete the script execution.

The log file resulting from this procedure has current SQL traces for this business scenario. Typically, any SQL statement over 0.1 seconds is considered suspect and needs to be investigated, either by optimizing the execution of the query (typically by creating an index on the database) or by altering the application to change the query.