Start of Content Area

Procedure documentation Monitoring SQL Statement (Informix)  Locate the document in its SAP Library structure

Use

You can use the SAP/Informix database monitor to monitor Informix SQL statements. This lets you monitor the statistics for SQL statements, so enabling identification of statements which are "expensive," that is, consuming an excessive amount of resources.

Running a single SQL statement can sometimes adversely affect performance for all database users. This can happen when the amount of data being searched is very large or when a great deal of processing, such as sorting, needs to be done to the returned set of data. The result is statements which are inefficient in their use of CPU time, database buffers and disk I/Os, leading to degraded performance for all other users.

It is important to monitor SQL statistics to identify these expensive statements and to determine whether action can be taken to improve their performance.

Prerequisites

For more information about what to do before you perform this procedure, see Checking SQL Statements (Informix).

Procedure

  1. Choose Tools ® CCMS ® Control/Monitoring ® Performance Menu ® Database ® Activity ® Detail Analysis Menu ® SQL Statement.

    2. The system prompts you for Selection Criteria (Session ID, Buffer Reads, and so on).

  2. Enter selection criteria if you want to restrict the display.
  3. Choose Continue.

    4. The system displays details of SQL statements, as shown below:

    This graphic is explained in the accompanying text

  4. Pay special attention to the following:
  1. Select Sort to order the display by each of the above areas. This helps to get an overall picture of the types of statements that are often executed.

    2. A high number of total executions is not necessarily a bad indication since some statements need to be frequently executed

  2. Investigate cases where a repeatedly run SQL statement shows a high number of "reads" or "gets" each time it is executed. Check such statements more closely to determine whether indexes are missing or fragmented.

    3. Perhaps indexes exist on the table, but the SQL statement is written in such a way that it cannot make good use of them. Such expensive SQL statements often reference tables which might benefit from a new secondary index.

    The information displayed cannot tell you which user or ABAP program is responsible for an expensive statement. It is sometimes difficult to find which program contains the "select" responsible for the expensive SQL statement.

  3. Use the SAP Repository Information System to see a description of the given table and its "where used" list. This normally helps to narrow down your search.

    4. Since Informix does not use a shared cursor cache, statements can be open redundantly in many sessions. The default view offered by the SQL statement monitor shows information about identical statements summarized in one line for each equivalent statement.

  4. Choose Per Sessions to expand the view to show all statements individually, even if they contain the same text. Group By SQL returns you to the consolidated view.
  5. If a statement appears to be performing poorly choose DB Analysis ® Explain.

This tells you what access method the optimizer chooses to gather the result set for the SQL statement. In the Informix implementation, the path chosen by the optimizer depends on the values of the variables used in the "where" clause (therefore, the optimizer is the "cost-based" type). Unfortunately, the Informix system database from which the information about the SQL statements is obtained does not store these values.

To enable you to enter reasonable values in the "where" clause of the affected statement, the "Explain" function of the SQL Statement Monitor first presents you the statement in an editor, where you can change the statement as required. When you exit the editor the explain function is performed.

The explain function is only supported on "select" statements.