Monitoring your queries using memory-resident database monitor

 

The Memory-Resident Database Monitor is a tool that provides another method for monitoring database performance. This tool is only intended for SQL performance monitoring and is useful for programmers and performance analysts. The monitor, with the help of a set of APIs, takes database monitoring information and manages them for the user in memory. This memory-based monitor reduces CPU overhead as well as resulting table sizes.

The Start Database Monitor (STRDBMON) can constrain server resources when collecting performance information. This overhead is mainly attributed to the fact that performance information is written directly to a database table as the information is collected. The memory-based collection mode reduces the server resources consumed by collecting and managing performance results in memory. This allows the monitor to gather database performance statistics with a minimal impact to the performance of the server as whole (or to the performance of individual SQL statements).

The monitor collects much of the same information as the STRDBMON monitor, but the performance statistics are kept in memory. At the expense of some detail, information is summarized for identical SQL statements to reduce the amount of information collected. The objective is to get the statistics to memory as fast as possible while deferring any manipulation or conversion of the data until the performance data is dumped to a result table for analysis.

The memory-based monitor is not meant to replace the STRDBMON monitor. There are circumstances where the loss of detail in the monitor will not be sufficient to fully analyze an SQL statement. In these cases, the STRDBMON monitor should still be used.

The memory-based monitor manages the data in memory, combining and accumulating the information into a series of row formats. This means that for each unique SQL statement, information is accumulated from each run of the statement and the detail information is only collected for the most expensive statement execution.

Each SQL statement is identified by the monitor according to the following:

• statement name

• package (or program)

• schema that contains the prepared statement

• cursor name that is used

For pure dynamic statements, the statement text is kept in a separate space and the statement identification will be handled internally via a pointer.

While this system avoids the significant overhead of writing each SQL operation to a table, keeping statistics in memory comes at the expense of some detail. Your objective should be to get the statistics to memory as fast as possible, then reserve time for data manipulation or data conversion later when you dump data to a table.

The memory-based monitor manages the data that is in memory by combining and accumulating the information into the new row formats. Therefore, for each unique SQL statement, information accumulates from each running of the statement, and the server only collects detail information for the most expensive statement execution.

Each SQL statement is identified by the monitor by the statement name, the package (or program) and schema that contains the prepared statement and the cursor name that is used. For pure dynamic statements:

• Statement text is kept in a separate space and

• Statement identification is handled internally via a pointer.

 

API support for the memory-based monitor

A set of APIs enable support for the memory-based monitor. An API supports each of the following activities:

• Start the new monitor

• Dump statistics to tables

• Clear the monitor data from memory

• Query the monitor status

• End the new monitor

When you start the new monitor, information is stored in the local address space of each job that the system monitors. As each statement completes, the system moves information from the local job space to a common system space. If more statements are executed than can fit in this amount of common system space, the system drops the statements that have not been executed recently.

• Memory-resident database monitor external API description
  The memory-resident database monitor is controlled by a set of APIs.

• Memory-resident database monitor external table description
  The memory resident database monitor uses its own set of tables instead of using the single table with logical files that the STRDBMON monitor uses. The memory resident database monitor tables closely match the suggested logical files of the STRDBMON monitor.

• Sample SQL queries
  As with the STRDBMON monitor, it is up to the user to extract the information from the tables in which all of the monitored data is stored. This can be done through any query interface that the user chooses.

• Memory-resident database monitor row identification
  The join key column QQKEY simplifies the joining of multiple tables together. This column replaces the join field (QQJFLD) and unique query counters (QQCNT) that the database monitor used. The join key column contains a unique identifier that allows all of the information for this query to be received from each of the tables.

 

Parent topic:

Optimizing query performance using query optimization tools

 

Related information

Start SQL Database Monitor (QQQSSDBM) API
Dump SQL Database Monitor (QQQDSDBM) API
Clear SQL Database Monitor Statistics (QQQCSDBM) API
Query SQL Database Monitor (QQQQSDBM) API
End SQL Database Monitor (QQQESDBM) API