DB2数据库管理最佳实践笔记-16优化器与性能调优

优化器

优化器是关系数据库的核心，相当于汽车的发动机，DB2在业界最值得称道的功能之一就是其强大的优化器。大多数情况下，用户不必干预优化器的执行，而且DB2优化器总是足够聪明地选择最佳访问计划，当然前提是数据库的统计信息被及时收集。

统计信息---每个数据库对象状态的信息，可以使用SYSSTAT模式的视图访问： db2 \

不同数据对象类型的统计信息使用不同的视图访问。

相比Visual Explain工具，作者强烈推荐使用db2exfmt工具，生成文本访问计划。相比前者生成图形化的访问计划，后者包含更多重要的信息。 例： db2sampl

db2 connect to sample

#创建访问计划表

db2 -tvf C:\\Program Files\\IBM\\SQLLIB\\MISC\\EXPLAIN.DDL>c:\\db2\\a.txt

db2 \empno,firstnme,lastname from employee,department where employee.workdept =department.deptno and mgrno='000060'\

#打开访问计划选项

db2 set current explain mode explain

#然后执行以下sql，此时会生成针对该sql的访问计划被存储在EXPPLAN.DDL文件中

db2 \empno,firstnme,lastname from employee,department where employee.workdept =department.deptno and mgrno='000060'\

#关闭访问计划选项

db2 set current explain mode explain no

#使用以下工具将这些信息汇总到一个文件中

db2exfmt -d SAMPLE -g TIC -w -l -n % -s % -# 0 -o prod_sample_exfmt.txt

接下来分析此文件！ 系统信息 系统设置

数据库中对优化器起作用的参数：

Parllelism = None 代表单分区 = Interparallel 代表多分区 = Intraparallel 代表分区内并行

CPU速度：当前cpu速度的一个估算值，该值并不代表任何有意义的单位，而是DB2在优化其中使用CPU速度作为一个参数，该数值在实例启动时自动计算。

Comm Speed：通信速度，在多分区系统中默认为100，可以根据需求调节。

Buffer Pool Size：系统中缓冲池总页数的和，注意DB2优化器在计算访问计划时，并不知道有哪些缓冲池将被使用（譬如说某些计划可能需要一些临时表，而临时表的缓冲池也许不同于数据表），因此缓冲池大小只是简单地将数据库中所有缓冲池的页面数量相加。

Sort Heap Size:排序区的大小，用来估算排序或者哈希关联是否会溢出。

Database Heap size:数据库堆栈大小，在通常情况下对访问计划不会产生决定性的影响。

Lock List Size、Maximum Lock List 、Average Applications与Locks Available是锁列表的信息，用来估算某些类型的数据访问是否有足够锁列表容纳相关数据。

要点：DB2的优化器足够强大，可以通过不同的查询条件排列次序估算出执行时哪个条件在前会比较有利。因此用户自己的排列不会被优化器所认可，优化器完全基于开销对访问计划进行评估。访问计划估算值并没有实际单位，比较两个访问计划时，一定要基于同一个系统。在不同系统间的开销不具有可比性。读访问计划时从上到下、从左到右。

性能调优

性能调优在很多方面与问题诊断有相通之处，甚至从某种程度上说，性能调优是建立在成功的问题诊断的基础之上。

性能诊断强调的是“诊断”二字，也就是弄明白系统为什么慢，瓶颈出在什么地方，弄明白问题但却不一定能真正解决这个问题。

调优的最终目的不是完全挖掘出问题的根源，而是用不同的手段提升系统的单位时间内吞吐量。

性能调优比性能分析更加直观与灵活。有的时候只要了解了系能问题的现象，通过经验与一定程度上对应用与数据库的理解，就能够大体上给出一个调优的方向（譬如是磁盘问题、CPU问题或者内存问题）

调优思路：

首先，尝试理解系统的架构。至少弄明白系统多少CPU、多少内存，多少连接之类的最基本问题。

其次，将问题细化到CPU、内存、I/O或者系统懒惰的大方向。

再次，就是要给出一个期望值，尝试用不同的手段将某种指标提高的目标之上，然后再次重复细化问题的步骤，直到系统的整体性能达到预期。

两个难点（第一个更重要）

1.如何找到一个合理的目标与期望值。（参考最佳实践文档或IBM官方网站寻找资料） 2.用什么手段达到目的。

另一个更具体的思路KPI（国内外无数性能专家多年来的总结） KPI可以被分为以下几个级别： 数据库级别 实例级别 操作系统级别

数据索引缓冲池命中率 数据库、缓冲池、表空间快照 （逻辑读-物理读）/逻辑读 优秀：>95% 良好：>80%

读行v.s.选择行 数据库快照 Rows Read/Rows Select OLTP系统：<=5 SAP系统：<=3 排序内存过量使用 实例快照 排序内存高水位>SHEAPTHRES

物理磁盘数量 操作系统信息 每个CPU内核对应6——20个物理磁盘数量

以下引用网友文章

http://www.db2china.net/club/thread-23175-1-1.html

性能优化的几个关键指标（KPI）

