搜索
DB2培训讲义性能优化入门有关的概念DB2性能优化的三个方面内存CPUI/O内存因素在内存方面，主要是考虑缓冲池(BUFFERPOOL)的使用。缓冲池是一片用来缓冲从磁盘上读取的数据和索引的内存区域，这些数据和索引信息在缓冲池中进行运算后最终还要写回磁盘。缓冲池的页面大小有四种(4K,8K,16K,32K)，分别对应四种不同页面大小的表空间。缓冲池的大小决定了能够从磁盘上缓冲数据的容量大小。当然缓冲池也不是越大越好，缓冲池过大可能会导致连接数据库的时间过长，因为在连接数据库时要为数据库的缓冲池分配内存空间。可以通过计算缓冲池的命中率来评估缓冲池的使用效率：缓冲池命中率=(1-((数据物理读+索引物理读)/(数据逻辑读+索引逻辑读)))*100%，缓冲池命中率越大说明缓冲池的使用效率高。缓冲池命中率太小说明缓冲池太小应当调大。其中的数据物理读，索引物理读以及数据逻辑读和索引逻辑读都可以从缓冲池的快照中获取。内存因素在内存方面要考虑的另外几个重要因素是排序堆(SORTHEAP)，锁列表(LOCKLIST),日志缓冲区(LOGBUFSZ)。排序堆在查询结果带有排序选项而没有相关索引对应时将会被使用，排序堆太小会产生排序溢出(Overflowed),那些在排序堆中装不下的排序数据将会溢出到一个临时表中，这会使性能下降。与SORTHEAP参数相关的是SHEAPTHRES_SHR和SHEAPTHRES，SHEAPTHRES_SHR限制了一个数据库中共享排序的最大内存，SHEAPTHRES限制了私有排序的最大内存。LOCKLIST指的是一个数据库中用来存放锁的内存空间，当这个参数设得过小会导致在锁用光这部分资源后导致锁升级（即多个行锁转化为一个表锁来释放出更多的资源）。这会导致系统的并行性下降，很多应用连接出现挂起，使得系统的性能衰退。内存因素所以尽可能调大LOCKLIST参数，这里需要指出：LOCKLIST指的并不是锁的个数，而是以数据库页为单位的一片内存区域（在32位系统中每个锁需要96个字节，在64位系统中每个锁需要128个字节）。与LOCKLIST参数对应的是MAXLOCKS参数，MAXLOCKS定义的是一个百分数，它指定了一个应用程序所能占用的最大的锁空间占LOCKLIST的比例。日志缓冲区(LOGBUFSZ)指的是日志在写到磁盘以前用于缓冲的一片内存空间，这样可以减少写日志带来的过多的I/O。内存因素从版本9以后DB2推出了一个新特性自调节内存管理器(STMM:SelfTuningMemoryManager),这个特性使得很多内存参数如前面所述的SORTHEAP，LOCKLIST，LOGBUFSZ等进行自动调节，当数据库参数SELF_TUNING_MEM设为ON,这些参数设为AUTOMATIC即可以进行自动调整。这样可以节省很多人工调整的时间。CPU因素关于CPU因素首先是考虑DB2优化器(OPTIMIZER)对访问计划(ACCESSPLAN)的分析与优化。一般来说，一条SQL在执行时首先会被解析，然后进行语义分析，进而重写SQL,优化器会对重写过的SQL进行基于成本的分析最终选择最有效的访问计划。最终生成可执行代码（执行计划）来执行这条语句。查询访问计划的工具有很多，既有图形化工具VisualExplain，也有命令db2exfmt来格式化解释表(Explaintables)中的数据生成ACCESSPLAN。还有命令db2expln查询ACCESSPLAN。CPU因素在DB2里的优化级别分为九级，缺省是第五级，级别越高优化器分析得程度越深。这个级别有数据库配置参数DFT_QUERYOPT决定。并不是级别设得越高性能越好，因为对于一些较为简单的SQL语句，如果优化级别过高那么花在优化SQL上的时间就会过长，而执行时间相对来说很短，有些得不偿失。在选择访问计划时，索引扫描的效率往往会比表扫描要高，所以索引的优化也是值得注意的。正确的建立索引会使查询性能大幅度的提高。CPU因素在DB2中连接(JOIN)分为三种：嵌套循环连接(nest-loopjoin),合并连接(merge-join),散列表连接(hash-join)。一般来说效率最低的是嵌套循环连接，这种连接采用的是笛卡儿集，进行多次循环遍历得到结果。而合并连接和散列表连接只进行一次循环遍历，相对来说效率较高。其中散列表连接可以采用多个等式做为条件而合并连接只能采用单个等式作为条件。但是在有索引扫描的情况下嵌套循环连接效率则更高。当优化级别等于零时，连接只能采用嵌套循环连接，当优化级别大于等于1时，连接可以采用合并连接。当优化级别大于5时连接可以采用散列表连接。散列表连接要求SORTHEAP比较大，因为要为生成散列表准备空间。CPU因素在考虑CPU因素时还要考虑CPUSPEED这个参数，这个参数标明了CPU的运行速度，它会帮助优化器评估最好的访问计划。一般来说这个参数设为-1，优化器将自动计算CPU的速度。另外运用多分区的特性可以把一个数据库分布到多台机器上，这样可以充分利用多台机器的CPU的资源对应用程序的事务进行并行处理，从而提高数据库的性能。I/O因素关于I/O因素要考虑以下几个方面：首先是磁盘的I/O,为了能够最大化磁盘的I/O可以把数据，索引以及日志分别放在不同的硬盘上。因为在一个事务中数据和索引可能需要同时访问，而在事务提交时，数据和日志要同时写入磁盘，而且有可能索引也要同步维护，所以将它们放在不同的硬盘上可以使它们的读写并行运行，从而不致使磁盘成为瓶颈。同时选择数据库管理表空间(DMS)要比系统管理表空间(SMS)性能要好，因为读写SMS需要经过操作系统的cache再到缓冲池，而可以采用裸设备的DMS则不需要。但是DMS相对SMS来说维护起来较麻烦。I/O因素其次要考虑的是日志文件的大小，当数据库在写事务日志时当一个日志文件写满后会转向另外一个日志文件，这种日志文件的切换会造成操作系统上的开销。所以应当尽量将日志文件大小（LOGFILSIZ）设得大一些，这样可以减少日志文件切换的次数。但是日志文件过大难免会造成一些空间的浪费。I/O因素同时也要考虑到隔离级别的因素，在DB2中隔离级别分成4级：可重复的读，读稳定性，游标稳定性和未提交的读。这四种级别逐个降低。越高的隔离级别越能保证数据完整性，但却会降低并发性，所以应当综合权衡后做出决定。隔离级别可以通过如下命令来改变：CHANGEISOLATIONTO=CS|RR|RS|UR在连接方面还要考虑到代理和连接的关系，这也会影响到数据库的并发性。最后要考虑的还是关于多分区的特性。在多分区数据库中，一个请求首先传到协调分区，然后由协调分区将请求细分成多个部分发送到其他分区，这样数据可以在各个分区进行并行读写，实现I/O最大化。性能优化工具在DB2中有很多和性能优化相关的工具和命令，下面简单地介绍几种：SNAPSHOTDB2PDRUNSTATSREORGDB2DARTDB2SUPPORT性能优化工具SNAPSHOT:这是DB2获取数据库信息快照的一种方法。它能够获取在数据库中关于缓冲池，锁，排序以及SQL等等信息。DBA可以通过获取这些信息来对数据库中的各组件进行评估来分析问题的瓶颈。DB2PD:这个命令是用来分析数据库的当前状态，它带有很多参数。可以用来分析应用程序，代理，内存块，缓冲池，日志及锁状态等信息。RUNSTATS:这个命令是用来收集数据库中数据的最新统计信息，并更新到系统表中。更新统计信息将会促使优化器选择更加符合实际的高效的访问计划，从而提高工作效率。REORG:这个命令用来重新整理数据库中数据和索引的碎片，使其在物理上可以得以按一定规则排列，这样可以加快检索的速度。性能优化工具DB2DART:这个命令是一个数据库的分析和报告工具，它用来检查表空间，索引以及数据库结构的正确性，分析在性能问题上的一些原因。DB2SUPPORT:这个命令用来收集DB2和操作系统的所有相关信息并生成一个压缩文件，可传送给优化人员进行分析。DB2性能问题分类与分析思路DML性能问题：查询优化DML(DataManipulationLanguage)包括了查询，增加，删除和更新纪录等操作。首先看一下查询的性能问题，在查询一张表或多张表的联合查询时有时反应时间会比较长，这使得用户难以忍受。针对这种问题，可以通过下述方法来分析：在查询的连接或条件子句中的相关字段是否加了索引。察看缓冲池的大小，缓冲池太小会造成很多数据不能读到缓冲池而直接从硬盘上读取，造成很大的瓶颈。另一方面关于缓冲池预取的设置，一般能将预取大小(PREFETCHSIZE)设定为区段大小与容器个数的积，这样可以最大利用到预取的并行性。在查询中涉及到orderby字句时，如果排序的字段没有设置索引那么排序将会用到内存中的排序堆(sortheap)。如果排序堆过小会造成排序溢出到硬盘上(Overflowed)造成性能衰退。DML性能问题：查询优化同时还要考虑到RUNSTATS/REORG因素。RUNSTATS命令可以更新表中的统计信息。当表中的数据经过频繁的增删改后其相应的统计信息会发生变化，而优化器选择执行计划的时候是根据这种统计信息来计算的，所以运行RUNSTATS此时显得尤为重要。REORG可以整理数据存储的物理结构，也能减少数据扫描的时间，提高查询的性能。从存储方面应当注意的是选取裸设备的DMS要比SMS性能要好，因为它少了一层文件系统的缓冲而直接访问缓冲池。学会使用optimizefornrows子句，它可以提高前面n条记录的显示速度。这样可以使用户能够先快速查看这n条记录，然后再看其他纪录。减少了用户的等待时间。DML性能问题：查询优化针对复杂查询时可以将数据库配置参数DFT_QUERYOPT(缺省查询优化类)的值设得高一些（7或9），针对简单查询可以将它设得低一些(3或5)，因为设置越高优化器所作的分析就越深入，耗费在生成计划上的时间就越多。针对C/S结构的查询可以将查询语句写在服务器端生成存储过程来减少数据的网络传输以及客户端的压力。而经过编译的存储过程执行得更加高效。DML性能问题：查询优化还要考虑到隔离级别与锁的因素，隔离级别越高越能保证数据的完整性，但同时会减弱并发性。这一点需要权衡需求而定。网络因素也不可忽视，将数据库服务器参数RQRIOBLK设为65534可以相应地提高网络吞吐量。（缺省值32767）最后需要考虑的是数据库的结构，在某些情况下，在某些表中增加一些冗余字段虽然牺牲了一些空间和维护成本，但是在查询时可以减少很多连接操作，这样可以大大提高查询性能。就是用空间换取时间。DML性能问题：增删改优化首先是索引因素，在做增删改时数据库会对表中的索引做相应的修改。这会消耗一定的资源，所以在保证数据完整性的前提下可以先将索引删除，待到增删改结束后再重建这些索引。这也会节省一些时间。将索引和数据放在不同的硬盘上也可以增加写操作的并行性。其次要考虑日志因素，在数据写操作的同时，数据库系统也在维护着事务日志，所以应尽量减少日志维护的代价。将autocommit设为false，可以减少提交的次数（同时也减少了写日志的次数）。增大LOGBUFSZ,LOGFILSZ可以减少刷新日志的次数以及日志文件切换的次数。或者将表的属性改为”ACTIVATENOTLOGGEDINITIALLY”,这样可以屏蔽表的日志操作，以提高写操作的性能，但是失去事务日志的表的数据很难修复，这一点需要权衡。DML性能问题：增删改优化将日志和数据分别放在不同的硬盘上也可以增加写操作的并行性。在插入记录时采用APPENDMODE可以消除DB2寻找表中间的空余空间的时间而直接插到表尾，从而提高插入的性能。还要考虑到约束和触发器的影响，在写操作时应当尽量避免表中有约束和触发器。在保证数据完整性的前提下可在频繁大批量写操作时先将约束或触发器去除，完毕后重建。和查询一样，写操作同样要考虑到隔