Usage
Buffer pool hit ratios are one of the most fundamental metrics for measuring database performance. They provide an important overall measure of how effectively the system is exploiting memory to avoid disk IO. The metrics for individual buffer pool can help you spot buffer pools that are sized too small. Low overall hit ratio for a particular buffer pool in conjunction with a high read rate relative to its peers suggests that the buffer pool is perhaps sized too small.
NOTE: only non-system buffer pools are tracked. The special buffer pools IBMSYSTEMPBP* are not monitored by IBM Data Server Manager.
Source
The data in the view mon_get_bufferpool comes primarily from the table function of the same name. Data is aggregated across all members.
Columns
As with all repository views, the columns marked Y in the “Delta” column are actually two columns. For example, the metric described here as “pool_data_l_reads” is actually a pair of columns “pool_data_l_reads” which is a counter of how many logical data reads have been performed by this buffer pool since the data server was activated and “pool_data_l_reads_delta”, which tells how many logical data reads have been performed by this buffer pool in the collection interval. For most purposes, the _delta version of the metrics is the most useful. The raw counter version is provided for completeness.
|
Column Name |
Delta |
Type |
Description |
|
dbconn_int |
n |
integer |
The internal ID for connection name. Included for completeness. |
|
dbconn_id |
n |
varchar(255) |
The string version of the connection name. When the user configures a database to be monitored, this is the name they specified for the connection. |
|
prev_collected |
n |
timestamp |
Timestamp of the start of the collection interval |
|
collected |
n |
timestamp |
Timestamp of the end of the collection interval |
|
delta_msec |
n |
bigint |
Number of milliseconds in the collection interval |
|
bp_name |
n |
varchar(128) |
The name of the buffer pool. This serves as the row ID for this view. |
|
member |
n |
smallint |
|
|
pool_data_l_reads |
Y |
bigint |
|
|
pool_data_p_reads |
Y |
bigint |
|
|
pool_temp_data_l_reads |
Y |
bigint |
|
|
pool_temp_data_p_reads |
Y |
bigint |
|
|
pool_index_l_reads |
Y |
bigint |
|
|
pool_index_p_reads |
Y |
bigint |
|
|
pool_temp_index_l_reads |
Y |
bigint |
|
|
pool_temp_index_p_reads |
Y |
bigint |
|
|
pool_xda_l_reads |
Y |
bigint |
|
|
pool_xda_p_reads |
Y |
bigint |
|
|
pool_temp_xda_l_reads |
Y |
bigint |
|
|
pool_temp_xda_p_reads |
Y |
bigint |
|
|
pool_data_writes |
Y |
bigint |
|
|
pool_xda_writes |
Y |
bigint |
|
|
pool_index_writes |
Y |
bigint |
|
|
direct_read_time |
Y |
bigint |
|
|
direct_write_time |
Y |
bigint |
|
|
pool_async_data_reads |
Y |
bigint |
|
|
pool_async_index_reads |
Y |
bigint |
|
|
pool_async_xda_reads |
Y |
bigint |
|
|
direct_reads |
Y |
bigint |
|
|
direct_writes |
Y |
bigint |
|
|
pool_data_lbp_pgs_fnd |
Y |
bigint |
|
|
pool_index_lbp_pgs_fnd |
Y |
bigint |
|
|
pool_async_dt_lbp_pgs_fnd |
Y |
bigint |
|
|
pool_async_ix_lbp_pgs_fnd |
Y |
bigint |
|
|
pool_xda_lbp_pages_found |
Y |
bigint |
|
|
pool_async_xd_lbp_pgs_fnd |
Y |
bigint |
|
|
pool_col_l_reads |
Y |
bigint |
|
|
pool_col_p_reads |
Y |
bigint |
|
|
pool_temp_col_l_reads |
Y |
bigint |
|
|
pool_temp_col_p_reads |
Y |
bigint |
|
|
pool_col_lbp_pages_found |
Y |
bigint |
|
|
pool_asnc_cl_lbp_pgs_fnd |
Y |
bigint |
|
|
pool_col_writes |
Y |
bigint |
|
|
bp_cur_buffsz |
n |
bigint |
The number of pages currently allocated to a buffer pool. |
|
unread_prefetch_pages |
Y |
bigint |
|
|
prefetch_wait_time |
Y |
bigint |
|
|
prefetch_waits |
Y |
bigint |
|
|
pool_async_col_reads |
Y |
bigint |
|
|
pool_read_time |
Y |
bigint |
|
|
pool_async_data_writes |
Y |
bigint |
|
|
pool_async_index_writes |
Y |
bigint |
|
|
pool_async_xda_writes |
Y |
bigint |
|
|
pool_async_col_writes |
Y |
bigint |
|
|
pagesize |
n |
integer |
|
|
self_tuning_enabled |
n |
smallint |
|