在性能调优时，经常有无从下手的困惑，尤其是当发现数据库变慢时，不知道如何进行量化分析。我们将从经验的角度给出一些关键参数的阀值，这样在进行性能分析时，就可以从这些关键参数入手了。

下面的关键性能指标，我们又称之为KPI（Key Performance Indicator），供大家参考。 数据缓冲区命中率（Data Bufferpool Hit Ratio）：> 95% 索引缓冲区命中率（Index Bufferpool Hit Ratio）：> 98% 平均读写时间（Read / Write Avarage Times）：< 5 ms 高速缓存区命中率（Package Cache Hit Ratio）：> 98% 编目缓冲区命中率（Catalog Cache Hit Ratio）：> 98% 锁升级及死锁（Lock Escalations and Deadlocks）：仅有少量的锁升级或死锁 日志缓冲区（Log Buffer Hit Ratio）：> 98%

排序溢出次数 / 排序次数 （Sort Overflow / Total Sort）：< 1%

注意：我们给出的KPI阀值并不是一个严格的限制，比如某些参数略低于KPI阀值也是允许的。另外，也需要考虑将这些值与性能问题出现之前的值进行比较，如果相差不大，则需要从其他角度考虑，例如数据库布局、磁盘或者网络是否有瓶颈等。

实例1

Linux 64位 8颗CPU内核，32G内存，db2版本9.5 Fixpack 5 一个实例 单分区。

问题描述：每年年底在运行结算程序时，系统性能严重下降。平时表现一般，每年年底结算程序运行时，一定要减少并发数量，否则会轻易造成100%CPU使用率。

分析方法：

用户重置快照后，15分钟收集了若干包括数据库、实例、表空间、sql、应用程序等快照和一些系统信息进行分析

vmstat 收集的信息可观察到CPU使用率维持在50%-70%左右。可从侧面反映客户描述的CPU资源消耗情况。

ps -elf输出中，db2sysc进程的C列维持在99，db2fmp也在使用不少的CPU,但比前者少很多。

由上可定位db2sysc中存在大量消耗CPU的任务。

然后在数据库快照中看到：

Buffer pool data logical reads = 3037962816 Buffer pool index logical reads = 854461868

两者比例相差将近4倍，也就是非常多的读是走表扫描，而不是索引扫描。（当然，对于OLAP系统来讲，有时候这种现象是正常的，分析人员一定要理解应用程序正在执行的业务）继续观察到

Rows selected =76395867 Rows read = 45143452324

比例达到500以上，对比KPI，在OLTP系统中预期数值是5，这个系统显然是OLAP，但这也是明显不合理的。

然后进入SQL快照，通过计算Total user cpu time/Number of executions 得到单一SQL的平均消耗CPU时间。从而定位消耗CPU资源的SQL，发现INSERT占用大量的资源。

然后查看应用程序快照，统计UOW Executing的连接，与以上SQL进行对应，发现两个JAVA程序都是执行同样的INSERT，另外8个.exe程序都在执行前面所看到的那个消耗CPU最大的查询。

到此有足够的理由认为，这几个（尤其那个消耗CPU最大的查询）查询需要被进一步优化，而入手点则应该从减少逻辑读开始。因为单纯的内存读并不占用I/O,但是大量的内存访问绝对对CPU是一大负担。从DBA的角度，可以尝试加一些索引或其他一些物理设计影响优化器的执行计划，减少表扫描或排序，降低CPU和其它资源的占用。如果不能解决问题，那就要协调设计开发从逻辑角度减少数据访问的数量。

实例2

2010年初国外一家银行。系统运行DB2 V8,主要用于全国各地支行的柜台业务员在工作时间的一些业务处。

问题集中在一个存储过程的执行中，该存储过程并不复杂，最主要一部分是一个INSERT操作，从4个表中做SELECT然后插入一个临时表。每周开始执行大概在0.5秒左右。可是随着数据量得增加该存储过程的性能不断下降。到了周五就需要3-4秒了。然后第二个周一恢复正常，在一周中持续下降，到周五又变成3-4秒了。

通过描述，第一个问题就是：每天的数据量是否相同。

深入研究后，发现用户每周的维护时间都使用脚本将一个大表清空一半左右的数据，然后进行REORG RUNSTATS REBIND,开始新一周的工作。

由此推断，随着数据量得上升，系统的统计信息无法反应最真实的当前状态，优化器一直认为表中数据量相对较少，因而选择了一个对当前数据量并非最优的访问计划。逻辑上的推理完全能够解释这个现象。

根据当时的数据观察，问题的大表所被读取的行数在一天内每天都有增长，同时对应的语句在一周内不同时间使用动态SQL抓exfmt,所显示的访问计划与维护完成之后的计划明显有所区别。

这些数据都可以从侧面证实我们的推论（与性能诊断相同，在调优中千万不要由于某几条快照信息就匆忙的下结论，一定要从多方向多角度分析）

但是用户的系统维护策略无法让他们每天运行RUNSTATS和REBIND,因此我们必须使用其他的方法来保证存储过程使用最优的访问计划。

