我用DB2的这几年(三)

原创 2004年08月04日 15:05:00

系统运行一段时间以后,用户抱怨某些操作响应速度过慢;这个在项目前期没有出现过类似问题,因此怀疑是数据量过大造成的原因。但是,查询相关业务表中仅仅只有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 命令的时候,必须指定表所在的模式。

我这几年的变化

写这篇文章的起源是因为两件事情,第一件是今天下午我妈在看个电视采访,访问的对象是著名的越剧演员徐玉兰,我妈可以说看了一辈子的越剧,当初我妈17,8岁时候看越剧的时候,徐玉兰还只有40岁左右,现在一转眼...
  • fangzhiyuan
  • fangzhiyuan
  • 2004-11-21 00:40:00
  • 1176

我用DB2的这几年

我用DB2的这几年序首先声明我不是在写自传;我不是什么高手,也不想在众位高手面前买弄风骚。从毕业到现在算是一直跟DB2在打交道,有一些感想,拿出来跟大家分享一下吧。 第一次亲密接触第一次接触DB2,要...
  • Mr_Bean
  • Mr_Bean
  • 2004-02-15 23:39:00
  • 2032

我在公司的这几年

时间过得太快了,特别是年龄大了以后总感觉时间过得好快。 很惭愧,我之前没写过技术博客,算是一个不合格的程序员,而且是很不合格的,而我在12年之前的程序员之路的确也算得上是不合格的,那几年可是我生命中...
  • jmmx
  • jmmx
  • 2015-01-09 14:27:06
  • 1511

我的IT这几年(三)

03 我们这几个过完寒假,回到学校,考研是意料之中的落榜,高程通过,这个时候看来,当时签合同还是挺明智的,最起码个自己留了条后路,而且工资也会再加上400块,心里美了一下。 要开始毕业设计了,已经跟F...
  • slamdunkning1983
  • slamdunkning1983
  • 2008-02-28 17:10:00
  • 340

谈谈这几年干的一些事情和认识的一些人把。

我是2013年来到武汉,本该呆在天津的我被亲戚带到武汉发展,起初并没有想到会成立公司,会去接项目,只是单纯的不想听从父母的安排去学医。 刚到武汉的我什么也会在亲戚的安排进入一家科技公司完全不会...
  • qq_23959977
  • qq_23959977
  • 2016-05-23 22:55:33
  • 166

我用DB2的这几年(二)

题外话上次写的东西发表以后有人提意见说我写的太罗索,那我在这里把前次的东西简明扼要的提议下,就是我装DB2数据库之后私自修改了db2admin用户的密码,造成DB2服务无法正常启动;因此建议大家以后修...
  • Mr_Bean
  • Mr_Bean
  • 2004-02-18 23:32:00
  • 2361

我用DB2的这几年(六)

不同平台DB2数据库之间大批量的移动数据(二)——Load篇在前面一篇文章中我介绍了Export/Import在数据交换中的使用方法。在本次我将详细介绍另外一种导入数据的工具LOAD(装载)的使用。L...
  • Mr_Bean
  • Mr_Bean
  • 2006-09-12 10:10:00
  • 1784

我在上海奋斗五年 从月薪3500到700万

 无意看到这篇帖子,很有感触。希望能给身边的一些有志青年带来一点感触。                    偶的忠告:要想学点什么,首先学会有耐心              阅读准备:眼药水+眼镜+...
  • dxt613
  • dxt613
  • 2007-02-23 21:02:00
  • 1107

AC一年了

记得去年的这个晚上,我刚会敲a+b,并且AC了hdoj的1000。  ...... 这一年来,经历了很多,看透了很多,明白了很多。心中真的真的有很多话想说,但是有些话又不想说出来,亦或是不能说出来...
  • chenzhenyu123456
  • chenzhenyu123456
  • 2015-10-06 21:17:59
  • 467

留一手教你在美国亚马逊网购,让国内代购都去吃屎!

1. 为什么要去美国amazon购物? 美国商品质优价廉,随着人民币的不断升值,人民币的购买力也在不断增强。 可国内进口商品的各种坑爹价格,使得我们这些村炮不得不将目光投向屌爆的美利坚。 2...
  • u011676589
  • u011676589
  • 2014-03-29 10:23:36
  • 785
收藏助手
不良信息举报
您举报文章:我用DB2的这几年(三)
举报原因:
原因补充:

(最多只允许输入30个字)