Using the Application Monitor › SQL Statistics (PF11)

SQL Statistics (PF11)

 PM-Rnn.n SYSTEM71         CA, Inc.                  V71      08.348 16:49:07.25
 CMD-->                                                             WINDOW : 02

   02 SQL Statistics      IDMSSQL   137                     16:45:06.7131

  * I/O Information *                        * DB Navigation *
    Pages Written                              Rows Requested           5
    Pages Read                    2            Rows Current             5
    Pages Requested               4           * Locking Information *
  * Row Level Information *                    Select Locks
    Fetched                       5            Update Locks
    Inserted                                 * Sort Information *
    Updated                                    # of Sorts
    Deleted                                    High Row
  * Access Module Information *                Low Row
    Recompiles                                 # of Rows Sorted
  * SQL Statement Information *
    # Processed                   1

Screen Description

The SQL Statistics screen displays detailed SQL statistics for the current task.

Using this Screen

• Press [PF2] to go to the DC Statistics screen.
• Press [PF9] to go to the General Statistics screen.
• Press [PF4] to display SQL statistics for the next task, as shown on the Task List screen.
• Press [PF5] to display SQL statistics for the previous task, as shown on the Task List screen.

What To Look For

• Look for a high number in the Rows Requested field compared to the number in the Rows Current field.
• Look at the ratio of pages requested to pages read. The ratio indicates the effectiveness of the buffer size and database design. Low ratios may indicate that the buffer is too small or that the database needs to be tuned.
• Look for large values under Access Module Recompiles. Three reasons for recompiles:
  • Changes in the physical database definition

    Note: Use discretion in planning changes to components of the physical database definition.

  • Program recompiling; the recompile changes the date/time stamp, necessitating an AM recompile

    Note: Try to limit program compiles on a production system.

  • An SQL statement referencing a temporary table before the table is defined

    Note: Define temporary tables before referencing them.