兩隻小虎,一隻豬

Buffer Pool 是Memory 中的一塊暫存區塊,用來暫時儲存讀取或修改的Page(Data Page and Index Page),
Buffer pool 的用途是為了提升Database 的效能, 一般而言資料從記憶體中讀取的速度會遠快於從磁碟中讀取
因此Buffer pool hit ratio 的大小通常會影響DB 的效能, hit ratio 愈高代表著資料直接從記憶體讀取的次數愈多
而不必浪費時間從較慢的磁碟讀取資料,減少I/O的時間

■ Query Buffer Pool Size
當DB2 建立時,預設會建立IBMDEFAULTBP Buffer Pool, 可以從SYSCAT.BUFFERPOOLS中查詢目前DB使用了哪些Buffer Pool

SELECT * FROM SYSCAT.BUFFERPOOLS

NPAGES = -1, indicates that the buffer pool is to use the BUFFPAGE parameter as the number of buffer pool pages
NPAGES = -2, self tuning is enabled for a buffer pool
When self tuning is disabled, the NPAGES field will be set to the buffer pool's current size.

C:\> db2 conect to sample 
C:\> db2 get db cfg | grep BUFFPAGE
 Buffer pool size (pages)                     (BUFFPAGE) = 1000

■ Alter Buffer Pool Size

C:\> db2  alter bufferpool IBMDEFAULTBP size -1
C:\> db2  update db cfg for sample using BUFFPAGE bigger_value

■ Calculate Buffer Pool Hit Ratio
(1 - ((buffer pool data physical reads + buffer pool index physical reads) / (buffer pool data logical reads + pool index logical reads)) ) * 100%

C:\> db2 update monitor switches using bufferpool on
C:\> db2 get monitor switches
C:\> db2 reset monitor all

-- run your application --
C:\> db2 get snapshot for all databases >; snap.out
C:\> db2 get snapshot for dbm >;>; snap.out
C:\> db2 get snapshot for all bufferpools >;>; snap.out
C:\> db2 reset monitor all
C:\> db2 terminate

-- Related lines from a sample of bufferpool snapshots --
Buffer pool data logical reads = 702033
Buffer pool data physical reads = 0
Buffer pool data writes = 414
Buffer pool index logical reads = 168255
Buffer pool index physical reads = 0