BP调优是批量调优以及交易调优的一个重要手段，从bP的性能我们可以明显看出程序的性能瓶颈在哪里，因此BP的作用也是不可轻视的。
Buffer pools are areas of storage above the 2 GB bar that temporarily store pages of table spaces or indexes.（location）
When a program accesses a row of a table, DB2 places the page containing that row in a buffer. When a program changes a row of a table, DB2 must write the data in the buffer back to disk, typically either at a checkpoint or a write threshold.（工作原理）
They way buffer pools work is fairly simple by design, but tuning these simple
operations that make a significant impact to the performance of our applications. The
data manager issues get page requests to the buffer manager who—hopefully—can
satisfy the request from the buffer pool instead of having to retrieve the page from
disk. We often trade CPU for I/O in order to manage buffer pools efficiently. Buffer
pools are maintained by subsystem, but individual buffer pool design and use should
be by object granularity and, in some cases, also by application.
Initial buffer pool definitions are set at installation/migration are often hard to
configure at this time because the application process against the objects is usually
not detailed at installation. But regardless of what is set at installation time, we can
use the ALTER command any time after the install to add and delete buffer pools,
resize the buffer pools, or change any of the thresholds.
The buffer pool definitions
are stored in the boot strap data set (BSDS) and we can move objects between buffer
pools via an ALTER INDEX/TABLESPACE and a subsequent START/STOP
command of the object.（BP的定义是在BSDS中的，这一点要知道）
We can have up to 80 virtual buffer pools. This allows for up to:（BP的最大个数是80个，针对一个subsystem）
■ 50 4K page buffer pools
■ 10 32K page buffer pools
■ 10 8K page buffer pools
■ 10 16K page buffer pools
在V10中的增强：
Buffer pool allocation （BP的分配）
BPs are created in DB2’s DBM1 address space, above the 64-bit bar
? Created at first data set Open（这一点要知道，也就是一个触发条件吧）
? Buffer pool deleted when all referenced data sets are closed
? DB2 10 allocates and extends piecemeal on demand
? Avoids real storage penalty of over-sizing a buffer pool if PGFIX(YES) is used（比较重要的BP，但是不能太大，最好实存也是比较大的）
AUTOSIZE (YES)
? WLM may direct DB2 to increase or decrease VPSIZE by up to 25% based on system needs
? If DB2 is restarted, the 25% is relative to the size when DB2 is restarted
BP中比较重要的参数：
Buffer pool parameters
How many buffer pools are needed and how to assign objects to buffer pools Buffer pool size - VPSIZE
Virtual Pool Sequential Threshold – VPSEQT (default 80%)
Horizontal Deferred Write Queue Threshold – DWQT (default 30%)
Vertical Deferred Write Queue Threshold – VDWQT (default 5%)
To page fix or not to page fix – PGFIX (YES or NO) (default NO)
Page steal method – PGSTEAL (LRU, FIFO, or NONE) (default LRU)
Auto size – AUTOSIZE (YES or NO) (default NO)
V10中pagesize的选择因素：
Page size selection
4K pages usually optimize the buffer hit ratio
Special considerations
The page needs to be large enough to store the max row size
DB2 can store at most 255 rows on a page
When rows are large, a large page helps minimize DASD space
consumption
On average, each page wastes a half-row of space
E.g. If you average 10 rows per page, you waste 5% of the space
Index considerations
A large page is necessary for index compression
A large page minimizes index splits
A large page reduces the number of index levels
A large page (8K or 16K) provides better sequential performance
With DB2 10, a large page size helps enable inline LOBs, which may help improve I/O and CPU performance significantly
关于DB2的prefetch我们前面已经介绍过，就不在介绍
BP中Page的分类以及LRU的处理过程：
Page classification and LRU processing
Pages in a BP are classified as either random or sequential in order to protect OLTP from the effects of queries Pages read from DASD
A page read in via synchronous I/O is classified as random
A page read in via any form of prefetch I/O is classified as sequential
Pages that already exist in the BP from previous work
A random page is never re-classified as sequential
Random page 与sequential page虽然都是使用LRU方式进行处理，但是二者还是略有不同，主要体现在：
If the length of the SLRU chain is greater than VPSEQT, then DB2 always steals the oldest buffer from the SLRU chain. Otherwise it
steals the oldest buffer on the LRU chain.
When a buffer is referenced, it becomes the “youngest” or “most recently used” buffer on the chain
DB2 has a mechanism to prevent sequentially accessed data from monopolizing the BP and pushing out useful
random pages random rage在BP中所占的比例是很小的
? Maintains two chains
? LRU with all pages (random and sequential)
? SLRU with only the sequential pages
? Steals from the LRU chain until VPSEQT is reached, and then steals preferentially from the SLRU chain
General recommendations
? Set VPSEQT to 99% for the sort workfile BP, 90% for other workfile usage
通常dynamic prefetch ，list prefetch的page都是sequential page
When dynamic prefetch is used, Sequoia will classify the Page-sequential?pages as sequential instead of random (unlike all prior DB2 versions)
Consequently dynamic prefetch cannot overwhelm the buffer pool
When list prefetch is used for disorganized index scans or RID list
scans, the Getpages will be classified as sequential
Consequently these uses of list prefetch cannot overwhelm the buffer pool
在DB2 V10中sequential 更加细化，主要分类两类：
Sparse skip sequential with DB2 10
DB2 10 does synchronous I/O for sparse pages
Dense skip sequential
DB2 prefetches all of the pages, 32 pages per I/O
When the Getpages “skip” pages, DB2 uses buffers and reads the skipped pages from DASD
Buffer Hit Ratio = --------------------------------------
#Getpages
Possible causes of a negative hit ratio
If a page is prefetched that is “skipped”, the BP hit ratio may be negative,
because #Asynch Pages may exceed #Getpages
? This is not a problem, and increasing VPSIZE won’t change a thing
Sequential prefetch
Starting with DB2 9, sequential prefetch is only used for table scans
? Not good for skip sequential, or for a mixture of sequential and random access
Uses prefetch trigger pages
? For example, every 64th page triggers a prefetch for 64 pages
Dynamic prefetch – sequential detection
In DB2 9, some queries switched from Sequential Prefetch to Dynamic
Prefetch
Improved in DB2 10 – Row Level Sequential Detection
? The first prefetch done by a Getpage after 5 out of 8 sequential rows are
scanned. (DB2 9 only counted pages.)
DB2 10 also introduced a progressive prefetch quantity.
? The first prefetch reads 32K, then 64K, then 128K thereafter
Prefetches sequential pages and does synchronous I/O for random pages
/**************************************************************************************
Deferred Writes
VDWQT (Vertical Deferred Write Queue Threshold) based on the data set level as a % of VPSIZE or number of buffers
?DB2 schedules a write for up to 128 pages, sorts them in sequence, and writes them out in at least 4 I/Os. A page distance of 180 pages is applied to each I/O to avoid high page latch contention, since buffers are latched during I/O.
DWQT (horizontal Deferred Write Queue) at the BP level
/**************************************************************************************
