Troubleshooting Performance Using SQL Query Plans

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

If you identify a problematic query in the SQL trace file, then you can obtain more information about it by using the database query tool provided with the RDBMS, such as SQL*Plus for Oracle Database.

Copy and paste the SQL statement from the trace file into the database query tool, execute the query against the Siebel database, then generate a query plan. A query plan is a detailed reporting of various statistics about the query you executed. For an example of generating a query plan against the local database, see Example of Obtaining a Query Plan.

Use query plans to check:

• The use of indexes

• The use of temporary tables

• The use of sequential table scans

Finally, compare your results with a standard application (that is, not custom-configured) in order to identify any potentially slow queries. You can resolve many performance issues either by modifying search specifications or sort specifications, or by creating new indexes on the base table.

Caution: Only specially trained Oracle personnel can modify existing Siebel indexes. This restriction is enforced so that performance in other modules (such as Siebel EIM) is not adversely affected by any index modifications that you make to improve query performance through the user interface. For more information, see Managing Database Indexes in Sorting and Searching.

Consider any potential performance implications before modifying search specification and sort specification properties for a business component. By spooling out the SQL into trace files, you can analyze which indexes are likely to be used when your application queries the business component through each applet.

Run your query plans against datasets that are comparable to the production dataset. For example, 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 might 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. 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 you with a 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.

Note: When you review a query plan, keep track of the business object to which each query applies, You can tell where each new business object is being opened by searching for the S_APP_QUERY statement. The business object that was accessed is represented using the bind variable statements beneath the query.

Bind variables are the values that determine which records are brought back. The RDBMS 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. For example, 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, nor used for passing values to detail 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 (MVL) 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 an MVG 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 Using Primary ID Fields to Improve Performance.