Bookshelf Home | Contents | Index | Search | PDF

Siebel Tools Reference > Performance Improvement > Performance Troubleshooting Using Spooled SQL >

Analyzing the Trace File and Query Plans

---

A query plan is a detailed reporting of various statistics about the query you executed.

To obtain a query plan for an SQL statement in your trace file

Execute the Interactive SQL (dbisqlc.exe) program, located in Siebel Tools and in the Web Client Installation Directory. The Data window opens.
Copy and paste the SQL statement to analyze into the Command windowpane.
Replace bind variable references with the corresponding bind variable values.
Click the Execute button.

The query runs against the local SQL Anywhere database.

The Statistics windowpane provides analysis information.

As you look through the trace file and run query plans, you should be aware of:

The number and complexity of SQL statements
Execution times
Sorting criteria in the ORDER BY clauses, indicating sort specifications
Selection criteria in the WHERE clauses, indicating search specifications
The use of indexes (these are identified when you run a query plan)
The use of temporary tables (also in the query plan)
The use of sequential table scans (also in the query plan)

Run your query plans against datasets that are comparable to the production dataset. You will not obtain useful results analyzing the performance of a query against a 30-record test dataset when the production database has 200,000 records.

You may find it useful to prioritize the views to examine, as follows:

First priority—Views that are known to have the biggest performance bottlenecks
Second priority—Those views that are accessed most frequently
Third priority—Views that are the most highly configured (as compared to the standard Siebel application)

Comparison with the standard Siebel application provides your benchmark for evaluation. It is often very useful to obtain a trace file from the standard Siebel application, following a preselected route through the views. Then you obtain a separate trace file from the custom-configured application, following the same route as closely as possible. The two trace files are compared, noting differences in the bullet items listed previously.

You can tell where each new business object is being opened by searching for the S_APP_QUERY statement. You can determine which business object has been accessed by checking the Bind Variable statements beneath the query. Bind variables are the values that determine which records are brought back. The database software substitutes the value of a bind variable into an SQL statement when the same SQL statement is being reused, generally in place of each occurrence of a question mark or series of question marks. A business object bind variable is used in an S_APP_QUERY statement because the purpose of this statement is to open the business object.

Watch for the following indications of potential problems:

Unnecessary fields are being accessed, especially ones not exposed in the user interface and not needed for calculated fields, or used for passing values to child records.
Unnecessary joins are occurring, particularly to tables that are not being accessed.
Unnecessary multiple joins are being made to the same table. This can indicate duplicate join or multi-value link object definitions, or joins using the same foreign key.
Multiple short queries similar to the following:

...FROM

SIEBEL.S_ADDR_PER T1

When a short query appears many times, this generally indicates that a multi-value group without a primary join is being accessed by a list applet. The system is running a secondary query for each master record to obtain its detail records. The secondary queries are the short queries appearing in the log file. This is usually your best diagnostic indicator of the need for a primary join.

When a short query appears only once, it indicates the same situation, but accessed in a form applet. In either case, the cure is a primary join, as explained in Multi-Value Group Performance.

Bookshelf Home | Contents | Index | Search | PDF