Identifying Problems with One Transaction Running Slow (Informix (SAP Library

Identifying Problems with One Transaction Running Slow (Informix)

Use

This procedure tells you how to identify problems that only affects certain users. If the problem affects all users, see Identifying Problems with All Transactions Running Slow (Informix).

The normal method is to use the SQL and ABAP trace facilities to trace the transaction that is causing difficulties. Most transaction-specific performance problems are due to inefficient SQL statements or ABAP coding. The trace facilities can almost always help you identify these problems.

This procedure explains how to identify performance problems due to:

Long running SQL statements

Lock wait situations

Missing secondary indexes

Procedure

To capture a statement with the SQL trace facility, turn the trace on before a statement goes to the database and do not turn off the trace until the statement has returned.

Sometimes it is difficult to find long running SQL statements due to the following:

Poor SQL statements can take several minutes to return.
The SQL trace file wraps around when it is full.

For more information, see Monitoring Sessions and Tables (Informix).

Use the database monitor to identify performance problems that affect certain users when the cause of the problem is a lock wait situation.

Sometimes a transaction doing collective processing or a mass data input transaction sends a large number of posting requests on related objects, causing each to wait on locks held by the others.

For more information, see Monitoring Lockwaits (Informix).

Use the database monitor to compare the definition of secondary indexes requested by SAP developers with the actual indexes on the database.

Sometimes indexes can get lost as a result of handling errors in copy or reorganization operations. This can cause a severe performance degradation for statements designed to use the index. To determine if any indexes are missing, check the state on disk.