The MON_GET_BUFFERPOOL table function returns monitor metrics for one or more buffer pools.
EXECUTE privilege on the MON_GET_BUFFERPOOL function.
Compute the buffer pool hit ratio.
WITH BPMETRICS AS (
SELECT bp_name,
pool_data_l_reads + pool_temp_data_l_reads +
pool_index_l_reads + pool_temp_index_l_reads +
pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
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 as physical_reads,
member
FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
SELECT
VARCHAR(bp_name,20) AS bp_name,
logical_reads,
physical_reads,
CASE WHEN logical_reads > 0
THEN DEC((1 - (FLOAT(physical_reads) / FLOAT(logical_reads))) * 100,5,2)
ELSE NULL
END AS HIT_RATIO,
member
FROM BPMETRICS;
The following example is a sample output from this query.
BP_NAME LOGICAL_READS PHYSICAL_READS HIT_RATIO MEMBER
---------------- ---------------- -------------- --------- ---------
IBMDEFAULTBP 619 385 37.80 0
IBMSYSTEMBP4K 0 0 - 0
IBMSYSTEMBP8K 0 0 - 0
IBMSYSTEMBP16K 0 0 - 0
IBMSYSTEMBP32K 0 0 - 0
5 record(s) selected.
Output for query (continued).
... HIT_RATIO MEMBER
... --------- ------
... 37.80 0
... - 0
... - 0
... - 0
... - 0
The MON_GET_BUFFERPOOL table function returns one row of data per database buffer pool and per database member. No aggregation across database members is performed. However, aggregation can be achieved through SQL queries as shown in the example.
Metrics collected by this function are controlled at the database level using the mon_obj_metrics configuration parameter. By default, metrics collection is enabled.
Column Name | Data Type | Description or corresponding monitor element |
---|---|---|
BP_NAME | VARCHAR(128) | bp_name - Buffer pool name |
MEMBER | SMALLINT | member- Database member |
AUTOMATIC | SMALLINT | automatic - Buffer pool automatic |
DIRECT_READS | BIGINT | direct_reads - Direct reads from database |
DIRECT_READ_REQS | BIGINT | direct_read_reqs - Direct read requests |
DIRECT_WRITES | BIGINT | direct_writes - Direct writes to database |
DIRECT_WRITE_REQS | BIGINT | direct_write_reqs - Direct write requests |
POOL_DATA_L_READS | BIGINT | pool_data_l_reads - Buffer pool data logical reads |
POOL_TEMP_DATA_L_READS | BIGINT | pool_temp_data_l_reads - Buffer pool temporary data logical reads |
POOL_XDA_L_READS | BIGINT | pool_xda_l_reads - Buffer pool XDA data logical reads |
POOL_TEMP_XDA_L_READS | BIGINT | pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads |
POOL_INDEX_L_READS | BIGINT | pool_index_l_reads - Buffer pool index logical reads |
POOL_TEMP_INDEX_L_READS | BIGINT | pool_temp_index_l_reads - Buffer pool temporary index logical reads |
POOL_DATA_P_READS | BIGINT | pool_data_p_reads - Buffer pool data physical reads |
POOL_TEMP_DATA_P_READS | BIGINT | pool_temp_data_p_reads - Buffer pool temporary data physical reads |
POOL_XDA_P_READS | BIGINT | pool_xda_p_reads - Buffer pool XDA data physical reads |
POOL_TEMP_XDA_P_READS | BIGINT | pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads |
POOL_INDEX_P_READS | BIGINT | pool_index_p_reads - Buffer pool index physical reads |
POOL_TEMP_INDEX_P_READS | BIGINT | pool_temp_index_p_reads - Buffer pool temporary index physical reads |
POOL_DATA_WRITES | BIGINT | pool_data_writes - Buffer pool data writes |
POOL_XDA_WRITES | BIGINT | pool_xda_writes - Buffer pool XDA data writes |
POOL_INDEX_WRITES | BIGINT | pool_index_writes - Buffer pool index writes |
DIRECT_READ_TIME | BIGINT | direct_read_time - Direct read time |
DIRECT_WRITE_TIME | BIGINT | direct_write_time - Direct write time |
POOL_READ_TIME | BIGINT | pool_read_time - Total buffer pool physical read time |
POOL_WRITE_TIME | BIGINT | pool_write_time - Total buffer pool physical write time |
POOL_ASYNC_DATA_READS | BIGINT | pool_async_data_reads - Buffer pool asynchronous data reads |
POOL_ASYNC_DATA_READ_REQS | BIGINT | pool_async_data_read_reqs - Buffer pool asynchronous read requests |
POOL_ASYNC_DATA_WRITES | BIGINT | pool_async_data_writes - Buffer pool asynchronous data writes |
POOL_ASYNC_INDEX_READS | BIGINT | pool_async_index_reads - Buffer pool asynchronous index reads |
POOL_ASYNC_INDEX_READ_REQS | BIGINT | pool_async_index_read_reqs - Buffer pool asynchronous index read requests |
POOL_ASYNC_INDEX_WRITES | BIGINT | pool_async_index_writes - Buffer pool asynchronous index writes |
POOL_ASYNC_XDA_READS | BIGINT | pool_async_xda_reads - Buffer pool asynchronous XDA data reads |
POOL_ASYNC_XDA_READ_REQS | BIGINT | pool_async_xda_read_reqs - Buffer pool asynchronous XDA read requests |
POOL_ASYNC_XDA_WRITES | BIGINT | pool_async_xda_writes - Buffer pool asynchronous XDA data writes |
POOL_NO_VICTIM_BUFFER | BIGINT | pool_no_victim_buffer - Buffer pool no victim buffers |
POOL_LSN_GAP_CLNS | BIGINT | pool_lsn_gap_clns - Buffer pool log space cleaners triggered |
POOL_DRTY_PG_STEAL_CLNS | BIGINT | pool_drty_pg_steal_clns - Buffer pool victim page cleaners triggered |
POOL_DRTY_PG_THRSH_CLNS | BIGINT | pool_drty_pg_thrsh_clns - Buffer pool threshold cleaners triggered |
VECTORED_IOS | BIGINT | vectored_ios - Number of vectored IO requests |
PAGES_FROM_VECTORED_IOS | BIGINT | pages_from_vectored_ios - Total number of pages read by vectored IO |
BLOCK_IOS | BIGINT | block_ios - Number of block IO requests |
PAGES_FROM_BLOCK_IOS | BIGINT | pages_from_block_ios - Total number of pages read by block IO |
UNREAD_PREFETCH_PAGES | BIGINT | unread_prefetch_pages - Unread prefetch pages |
FILES_CLOSED | BIGINT | files_closed - Database files closed |
POOL_DATA_GBP_L_READS | BIGINT | Reserved for future use |
POOL_DATA_GBP_P_READS | BIGINT | Reserved for future use |
POOL_DATA_LBP_PAGES_FOUND | BIGINT | Reserved for future use |
POOL_DATA_GBP_INVALID_PAGES | BIGINT | Reserved for future use |
POOL_INDEX_GBP_L_READS | BIGINT | Reserved for future use |
POOL_INDEX_GBP_P_READS | BIGINT | Reserved for future use |
POOL_INDEX_LBP_PAGES_FOUND | BIGINT | Reserved for future use |
POOL_INDEX_GBP_INVALID_PAGES | BIGINT | Reserved for future use |
POOL_ASYNC_DATA_GBP_L_READS | BIGINT | Reserved for future use |
POOL_ASYNC_DATA_GBP_P_READS | BIGINT | Reserved for future use |
POOL_ASYNC_DATA_LBP_PAGES_FOUND | BIGINT | Reserved for future use |
POOL_ASYNC_DATA_GBP_INVALID_PAGES | BIGINT | Reserved for future use |
POOL_ASYNC_INDEX_GBP_L_READS | BIGINT | Reserved for future use |
POOL_ASYNC_INDEX_GBP_P_READS | BIGINT | Reserved for future use |
POOL_ASYNC_INDEX_LBP_PAGES_FOUND | BIGINT | Reserved for future use |
POOL_ASYNC_INDEX_GBP_INVALID_PAGES | BIGINT | Reserved for future use |
POOL_XDA_GBP_L_READS | BIGINT | Reserved for future use |
POOL_XDA_GBP_P_READS | BIGINT | Reserved for future use |
POOL_XDA_LBP_PAGES_FOUND | BIGINT | Reserved for future use |
POOL_XDA_GBP_INVALID_PAGES | BIGINT | Reserved for future use |
POOL_ASYNC_XDA_GBP_L_READS | BIGINT | Reserved for future use |
POOL_ASYNC_XDA_GBP_P_READS | BIGINT | Reserved for future use |
POOL_ASYNC_XDA_LBP_PAGES_FOUND | BIGINT | Reserved for future use |
POOL_ASYNC_XDA_GBP_INVALID_PAGES | BIGINT | Reserved for future use |
POOL_ASYNC_READ_TIME | BIGINT | pool_async_read_time - Buffer Pool Asynchronous Read Time monitor element |
POOL_ASYNC_WRITE_TIME | BIGINT | pool_async_write_time - Buffer pool asynchronous write time monitor element |
BP_CUR_BUFFSZ | BIGINT | bp_cur_buffsz - Current Size of Buffer Pool monitor element |
ADDITIONAL_DETAILS | BLOB(100K) | Reserved for future use |