讲解DB2数据库性能调整的十个实用技巧

ablespace, 至少两个BP_RAND and BP_SEQ. 随机存取的Tablespaces 应该有一个buffer pool来应付随机的objectives,这就是 BP_RAND. 顺序存取的Tablespaces (with asynchronous prefetch I/O) 应该建立一个buffer pool给sequential objectives, BP_SEQ. 也可以建立其它的buffer pools，这要根据应用来说。比如可以建立一个足够大的buffer pool 来存放热点经常存取的数据。有时候需要为大的table建立单一的buffer pool.

太小的buffer pool会导致大量的、不必要的物理I/O。太大的biffer pool有可能会产生系统paging，增加不必要的CPU管理内存开销。

buffer pool的大与小是相对的，一个系统的buffer pool大小应该"合适的"！当达到diminishing return达到时，就是合适的。如果不是使用自动工具，应该有条理的测试buffer pool性能，比如命中率，I/O次数，物理I/O读的比率，直到达到合适状态。当然，应用是变化的，所以最优状态不是不边的，也是要定期的评估。

3. TABLESPACE ANALYSIS

tablespace snapshot对理解哪些数据被访问和怎么访问的有很大的价值。

db2 "get snapshot for tablespaces on DBNAME"

对每一个tablespace，要注意：

What is the average read time (ms)?

What is the average write time (ms)?

What percentage of the physical I/O is asynchronous (prefetched) vs. synchronous (random)?

What are the buffer pool hit ratios for each tablespace?

How many physical pages are being read each minute?

How many physical and logical pages are being read for each transaction?

对所有的tablespaces,注意：

Which tablespaces have the slowest read and write times? Why?

Containers on slow disks? Are container sizes unequal?

Are the access attributes, asynchronous versus synchronous access, consistent with expectations?

Randomly read tables should have randomly read tablespaces, meaning high synchronous read percentages, usually higher buffer pool hit ratios, and lower physical I/O rates.

对每个tablespace，要注意Prefetch size是Extent size的倍数。如果必要，可以修改tablespace的prefetch size。

显示tablespace信息：db2 "list tablespaces show detail"

显示containers 信息：db2 "list tablespace containers for N show detail"

4. TABLE ACCESS

要查出来每次查询读出的row，

1） db2 "get snapshot for database on DBNAME"

看到多少交易发生，the sum of Commit statements attempted + Rollback statements attempted

2） db2 "get snapshot for tables on DBNAME"

区分出交易读出的row。divide the number of rows read by the number of transactions (RowsPerTX).OLTP一般每次交易从一个table里面读出20 row,如果发现一个交易能读出成百上千行数据，表扫描就可能出现，可能需要看看index是否需要。简单情况下是运行runstats收集信息。

Sample output from "get snapshot for tables on DBNAME" follows:

Snapshot timestamp = 09-25-2000 4:47:09.970811

Database name= DGIDB

Database path= /fs/inst1/inst1/NODE0000/SQL00001/

Input database alias= DGIDB

Number of accessed tables= 8

Table List

Table Schema= INST1

Table Name= DGI_SALES_ LOGS_TB

Table Type= User

Rows Written= 0

Rows Read= 98857

Overflows= 0

Page Reorgs= 0