Troubleshooting Performance Using SQL Trace Files

This topic is part of Analyzing Generated SQL for Performance Issues.

As described, you can generate SQL trace files related to your configuration changes, such as for a particular view you have configured. Analyze the contents of the SQL trace file to identify any possible performance issues.

As you look through the SQL trace file, be aware of factors such as:

• The number and complexity of SQL statements.

• Execution times for SQL statements. This is the SQL execution time plus the time it takes to return rows. It does not include time for client-side processing.

• Selection criteria in the WHERE clauses, indicating search specifications.

• Sorting criteria in the ORDER BY clauses, indicating sort specifications. (In general, it is better for a query to first filter data using WHERE clauses, in order to reduce the volume of data to be then sorted. Applying sorting criteria that match users' needs reduces the likelihood of users performing their own sort operations, which would require additional system resources.)

• The use of joins.

Note: If the same SQL statement is executed repeatedly, the Siebel application displays the entire statement for the first query. For each subsequent iteration of the same query, only the bind variables are displayed. You can recognize a 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. Queries that populate views are also present in the SQL trace file, and are easily distinguishable based on the tables they access.