通过一般的逻辑判断，假设一个查询的访问计划对于大数据量时是最优的，那么当数据量减少后，尽管该计划可能并非最优，但是执行时间应该不会比数据量占用更多时间长。因为按照同样访问数据的方法，在数据量减少的情况下，性能不会比数据量多时更差。

在这个前提条件下，我们测试了手工RUNSTATS和REBIND,发现在数据量最多的情况下，使用最新的统计数据可以使存储过程维持在一秒之内。

最终解决此问题，有三种方法： 1.使用profile,比较复杂。

2.在删除数据之前做RUNSTATS REBIND,然后REBIND表释放空间。 3.找一个周末，删除数据之前做RUNSTATS+REBIND的存储过程，然后在今后的维护脚本中永远不进行REBIND。

经过两个实例的讲解，再次强调，性能调优不同于性能分析，我们的终极目标并不是找到根源并且硬碰硬地解决它，而是通过各种迂回的办法绕过这些问题。在很多时候，我们可能并

不知道问题的根源，而是凭着处理过大量性能问题后得到的宝贵经验，对某一个或几个参数进行调整后提升性能，都是被认可的成功性能调优。

索引

索引实际上就是B+树，树中的每一个节点包含键值与一系列RID。索引之所以能够提升性能，就是在于当查找SQL的某个谓词时，可以使用B+树迅速的找到对应的一系列键值，而不去扫描整个表中所有的数据页。

对于给定的谓词，能够将谓词中的条件与索引键值所代表的列相匹配，并且当优化器判定使用B+树扫描的效率高于全表数据页扫描的时候，这个索引就是有效的。

两个条件

第一个条件是，怎样能够让谓词条件与索引键值对应的数据列所匹配； 第二个条件是，怎样让优化器认为使用索引的效率更高。

以下有第一个条件相关

假设一个键中包含数据列（C1，C2，C3）,那么想要判定C2的时候，给定的谓词条件中必须包含C1。同理，想要判定C2的时候，谓词条件必须保证C1、C2列都被包含。只有满足这个条件的谓词，才能够在索引扫描时使用Start/Stop Key谓词检索。否则即使使用索引，也是Sargable谓词或者Residual谓词扫描整个B+树，其开销在很多情况下甚至高于表扫描。具体这些谓词含义的解释，可见《DB2数据库高级管理》中优化器部分的讨论。

Start/Stop Key与Sargable谓词显示的例子： db2 \db2 \

#插入一些不同的数据

db2 \db2 set current query potimization 0 db2 set current explain mode explain

db2 \ #此语句索引扫描时将使用Start/Stop Key谓词检索。

db2 \

#此语句索引扫描时将使用Sargable谓词检索。

db2 set current explain mode explain no

db2exfmt -d SAMPLE -g TIC -w -l -n % -s % -# 0 -o prod_sample_exfmt.txt

而第二个条件，就是一个更为复杂的问题。由于对优化器的详细介绍在下一本数据仓库书中

进行，在这里不讨论优化器的算法。

一般来说，想要使用优化器，需要从统计信息上做文章。 如果用户发现即使建立的索引合法，但是有时候优化器依然使用全表扫描，这个时候用户可以将优化级别调为0，或者在表上加volatile(意思是可变的、容易变的)标识，使优化器更倾向于使用索引扫描而不是全表扫描。

如果一个表被设定了volatile属性，优化器就会尽量忽略该表的统计信息，尽量使用索引扫描，避免执行根据不够实时的统计信息制定不够优化的访问计划。

用户可以通过alter table<表名> voliatile来设定其属性。

在创建索引时，应该遵循以下最佳实践

1.分析组合索引键的顺序，如：（a,b）和（b,a）是完全不一样的。

2.不要创建冗余索引，如：某张表有（a）,(a,b)两个索引，前者是多余的。 3.验证索引是否被用到，如果没有用到，建议删除。

4.尽可能通过include创建index-only索引，减少数据获取I/O,提升效能。

索引是DBA的性能调优器，主要优点概括

1.最主要的目的是提高查询速度。

2.避免不必要的表扫描，表扫描是CPU的第一杀手。 3.避免排序操作，排序是CPU的第二杀手。 4.减少死锁发生的概率。

索引缺点

1.增加了insert/update/delete等操作的负担。 2.索引需要占用额外的磁盘空间。

3.增加了运维成本，如RUNSTATS,REORG,LOAD等操作都需要维护索引。

因此，索引并不是越多越好，对于DBA来说，需要找出长期不需要的索引并将其删除，这也是一项比较重要的工作。

怎样找出无效的索引

以下方法可供选择：

1.通过db2pd -d sample –tcbstats -index。有一个字段是scans，找到一段时间内scans为0的值，表示这个索引没有被用到，即可删除。

2.DB2 9.7提供了一个新的监控表MON_GET_INDEX用来识别没有用到的索引。？

3.DB2 9.7还提供了一个方法用来查看索引最后使用的时间。

9.7在syscat.tables,syscat.indexes和syscat.packages视图中增加了一个字段lastused，用来表示此对象最后一次被访问的时间。

