DB2 Version 9.7 for Linux, UNIX, and Windows

pool_xda_l_reads - Buffer pool XDA data logical reads monitor element

Indicates the number of data pages for XML storage objects (XDAs) which have been requested from the buffer pool (logical) for regular and large table spaces.

Table 1. Table Function Monitoring Information
Table Function	Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics	DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics	REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics	REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics	DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics	DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics	REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics	REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document)	ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache	ACTIVITY METRICS BASE

Table 2. Snapshot Monitoring Information
Snapshot Level	Logical Data Grouping	Monitor Switch
Database	dbase	Buffer Pool
Table Space	tablespace	Buffer Pool
Buffer Pool	bufferpool	Buffer Pool
Application	appl	Buffer Pool
Application	stmt	Buffer Pool
Dynamic SQL	dynsql	Buffer Pool, Statement

For snapshot monitoring, this counter can be reset.

Table 3. Event Monitoring Information
Event Type	Logical Data Grouping	Monitor Switch
Activities	event_activity (reported in the details_xml document)	ACTIVITY METRICS BASE
Activities	event_activitymetrics	ACTIVITY METRICS BASE
Statistics	event_scstats (reported in the metrics document)	REQUEST METRICS BASE
Statistics	event_wlstats (reported in the metrics document)	REQUEST METRICS BASE
Unit of work	Reported in the system_metrics document.	-
Database	event_db	-
Tablespaces	event_tablespace	-
Connection	event_conn	-
Statement	event_stmt	-
Activities	event_activity	Buffer Pool, Statement
Package cache	Reported in the activity_metrics document.	ACTIVITY METRICS BASE
Statistics	event_scmetrics*	REQUEST METRICS BASE
Statistics	event_wlmetrics*	REQUEST METRICS BASE
* When returned as part of this logical data group, this element reflects the change in value of this metric since the last statistics collection or database activation, whichever was more recent.

Usage

This count includes accesses to data that is:

Already in the buffer pool when the database manager needs to process the page
Read into the buffer pool before the database manager can process the page.

Use the pool_xda_l_reads, pool_xda_p_reads, pool_data_l_reads, and pool_data_p_reads monitor elements to calculate the data page hit ratio for the buffer pool by using the following formula:

 
 1 - ((pool_data_p_reads + pool_xda_p_reads)
    / (pool_data_l_reads + pool_xda_l_reads))

The overall buffer pool hit ratio can be calculated as follows:

     1 - ((pool_data_p_reads + pool_xda_p_reads + 
	pool_index_p_reads + pool_temp_data_p_reads 
	+ pool_temp_xda_p_reads + pool_temp_index_p_reads )
     	/ (pool_data_l_reads + pool_xda_l_reads + pool_index_l_reads + 
     	pool_temp_data_l_reads + pool_temp_xda_l_reads 
     	+ pool_temp_index_l_reads )) * 100%

This calculation takes into account all of the pages (index and data) that are cached by the buffer pool.

Increasing buffer pool size will generally improve the hit ratio, but you will reach a point of diminishing return. Ideally, if you could allocate a buffer pool large enough to store your entire database, then once the system is up and running you would get a hit ratio of 100%. However, this is unrealistic in most cases. The significance of the hit ratio depends on the size of your data, and the way it is accessed. A very large database where data is accessed evenly would have a poor hit ratio. There is little you can do with very large tables. In such case, you would focus your attention on smaller, frequently accessed tables, and on the indexes.