缓冲池大小的设置
通过使用 "get snapshot for all on dbname" 可以为数据库上的每个缓冲池生成一个快照。 清单 6展示了那样一个快照：
清单 6. 示例缓冲池快照
Bufferpool Snapshot
Bufferpool name = IBMDEFAULTBP
Database name = SAMPLE
Database path = C:\\DB2\\NODE0000\\SQL00002\\
Input database alias = SAMPLE
Snapshot timestamp = 02-20-2004 06:24:45.991065
Buffer pool data logical reads = 370
Buffer pool data physical reads = 54
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 3
Buffer pool index logical reads = 221
Buffer pool index physical reads = 94
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 287
Total buffer pool write time (ms) = 1
Asynchronous pool data page reads = 9
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 3
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 86
Direct writes = 4
Direct read requests = 14
Direct write requests = 2
Direct reads elapsed time (ms) = 247
Direct write elapsed time (ms) = 56
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 3
Pages from vectored IOs = 9
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 4
Alter bufferpool information:
Pages left to remove = 0
Current size = 250
Post-alter size = 250
|
为了判断一个缓冲池的效率，需要计算它的缓冲池命中率（BPHR）。您所需的重要信息在上面已经用粗体标出来了。如果可能的话，一个理想的 BPHR 在某些地方应超过 90%。公式如下：
BPHR (%) = (1 - (("Buffer pool data physical reads" + "Buffer pool index physical reads") /
("Buffer pool data logical reads" + "Buffer pool index logical reads"))) * 100
|
在 IBMDEFAULTBP 缓冲池的 以上快照中，我们可以这样来计算 BPHR：
= (1-((54 + 94) / (370 + 221))) * 100
= (1-(148 / 591)) * 100
= (1- 0.2504) * 100
= 74.96
|
在这种情况下，BPHR 大约等于 75%。当前，缓冲池只有 250 * 4KB 页（1MB）。增加该缓冲池的大小，看看 BPHR 是否会随之增加，这样做是值得的。如果 BPHR 还是比较低，那么可能就需要像 创建缓冲池和 创建表空间这两节中讨论的那样重新设计逻辑布局。
基于块的缓冲池的效率
如果是一个基于块的缓冲池，并且看到 "Block IOs" 的值较低，那么应考虑修改缓冲池，增加 NUMBLOCKPAGES 的大小。如果这时看到 "Block IOs" 的值更大了，则可以考虑将 NUMBLOCKPAGES 再增大一些。如果结果适得其反，则应减小 NUMBLOCKPAGES 的大小。
DBM 和 DB 配置
DB2 有几十个配置参数。很多参数都是由 DB2 自动配置的，而其他一些参数都有它们的缺省值，这些缺省值都被证明在大多数环境中能够发挥得很好。接下来，我们只描述那些常常需要另外进行配置的参数。
有些数据库管理器（即实例）配置参数可以在线更改（立即生效），而另一些参数则要求对实例实行再循环（即 DB2STOP 之后接着又是 DB2START）。对于数据库配置参数也是一样。有些参数的更改可以立即生效，而另一些参数则要求先停止数据库，再重新激活数据库。关于每种配置参数的文档都规定了参数是否可以在线配置。
数据库管理器和数据库配置文件的基本管理命令如 表 3所示：
表 3. 数据库管理器和数据库配置管理
| 命令 | 描述 |
| GET DBM CFG [SHOW DETAIL] | 列出数据库管理器配置文件中的当前值 |
| UPDATE DBM CFG USING config_param value | 将指定的数据库管理器配置参数设置成指定的值 |
| GET DB CFG FOR db_name[SHOW DETAIL] | 列出某个特定数据库的配置文件中的当前值 |
| UPDATE DB CFG FOR db_nameUSING config_param value | 将指定的数据库管理器配置参数设置成指定的值 |
当您对一个配置参数作了更改时，就可以用下面的 DB2 CLP 命令查看该设置是否立即生效（在线）：
GET DBM CFG SHOW DETAIL
GET DB CFG FOR dbname SHOW DETAIL
例如，在接下来的情况中，MAX_QUERYDEGREE 和 MAXTOTFILOP 分别增加到了 3 和 19000。如果参数是在线配置的，则 Delayed Value 跟 Current Value 应该是一样的。否则，就需要重新启动实例，或者重新激活数据库。
清单 7. Show Details 实例
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Description Parameter Current Value Delayed Value
-------------------------------------------------------------------------------------------
Maximum query degree of parallelism (MAX_QUERYDEGREE) = 3 3
Maximum total of files open (MAXTOTFILOP) = 16000 19000
|
下面的配置参数中，有些是从共享内存分配空间的，所以应该记住 OS 的限制（在 前面已讨论）。您必须确保没有过度分配内存。如果过度分配内存，就会导致操作系统发生换页（page），这对于性能来说是灾难性的。
DB2 v8 Documentation:
- Reference ==> Configuration parameters ==> Database manager
- Reference ==> Configuration parameters ==> Database
清单 8和 清单 9显示了一个数据库管理器和数据库快照的示例。顺着右边（顺带提一下），可以看到能根据输出进行调优的配置参数。
清单 8. 数据库管理器快照
Database Manager Snapshot
Node name =
Node type = Enterprise Server Edition with
local and remote clients
Instance name = DB2
Number of database partitions in DB2 instance = 1
Database manager status = Active
Product name = DB2 v8.1.4.341
Service level = s031027 (WR21326)
Private Sort heap allocated = 0 (SHEAPTHRES
Private Sort heap high water mark = 1024
Post threshold sorts = 0 and
Piped sorts requested = 0
Piped sorts accepted = 0 SORTHEAP)
Start Database Manager timestamp = 02-17-2004 14:24:37.107003
Last reset timestamp =
Snapshot timestamp = 02-20-2004 06:19:53.272049
Remote connections to db manager = 0 (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)
Remote connections executing in db manager = 0
Local connections = 1 (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)
Local connections executing in db manager = 0
Active local databases = 1 (NUMDB)
High water mark for agents registered = 8 (MAXAGENTS)
High water mark for agents waiting for a token = 0
Agents registered = 8 (MAXAGENTS)
Agents waiting for a token = 0
Idle agents = 6 (NUM_POOLAGENTS and NUM_INITAGENTS)
Committed private Memory (Bytes) = 46645248
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 02-20-2004 06:18:57.403336
Lock Information (LOCK) = ON 02-20-2004 06:18:57.403338
Sorting Information (SORT) = ON 02-20-2004 06:18:57.403339
SQL Statement Information (STATEMENT) = ON 02-20-2004 06:18:57.403333
Table Activity Information (TABLE) = ON 02-20-2004 06:18:57.403335
Take Timestamp Information (TIMESTAMP) = ON 02-17-2004 14:24:37.107003
Unit of Work Information (UOW) = ON 02-20-2004 06:18:57.403328
Agents assigned from pool = 26 (NUM_POOLAGENTS and NUM_INITAGENTS)
Agents created from empty pool = 10 (NUM_POOLAGENTS and NUM_INITAGENTS)
Agents stolen from another application = 0 (MAXAGENTS)
High water mark for coordinating agents = 8
Max agents overflow = 0 (MAXAGENTS)
Hash joins after heap threshold exceeded = 0
Total number of gateway connections = 0
Current number of gateway connections = 0
Gateway connections waiting for host reply = 0
Gateway connections waiting for client request = 0
Gateway connection pool agents stolen = 0
Node FCM information corresponds to = 2
Free FCM buffers = 4093
Free FCM buffers low water mark = 4087 (FCM_NUM_BUFFERS)
Free FCM message anchors = 1279
Free FCM message anchors low water mark = 1276
Free FCM connection entries = 1280
Free FCM connection entries low water mark = 1276
Free FCM request blocks = 2031
Free FCM request blocks low water mark = 2026
Number of FCM nodes = 4
Node Total Buffers Total Buffers Connection (FCM_NUM_BUFFERS)
Number Sent Received Status
----------- ------------------ ------------------ -----------------
0 282 275 Active
1 51 48 Active
2 0 0 Active
3 1 1 Active
Memory usage for database manager:
Memory Pool Type = Backup/Restore/Util Heap (UTIL_HEAP_SZ*)
Current size (bytes) = 16384
High water mark (bytes) = 16384
Maximum size allowed (bytes) = 20660224
Memory Pool Type = Package Cache Heap (PCKCACHESZ*)
Current size (bytes) = 327680
High water mark (bytes) = 327680
Maximum size allowed (bytes) = 1071644672
Memory Pool Type = Catalog Cache Heap (CATALOGCACHE_SZ*)
Current size (bytes) = 81920
High water mark (bytes) = 81920
Maximum size allowed (bytes) = 1071644672
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 1179648
High water mark (bytes) = 1179648
Maximum size allowed (bytes) = 1071644672
Memory Pool Type = Lock Manager Heap (LOCKLIST*)
Current size (bytes) = 278528
High water mark (bytes) = 278528
Maximum size allowed (bytes) = 425984
Memory Pool Type = Database Heap (DBHEAP*)
Current size (bytes) = 3342336
High water mark (bytes) = 3342336
Maximum size allowed (bytes) = 6275072
Memory Pool Type = Database Monitor Heap (MON_HEAP_SZ)
Current size (bytes) = 180224
High water mark (bytes) = 425984
Maximum size allowed (bytes) = 442368
Memory Pool Type = Other Memory
Current size (bytes) = 8060928
High water mark (bytes) = 8159232
Maximum size allowed (bytes) = 1071644672
|
快照总是显示 Current size (bytes) = High water mark (bytes)，因为内存是在数据库激活的时候分配的。
