DB2优化（简易版）

核心提示：预备—monitors ON db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON" 打开监视开关,获取需要的性能信息 最简单而最见成效的—Bufferpool

　　预备—monitors ON
db2 "update monitor switches using 
lock ON sort ON bufferpool ON uow ON 
table ON statement ON"
打开监视开关,获取需要的性能信息
最简单而最见成效的—Bufferpool
缓冲池是内存中的一块存储区域,用于临时读入和更改数据库页(包含表行或索引项)。缓冲池的用途是为了提高数据库系统的性能。从内存访问数据要比从磁盘访问数据快得多。因此,数据库管理器需要从磁盘读取或写入磁盘的次数越少,性能就越好。对一个或多个缓冲池进行配置之所以是调优的最重要方面,是因为连接至数据库的应用程序的大多数数据(不包括大对象和长字段数据)操作都在缓冲池中进行。
缺省情况下,应用程序使用缓冲池 IBMDEFAULTBP,它是在创建数据库时创建的。当 SYSCAT.BUFFERPOOLS 目录表中该缓冲池的 NPAGES 值为 -1 时,DB2 数据库配置参数 BUFFPAGE 控制着缓冲池的大小。否则会忽略 BUFFPAGE 参数,并且用 NPAGES 参数所指定的页数创建缓冲池。
建议对于仅使用一个缓冲池的应用程序,将 NPAGES 更改成 -1,这样 BUFFPAGE 就可以控制该缓冲池的大小。这使得更新和报告缓冲池大小以及其它 DB2 数据库配置参数变得更加方便。
确保可以使用数据库配置中的 BUFFPAGE 参数来控制缓冲池大小之后,将该参数设置成合适的值。根据数据库的大小和应用程序的性质将该参数设置成一个合理的大值,这种做法很安全。通常,该参数的缺省值非常小,可能满足不了要求。
db2 "get snapshot for all bufferpools"
在数据库快照或缓冲池快照的快照输出中,查找下列"logical reads"和"physical reads",这样就可以计算出缓冲池命中率,它可以帮助调优缓冲池:
缓冲池命中率表明数据库管理器不需要从磁盘装入页(即该页已经在缓冲池中)就能处理页请求的时间百分比。缓冲池的命中率越高,使用磁盘 I/O 的频率就越低。按如下计算缓冲池命中率:
(1 - ((buffer pool data physical reads + buffer pool index physical reads) /
(buffer pool data logical reads + pool index logical reads))
) * 100%
这个计算考虑了缓冲池高速缓存的所有页(索引和数据)。理想情况下,该比率应当超过 95%,并尽可能接近 100%。要提高缓冲池命中率,请尝试下面这些方法:
增加缓冲池大小。 
考虑分配多个缓冲池,如果可能的话,为每个经常被访问的大表所属的表空间分配一个缓冲池,为一组小表分配一个缓冲池,然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。 
如果已分配的内存不能帮助提高性能,那么请避免给缓冲池分配过多的内存。应当根据取自测试环境的快照信息来决定缓冲池的大小。
太小的缓冲池会产生过多的、不必要的物理 I/O。太大的缓冲池使系统处在操作系统页面调度的风险中并消耗不必要的 CPU 周期来管理过度分配的内存。正好合适的缓冲池大小就在"太小"和"太大"之间的某个平衡点上。适当的大小存在于回报将要开始减少的点上。
获得最佳性能的—SQL
一条糟糕的 SQL 语句会彻底破坏一切。一个相对简单的 SQL 语句也能够搞糟一个调整得很好的数据库和机器。对于很多这些语句,天底下(或在文件中)没有 DB2 UDB 配置参数能够纠正因错误的 SQL 语句导致的高成本的情况。
更糟糕的是,DBA 常常受到种种束缚:不能更改 SQL(可能是因为它是应用程序供应商提供的)。这给 DBA 只留下三条路可走:
1. 更改或添加索引
2. 更改群集
3. 更改目录统计信息
健壮的应用程序由成千上万条不同的 SQL 语句组成。这些语句执行的频率随应用程序的功能和日常的业务需要的不同而不同。SQL 语句的实际成本是它执行一次的成本乘以它执行的次数。
每个 DBA 所面临的重大的任务是,识别具有最高"实际成本"的语句的挑战,并且减少这些语句的成本。
通过本机 DB2 Explain 实用程序、一些第三方供应商提供的工具或 DB2 UDB SQL Event Monitor 数据,可以计算出执行一次 SQL 语句所用的资源成本。但是语句执行频率只能通过仔细和耗时地分析 DB2 UDB SQL Event Monitor 的数据来了解。
最佳性能不仅需要排除高成本 SQL 语句,而且需要确保相应的物理基础结构是适当的。当所有的调节旋钮都设置得恰到好处、内存被有效地分配到池和堆而且 I/O 均匀地分配到各个磁盘时,才可得到最佳性能。
不可遗漏的—Lock
这些与锁相关的控制都是数据库配置参数: 
LOCKLIST 表明分配给锁列表的存储容量。每个数据库都有一个锁列表,锁列表包含了并发连接到该数据库的所有应用程序所持有的锁。锁定是数据库管理器用来控制多个应用程序并发访问数据库中数据的机制。行和表都可以被锁定。根据对象是否还持有其它锁,每把锁需要 32 个或 64 个字节的锁列表: 
需要 64 个字节来持有某个对象上的锁,在这个对象上,没有持有其它锁。 
需要 32 个字节来记录某个对象上的锁,在这个对象上,已经持有一个锁。
MAXLOCKS 定义了应用程序持有的锁列表的百分比,在数据库管理器执行锁升级之前必须填充该锁列表。当一个应用程序所使用的锁列表百分比达到 MAXLOCKS 时,数据库管理器会升级这些锁,这意味着用表锁代替行锁,从而减少列表中锁的数量。当任何一个应用程序所持有的锁数量达到整个锁列表大小的这个百分比时,对该应用程序所持有的锁进行锁升级。如果锁列表用完了空间,那么也会发生锁升级。数据库管理器通过查看应用程序的锁列表并查找行锁最多的表,来决定对哪些锁进行升级。如果用一个表锁替换这些行锁,将不再会超出 MAXLOCKS 值,那么锁升级就会停止。否则,锁升级就会一直进行,直到所持有的锁列表百分比低于 MAXLOCKS。MAXLOCKS 参数乘以 MAXAPPLS 参数不能小于 100。
虽然升级过程本身并不用花很多时间,但是锁定整个表(相对于锁定个别行)降低了并发性,而且数据库的整体性能可能会由于对受锁升级影响的表的后续访问而降低。
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"
如果 Lock list memory in use (Bytes) 超过所定义 LOCKLIST 大小的 50%,那么在 LOCKLIST 数据库配置中增加 4k 页的数量。