Start of Content Area

Procedure documentation Analyzing Sessions and Tables (Informix)  Locate the document in its SAP Library structure

Use

To find the cause of acute Informix database performance problems, you can analyze the database sessions and tables. This is particularly helpful if the problems are caused by errors or bad coding in the application.

Prerequisites

The basic approach is to:

  1. Check the activity for the last time period, and sort by relevant statistic values, for example, buffer writes, or (for sessions only) runtime.
  2. See which table has the most activity, or which session is causing the highest load on the database. With this information, you can usually determine the user, program, and SQL statement that are responsible for the high resource consumption.

Procedure

To analyze sessions and tables:

  1. Choose Tools ® CCMS ® Control/Monitoring ® Performance Menu ® Database ® Activity ® Detail Analysis Menu ® Informix Session or Table Activity.
  2. Choose Reset to reset the database statistics to zero.
  3. Choose Since Reset to get values for the period since you chose Reset in the previous step.
  4. Sort on a performance-relevant value. Run Time is a good value for sessions. For tables, interesting values to sort on are Pagereads (used by the system when the list is first displayed), Bufreads, Bufwrites and Seqscans.
  5. Choose Current SQL to find out what a session that is using an excessive amount of resources is doing. Use transaction SM51 to determine which user and program are currently in the work process and are so responsible for the database activity of the session.
  6. If you find a table that is being accessed particularly frequently, check the Informix session display for the most active sessions looking for SQL statements on the table. Then, use transaction SM51 to find the user and program.