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.

Important: The BP_HITRATIO administrative view is deprecated and has been replaced by the MON_BP_UTILIZATION administrative view - Retrieve metrics for bufferpools.
Note: This administrative view works only in Db2® environments without the IBM® Db2 pureScale® Feature. For information about calculating hit ratios in a Db2 pureScale environment, see Calculating buffer pool hit ratios in a Db2 pureScale environment.

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
  • DBADM authority
  • SQLADM authority
  • ACCESSCTRL authority
  • SECADM authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
  • SYSMON
  • SYSCTRL
  • SYSMAINT
  • SYSADM

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

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 is an example of 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 Timestamp when the report was requested.
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 dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element