MON_GET_BUFFERPOOL table function - Get buffer pool metrics

The MON_GET_BUFFERPOOL table function returns monitor metrics for one or more buffer pools.

Syntax

Read syntax diagramSkip visual syntax diagramMON_GET_BUFFERPOOL(bp_name ,member)

The schema is SYSPROC.

Table function parameters

bp_name
An input argument of type VARCHAR(128) that specifies a valid buffer pool name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all buffer pools in the database.
member
An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned for MON_GET_BUFFERPOOL
Column Name Data Type Description
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_TEMP_DATA_CACHING_TIER_L_READS BIGINT pool_temp_data_caching_tier_l_reads - pool temporary data page caching tier l reads monitor element
POOL_TEMP_DATA_CACHING_TIER_PAGES_FOUND BIGINT pool_temp_data_caching_tier_pages_found - Buffer pool temporary data pages found in caching tier monitor element
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_TEMP_XDA_CACHING_TIER_L_READS BIGINT pool_temp_xda_caching_tier_l_reads - pool XML data-type page caching tier l reads monitor element
POOL_TEMP_XDA_CACHING_TIER_PAGES_FOUND BIGINT pool_temp_xda_caching_tier_pages_found - pool temporary XML data-type tier pages found monitor element
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_TEMP_INDEX_CACHING_TIER_L_READS BIGINT pool_temp_index_caching_tier_l_reads - pool temporary index-type page caching tier l reads monitor element
POOL_TEMP_INDEX_CACHING_TIER_PAGES_FOUND BIGINT pool_temp_index_caching_tier_pages_found - pool temporary index-type tier pages found monitor element
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 pool_data_gbp_l_reads - Group buffer pool data logical reads monitor element
POOL_DATA_GBP_P_READS BIGINT pool_data_gbp_p_reads - Group buffer pool data physical reads monitor element
POOL_DATA_LBP_PAGES_FOUND BIGINT pool_data_lbp_pages_found - Local buffer pool found data pages monitor element
POOL_DATA_GBP_INVALID_PAGES BIGINT pool_data_gbp_invalid_pages - Group buffer pool invalid data pages monitor element
POOL_INDEX_GBP_L_READS BIGINT pool_index_gbp_l_reads - Group buffer pool index logical reads monitor element
POOL_INDEX_GBP_P_READS BIGINT pool_index_gbp_p_reads - Group buffer pool index physical reads monitor elements
POOL_INDEX_LBP_PAGES_FOUND BIGINT pool_index_lbp_pages_found - Local buffer pool index pages found monitor element
POOL_INDEX_GBP_INVALID_PAGES BIGINT pool_index_gbp_invalid_pages - Group buffer pool invalid index pages monitor element
POOL_ASYNC_DATA_GBP_L_READS BIGINT pool_async_data_gbp_l_reads - Asynchronous group buffer pool data logical reads monitor element
POOL_ASYNC_DATA_GBP_P_READS BIGINT pool_async_data_gbp_p_reads - Asynchronous group buffer pool data physical reads monitor element
POOL_ASYNC_DATA_LBP_PAGES_FOUND BIGINT pool_async_data_lbp_pages_found - Asynchronous local buffer pool data pages found monitor element
POOL_ASYNC_DATA_GBP_INVALID_PAGES BIGINT pool_async_data_gbp_invalid_pages - Asynchronous group buffer pool invalid data pages monitor element
POOL_ASYNC_INDEX_GBP_L_READS BIGINT pool_async_index_gbp_l_reads - Asynchronous group buffer pool index logical reads monitor element
POOL_ASYNC_INDEX_GBP_P_READS BIGINT pool_async_index_gbp_p_reads - Asynchronous group buffer pool index physical reads monitor element
POOL_ASYNC_INDEX_LBP_PAGES_FOUND BIGINT pool_async_index_lbp_pages_found - Asynchronous local buffer pool index pages found monitor element
POOL_ASYNC_INDEX_GBP_INVALID_PAGES BIGINT pool_async_index_gbp_invalid_pages - Asynchronous group buffer pool invalid index pages monitor element
POOL_XDA_GBP_L_READS BIGINT pool_xda_gbp_l_reads - Group buffer pool XDA data logical read requests
POOL_XDA_GBP_P_READS BIGINT pool_xda_gbp_p_reads - Group buffer pool XDA data physical read requests
POOL_XDA_LBP_PAGES_FOUND BIGINT pool_xda_lbp_pages_found - Local buffer pool XDA data pages found
POOL_XDA_GBP_INVALID_PAGES BIGINT pool_xda_gbp_invalid_pages - Group buffer pool invalid XDA data pages
POOL_ASYNC_XDA_GBP_L_READS BIGINT pool_async_xda_gbp_l_reads - Group buffer pool XDA data asynchronous logical read requests
POOL_ASYNC_XDA_GBP_P_READS BIGINT pool_async_xda_gbp_p_reads - Group buffer pool XDA data asynchronous physical read requests
POOL_ASYNC_XDA_LBP_PAGES_FOUND BIGINT pool_async_xda_lbp_pages_found - Asynchronous local buffer pool XDA data pages found
POOL_ASYNC_XDA_GBP_INVALID_PAGES BIGINT pool_async_xda_gbp_invalid_pages - Asynchronous group buffer pool invalid XDA data pages
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
POOL_QUEUED_ASYNC_DATA_REQS BIGINT pool_queued_async_data_reqs - Data prefetch requests
POOL_QUEUED_ASYNC_INDEX_REQS BIGINT pool_queued_async_index_reqs - Index prefetch requests
POOL_QUEUED_ASYNC_XDA_REQS BIGINT pool_queued_async_xda_reqs - XDA prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_DATA_REQS BIGINT pool_queued_async_temp_data_reqs - Data prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_INDEX_REQS BIGINT pool_queued_async_temp_index_reqs - Index prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_XDA_REQS BIGINT pool_queued_async_temp_xda_reqs - XDA data prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_OTHER_REQS BIGINT pool_queued_async_other_reqs - Non-prefetch requests
POOL_QUEUED_ASYNC_DATA_PAGES BIGINT pool_queued_async_data_pages - Data pages prefetch requests
POOL_QUEUED_ASYNC_INDEX_PAGES BIGINT pool_queued_async_index_pages - Index pages prefetch requests
POOL_QUEUED_ASYNC_XDA_PAGES BIGINT pool_queued_async_xda_pages - XDA pages prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_DATA_PAGES BIGINT pool_queued_async_temp_data_pages - Data pages prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES BIGINT pool_queued_async_temp_index_pages - Index pages prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_XDA_PAGES BIGINT pool_queued_async_temp_xda_pages - XDA data pages prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_DATA_REQS BIGINT pool_failed_async_data_reqs - Failed data prefetch requests
POOL_FAILED_ASYNC_INDEX_REQS BIGINT pool_failed_async_index_reqs - Failed index prefetch requests
POOL_FAILED_ASYNC_XDA_REQS BIGINT pool_failed_async_xda_reqs - Failed XDA prefetch requests
POOL_FAILED_ASYNC_TEMP_DATA_REQS BIGINT pool_failed_async_temp_data_reqs - Failed data prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_TEMP_INDEX_REQS BIGINT pool_failed_async_temp_index_reqs - Failed index prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_TEMP_XDA_REQS BIGINT pool_failed_async_temp_xda_reqs - Failed XDA prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_OTHER_REQS BIGINT pool_failed_async_other_reqs - Failed non-prefetch requests
SKIPPED_PREFETCH_DATA_P_READS BIGINT skipped_prefetch_data_p_reads - Skipped prefetch data physical reads
SKIPPED_PREFETCH_INDEX_P_READS BIGINT skipped_prefetch_index_p_reads - Skipped prefetch index physical reads
SKIPPED_PREFETCH_XDA_P_READS BIGINT skipped_prefetch_xda_p_reads - Skipped prefetch XDA physical reads
SKIPPED_PREFETCH_TEMP_DATA_P_READS BIGINT skipped_prefetch_temp_data_p_reads - Skipped prefetch temporary data physical reads
SKIPPED_PREFETCH_TEMP_INDEX_P_READS BIGINT skipped_prefetch_temp_index_p_reads - Skipped prefetch temporary index physical reads
SKIPPED_PREFETCH_TEMP_XDA_P_READS BIGINT skipped_prefetch_temp_xda_p_reads - Skipped prefetch temporary XDA data physical reads
SKIPPED_PREFETCH_UOW_DATA_P_READS BIGINT skipped_prefetch_uow_data_p_reads - Skipped prefetch unit of work data physical reads
SKIPPED_PREFETCH_UOW_INDEX_P_READS BIGINT skipped_prefetch_uow_index_p_reads - Skipped prefetch unit of work index physical reads
SKIPPED_PREFETCH_UOW_XDA_P_READS BIGINT skipped_prefetch_uow_xda_p_reads - Skipped prefetch unit of work XDA data physical reads
SKIPPED_PREFETCH_UOW_TEMP_DATA_P_READS BIGINT skipped_prefetch_uow_temp_data_p_reads - Skipped prefetch unit of work temporary data physical reads
SKIPPED_PREFETCH_UOW_TEMP_INDEX_P_READS BIGINT skipped_prefetch_uow_temp_index_p_reads - Skipped prefetch unit of work temporary index physical reads
SKIPPED_PREFETCH_UOW_TEMP_XDA_P_READS BIGINT skipped_prefetch_uow_temp_xda_p_reads - Skipped prefetch unit of work temporary XDA data physical reads
PREFETCH_WAIT_TIME BIGINT prefetch_wait_time - Time waited for prefetch
PREFETCH_WAITS BIGINT prefetch_waits - Prefetcher wait count
POOL_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found in local buffer pool monitor element
POOL_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found in local buffer pool monitor element
POOL_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_xda_gbp_indep_pages_found_in_lbp - Group buffer pool XDA independent pages found in local buffer pool monitor element
POOL_ASYNC_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_async_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found by asynchronous EDUs in a local buffer pool monitor element monitor element
POOL_ASYNC_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_async_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found by asynchronous EDUs in a local buffer pool monitor element monitor element
POOL_ASYNC_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_async_xda_gbp_indep_pages_found_in_lbp - Group buffer pool independent XML storage object(XDA) pages found by asynchronous EDUs in a local buffer pool monitor element monitor element
POOL_COL_L_READS BIGINT pool_col_l_reads - Buffer pool column-organized logical reads monitor element
POOL_TEMP_COL_L_READS BIGINT pool_temp_col_l_reads - Buffer pool column-organized temporary logical reads
POOL_TEMP_COL_CACHING_TIER_L_READS BIGINT pool_temp_col_caching_tier_l_reads - pool temporary columnar-type page caching tier l reads monitor element
POOL_TEMP_COL_CACHING_TIER_PAGES_FOUND BIGINT pool_temp_col_caching_tier_pages_found - pool temporary columnar-type tier pages found monitor element
POOL_COL_P_READS BIGINT pool_col_p_reads - Buffer pool column-organized physical reads
POOL_TEMP_COL_P_READS BIGINT pool_temp_col_p_reads - Buffer pool column-organized temporary physical reads
POOL_COL_LBP_PAGES_FOUND BIGINT pool_col_lbp_pages_found - Buffer pool column-organized LBP pages found
POOL_COL_WRITES BIGINT pool_col_writes - Buffer pool column-organized writes
POOL_ASYNC_CACHING_TIER_PAGE_READ_TIME BIGINT pool_async_caching_tier_page_read_time - Buffer pool caching tier page read time
POOL_ASYNC_COL_READS BIGINT pool_async_col_reads - Buffer pool asynchronous column-organized reads
POOL_ASYNC_COL_READ_REQS BIGINT pool_async_col_read_reqs - Buffer pool asynchronous column-organized read requests
POOL_ASYNC_COL_WRITES BIGINT pool_async_col_writes - Buffer pool asynchronous column-organized writes
POOL_ASYNC_COL_LBP_PAGES_FOUND BIGINT pool_async_col_lbp_pages_found - Asynchronous local buffer pool column-organized pages found
POOL_COL_GBP_L_READS BIGINT pool_col_gbp_l_reads - Buffer pool column-organized GBP logical reads
POOL_COL_GBP_P_READS BIGINT pool_col_gbp_p_reads - Buffer pool column-organized GBP physical reads
POOL_COL_GBP_INVALID_PAGES BIGINT pool_col_gbp_invalid_pages - Buffer pool column-organized GBP invalid data pages
POOL_COL_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_col_gbp_indep_pages_found_in_lbp - Buffer pool column-organized GBP independent pages found in local buffer pool
POOL_ASYNC_COL_GBP_L_READS BIGINT pool_async_col_gbp_l_reads - Asynchronous group buffer pool column-organized logical reads
POOL_ASYNC_COL_GBP_P_READS BIGINT pool_async_col_gbp_p_reads - Asynchronous group buffer pool column-organized physical reads
POOL_ASYNC_COL_GBP_INVALID_PAGES BIGINT pool_async_col_gbp_invalid_pages - Asynchronous group buffer pool invalid data pages
POOL_ASYNC_COL_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_async_col_gbp_indep_pages_found_in_lbp - Asynchronous group buffer pool column-organized independent data pages found in local buffer pool
POOL_QUEUED_ASYNC_COL_REQS BIGINT pool_queued_async_col_reqs - Column-organized prefetch requests
POOL_QUEUED_ASYNC_TEMP_COL_REQS BIGINT pool_queued_async_temp_col_reqs - Column-organized temporary prefetch requests
POOL_QUEUED_ASYNC_COL_PAGES BIGINT pool_queued_async_col_pages - Column-organized page prefetch requests
POOL_QUEUED_ASYNC_TEMP_COL_PAGES BIGINT pool_queued_async_temp_col_pages - Column-organized page temporary prefetch requests
POOL_FAILED_ASYNC_COL_REQS BIGINT pool_failed_async_col_reqs - Failed column-organized prefetch requests
POOL_FAILED_ASYNC_TEMP_COL_REQS BIGINT pool_failed_async_temp_col_reqs - Failed column-organized temporary prefetch requests
SKIPPED_PREFETCH_COL_P_READS BIGINT skipped_prefetch_col_p_reads - Skipped prefetch column-organized physical reads
SKIPPED_PREFETCH_TEMP_COL_P_READS BIGINT skipped_prefetch_temp_col_p_reads - Skipped prefetch column-organized temporary physical reads
SKIPPED_PREFETCH_UOW_COL_P_READS BIGINT skipped_prefetch_uow_col_p_reads - Skipped prefetch unit of work column-organized physical reads
SKIPPED_PREFETCH_UOW_TEMP_COL_P_READS BIGINT skipped_prefetch_uow_temp_col_p_reads - Skipped prefetch unit of work column-organized temporary physical reads
BP_PAGES_LEFT_TO_REMOVE BIGINT bp_pages_left_to_remove - Number of Pages Left to Remove
BP_TBSP_USE_COUNT BIGINT bp_tbsp_use_count - Number of Table Spaces Mapped to Buffer Pool
POOL_DATA_CACHING_TIER_L_READS BIGINT pool_data_caching_tier_l_reads - pool data caching tier l reads
POOL_INDEX_CACHING_TIER_L_READS BIGINT pool_index_caching_tier_l_reads - pool index caching tier l reads
POOL_XDA_CACHING_TIER_L_READS BIGINT pool_xda_caching_tier_l_reads - pool XML data caching tier l reads
POOL_COL_CACHING_TIER_L_READS BIGINT pool_col_caching_tier_l_reads - pool column data caching tier l reads
POOL_DATA_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_CACHING_TIER_PAGE_READ_TIME BIGINT pool_caching_tier_page_read_time - pool caching tier page read time
POOL_CACHING_TIER_PAGE_WRITE_TIME BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_PAGES_FOUND BIGINT pool_data_caching_tier_pages_found - Buffer pool data caching tier pages found monitor element
POOL_INDEX_CACHING_TIER_PAGES_FOUND BIGINT pool_index_caching_tier_pages_found - Buffer pool index caching tier pages found monitor element
POOL_XDA_CACHING_TIER_PAGES_FOUND BIGINT pool_xda_caching_tier_pages_found - Buffer pool XML data caching tier pages found monitor element
POOL_COL_CACHING_TIER_PAGES_FOUND BIGINT pool_col_caching_tier_pages_found - Buffer pool columnar-type caching tier pages found monitor element
POOL_DATA_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_DATA_CACHING_TIER_READS BIGINT pool_async_data_caching_tier_reads - Buffer pool asynchronous data-type caching tier page reads
POOL_ASYNC_INDEX_CACHING_TIER_READS BIGINT pool_async_index_caching_tier_reads - Buffer pool asynchronous index-type caching tier page reads
POOL_ASYNC_XDA_CACHING_TIER_READS BIGINT pool_async_xda_caching_tier_reads - Buffer pool asynchronous XML data-type caching tier page reads
POOL_ASYNC_COL_CACHING_TIER_READS BIGINT pool_async_col_caching_tier_reads - Buffer pool asynchronous columnar-type caching tier page reads
POOL_ASYNC_DATA_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_ASYNC_INDEX_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_ASYNC_XDA_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_ASYNC_COL_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_ASYNC_DATA_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_ASYNC_INDEX_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_ASYNC_XDA_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_ASYNC_COL_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_ASYNC_DATA_CACHING_TIER_PAGES_FOUND BIGINT pool_async_data_caching_tier_pages_found - Buffer pool asynchronous data caching tier pages found
POOL_ASYNC_INDEX_CACHING_TIER_PAGES_FOUND BIGINT pool_async_index_caching_tier_pages_found - Buffer pool asynchronous index caching tier pages found
POOL_ASYNC_XDA_CACHING_TIER_PAGES_FOUND BIGINT pool_async_xda_caching_tier_pages_found - Buffer pool asynchronous XML caching tier pages found
POOL_ASYNC_COL_CACHING_TIER_PAGES_FOUND BIGINT pool_async_col_caching_tier_pages_found - Buffer pool asynchronous columnar caching tier pages found
POOL_ASYNC_DATA_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_INDEX_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_XDA_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_COL_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_DATA_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_INDEX_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_XDA_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_COL_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
LOB_PREFETCH_WAIT_TIME BIGINT lob_prefetch_wait_time - LOB prefetch wait time
LOB_PREFETCH_REQS BIGINT lob_prefetch_reqs - LOB prefetcher request count
CACHING_TIER_DIRECT_READS BIGINT caching_tier_direct_reads - Direct reads from caching tier monitor element
CACHING_TIER_DIRECT_READ_REQS BIGINT caching_tier_direct_read_reqs - Cache direct read requests monitor element
CACHING_TIER_DIRECT_READ_TIME BIGINT caching_tier_direct_read_time - Cache direct read time monitor element
CACHING_TIER_WRITE_BYTES BIGINT caching_tier_write_bytes - Bytes for write requests to local tier monitor element
CACHING_TIER_WRITE_REQS BIGINT caching_tier_write_reqs - Write requests to local cache monitor element
CACHING_TIER_WRITE_TIME BIGINT caching_tier_write_time - Elapsed time for write requests monitor element
COMPACTION_READ_BYTES BIGINT compaction_read_bytes - Bytes read during compaction monitor element
COMPACTION_READ_REQS BIGINT compaction_read_reqs - Read requests for compaction monitor element
COMPACTION_READ_TIME BIGINT compaction_read_time - Elapsed time to read during compaction monitor element
COMPACTION_WRITE_BYTES BIGINT compaction_write_bytes - Bytes written during compaction monitor element
COMPACTION_WRITE_REQS BIGINT compaction_write_reqs - Write request during compaction monitor element
COMPACTION_WRITE_TIME BIGINT compaction_write_time - Elapsed time to write during compaction monitor element
LOCAL_TIER_WRITE_BYTES BIGINT local_tier_write_bytes - Bytes to write to local tier monitor element
LOCAL_TIER_WRITE_REQS BIGINT local_tier_write_reqs - Write request for local tier monitor element
LOCAL_TIER_WRITE_TIME BIGINT local_tier_write_time - Elapsed time to write monitor element
REMOTE_STORAGE_TIER_WRITE_BYTES BIGINT remote_storage_tier_write_bytes - Bytes to write to remote storage tier monitor element
REMOTE_STORAGE_TIER_WRITE_REQS BIGINT remote_storage_tier_write_reqs - Write requests to remote storage monitor element
REMOTE_STORAGE_TIER_WRITE_TIME BIGINT remote_storage_tier_write_time - Elapsed time to write to remote storage monitor element

Usage notes

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 by using the mon_obj_metrics configuration parameter. By default, metrics collection is enabled.

Example

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 is an example of 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.