[转载] 让数据库飞起来 十大DB2优化技巧

转载于：http://sd.csdn.net/a/20100819/278402.html

?

IBM DB2数据库专业论坛 版主hchao曾发表过一篇博文 ，为我们分享了DB2调优的十大技巧，现转载于此，全文如下：

为了帮助 DB2 DBA 避免性能灾难并获得高性能，我为我们的客户、用户和 DB2 专家同行总结了一套故障诊断流程。以下详细说明在 Unix、Windows 和 OS/2 环境下使用 DB2 UDB 的电子商务 OLTP 应用程序的 10 条最重要的性能改善技巧 - 并在本文的结束部分作出 总结。

一、 监视开关

确保已经打开监视开关。如果它们没有打开，您将无法获取您需要的性能信息。要打开该监视开关，请发出以下命令：

1 db2 "update monitor switches using

2 lock ON sort ON bufferpool ON uow ON

3 table ON statement ON"

二、代理程序

确保有足够的 DB2 代理程序来处理工作负载。要找出代理程序的信息，请发出命令：

db2 "get snapshot for database manager"

并查找以下行：

1 High water mark for agents registered = 7

2 High water mark for agents waiting for a token = 0

3 Agents registered= 7

4 Agents waiting for a token= 0

5 Idle agents= 5

6 Agents assigned from pool= 158

7 Agents created from empty Pool = 7

8 Agents stolen from another application= 0

9 High water mark for coordinating agents= 7

10 Max agents overflow= 0

如果您发现Agents waiting for a token或Agents stolen from another application不为 0，那么请增加对数据库管理器可用的代理程序数(MAXAGENTS 和/或 MAX_COORDAGENTS取适用者)。

三、最大打开的文件数

DB2 在操作系统资源的约束下尽量做一个“优秀公民”。它的一个“优秀公民”的行动就是给在任何时刻打开文件的最大数设置一个上限。数据库配置参数 MAXFILOP约束 DB2 能够同时打开的文件最大数量。当打开的文件数达到此数量时，DB2 将开始不断地关闭和打开它的表空间文件(包括裸设备)。不断地打开和关闭文件减缓了 SQL 响应时间并耗费了 CPU 周期。要查明 DB2 是否正在关闭文件，请发出以下命令：

db2 "get snapshot for database on DBNAME"

并查找以下的行：

Database files closed = 0

如果上述参数的值不为 0，那么增加MAXFILOP的值直到不断打开和关闭文件的状态停埂。

db2 "update db cfg for DBNAME using MAXFILOP N"

四、锁

LOCKTIMEOUT的缺省值是 -1，这意味着将没有锁超时(对 OLTP 应用程序，这种情况可能会是灾难性的)。尽管如此，我还是经常发现许多 DB2 用户用LOCKTIMEOUT= -1。将LOCKTIMEOUT设置为很短的时间值，例如 10 或 15 秒。在锁上等待过长时间会在锁上产生雪崩效应。

首先，用以下命令检查LOCKTIMEOUT的值：

db2 "get db cfg for DBNAME"

并查找包含以下文本的行：

Lock timeout (sec) (LOCKTIMEOUT) = -1

如果值是 -1，考虑使用以下命令将它更改为 15 秒(一定要首先询问应用程序开发者或您的供应商以确保应用程序能够处理锁超时)：

db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"

您同时应该监视锁等待的数量、锁等待时间和正在使用锁列表内存(lock list memory)的量。请发出以下命令：

db2 "get snapshot for database on DBNAME"

查找以下行：

1 Locks held currently= 0

2 Lock waits= 0

3 Time database waited on locks (ms)= 0

4 Lock list memory in use (Bytes)= 576

5 Deadlocks detected= 0

6 Lock escalations= 0

7 Exclusive lock escalations= 0

8 Agents currently waiting on locks= 0

9 Lock Timeouts= 0

如果Lock list memory in use (Bytes)超过所定义LOCKLIST大小的 50%，那么在LOCKLIST数据库配置中增加 4k 页的数量。

五、临时表空间

为了改善 DB2 执行并行 I/O 和提高使用TEMPSPACE的排序、散列连接(hash join)和其它数据库操作的性能，临时表空间至少应该在三个不同的磁盘驱动器上拥有三个容器。

要想知道您的临时表空间具有多少容器，请发出以下命令：

db2 "list tablespaces show detail"

查找与以下示例类似的TEMPSPACE表空间定义：

1 Tablespace ID= 1

2 Name= TEMPSPACE1

3 Type= System managed space

4 Contents= Temporary data

5 State= 0x0000

6 Detailed explanation: Normal

7 Total pages= 1

8 Useable pages= 1

9 Used pages= 1

10 Free pages= Not applicable

11 High water mark (pages)= Not applicable

12 Page size (bytes)= 4096

13 Extent size (pages)= 32

14 Prefetch size (pages)= 96

15 Number of containers= 3

注意Number of containers的值是 3，而且Prefetch size是Extent size的三倍。为了得到最佳的并行 I/O 性能，重要的是Prefetch size为Extent size的倍数。这个倍数应该等于容器的个数。

要查找容器的定义，请发出以下命令：

db2 "list tablespace containers for 1 show detail"

指的是tablespace ID #1，它是刚才所给出的示例中的TEMPSPACE1。

六、内存排序

OLTP 应用程序不应该执行大的排序。它们在 CPU、I/O 和所用时间方面的成本极高，而且将使任何 OLTP 应用程序慢下来。因此，256 个 4K 页(1MB)的缺省SORTHEAP大小(1MB)应该是足够了。您也应该知道排序溢出的数量和每个事务