我用DB2的这几年（三）
2020-03-09 22:30:06
供稿：网友
系统运行一段时间以后，用户抱怨某些操作响应速度过慢；这个在项目前期没有出现过类似问题，因此怀疑是数据量过大造成的原因。但是，查询相关业务表中仅仅只有3万多的的数据量，不足以构成影响程序响应速度过慢的瓶颈。更奇怪的是采用导入的方法将此表数据装载进来却没有发现上述现象，我百思不得其解。
几天后，无意间翻阅一本杂志，其中有这么一段话——“每当sql语句被发送到到db2 数据库管理器中处理时，sql 优化器会去读取系统编目表来确定被引用的列的特性以及在被引用的表中时候已经定义了索引，同时被语句引用的每个表的大小也包括在内。根据这些得到的信息，优化器可以估算出能满足sql语句需要的每一种数据存取路径的成本，然后推荐最佳的一个。 优化器用于做决策的数据库统计集合数据在系统编目表中是一个关键性的元素。所以，统计的变化可能导致选择存取路径的变化；如果信息丢失或过时，优化器也许选择出来的存取计划将导致sql语句执行时间比正常的要长。例如，一个删除操作可能留下以后不能再使用的空的数据页面。对各种长度的字段进行更新可能导致新的字段值不适合在同一个数据页面中存放。这将导致某些行被移动到不同得页面并且在表里产生内部空隙或者未使用空间。因此，db2不得不去读取更多的物理页面来取回应用程序所需要的数据”。结合前面遇见的这个问题，该操作所涉及的物理表的确是经常进行增删改操作的，是不是因为这个原因呢？刚好前段时间学习过关于表重组和运行统计的内容，知道db2有runstats和reorg工具来完成表的运行统计和重组。于是我就做了以下试验：
---1首先检查是否要重新组织数据 reorgchk current statistics on table db2admin.t_ckd 得到表的统计信息和索引的统计信息显示如下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
表统计信息：
表统计信息：
f1: 100 * overflow / card < 5
f2: 100 * tsize / ((fpages-1) * (tablepagesize-76)) > 70
f3: 100 * npages / fpages > 80
creator name card ov np fp tsize f1 f2 f3 reorg
--------------------------------------------------------------------------------
db2admin t_ckd 1 0 1 12 9 0 0 8 -**
--------------------------------------------------------------------------------
索引统计信息：
f4: clusterratio 或正常化的 clusterfactor > 80
f5: 100 * (keys * (isize+8) + (card-keys) * 4) / (nleaf * indexpagesize) > 50
f6: (100-pctfree) * (indexpagesize-96) / (isize+12) ** (nlevels-2) * (indexpagesize-96) / (keys * (isize+8) + (card-keys) * 4) < 100
creator name card leaf lvls isize keys f4 f5 f6 reorg
--------------------------------------------------------------------------------
表：db2admin.t_ckd
db2admin xak1t_ckd 1 1 2 28 1 100 - +++ ---
db2admin xie1t_ckd 1 1 1 10 1 100 - - ---
db2admin xie2t_ckd 1 1 1 10 1 100 - - ---
db2admin xie3t_ckd 1 1 1 4 1 100 - - ---
db2admin xie4t_ckd 1 1 1 18 1 100 - - ---
sysibm sql010510174815750 1 1 2 28 1 100 - +++ ---
--------------------------------------------------------------------------------
clusterratio 或正常化的 clusterfactor (f4) 将指示索引需要 reorg，该索引与基表不在相同的序列中。当在表中定义了多个索引时，一个或多个索引可能被标记为需要 reorg。 指定 reorg 顺序的最重要索引。
可以看到表统计信息中要求f1<5,f2>70,f3>80而实际的表的f1＝0，f2＝0，f3＝8不能满足要求，索引的大部分f4,f5,f6也不能满足要求，必须进行重新统计
----2重新组织数据库表的索引
reorg table db2admin.t_ckd index db2admin.xie3t_ckd
----3重新统计索引
runstats on table db2admin.t_ckd and indexes all
----4重新统计后可以再看看数据表的信息 reorgchk current statistics on table db2admin.t_ckd 得到表的统计信息和索引的统计信息显示如下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
表统计信息：
表统计信息：
f1: 100 * overflow / card < 5
f2: 100 * tsize / ((fpages-1) * (tablepagesize-76)) > 70
f3: 100 * npages / fpages > 80
creator name card ov np fp tsize f1 f2 f3 reorg
--------------------------------------------------------------------------------
db2admin t_ckd 4893 0 401 401 1546188 0 96 100 ---
--------------------------------------------------------------------------------
索引统计信息：
f4: clusterratio 或正常化的 clusterfactor > 80
f5: 100 * (keys * (isize+8) + (card-keys) * 4) / (nleaf * indexpagesize) > 50
f6: (100-pctfree) * (indexpagesize-96) / (isize+12) ** (nlevels-2) * (indexpagesize-96) / (keys * (isize+8) + (card-keys) * 4) < 100
creator name card leaf lvls isize keys f4 f5 f6 reorg
--------------------------------------------------------------------------------
表：db2admin.t_ckd
db2admin xak1t_ckd 4893 49 2 28 4893 81 87 2 ---
db2admin xie1t_ckd 4893 7 2 10 3 99 68 18 ---
db2admin xie2t_ckd 4893 7 2 10 2 99 68 18 ---
db2admin xie3t_ckd 4893 7 2 4 18 100 68 18 ---
db2admin xie4t_ckd 4893 6 2 18 6 90 80 18 ---
sysibm sql010510174815750 4893 49 2 28 4893 81 87 2 ---
--------------------------------------------------------------------------------
clusterratio 或正常化的 clusterfactor (f4) 将指示索引需要 reorg，该索引与基表不在相同的序列中。当在表中定义了多个索引时，一个或多个索引可能被标记为需要 reorg。 指定 reorg 顺序的最重要索引。
至此，试验完成。接下来比较一下运行统计和重组前后运行成本，如下图：
运行重组统计前
运行重组统计后
对比运行统计前后的sql语句成本可以看出由运行前的4469变成了运行后的1572，运行成本是原来的三分之一多。然后再运行程序发现响应速度比以前有大幅度的提高，到此这个棘手的问题算是解决了（当然这是治标不治本，要从根本改变就应该从sql语句本身入手优化它的性能）。同时我对于“采用导入的方法将此表数据装载进来却没有发现上述现象”这个问题也找到了答案，那就是——在import过程中由于导入目标表示新表，import工具将会用类似运行统计的方式将数据均匀填充到叶面当中，因此速度也会加快。这个问题说明对于在数据库中那些经常发生变动的表，定期进行运行统计是对数据库性能提高是有帮助的。
【附录：一些其他的背景知识】
对 reorgchk 所使用的度量的考虑因素包括：（当查看 reorgchk 工具的输出时，找到用于表的 f1、f2 和 f3 这几列，以及用于索引的 f4、f5、f6、f7 和 f8 这几列。如果这些列中的任何一列有星号 (*)，则说明当前的表和/或索引超出了阈值。） f1: 属于溢出记录的行所占的百分比。当这个百分比大于 5% 时，在输出的 f1 列中将有一个星号 (*)。
f2: 数据页中使用了的空间所占的百分比。当这个百分比小于 70% 时，在输出的 f2 列上将有一个星号 (*)。
f3: 其中含有包含某些记录的数据的页所占的百分比。当这个百分比小于 80% 时，在输出的 f3 列上将有一个星号 (*)。
f4: 群集率，即表中与索引具有相同顺序的行所占的百分比。当这个百分比小于 80% 时，那么在输出的f4 列上将有一个星号 (*)。
f5: 在每个索引页上用于索引键的空间所占的百分比。当这个百分比小于 50% 时，在输出的 f5 列上将有一个星号 (*)。
f6: 可以存储在每个索引级的键的数目。当这个数字小于 100 时，在输出的 f6 列上将有一个星号 (*)。
f7: 在一个页中被标记为 deleted 的记录 id（键）所占的百分比。当这个百分比大于 20% 时，在输出的 f7 列上将有一个星号 (*)。
f8: 索引中空叶子页所占的百分比。当这个百分比大于 20% 时，在输出的 f8 列上将有一个星号 (*)。
对所有表运行 reorgchk 工具，并确保您正在使用当前统计信息，可使用命令：
reorgchk update statistics on table user
可以使用如下语句来检查任何没有统计信息的表：
select tabname from syscat.tables where stats_time is null
可以使用如下语句来检查任何没有统计信息的索引：
select indname from syscat.indexes where stats_time is null
可以使用如下语句来查找具有时间超过 30 天的统计信息的表和索引：
select tabname from syscat.tables where stats_time < current timestamp - 30 days select indname from syscat.indexes where stats_time < current timestamp - 30 days
注意： 在使用 runstats 命令的时候，必须指定表所在的模式。
