The BP_READ_IO administrative view returns bufferpool read performance information. This view can be used to look at each bufferpool to see how effective the prefetchers are.
The schema is SYSIBMADM.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT SUBSTR(BP_NAME, 1, 15) AS BP_NAME, TOTAL_PHYSICAL_READS,
AVERAGE_READ_TIME_MS, DBPARTITIONNUM
FROM SYSIBMADM.BP_READ_IO ORDER BY DBPARTITIONNUM
BP_NAME TOTAL_PHYSICAL_READS AVERAGE_READ_TIME_MS DBPARTITIONNUM
--------------- -------------------- -------------------- --------------
IBMDEFAULTBP 811 4 0
IBMSYSTEMBP4K 0 - 0
IBMSYSTEMBP8K 0 - 0
IBMSYSTEMBP16K 0 - 0
IBMDEFAULTBP 34 0 1
IBMSYSTEMBP4K 0 - 1
IBMSYSTEMBP8K 0 - 1
IBMDEFAULTBP 34 0 2
IBMSYSTEMBP4K 0 - 2
IBMSYSTEMBP8K 0 - 2
10 record(s) selected.
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | Date and time the report was generated. |
BP_NAME | VARCHAR(128) | bp_name - Buffer pool name |
TOTAL_PHYSICAL_READS | BIGINT | Total physical reads. |
AVERAGE_READ_TIME_MS | BIGINT | Average read time in milliseconds. |
TOTAL_ASYNC_READS | BIGINT | Total asynchronous reads. |
AVERAGE_ASYNC_READ_TIME_MS | BIGINT | Average asynchronous read time in milliseconds. |
TOTAL_SYNC_READS | BIGINT | Total synchronous reads. |
AVERAGE_SYNC_READ_TIME_MS | BIGINT | Average synchronous read time in milliseconds. |
PERCENT_SYNC_READS | DECIMAL(5,2) | Percentage of pages read synchronously without prefetching. If many of the applications are reading data synchronously without prefetching then the system might not be tuned optimally. |
ASYNC_NOT_READ_PERCENT | DECIMAL(5,2) | Percentage of pages read asynchronously from disk, but never accessed by a query. If too many pages are read asynchronously from disk into the bufferpool, but no query ever accesses those pages, then the prefetching might degrade performance. |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |