db2优化入门

从3个方面考虑 内存,cpu和I/O
1.内存:主要就是在缓冲池的使用(buffpool)
可以通过计算缓冲池的命中率来评估缓冲池的使用效率：缓冲池命中率 =(1-(( 数据物理读 + 索引物理读 )/( 数据逻辑读 + 索引逻辑读 ))) *100%，缓冲池命中率越大说明缓冲池的使用效率高。缓冲池命中率太小说明缓冲池太小应当调大。其中的数据物理读，索引物理读以及数据逻辑读和索引逻辑读都可以从缓冲池的快照中获取。

从版本 9 以后 DB2 推出了一个新特性自调节内存管理器 (STMM: Self Tuning Memory Manager), 这个特性使得很多内存参数如前面所述的 SORTHEAP，LOCKLIST，LOGBUFSZ 等进行自动调节，当数据库参数 SELF_TUNING_MEM 设为 ON, 这些参数设为 AUTOMATIC 即可以进行自动调整。这样可以节省很多人工调整的时间。

2.cup:
一般来说，一条 SQL 在执行时首先会被解析，然后进行语义分析，进而重写 SQL, 优化器会对重写过的 SQL 进行基于成本的分析最终选择最有效的访问计划。最终生成可执行代码（执行计划）来执行这条语句

在 DB2 里的优化级别分为九级，缺省是第五级，级别越高优化器分析得程度越深。这个级别有数据库配置参数 DFT_QUERYOPT 决定。并不是级别设得越高性能越好，因为对于一些较为简单的 SQL 语句，如果优化级别过高那么花在优化 SQL 上的时间就会过长，而执行时间相对来说很短，有些得不偿失。在选择访问计划时，索引扫描的效率往往会比表扫描要高，所以索引的优化也是值得注意的。正确的建立索引会使查询性能大幅度的提高

3.I/O:
首先是磁盘的 I/O, 为了能够最大化磁盘的 I/O 可以把数据，索引以及日志分别放在不同的硬盘上。

同时选择数据库管理表空间 (DMS) 要比系统管理表空间 (SMS) 性能要好，因为读写 SMS 需要经过操作系统的 cache 再到缓冲池，而可以采用裸设备的 DMS 则不需要。但是 DMS 相对 SMS 来说维护起来较麻烦。

其次要考虑的是日志文件的大小，当数据库在写事务日志时当一个日志文件写满后会转向另外一个日志文件，这种日志文件的切换会造成操作系统上的开销。所以应当尽量将日志文件大小（LOGFILSIZ）设得大一些，这样可以减少日志文件切换的次数。但是日志文件过大难免会造成一些空间的浪费。

同时也要考虑到隔离级别的因素，在 DB2 中隔离级别分成 4 级：可重复的读，读稳定性，游标稳定性和未提交的读。这四种级别逐个降低。越高的隔离级别越能保证数据完整性，但却会降低并发性，所以应当综合权衡后做出决定。隔离级别可以通过如下命令来改变：
CHANGE ISOLATION TO=CS|RR|RS|UR

最后要考虑的还是关于多分区的特性。在多分区数据库中，一个请求首先传到协调分区，然后由协调分区将请求细分成多个部分发送到其他分区，这样数据可以在各个分区进行并行读写，实现 I/O 最大化
=========================================================

具体到DML语言的优化:
1.查询优化
(1)在查询的连接或条件子句中的相关字段是否加了索引。 ( 关于 SQL 的优化可以参见 SQL 优化相关文章，本文不再赘述 ) 。
(2)察看缓冲池的大小，缓冲池太小会造成很多数据不能读到缓冲池而直接从硬盘上读取，造成很大的瓶颈。另一方面关于缓冲池预取的设置，一般能将预取大小 (PREFETCHSIZE) 设定为区段大小与容器个数的积，这样可以最大利用到预取的并行性。
(3)在查询中涉及到 order by 字句时，如果排序的字段没有设置索引那么排序将会用到内存中的排序堆 (sortheap) 。如果排序堆过小会造成排序溢出到硬盘上 (Overflowed) 造成性能衰退。
(4)同时还要考虑到 RUNSTATS/REORG 因素。 RUNSTATS 命令可以更新表中的统计信息。当表中的数据经过频繁的增删改后其相应的统计信息会发生变化，而优化器选择执行计划的时候是根据这种统计信息来计算的，所以运行 RUNSTATS 此时显得尤为重要。 REORG 可以整理数据存储的物理结构，也能减少数据扫描的时间，提高查询的性能。
(5)从存储方面应当注意的是选取裸设备的 DMS 要比 SMS 性能要好，因为它少了一层文件系统的缓冲而直接访问缓冲池。
(6)学会使用 optimize for n rows 子句，它可以提高前面 n 条记录的显示速度。这样可以使用户能够先快速查看这 n 条记录，然后再看其他纪录。减少了用户的等待时间。
(7)物化查询表 (MQT) 也是提高查询性能的一种手段，它可以将经常用到的查询结果集存储到一张中间表中，在查询时减少了数据检索的时间。
(8)在架构上采用 MPP 或 SMP 也是提高查询或写操作性能的手段。
(9)针对复杂查询时可以将数据库配置参数 DFT_QUERYOPT( 缺省查询优化类 ) 的值设得高一些（7 或 9），针对简单查询可以将它设得低一些 (3 或 5)，因为设置越高优化器所作的分析就越深入，耗费在生成计划上的时间就越多。
(10)针对 C/S 结构的查询可以将查询语句写在服务器端生成存储过程来减少数据的网络传输以及客户端的压力。而经过编译的存储过程执行得更加高效。
(11)还要考虑到隔离级别与锁的因素，隔离级别越高越能保证数据的完整性，但同时会减弱并发性。这一点需要权衡需求而定。
(12)网络因素也不可忽视，将数据库服务器参数 RQRIOBLK 设为 65534 可以相应地提高网络吞吐量。（缺省值 32767）
(13)最后需要考虑的是数据库的结构，在某些情况下，在某些表中增加一些冗余字段虽然牺牲了一些空间和维护成本，但是在查询时可以减少很多连接操作，这样可以大大提高查询性能。就是用空间换取时间。

2.增加.修改,删除优化
(1)首先是索引因素，在做增删改时数据库会对表中的索引做相应的修改。这会消耗一定的资源，所以在保证数据完整性的前提下可以先将索引删除，待到增删改结束后再重建这些索引。这也会节省一些时间。将索引和数据放在不同的硬盘上也可以增加写操作的并行性。
(2)其次要考虑日志因素，在数据写操作的同时，数据库系统也在维护着事务日志，所以应尽量减少日志维护的代价。将 auto commit 设为 false，可以减少提交的次数（同时也减少了写日志的次数）。增大 LOGBUFSZ,LOGFILSZ 可以减少刷新日志的次数以及日志文件切换的次数。或者将表的属性改为” ACTIVATE NOT LOGGED INITIALLY ” , 这样可以屏蔽表的日志操作，以提高写操作的性能，但是失去事务日志的表的数据很难修复，这一点需要权衡。
(3)将日志和数据分别放在不同的硬盘上也可以增加写操作的并行性。
(4)在插入记录时采用 APPEND MODE 可以消除 DB2 寻找表中间的空余空间的时间而直接插到表尾，从而提高插入的性能。
(5)关于并行性的因素，采用 MPP 模式可以使用并行处理的方式增加写操作的性能。将容器分散在不同的硬盘上也可以增加写操作的性能。
(6)还要考虑到约束和触发器的影响，在写操作时应当尽量避免表中有约束和触发器。在保证数据完整性的前提下可在频繁大批量写操作时先将约束或触发器去除，完毕后重建。
(7)和查询一样，写操作同样要考虑到隔离级别和锁的因素（参见查询优化部分）。
(8)在 insert 语句中包括多行可以减少客户机 - 服务器通信次数，提高插入性能。如：insert into table1 values (1, ’ a ’ ),(2, ’ b ’ ),(3, ’ c ’ ) 。
(9)还有一个需要考虑的因素是 DB2 V95 在 UNIX 上的采用线程模型，在操作系统中的开销变小，使得写操作性能要比之前的 DB2 的版本要好。

DB2 实用程序的性能优化

1.先来看一下如何提高备份操作的性能：
提高数据库配置参数 UTIL_HEAP_SZ 的大小，这个内存区域用来为备份和恢复操作提供缓冲。
减少整库备份，多采用表空间备份需要的表空间。
减少完全备份，多采用增量备份或 DELTA 备份。
增加备份命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。
增加备份命令中的 BUFFER 参数值。
增加备份的目标目录，最好能将多个目录放在不同的硬盘上，这样可以增加备份的并行程度。

2.如何提高恢复操作的性能：
和备份操作一样，需要增大数据库配置参数 UTIL_HEAP_SZ 的大小。
增加恢复命令中的 BUFFERS 参数值。
增加恢复命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。
容器分布于不同的硬盘上也可以使恢复操作加快（提高并行性）。
采用 SMP 模式来激活多代理来增加恢复操作的并行性。

3.提高导入操作（import）的性能 :
import 操作类似 insert 操作，因此很多方法可以参见 insert 的调优步骤。
添加 compound=x 选项可使导入操作批量进行而减少了网络的通信量。
增加 COMMITCOUNT 的值已减少 LOG 的 I/O 次数。
启用缓冲区插入，对 db2uimpm 程序包使用 INSERT BUF 选项重新绑定到数据库。在 import 以前执行命令: db2 bind db2uimpm.bnd insert buf

4.提高导出操作 (export) 的性能：
Export 操作类似 select 操作，因此很多方法可以参见 select 的调优步骤。
将 export 操作导出的文件放在与数据和日志不同的硬盘上以减少 I/O 的竞争。

5.提高载入操作 (load) 的性能：load 操作中日志的写操作比 import 要少，所以 load 的性能比 import 要好很多，下面还是看看如何更好地提高 load 的性能。
在多分区环境下，db2 load 会进行并行装载，性能会大幅度提高。
添加 buffer 参数可以增加装载过程中的缓存空间，提高性能。

6.并发连接时的性能考虑
一般来说在连接数较少情况下，db2 的性能会比较稳定。因为这时连接的应用所产生的请求比 db2 代理池中所能产生的协调代理少，这时基本上能够满足每一个请求都能够被及时的协调代理所响应处理。 在连接集中器激活（MAX_CONNECTIONS > MAX_COORDAGENTS）的情况下，如果连接数超过了协调代理，这时连接所过来的请求就会进入队列等候协调代理服务，并发的连接数提高了，但是某些连接的性能就会显著下降。此时应当考虑激活分区间并行 (SMP) 或多分区（MPP）特性来增加 I/O 的并行性以及多个 CPU 的并行运算。