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
- ACCESSCTRL authority
- SECADM authority
Default PUBLIC privilege
None
Information returned
| 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). In a Db2 pureScale environment, this value is the percentage of time the database manager used to locate a data page for an XDA in the local buffer pool. |
| COL_PHYSICAL_READS | BIGINT | Indicates the number of column-organized table data pages read from the physical table space containers for temporary, regular, and large table spaces. This is calculated as (pool_col_p_reads + pool_temp_col_p_reads) where pool_col_p_reads and pool_temp_col_p_reads represent the following monitor elements: |
| COL_HIT_RATIO_PERCENT | DECIMAL(5,2) | Column-organized table data hit ratio. The percentage of time that the database manager did not need to load a page from disk to service a column-organized table 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. |
| 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 + pool_col_p_reads + pool_temp_col_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, pool_temp_xda_p_reads, pool_col_p_reads, and pool_temp_col_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 + pool_col_p_reads + pool_temp_col_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, pool_temp_xda_p_reads, pool_col_p_reads, and pool_temp_col_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 + pool_async_col_reads) where unread_prefetch_pages, pool_async_data_reads, pool_async_index_reads, pool_async_xda_reads, and pool_async_col_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 + pool_col_writes) where pool_data_writes, pool_index_writes, pool_xda_writes, and pool_col_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 + pool_col_writes) where pool_write_time, pool_data_writes, pool_index_writes, pool_xda_writes, and pool_col_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. |
| GBP_COL_HIT_RATIO_PERCENT | DECIMAL(5,2) | Group bufferpool column-organized table 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 column-organized table data request because the page was already in the group bufferpool. Outside of a Db2 pureScale environment environment, this value is null. |
| CACHING_TIER_DATA_HIT_RATIO_PERCENT | DECIMAL(5,2) | The percentage of data-type page read requests that the database manager was able to service utilizing the caching tier. |
| CACHING_TIER_INDEX_HIT_RATIO_PERCENT | DECIMAL(5,2) | The percentage of index-type page read requests that the database manager was able to service utilizing the caching tier. |
| CACHING_TIER_XDA_HIT_RATIO_PERCENT | DECIMAL(5,2) | The percentage of xda-type page read requests that the database manager was able to service utilizing the caching tier. |
| CACHING_TIER_COL_HIT_RATIO_PERCENT | DECIMAL(5,2) | The percentage of columnar-type page read requests that the database manager was able to service utilizing the caching tier. |
| 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. |