The following thresholds are a percent of unavailable pages to total pages, where unavailable means either updated or in use by a process.
SPTH
The Sequential Prefetch Threshold (SPTH) is checked before a prefetch operation is scheduled and during buffer allocation for a previously scheduled prefetch. If the SPTH threshold is exceeded, prefetch will not be scheduled or will be canceled.
PREFETCH DISABLED – NO BUFFER will be incremented every time a virtual buffer pool reaches 90 percent of active unavailable pages, disabling sequential prefetch. This value should always be 0. If this value is not 0, you are probably experiencing degradation in performance due to all prefetch being disabled. To eliminate this degradation, increase the size of the buffer pool or have more frequent commits in the application programs to free pages in the buffer pool because this puts the pages on the write queues.
DMTH
The Data Manager Threshold (DMTH) occurs when 95 percent of all buffer pages are in use. The Buffer Manager requests all threads to release any possible pages immediately. This action occurs by setting GETPAGE/RELPAGE processing by row instead of page. After a GETPAGE and single row is processed, a RELPAGE is issued. This sequence causes CPU to become high for objects in that buffer pool and I/O sensitive transaction can suffer. This result can occur if the buffer pool is too small. You can observe when this occurs by seeing a non-zero value in the DM THRESHOLD REACHED indicator on a statistics report. This is checked every time that a page is read or updated. If this threshold is not reached, DB2 accesses the page in the virtual pool once for each page. If this threshold has been reached, DB2 accesses the page in the virtual pool once for every ROW on the page that is retrieved or updated.
IWTH
The Immediate Write Threshold (IWTH) is reached when 97.5 percent of the buffers are in use. Reaching this threshold causes synchronous writes to begin, which presents a performance problem. For example, if there are 100 rows in page and there are 100 updates, 100 synchronous writes occur, one by one, for each row. Synchronous writes are not concurrent with SQL, but serial, so the application will be waiting while the write occurs including 100 log writes that must occur first. This activity causes large increases in I/O time. It is not recorded explicitly in a statistic report, but DB2 will appear to be hung and you will see synchronous writes begin to occur when this threshold is reached. Some monitors will send exception messages to the console when synchronous writes occur and refers to them as IWTH reached; however, not all synchronous writes are caused by this threshold being reached. This is simply being reported incorrectly.
Note: Some performance reports, the IWTH counter can also be incremented when dirty pages are on the write queue have been re-referenced, which has caused a synchronous I/O before the page could be used by the new process. This threshold counter can also be incremented if more than two checkpoints occur before an updated page is written because this action causes a synchronous I/O to write out the page.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|