Purpose

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. This view shows hit ratios for all buffer pools and for various data types.

Screenshot

Metric shown

Gauge

Data hit%

Data page hit ratio. Formulas for calculating buffer pool hit ratios

= DATA_HIT_NUMERATOR / DATA_HIT_DENOMINATOR

Source:

DATA_HIT_NUMERATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_LBP_PAGES_FOUND - POOL_ASYNC_DATA_LBP_PAGES_FOUND)

DATA_HIT_DENOMINATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS)

Idx hit%

Index page hit ratio. Formulas for calculating buffer pool hit ratios

= IDX_HIT_NUMERATOR / IDX_HIT_DENOMINATOR

Source:

IDX_HIT_NUMERATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND)

IDX_HIT_DENOMINATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS)

Col hit%

Column-organized page hit ratio. Formulas for calculating buffer pool hit ratios

= COL_HIT_NUMERATOR / COL_HIT_DENOMINATOR

Source:

COL_HIT_NUMERATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_COL_LBP_PAGES_FOUND - POOL_ASYNC_COL_LBP_PAGES_FOUND)

COL_HIT_DENOMINATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

Temp hit%

Page hit ratio in temporary table spaces.

= TEMP_HIT_NUMERATOR / TEMP_HIT_DENOMINATOR

Source:

TEMP_HIT_NUMERATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).(SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS))-(SUM(POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_TEMP_XDA_P_READS + POOL_TEMP_COL_P_READS))

TEMP_HIT_DENOMINATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS)

XML hit%

XML storage object (XDA) page hit ratio. Formulas for calculating buffer pool hit ratios

= XDA_HIT_NUMERATOR / XDA_HIT_DENOMINATOR

Source:

XDA_HIT_NUMERATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_XDA_LBP_PAGES_FOUND - POOL_ASYNC_XDA_LBP_PAGES_FOUND)

XDA_HIT_DENOMINATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS)

All hit%

Overall hit ratio. Formulas for calculating buffer pool hit ratios

= ALL_HIT_RATIO_NUMERATOR / ALL_HIT_RATIO_DENOMINATOR

Source:

ALL_HIT_RATIO_NUMERATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_LBP_PAGES_FOUND + POOL_INDEX_LBP_PAGES_FOUND + POOL_XDA_LBP_PAGES_FOUND + POOL_COL_LBP_PAGES_FOUND - POOL_ASYNC_DATA_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_XDA_LBP_PAGES_FOUND - POOL_ASYNC_COL_LBP_PAGES_FOUND)

ALL_HIT_RATIO_DENOMINATOR = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Grid

Logical reads

The number of pages read from the local buffer pool (logical).

Source: MON_GET_BUFFERPOOL(NULL, #MEMBER#).(SUM(POOL_DATA_L_READS) + SUM(POOL_INDEX_L_READS) + SUM(POOL_XDA_L_READS) + SUM(POOL_COL_L_READS + SUM(POOL_TEMP_DATA_L_READS) + SUM(POOL_TEMP_INDEX_L_READS) + SUM(POOL_TEMP_XDA_L_READS) + SUM(POOL_TEMP_COL_L_READS))

Physical reads

The number of pages read in from the table space containers (physical).

Source: MON_GET_BUFFERPOOL(NULL, #MEMBER#).(SUM(POOL_DATA_P_READS) + SUM(POOL_INDEX_P_READS) + SUM(POOL_XDA_P_READS) + SUM(POOL_COL_P_READS) + SUM(POOL_TEMP_DATA_P_READS) + SUM(POOL_TEMP_INDEX_P_READS) + SUM(POOL_TEMP_XDA_P_READS) + SUM(POOL_TEMP_COL_P_READS))

BP writes

The number of times a buffer pool page was physically written to disk.

Source: MON_GET_BUFFERPOOL(NULL, #MEMBER#).(SUM(POOL_DATA_WRITES) + SUM(POOL_INDEX_WRITES) + SUM(POOL_XDA_WRITES) + SUM(POOL_COL_WRITES))

Direct writes

The number of write operations that do not use the buffer pool.

Source: MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(DIRECT_WRITES)

IO type

The ratio of different type of page read and write.

= <IO_TYPE_R / IO_TOTAL>r | <IO_TYPE_W / IO_TOTAL >w | <IO_TYPE_D / IO_TOTAL >d | <IO_TYPE_I / IO_TOTAL >i | <IO_TYPE_T / IO_TOTAL >t IO_TOTAL = IO_TYPE_R + IO_TYPE_W + IO_TYPE_D + IO_TYPE_I + IO_TYPE_T

Source:

IO_TYPE_R = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_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 + DIRECT_READS)

IO_TYPE_W = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES + DIRECT_WRITES)

IO_TYPE_D = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_DATA_L_READS)

IO_TYPE_I = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_INDEX_L_READS)

IO_TYPE_T = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS)

Table

Bufferpool name

The buffer pool identifier for the buffer pool that is being monitored.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).BP_NAME

Actual l_reads

The number of pages read by agents from the buffer pool (logical).

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_L_READS) + SUM(POOL_INDEX_L_READS) + SUM(POOL_XDA_L_READS) + SUM(POOL_COL_L_READS) + SUM(POOL_TEMP_DATA_L_READS) + SUM(POOL_TEMP_INDEX_L_READS) + SUM(POOL_TEMP_XDA_L_READS) + SUM(POOL_TEMP_COL_L_READS))

Actual p_reads

The number of pages read in from the table space containers (physical).

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_P_READS) + SUM(POOL_INDEX_P_READS) + SUM(POOL_XDA_P_READS) + SUM(POOL_COL_P_READS) + SUM(POOL_TEMP_DATA_P_READS) + SUM(POOL_TEMP_INDEX_P_READS) + SUM(POOL_TEMP_XDA_P_READS) + SUM(POOL_TEMP_COL_P_READS))

Hit ratio %

Overall hit ratio. Formulas for calculating buffer pool hit ratios

= (LBP_PAGES_FOUND - ASYNC_LBP_PAGES_FOUND) / L_READS

Source:

LBP_PAGES_FOUND = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_LBP_PAGES_FOUND) + SUM(POOL_INDEX_LBP_PAGES_FOUND) + SUM(POOL_XDA_LBP_PAGES_FOUND) + SUM(POOL_COL_LBP_PAGES_FOUND))

ASYNC_LBP_PAGES_FOUND = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_LBP_PAGES_FOUND) + SUM(POOL_ASYNC_INDEX_LBP_PAGES_FOUND) + SUM(POOL_ASYNC_XDA_LBP_PAGES_FOUND) + SUM(POOL_ASYNC_COL_LBP_PAGES_FOUND))

L_READS = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_L_READS) + SUM(POOL_INDEX_L_READS) + SUM(POOL_XDA_L_READS) + SUM(POOL_COL_L_READS) + SUM(POOL_TEMP_DATA_L_READS) + SUM(POOL_TEMP_INDEX_L_READS) + SUM(POOL_TEMP_XDA_L_READS) + SUM(POOL_TEMP_COL_L_READS))

Async reads %

The percentage of pages which have been asynchronously read from the table space containers (physical).

= ASYNC_READS / P_READS

Source:

ASYNC_READS = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_READS) + SUM(POOL_ASYNC_INDEX_READS) + SUM(POOL_ASYNC_XDA_READS) + SUM(POOL_ASYNC_COL_READS))

P_READS = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_P_READS) + SUM(POOL_INDEX_P_READS) + SUM(POOL_XDA_P_READS) + SUM(POOL_COL_P_READS) + SUM(POOL_TEMP_DATA_P_READS) + SUM(POOL_TEMP_INDEX_P_READS) + SUM(POOL_TEMP_XDA_P_READS) + SUM(POOL_TEMP_COL_P_READS))

Actual bp writes

The number of times a buffer pool page was physically written to disk.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_WRITES) + SUM(POOL_INDEX_WRITES) + SUM(POOL_XDA_WRITES) + SUM(POOL_COL_WRITES))

Actual direct writes

The number of write operations that do not use the buffer pool.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(DIRECT_WRITES)

Actual a_reads

The number of pages which have been asynchronously read from the table space containers (physical).

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_READS) + SUM(POOL_ASYNC_INDEX_READS) + SUM(POOL_ASYNC_XDA_READS) + SUM(POOL_ASYNC_COL_READS))

Async readms

The average time spent reading in data and index pages from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for all types of table spaces.

= ASYNC_READ_TIME / ASYNC_READS

Source:

ASYNC_READ_TIME = MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(POOL_ASYNC_READ_TIME)

ASYNC_READS = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_READS) + SUM(POOL_ASYNC_INDEX_READS) + SUM(POOL_ASYNC_XDA_READS) + SUM(POOL_ASYNC_COL_READS))

Actual a_writes

The number of times a page was physically written to disk by either an asynchronous page cleaner, or a prefetcher.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_WRITES) + SUM(POOL_ASYNC_INDEX_WRITES) + SUM(POOL_ASYNC_XDA_WRITES) + SUM(POOL_ASYNC_COL_WRITES))

