Siebel Tools Reference > Performance Improvement >

Performance Troubleshooting Using Spooled SQL

Performance troubleshooting is an iterative process. You need to consider performance implications during design. During development, you need to note any changes to potentially troublesome areas, especially multi-value groups and sort and search specifications in new and changed business components, as well as the addition of joins, extension tables, and indexes. Then you test the application to determine bottlenecks, using realistic data volumes and distribution in your test environment. You should focus the testing effort on the slowest, most important, and most highly configured views.

If a performance problem is detected in testing or production, your next step is to analyze the SQL statements being spooled by Siebel applications. This is your most useful diagnostic tool for performance analysis.

To generate an SQL trace file, you add the /S parameter to the command line in the properties for the Start menu or desktop icon from which the Siebel application is invoked. The command line has the following syntax:

C:\Siebel\webclient\bin\twsiebel.exe /c config_file /s trace_file

The /s trace_file expression in the command line instructs the system to generate an SQL trace file with the specified filename and path. This trace file records the text of all of the SQL queries issued by the Siebel application, and identifies the amount of time spent processing each one. The trace file may be opened in a text editor for examination after the session has ended.

NOTE:  You can also programmatically start and stop SQL spooling though the Siebel Object Interfaces by using the TraceOn and TraceOff methods on the Application object. See Siebel Object Interfaces Reference for more information.

A sample SQL query and its bind variables and execute times appear below, extracted from a trace file:

SELECT

T1.LAST_UPD,

T1.CREATED_BY,

T3.RSTRCT_DEL_FLG,

T3.RSTRCT_TRNS_FLG,

T1.DEFAULT_STATE_ID,

T2.LOGIN,

T3.DESC_TEXT,

T1.CONFLICT_ID,

T1.ACTIVATE_DT,

T1.CREATED,

T1.ROW_ID,

T2.ROW_ID,

T1.DESC_TEXT,

T3.VALUE,

T1.EXPIRE_DT,

T3.ROW_ID,

T1.NAME,

T3.RSTRCT_UPD_FLG,

T1.MODIFICATION_NUM,

T1.FIELD_NAME,

T1.BUSCOMP_NAME,

T1.LAST_UPD_BY

FROM

SIEBEL.S_STATE_MODEL T1

INNER JOIN SIEBEL.S_EMPLOYEE T2 ON T1.CREATED_BY = T2.ROW_ID

LEFT OUTER JOIN SIEBEL.S_SM_STATE T3 ON T1.DEFAULT_STATE_ID = T3.ROW_ID

WHERE

(T1.BUSCOMP_NAME = ? AND T1.ACTIVATE_DT <= ? AND (T1.EXPIRE_DT IS NULL OR T1.EXPIRE_DT >= ?))

ORDER BY

T1.NAME

Bind variable 1: Opportunity

Bind variable 2: 08/11/1999 12:40:55

Bind variable 3: 08/11/1999 12:40:55

***** SQL Statement Execute Time: 0.170 seconds *****

NOTE:  The execute time is SQL execution time plus the time it takes to return rows. It does not include time for client-side processing.

NOTE:  If the same statement is executed repeatedly, the Siebel application will display the entire statement for the first query and only the bind variables for each subsequent iteration of the query. Different queries may be repeated in this way. You can recognize the query that is repeated by the specific set of bind variables it uses.

SQL statements are displayed for all queries, including housekeeping queries. These are queries that are necessary for system operation, such as looking up the user's login to obtain responsibilities, and determining today's alarms in the calendar. You will also see queries to the S_LST_OF_VAL table to populate picklists. The queries that populate views are also present in the trace file, and should be easily distinguishable based on the tables they access.


 Siebel Tools Reference, Version 7.5, Rev. A 
 Published: 18 April 2003