IFCID 239 - Buffer Manager Accounting Data
This topic shows detailed information about "Record Trace - IFCID 239 - Buffer Manager Accounting Data".
Record trace - IFCID 239 - Buffer Manager Accounting Data
The field labels shown in the following sample layout of "Record Trace - IFCID 239 - Buffer Manager Accounting Data" are described in the following section.
BUFFER MANAGER ACCOUNTING DATA
BUFFER POOL ID 1 SYNCHRON. READ 5
GETPAGES 2 SEQ. PREFETCH 6
GETPAGES FAILED 14 LIST PREFETCH 8
BUFFER UPDATES 3 DYNAMIC PREFETCH 9
SYNCHRON.WRITE 7 PAGES READ ASYN-PAR 15- BUFFER POOL ID
The buffer pool ID used by this thread.
Field Name: QBACPID
- SYNCHRON. READ
The number of synchronous read I/O operations. DB2 increments this counter for each media manager synchronous physical read. Asynchronous I/O requests are not counted.
Field Name: QBACRIO
- GETPAGES
The number of Getpage requests. This counter is incremented by successful Getpage requests for queries processed in parallel for each thread and for all successful and unsuccessful Getpage requests for queries that are not processed in parallel.
Background and Tuning Information
Reducing the number of Getpages can improve DB2 performance by reducing the number of synchronous page reads. With fewer Getpages, the requested page is more likely to be returned from the buffer pool. CPU usage is also reduced.
Check the ratio of Getpages to SQL DML statements, as a rule of
thumb,
try and keep this ratio below six for a typical online
transaction SQL. 
You might need to modify the database and query design, for example:
- Add indexes to tables to reduce the number of pages scanned.
- Reassess the number of tables used and denormalize them,
if necessary.
As an example, a large table with many columns can result in several pages being fetched to satisfy a simple query requesting just a few columns. Splitting such a table into several tables with fewer columns, tailored to queries, will result in fewer pages returned for each query.
- Use correlated rather than non-correlated queries to force the use of an index.
Field Name: QBACGET
This is an exception field.
- SEQ. PREFETCH
- The number of SEQUENTIAL PREFETCH requests.
This is incremented for each PREFETCH request.
Each request can result in an I/O read.
If it does, up to 64 pages can be read for SQL and
up to 128 pages for utilities.
For SQL, depending on the buffer pool size, a request does not
result
in an I/O if all the requested pages are already in the buffer
pool.
DB2 can use sequential prefetch if the data is accessed in sequential order even though sequential prefetch was not requested at bind time. This is known as sequential detection and is not included in the sequential prefetch count. Sequential detection is included in dynamic prefetch requests field.
Background and Tuning Information
Table space scans and nonmatching index scans generally use sequential prefetch.
Field Name: QBACSEQ
This is an exception field.
- GETPAGES FAILED
The number of times that a page requested for a query processed in parallel was unavailable because an I/O was in progress or the page was not found in the buffer pool. The agent does not wait, but control returns to the agent.
This counter is used only when queries are processed in parallel.
Background and Tuning Information
If this value is close to zero, most pages are already in the buffer pool, and wait time for synchronous I/O is small.
This counter can be high when, for example, there is a cluster index scan and the data is not truly clustered by the index key. In this instance, data pages are not accessed in their true order and the cluster ratio is not valid. Use the Runstats utility to update it.
The value of this field is also used to determine how many sequential prefetches of one page were scheduled.
Field Name: QBACNGT
- LIST PREFETCH
The number of LIST PREFETCH requests.
Special Considerations:- List prefetch allows DB2 to access data pages efficiently even if the needed data pages are not contiguous. It can be used with single index access and is always used with multiple index access.
- List prefetch is always used to access data from the inner table during a hybrid join.
- Data pages are read in quantities equal to the sequential prefetch quantity, which depends on the buffer pool size and is usually 64 pages.
- During bind time DB2 does not use list prefetch if the estimated number of RIDs to be processed would take more than 50% of the RID pool. During execution time, list prefetch processing terminates if DB2 detects that more than 25% of the rows in the table need to be accessed. If list prefetch is terminated, it is indicated in IFCID 125.
Field Name: QBACLPF
This is an exception field.
- BUFFER UPDATES
The number of times a buffer update occurs. This is incremented every time a page is updated and is ready to be written to DASD. If the same page is updated twice, for example, the number is incremented by 2.
This number is kept for all types of pages including data pages and work-file pages.
Background and Tuning Information
A nonzero value indicates any of the following activities:- SQL INSERT, UPDATE, or DELETE
- Merge scan join
- Internal sort activity on the work files
Field Name: QBACSWS
This is an exception field.
- DYNAMIC PREFETCH
The number of (dynamic) PREFETCH requests. This is triggered by sequential detection. This includes prefetches for segmented table spaces.
Background and Tuning Information
Dynamic prefetch is typically used for a SELECT or UPDATE that is run repeatedly, accessing the index for each access.
If sequential prefetch, list prefetch, and dynamic prefetch reads have large values, check whether the access path can be improved.
Field Name: QBACDPF
This is an exception field.
- SYNCHRON.WRITE
The number of immediate (synchronous) write I/O operations.
Background and Tuning Information
Although an immediate write is rare, a small nonzero value is acceptable. A large value indicates that the system needs tuning.
Field Name: QBACIMW
This is an exception field.
- PAGES READ ASYN-PAR
The number of asynchronous pages read by prefetch that the agent triggered.
Background and Tuning Information
This is used to determine the buffer pool hit ratio: (Getpage requests - Synchronous reads - Asynchronous pages read) / Getpage requests.
Field Name: QBACSIO
This is an exception field.