Async wrtms

The average elapsed time for each asynchronous write to complete.

= ASYNC_WRITE_TIME / ASYNC_WRITES

Source:

ASYNC_WRITE_TIME = MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(POOL_ASYNC_WRITE_TIME)

ASYNC_WRITES = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_WRITES) + SUM(POOL_ASYNC_INDEX_WRITES) + SUM(POOL_ASYNC_XDA_WRITES) + SUM(POOL_ASYNC_COL_WRITES))

Async wrt %

The percentage of times a page was physically written to disk by either an asynchronous page cleaner, or a prefetcher.

= ASYNC_WRITES / (POOL_WRITES + DIRECT_WRITES)

Source:

ASYNC_WRITES = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_WRITES) + SUM(POOL_ASYNC_INDEX_WRITES) + SUM(POOL_ASYNC_XDA_WRITES) + SUM(POOL_ASYNC_COL_WRITES))

POOL_WRITES = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_DATA_WRITES) + SUM(POOL_INDEX_WRITES) + SUM(POOL_XDA_WRITES) + SUM(POOL_COL_WRITES))

DIRECT_WRITES = MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(DIRECT_WRITES)

# of dbp

The number of buffer pool that is being monitored.

SELECT COUNT(*) AS NUMBER_OF_DBP
FROM TABLE(
  MON_GET_BUFFERPOOL(
    '', 
    #MEMBER#)) MGB
GROUP BY MGB.BP_NAME

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).BP_NAME

Bp pages

The current buffer pool size.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(BP_CUR_BUFFSZ)

Files closed

The total number of database files closed.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(FILES_CLOSED)

Block IOs

The number of block I/O requests.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(BLOCK_IOS)

Pages blkIOs

The total number of pages read by block I/O into the block area of the buffer pool.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(PAGES_FROM_BLOCK_IOS)

Vectored IOs

The number of vectored I/O requests.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(VECTORED_IOS)

Pages vctIOs

The total number of pages read by vectored I/O into the page area of the buffer pool.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(PAGES_FROM_VECTORED_IOS)

Victim pages

The number of times an agent did not have a preselected victim buffer available.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(POOL_NO_VICTIM_BUFFER)

Unread pref pages

The number of pages that the prefetcher read into the bufferpool that were never used.

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(UNREAD_PREFETCH_PAGES)

%unread pref pages

The percentage of pages that the prefetcher read into the bufferpool that were never used.

= UNREAD_PREFETCH_PAGES / ASYNC_WRITES

Source:

UNREAD_PREFETCH_PAGES = MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(UNREAD_PREFETCH_PAGES)

ASYNC_WRITES = MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_ASYNC_DATA_WRITES) + SUM(POOL_ASYNC_INDEX_WRITES) + SUM(POOL_ASYNC_XDA_WRITES) + SUM(POOL_ASYNC_COL_WRITES))

Xda l_reads

The number of pages for XML storage objects (XDAs) read from the buffer pool (logical).

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_XDA_L_READS) + SUM(POOL_TEMP_XDA_L_READS))

Xda p_reads

The number of pages for XML storage objects (XDAs) which have read from the table space containers (physical).

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).(SUM(POOL_XDA_P_READS) + SUM(POOL_TEMP_XDA_P_READS))

Xda writes

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

Source: MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(POOL_XDA_WRITES)

Avg direct reads

The average number of read operations that do not use the buffer pool per request.

= DIRECT_READS / DIRECT_READ_REQS

Source:

DIRECT_READS = MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(DIRECT_READS)

DIRECT_READ_REQS = MON_GET_BUFFERPOOL(”, #MEMBER#).SUM(DIRECT_READ_REQS)

Statements: Executed SQL (pkg cache)

IO: Prefetchers

IO / Bufferpools

Purpose

Screenshot

Metric shown

Gauge

Data hit%

Idx hit%

Col hit%

Temp hit%

XML hit%

All hit%

Grid

Logical reads

Physical reads

BP writes

Direct writes

IO type

Table

Bufferpool name

Actual l_reads

Actual p_reads

Hit ratio %

Async reads %

Actual bp writes

Actual direct writes

Actual a_reads

Async readms

Actual a_writes

Async wrtms

Async wrt %

# of dbp

Bp pages

Files closed

Block IOs

Pages blkIOs

Vectored IOs

Pages vctIOs

Victim pages

Unread pref pages

%unread pref pages

Xda l_reads

Xda p_reads

Xda writes

Avg direct reads