01/01/0001表示此对象从来没有被访问过，因此可用其作为过滤条件： db2 \

排序

在DB2数据库性能监控中，经常会遇到CPU资源使用过高问题，造成此类问题的原因很多，但最主要的原因有两个： 第一，过多的I/O读取； 第二，过多的排序（Sort）。

这两个因素有时也成为CPU的两大杀手。所谓过多的逻辑I/O读取，通常指发生在缓冲池中的表扫描。

排序是指某些数据按照某个（或某些）字段从大到小或从小到大排列的过程。

如果排序字段上没有索引，或者DB2认为索引的开销比表扫描更大时，DB2就会对数据进行排序。

除了ORDER BY之外，以下操作也可能引起排序：

1.对于包含DISTINCT、GROUP BY、HAVING、INTERSECT、EXCEPT 、UNION等操作的SQL语句，如果没有索引满足所取的顺序要求，或者优化器认为排序的代价低于索引扫描，就需要进行排序。

2.对于包含Max/Min/Sum/Cube/Rollup/Rank等聚集函数，DB2会把语句重写成一个使用排序的嵌套子查询。

3.对于查询计划中的Dynamic Bitmap Index ANDing (IXAND)、Hash Join(HSJOIN)等操作的生成的Hash表会放在SORTHEAP中。

通常情况下，大量排序会对性能造成极大影响。比如，排序会导致很高的CPU使用时间；增加SQL的执行时间；增加锁超时和死锁发生的几率；排序会严重的消耗有限的内存空间；排序溢出会临时表空间的频繁I/O等。

排序的原理

正常情况下，DB2排序发生在内存中，这块内存叫做排序堆，即SORTHEAP。当需要排序的数据超出SORTHEAP大小限制时，就会发生排序溢出。溢出的数据就会写到临时表中，从而产生更多的I/O,因此对性能会有较大影响。

DB2的内存集包括实例内存集、数据库共享内存集、应用程序内存集和代理私有内存集等。内存池是从内存集中分配的。根据排序内存池的分配来源，分为私有排序和共享排序。私有

排序是从代理私有内存集中分配的，而共享排序从数据库共享内存集中分配。

DB2选择私有排序还是共享排序，是由3个排序参数决定的。在不同配置组合下,DB2对排序内存的分配使用方式也大不相同，不同版本的DB2对排序内存的使用也存在较大差异。 1.SORTHEAP：数据库配置参数，指定为每个排序分配的最大内存大小，实际使用的大小有优化器来决定的。如果表的统计信息不准确，会导致优化器对要使用的排序内存的大小估算不准，有可能分配比实际需要要少的内存，导致不必要的排序溢出。这就提醒我们经常使用runstats更新统计信息。

2.SHEAPTHRES_SHR：数据库配置参数，该参数指定了数据库共享内存集中共享排序内存池的大小，它限制了该数据库上的所有应用能达到的共享排序内存上限。在DB2 8版本中，这个值是硬限制，当达到此限制后

请求排序的应用会收到SQL0955(reason code2)错误。从DB2 9.1起，这个参数改为软限制，超过此值的共享排序请求可以从数据共享内存集的溢出区（Database overflow Buffer）获得。

3.SHEAPTHRES:实例配置参数，指定为本实例中所有私有排序分配的内存上限的软限制。当私有排序分配的内存达到了此限制，新请求的私有排序的内存大小分配将会小于sortheap配置的大小。

简单的说，每个排序是从SORTHEAP中分配的。如果使用私有排序，那么允许分配的排序内存大小不能超过SHEAPTHRES实例参数；如果使用共享排序，允许分配的排序大小不能超过SHEAPTHRES_SHR的参数值。

从DB2 9.1开始，如果将SHEAPTHRES实例参数设置为0，DB2将使用共享排序，即排序内存从共享排序内存池中分配，最大限制受SHEAPTHRES_SHR控制。

排序的监控

db2 get snapshot for database on dbname|more

关键字：

Total sorts:表示发生总排序次数。

Total sort time(ms):表示发生总排序时间 Sort overflows:表示发生排序溢出的次数 Active sorts:表示监控时正在进行的排序次数

几个关键指标：

Sort overflows/Total sorts * 100%表示排序溢出百分比，通常情况下，该值应该小于3。否则，表示溢比例太高，需要优化

Total sort time/Total sorts 每次排序花费的时间（ms）,对于交易系统来说，最好小于50ms

Total sort time(ms)/(Commit statements attempted+Rollback statements attempted)表示每个事务花费在排序上的时间。一个事务响应时间包含很多方面，比如读/写时间，锁时间、排序

时间、CPU时间等。

除了数据库快照、应用程序快照、动态sql快照也包含一些排序信息。

排序的优化

影响排序的因素主要包括排序的行数，排序列的宽度和ORDER BY的列数等，排序的行数乘以排序列的宽度决定了一次排序占用的空间。因此，应该尽量减少排序的行数（很多情况下，排序列无法更改）。

常用排序优化方法 1.参数调优

