DB2 Version 9.7 for Linux, UNIX, and Windows

BP_HITRATIO administrative view - Retrieve bufferpool hit ratio information

The BP_HITRATIO administrative view returns bufferpool hit ratios, including total hit ratio, data hit ratio, XDA hit ratio and index hit ratio, for all bufferpools and all database partitions in the currently connected database.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the BP_HITRATIO administrative view
CONTROL privilege on the BP_HITRATIO administrative view
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Retrieve a report for all bufferpools in the connected database.

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME, 
   TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, 
   INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT, DBPARTITIONNUM 
   FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM

The following example is a sample output for this query.

DB_NAME  BP_NAME        TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT ...
-------- -------------- ----------------------- ---------------------- ...
TEST     IBMDEFAULTBP                     63.09                  68.94 ...
TEST     IBMSYSTEMBP4K                        -                      - ...
TEST     IBMSYSTEMBP8K                        -                      - ...
TEST     IBMSYSTEMBP16K                       -                      - ...
TEST     IBMSYSTEMBP32K                       -                      - ...

Output for this query (continued).

... INDEX_HIT_RATIO_PERCENT XDA_HIT_RATIO_PERCENT DBPARTITIONNUM
... ----------------------- --------------------- --------------
...                   43.20                     -              0
...                       -                     -              0
...                       -                     -              0
...                       -                     -              0
...                       -                     -              0

Usage notes

The ratio of physical reads to total reads gives the hit ratio for the bufferpool. The lower the hit ratio, the more the data is being read from disk rather than the cached buffer pool which can be a more costly operation.

Information returned

Table 1. Information returned by the BP_HITRATIO administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
DB_NAME	VARCHAR(128)	db_name - Database name
BP_NAME	VARCHAR(128)	bp_name - Buffer pool name
TOTAL_LOGICAL_READS	BIGINT	Total logical reads (index, XDA and data) in the bufferpool.
TOTAL_PHYSICAL_READS	BIGINT	Total physical reads (index, XDA and data) in the bufferpool.
TOTAL_HIT_RATIO_PERCENT	DECIMAL(5,2)	Total hit ratio (index, XDA and data reads).
DATA_LOGICAL_READS	BIGINT	pool_data_l_reads - Buffer pool data logical reads
DATA_PHYSICAL_READS	BIGINT	pool_data_p_reads - Buffer pool data physical reads
DATA_HIT_RATIO_PERCENT	DECIMAL(5,2)	Data hit ratio.
INDEX_LOGICAL_READS	BIGINT	pool_index_l_reads - Buffer pool index logical reads
INDEX_PHYSICAL_READS	BIGINT	pool_index_p_reads - Buffer pool index physical reads
INDEX_HIT_RATIO_PERCENT	DECIMAL(5,2)	Index hit ratio.
XDA_LOGICAL_READS	BIGINT	pool_xda_l_reads - Buffer Pool XDA Data Logical Reads
XDA_PHYSICAL_READS	BIGINT	pool_xda_p_reads - Buffer Pool XDA Data Physical Reads
XDA_HIT_RATIO_PERCENT	DECIMAL(5,2)	Auxiliary storage objects hit ratio.
DBPARTITIONNUM	SMALLINT	The database partition from which the data for the row was retrieved.