DB2 Version 10.1 for Linux, UNIX, and Windows

MON_BP_UTILIZATION administrative view - Retrieve metrics for bufferpools

The MON_BP_UTILIZATION administrative view returns key monitoring metrics, including hit ratios and average read and write times, for all buffer pools and all database partitions in the currently connected database. It provides information that is critical for performance monitoring, because it helps you check how efficiently you are using your buffer pools.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the MON_BP_UTILIZATION administrative view
  • CONTROL privilege on the MON_BP_UTILIZATION administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned by the MON_BP_UTILIZATION administrative view
Column name Data type Description or Monitor element
BP_NAME VARCHAR(128) bp_name - Buffer pool name
MEMBER SMALLINT member- Database member
DATA_PHYSICAL_READS BIGINT
Indicates the number of data pages read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (pool_data_p_reads + pool_temp_data_p_reads) where pool_data_p_reads and pool_temp_data_p_reads represent the following monitor elements:
DATA_HIT_RATIO_PERCENT DECIMAL(5,2) Data hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request. In DB2® pureScale® environments, this value is the percentage of time that the database manager located a data page in the local buffer pool.
INDEX_PHYSICAL_READS BIGINT
Indicates the number of index pages read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (pool_index_p_reads + pool_temp_index_p_reads) where pool_index_p_reads + pool_temp_index_p_reads represent the following monitor elements:
INDEX_HIT_RATIO_PERCENT DECIMAL(5,2) Index hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service an index data page request. In DB2 pureScale environments, this value is the percentage of time that the database manager located a data page in the local buffer pool.
XDA_PHYSICAL_READS BIGINT
Indicates the number of data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (pool_xda_p_reads + pool_temp_xda_p_reads) where pool_xda_p_reads and pool_temp_xda_p_reads represent the following monitor elements:
XDA_HIT_RATIO_PERCENT DECIMAL(5,2) Auxiliary storage objects hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request for XML storage objects (XDAs). On a DB2 pureScale system, this value is the percentage of time the database manager used to locate a data page for an XDA in the local buffer pool.
TOTAL_PHYSICAL_READS BIGINT

Indicates the number of data pages, index pages, and data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary as well as regular and large table spaces.

This is calculated as (pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads) where pool_data_p_reads, pool_temp_data_p_reads, pool_index_p_reads, pool_temp_index_p_reads, pool_xda_p_reads and pool_temp_xda_p_reads represent the following monitor elements:
AVG_PHYSICAL_READ_TIME BIGINT

Average time, in milliseconds, spent reading pages from the table space containers (physical) for all types of table spaces.

If the sum of physical reads is greater than zero, this is calculated as pool_read_time / (pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads)

where pool_read_time, pool_data_p_reads, pool_temp_data_p_reads, pool_index_p_reads, pool_temp_index_p_reads, pool_xda_p_reads and pool_temp_xda_p_reads represent the following monitor elements:

If the sum of physical reads is not greater than zero, NULL is returned.
PREFETCH_RATIO_PERCENT DECIMAL(5,2) Percentage of pages read asynchronously (with prefetching). If many applications are reading data synchronously without prefetching, your system might not be tuned optimally.
ASYNC_NOT_READ_PERCENT DECIMAL(5,2)

Percentage of pages read asynchronously from disk, but never accessed by a query. If too many pages are read asynchronously from disk into the bufferpool, but no query ever accesses those pages, the prefetching might degrade performance.

If the sum of asynchronous reads is greater than zero, this is calculated as unread_prefetch_pages / (pool_async_data_reads + pool_async_index_reads + pool_async_xda_reads) where unread_prefetch_pages, pool_async_data_reads, pool_async_index_reads and pool_async_xda_reads represent the following monitor elements:

If the sum of asynchronous reads is not greater than zero, NULL is returned.
TOTAL_WRITES BIGINT

The number of times a data, index, or data page for an XML storage object (XDA) was physically written to disk.

This is calculated as (pool_data_writes + pool_index_writes + pool_xda_writes) where pool_data_writes, pool_index_writes, and pool_xda_writes represent the following monitor elements:
AVG_WRITE_TIME BIGINT

Average time, in milliseconds, spent physically writing pages from the buffer pool to disk.

If the sum of write operations is greater than zero, this is calculated as pool_write_time / (pool_data_writes + pool_index_writes + pool_xda_writes) where pool_write_time, pool_data_writes, pool_index_writes, and pool_xda_writes represent the following monitor elements:

If the sum of write operations is not greater than zero, NULL is returned.
SYNC_WRITES_PERCENT DECIMAL(5,2) Percentage of write operations that are synchronous.
GBP_DATA_HIT_RATIO_PERCENT DECIMAL(5,2) Group bufferpool data hit ratio. The percentage of time that the database manager did not need to load a page from disk in order to service a data page request because the page was already in the group bufferpool. Outside of a DB2 pureScale environment, this value is null.
GBP_INDEX_HIT_RATIO_PERCENT DECIMAL(5,2) Group bufferpool index hit ratio. The percentage of time that the database manager did not need to load a page from disk in order to service an index page request because the page was already in the group bufferpool. Outside of a DB2 pureScale environment, this value is null.
GBP_XDA_HIT_RATIO_PERCENT DECIMAL(5,2) Group bufferpool auxiliary storage object hit ratio. The percentage of time that the database manager did not need to load a page from disk in order to service a data page request for XML storage object (XDAs) because the page was already in the group bufferpool. Outside of a DB2 pureScale environment, this value is null.
AVG_SYNC_READ_TIME BIGINT Average time, in milliseconds, spent in synchronous reading from bufferpool.
AVG_ASYNC_READ_TIME BIGINT Average time, in milliseconds, spent in asynchronous reading from bufferpool.
AVG_SYNC_WRITE_TIME BIGINT Average time, in milliseconds, spent in synchronous writing to the bufferpool.
AVG_ASYNC_WRITE_TIME BIGINT Average time, in milliseconds, spent asynchronous writing to the bufferpool.