从DB2 9.1开始，SHEAPTHRES_SHR实例参数的默认值为0，表示排序内存从数据库共享内存区分配，排序总量由SHEAPTHRES数据库参数控制。

SORTHEAP的默认大小为256页，即1MB。假定要排序的数据每行是1KB大小，那么每个SORTHEAP最多允许排序1M/1K=1024行，超出1024行的数据，排序将会溢出。

db2 get db cfg for sample|grep -i sort SHEAPTHRES_SHR（4KB） = 5000 SORTHEAP = 256

对于数据仓库系统来说，一般行记录很长，排序的行数也很多，可以适当增大SORTHEAP大小。

SHEAPTHRES_SHR该值的大小有两个指标来决定，SORTHEAP和同时排序的个数。但是同时间排序的个数很难估计，一般用并发数来估计。 并发数：

db2 get snapshot for database on dbname appls.executing in db manager currently = 10

考虑到单个应用可能需要多个排序，可设置： SHEAPTHRES_SHR = SORTHEAP * 应用并发数 *2

在DB2 9.1及以上版本，DB2提供了自动调优（STMM）,好处是DB2根据负载和资源情况，决定一些内存的分配和回收，比如缓冲池、排序堆、所列表和Package cache大小，对于稳定的系统，可考虑启用STMM,启用方法：

db2 update db cfg for dbname using SORTHEAP automatic db2 update db cfg for dbname using SHEAPTHRES automatic

1.数据池命中率

命中率是判定物理I/O频繁程度的一个最重要的指标之一，很多优化不足的系统中是一个最容易出现的问题。 缓冲池命中 hit 未命中 hit miss

对于OLTP一般要求数据Bufferpool达到90%的命中率。对于OLAP，由于经常需要进行表扫描，所以不必追求很高的命中率，但临时数据和索引的命中率需要关注，因为仓库系统中一些复杂的SQL语句需要进行大量的排序或哈希关联操作，而排序和哈希关联可能需要在临时表空间完成。

数据库整体缓冲池命中率：

(1 - (Buffer pool data physical reads + Buffer pool temporary data physical reads + Buffer pool index physical reads + Buffer pool temporary index physical reads) / (Buffer pool data logical reads + Buf-fer pool temporary data logical reads + Buffer pool index logical reads + Buffer pool temporary index logical reads))* 100%

请问：Buffer pool index physical reads和index logical reads 的数据从何而来？ 打开DBM 配置参数 DFT_MON_BUFPOOL

使用：

db2 get snapshot for Bufferpools on db_name

数据缓冲池命中率：

（1-（Buffer pool data physical reads）/(Buffer pool data logical reads)）*100%

索引缓冲池命中率：

（1-（Buffer pool index physical reads）/(Buffer pool data index reads))*100%

临时空间缓冲池命中率：

（1-（Buffer pool temporary data physical reads + Buffer pool temporary index + Buffer pool temporary index physical reads）/(Buf-fer pool temporary data logical reads + Buffer pool temporary index logical reads)）*100%

有一个问题需要格外注意，缓冲池命中率可能会蒙蔽我们的眼睛，我们经常会发现很多系统命中率很高，但仍然存在严重的性能问题。

举例来说，有一张表T1,大小为200M，包含100万行数据。该表对应的表空间缓冲池大小为1G。在T1表有一条SQL语句，带过滤条件，但过滤字段上没有索引，满足次此查询条件的行数只有2行。

由于没有索引，DB2通过表扫描将100万行数据全都缓存到缓冲池，每次查询时DB2在缓冲池里进行表扫描。由于每次都是逻辑读，缓冲池命中率高达100%。但是为了查找2行数据，每次都要在内存中对100万行数据进行全表扫描，效率是非常低的。

在某种意义上来说，命中率只能代表系能问题中很小的一部分，而且在某些情况下，该数值过高反而可能代表一些潜在的性能问题。

2.有效索引读

来源：数据库快照

公式：行读取/行选择（rows read/rows selected） 指标：OLTP<=5, SAP系统<=3

IREF(index read efficiency)= Rows read/Rows Selected(Fetched)

假如一张员工表有10万行数据，根据员工号查询员工信息，假定员工号唯一并且没有索引，那么rows read是10万行，rows selected是一行，IREF=100000/1=100000,即为了查找一行数据，需要扫描10万行数据，这个效率是非常低的。如果在员工号字段上有唯一性索引，则只需读取一行数据就可获取值，IREF=1/1=1，效率非常高。

在DB2 9.1以上版本中，可通过如下语句实现:

db2 \

IREF当然越小越好，如果是5，意味着找一行数据需要读取5行数据。

对于OLTP, IREF应当小于等于5，如果远远大于这个值，则意味着需要调优。

对于OLAP，IREF一般相对较高。值越高，则消耗的CPU资源越高，性能越差，因此对此值得监控仍然必要。当IREF比较高时，需要对SQL语句进行分析，找出IREF比较高的SQL语句，然后进行物理设计，比如设计优良的索引等。

3.包缓存命中率（package cache hit radio）

来源：数据库快照

公式：1-包缓存插入数量/包缓存读取数量（1-Package Cache Insert/Package Cache Lookup） 指标：1，或者能够长时间保持接近1的稳定数值

该指标表示有多少查询语句可以直接在包缓存中找到。

为了减少编译时间，建议在使用动态程序（如java等）开发时指定prepare statement ,这样就只需编译一次。使用静态程序（嵌入C等）时，建议使用绑定变量。

4.平均结果集大小

来源：数据库快照

公式：行选择/执行Select SQL的次数（Rows selected/Select SQL statements executed） 指标：OLTP<=10

平均结果集用来表示平均每条Select SQL语句返回的结果行数。 对于OLTP系统来说，结果集一般很小，通常小于10； 对于OLAP系统，结果集一般很大。

平均结果集的计算公式如下：

Avg_Result_Set = Rows selected/Select SQL statements executed

通过快照可以得出这两个元素的值。

db2 9以上版本，可以通过管理视图获得同样结果

db2 \ROWS_SELECTED/(SELECT_SQL_STATEMENTS+1) AS Avg_Result_Set from sysibmadm.snapdb\

通过平均结果集大小，可以判断数据库类型（OLTP OLAP）。假设某一个数据库是OLTP交易型系统，但发现集很大，这就说明应用程序有改善余地，比如在SQL语句上做查询条件过滤并降低返回的结果集大小，而不是将所有数据取出，然后在应用逻辑上过滤。

5.同步读取比例

来源：数据库快照

公式：异步读取/逻辑读取 100% * （1- Asynchronous read/Logical read） 指标：OLAP》=90%

DB2的数据读取I/O主要包括异步读和同步读。如果采用高效的索引获取结果集时，DB2将使用同步读I/O访问索引页和需要的数据页，当没有索引或物理设计不够有效时，DB2将采用异步读I/O访问索引页和需要的数据页。异步I/O的DB2通过预取（prefetcher）进程、线程执行的，当查询的结果集较大，DB2认为顺序预取有效率时就会触发预取请求，异步读取I/O的比例越高，则表示获取的数据量越大，性能就可能越差，而更高的同步读则表示索引的高效。

通过快照监控结果可知，并没有任何元素表示同步I/O读的页数，但提供了异步I/O读和所有I/O读的值，用所有I/O读减去异步I/O值就可以计算出同步I/O读的值。 公式如下：

SRP=100-(((Asynchronous pool data page reads + Asynchronous pool index page reads)*100)/(Buffer pool data physical reads + Buffer pool index physical reads))

这个公式不仅适用于整个数据库异步读监控，也适用于表空间和缓冲池对象。

db2 9以上版本，可以通过一下语句实现： db2 \100 - (((pool_async_data_reads + pool_async_index_reads)*100)/(pool_data_p_reads + pool_index_p_reads + 1) as SRP from sysibmadm.snapdb where DB_NAME = 'DB1'\

在OLTP系统中，如果同步读高于90%，则表明数据库使用了高质量的同步I/O获取结果集。

如果低于50%，则系统性能一般会较差，或者是响应时间慢，或者CPU利用率比较高，一般是由于没有进行很好的物理设计引起的，比如缺乏有效的索引。

在OLAP系统中，往往需要进行数据扫描，并返回大量的结果集，这时可能采用异步预取I/O效率更高。

6.数据、索引页清除

来源：数据库快照

公式：异步写入/总写入（async writes/total writes） 指标：>=95%

该指标代表着页面清除进程（线程）是否能够有效地将脏页在后台刷入磁盘。

由于缓冲池的大小是有限的，一般来说数据库不可能把所有的数据都放入内存。这时，哪些数据需要驻留内存；哪些被更新的数据需要被写入磁盘，然后留出空间给其他数据，就是DB2缓冲池管理模块需要决定的。

当缓冲池中的被修改的数据页（脏页）与缓冲池总大小的比例超过总大小的比例超过一定阀值的时候（chngpgs_thresh）,DB2就会触发后台的页面清除进程/线程，将被选择的页（victim pages）以异步方式物理写入磁盘。

但是如果该清除机制触发的不够频繁，或者缓冲池大小使得系统无法有效的找到一个干净的页面，DB2就会选择一个脏页，然后将它写入磁盘，然后读取另外一个页面 进入内存，这种写入方式叫做同步写入。

可以想象，相对异步写入，同步写入会对数据的读取造成很大的性能问题。因而，该指标的用途就是监测异步写入与总写入的比例：

db2 get snapshot for database on sample|grep -i \

7.脏页偷取（dirty page steal）

来源：数据库快照

公式：脏页偷取触发次数（dirty page steal cleaner triggers） 指标：非常低

正如上文中刚提到的，脏页偷取是一种对性能影响极大的操作。当系统中的脏页偷取过多的时候，意味着DBA需要让页清除器工作的更加卖力。

如果发现系统中的页清除器一直很空闲，则可以通过调节softmax与chngpgs_thresh来让他们忙起来。这两个参数都是控制何时触发页清除器的参数。

其中softmax是按照缓冲池中MinbuffLSN与当前LSN之间的差距来计算何时需要触发，而chnggpgs_thresh则是计算缓冲池中脏页的数量与可用页面总数来进行计算。两者的作用同

样都是触发页清除器，只不过从不同的角度计算而已。

但是如果我们通过db2pd -stack all抓取的stack发现所有的页清除器一直非常繁忙，但是无论如何刷新磁盘的速度也赶不上数据写入缓冲区的速度，这时就需要增加页清除器的数量了。

默认情况下chngpgs_thresh为60，即缓冲池60%的页为脏页时，会自动触发页清除器。

曾经欧洲的一家大型企业使用单个缓冲区高达300G,为了能够让系统更有效地将数据均匀的写入磁盘，chngpgs_thresh在调优之后被设定为5%。

8.缓冲池读写I/O响应时间

来源：数据库快照

公式：缓冲区读取时间/物理读取次数 指标：1-10毫秒

对数据和索引的读取都要先通过磁盘独到缓冲区，我们曾经提过，快照中的缓冲区读取时间并不包括内存读取。也就是说，该数值仅仅包括物理读取的时间。

可以通过以下公式计算出一个物理读需要的平均时间：

Overall Average Read Time(ms)=(Total buffer pool read time(milliseconds)/(Buffer pool data physical reads + Buffer pool index physical reads + Buffer pool temporary data physical reads + Buffer pool temporary index physical reads))

对于现代存储系统来说，平均物理读/写时间一般在1-10毫秒左右（取决于存储系统的性能与缓存）。因此，如果我们监控的指标值超出此值，则需要结合操作系统I/O监控工具，调查I/O监控工具，调查I/O性能的瓶颈：

db2 \tbsp_name,(POOL_READ_TIME/(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + 1) as TSORMS from sysibmadm.snaptbsp order by TSORMS desc fetch first 10 rows only)\

这个公式可以找出时间最慢的表空间，然后评估这个表空间的设计是否为最佳。

当发现该值过高时（譬如超过20毫秒），一般来说系统会明显感觉到性能下降。这时，为了能够直观的证明存储的性能问题，可以通过UNIX/Linux中的dd工具向容器所在的文件系统（而不是容器文件）写入几千个数据页，计算平均写入时间（该数值为顺序写入），然后可以使用同样的工具从容器文件中随机读取几千个数据页，计算平均读取时间。

9.Direct I/O时间

来源：数据库快照

公式：直接读取（写入）时间/直接读取（写入）次数

指标：1-10毫秒

Direct 是指直接从磁盘访问而不经过缓冲池的I/O，主要针对LONG/LOB数据的访问。

当一列被定义为LONG/LOB，那么该列的数据则不存储在表的数据页内。而在数据页记录中该列所对应的位置，则是一些“指针”指向该数据真实所在的位置。

DRIOMS = Direct reads elapsed time (ms) / Direct Reads DWIOMS = Direct write elapsed time (ms) / Direct Writes

10.直接I/O读取（写入）的次数

来源：数据库快照

公式：直接读取（写入）次数/缓冲区读取（写入）次数 指标：非常低

通过对比Direct I/O与普通缓冲池读写的比例，我们可以知道到底有多少数据访问是读取大数据的。

除了有限的特定应用外（比如在线视频网站），大部分的应用程序都应该尽量避免频繁的大数据访问。一般来说，如果一列数据可以被定义为普通列的话，就尽量不要使用大对象。 当一定要使用大对象定义某列的时候，在应用的某些操作中不是真正需要访问该列时，就尽量在查询时不要指定大对象数据列，避免不必要的Direct I/O。

11.编目缓冲区插入比例

来源：数据库快照 公式：编目缓冲区插入次数/编目缓冲区查询次数（catalog cache inserts/catalog cache lookups） 指标：0，或者在接近0的数值上长时间维持稳定

在系统的每一个分区中，database heap中都会分配出一块空间用于缓冲编目表的信息。 由于在数据库的日常操作中，查询编目表是一个非常频繁的操作。譬如当用户从一个表读取数据时，系统就要查询编目表，理解该表在什么表空间、应该如何访问等信息。因此为了性能着想，DB2在数据库栈内存中单独开辟一块空间，用于存储编目信息。 但是如果用户有很多数据库对象，而该编目缓存的大小过小，则该内存无法容纳下所有的信息。那么当新的信息来临时，就会把一些不常用的信息替换出去。 因此，一般建议将catalogcache_sz设置逐渐增大，直到系统中不再频繁出现编目缓冲区插入的操作。

排序指标 1.排序溢出比例

来源：数据库快照

公式：排序溢出次数/排序总数（sort overflow/total sorts） 指标：OLTP:0，或者接近0的数值上长时间维持稳定

排序溢出就是当排序内存不够时，数据需要使用临时空间进行排序。很多时候排序的根源是由于SQL语句引起的。

2.平均排序时间

来源：数据库快照

公式：排序总时间/排序总数（total sort time/total sorts） 指标：远小于系统预期平均语句的执行时间

这个指标是理解平均每次排序所消耗的时间。

一般来说，一条语句的执行包括锁等待、数据读取时间（内存+I/O）加上排序时间（内存读取和内存排序都属于user CPU时间）。因此用数据库中平均的排序时间对比平均语句返回的速度，就可以大概估算出执行一条语句时有多少的时间用于排序。

作为DBA，我们一般都希望该排序时间越少越好。如果该值过高，用户可以考虑优化查询与添加索引，尽量减少排序的消耗。

3.平均每条交易的排序次数

来源：数据库快照

公式：排序总数/交易总数（total sorts/(Commit statements attempted + Rollback statements attempted)） 指标：OLTP<5

对于OLTP应用来说，由于每条交易的短小精干的特征，我们需要尽可能减少每条交易所需的排序数量。

大部分情况下，这种调优需要开发人员的配合，一方面在数据库建立合适索引的同时，另一方面优化应用程序逻辑，减少SQL所需要排序的次数。在典型的OLTP系统中，尽量将每一条交易平均所需的排序数量维持在5以下。

基于事务的指标度量 1.总的事务数量

来源：数据库快照 公式：提交事务 + 回滚事务（Commit statements attempted + Rollback statements attempted） 指标：无

事务是一个完整的工作单元，具有ACID特性。该指标可以直接反应出系统单位时间内的吞吐量高低。

db2 \COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS as txn_counts from sysibmadm.snapdb\

2.每个事务包含的查询SQL语句的数量

来源：数据库快照 公式：查询语句数量/交易总数（Select SQL statements executed/(Commit statements attempted + Rollback statements attempted)） 指标：OLTP<=10

对于OLTP系统来说，每个事务执行查询次数一般小于10。如果事务太长，可能会造成一些锁等，影响并发性能。 db2 \decimal(SELECT_SQL_STMTS)/decmial(COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS) as selpertxn from sysibmadm.snapdb\

3.每个事务包含的增删改语句数量

来源：数据库快照

公式：插入、更新与删除语句的数量/交易总数（Update/Insert/Delete statements executed/(Commit statements apptempted + Rollback statements attempted) ）

指标：OLTP<=5

在OLTP系统中建议不要在同一条交易中使用过多的数据更改语句，单条交易中过多的这类语句会造成大量的锁，容易引起锁等待甚至死锁，同时过长的交易可能会引起活动日志过长，导致日志空间占满。

db2 \decimal(UID_SQL_STMTS)/decimal(COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS +1) as uidpertxn from sysibmadm.snapdb\

4.每个事务返回的结果集行数

来源：数据库快照

公式：选择的行数/事务总数（Rows Selected/(Commit statements attempted + Rollback statements attempted)）

指标：针对OLTP业务，尽量靠近最终用户真正在终端上看到的结果集的数量。

这个指标用来计算每个事务返回的平均结果集。这个值尽管无法控制，但如果发现该指标异常高，表明应用程序获取大量数据，可能的原因是在应用层进行数据过滤，可以考虑在数据库层增加过滤条件，减少返回到应用层的结果集。

db2 \decimal(rows_selected)/decimal(COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS+1) as rowsselpertxm from sysibmadm.snapdb\

5.每个事务读的行数

来源：数据库快照 公式：读取的行数/事务总数（Rows Read/(Commit statements attempted + Rollback statements attempted)）

指标:尽量靠拢前一个指标

该指标与上一个指标不同。在获取结果集的过程中，DB2必须读取数据页，因此要读取的数据行数（rows_read）可能远远大于结果集（row_selected）

通过优化物理设计，比如设计好的索引，DBA可以提高结果集与读取行数的比例。如果缺乏好的索引，DB2必须读更多的行才能找到满足条件的数据。当应用了索引后，记得重新计算和评估这两个指标，验证读取的行数是否降低。

db2 \decimal(rows_read)/decimal(COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS+1) as rowsreadpertxn from sysibmadm.snapdb\

6.每个事务需要的缓冲池逻辑读

来源：数据库快照 公式：逻辑读的总数/事务总数（（Buffer pool data logical reads+Buffer pool index logical reads）/(Commit statements attempted + Rollback statements attempted)) 指标：取决于业务量，在OLTP中尽量降低

DB2对数据页和索引页的读取是通过缓冲区执行的。如果请求的数据不再bufferpool中，则首先将磁盘数据读到缓冲区。如果数据已经在缓冲区，则直接从缓冲区获取，我们把这种数据叫做逻辑读。=

牢记一点：逻辑读与cpu资源消耗成正比。

为了降低该指标，需要找到那些开销高的SQL语句，比如CPU时间，排序时间、I/O时间、读取的行数、写行数等，一旦找到，可以通过物理设计多对SQL语句进行调优的方法降低指数。

降低数据库整体逻辑I/O可从SQL语句着手，通过get snapshot for dynamic sql可监控每个SQL语句的逻辑读，然后对语句进行分析。也可以通过event monitor for statments进行监控。

本文来源：https://www.bwwdw.com/article/1yc2.html