我用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 命令的时候,必须指定表所在的模式。

DB2分组函数ROLLUP和CUBE的使用

DB2的GROUP BY语句除了最基本的语法之外,还支持ROLLUP和CUBE语句。ROLLUP和CUBE在数据统计和报表生成过程中带来极大的便利,而且效率比起来GROUP+UNION组合方式效率高很...
  • guotianlaile
  • guotianlaile
  • 2016年05月13日 18:22
  • 1499

DB2日期时间函数

1、year(exp) :取exp的year部分。  参数:date、timestamp类型,日期间隔,时间戳间隔,        或者一个有效的date或者timestamp字符串(非CLOB类...
  • Happy_wu
  • Happy_wu
  • 2016年09月20日 11:26
  • 851

db2 数据库的常用命令

1.改变列的长度    db2 "alter table [tablename] alter column [columnname] set data type varchar(length)" ...
  • yht_817
  • yht_817
  • 2017年01月18日 18:58
  • 937

Linux下安装DB2的步骤

linuxdb2安装:Linux下安装DB2的步骤 由于工作的需要,刚刚尝试完在Linux下安装DB2数据库,已经连接测试成功,简单做了下总结,由于采用的多为命令行,所以没有必要截图了。望能对其他...
  • yangyi1018
  • yangyi1018
  • 2017年04月11日 12:14
  • 570

db2 import 五种方式详解

db2菜鸟使用db2过程中,遇到很多问题,无奈有关db2的资料实在是少,在工作中遇到db2导入的问题,看到这边文章解析的不错,摘抄一部分过来,以后备用,摘抄地址:http://blog.itpub.n...
  • innodyz
  • innodyz
  • 2015年12月01日 17:28
  • 7199

DB2中的索引(Index)和约束(Constraint)

索引: 可通过 SYSCAT.INDEXES JOIN SYSCAT.INDEXCOLUSE来查询 索引的字段有升序ASC和降序DESC,分别表示为SYSCAT.INDEXES的COLNAMES中...
  • davinciyxw
  • davinciyxw
  • 2013年04月09日 16:49
  • 6039

DB2内连接查询和外连接查询

DB2内连接查询返回连接表中符合连接条件和查询条件的数据行,下面就为你详细介绍DB2内连接查询的方法,供您参考学习。   DB2内连接查询(INNER JOIN):   DB2内连接查询有两种...
  • fanyun_01
  • fanyun_01
  • 2017年01月25日 09:14
  • 1005

DB2 with的定义与用法

With定义与用法 -------部分内容为转载并经整理处理--------------------- 1.with理解与基本用法 说起WITH 语句,除了那些第一次听说...
  • kouge94
  • kouge94
  • 2016年03月25日 11:01
  • 4734

DB2和Oracle区别

今天客户来访(日本人),问我DB2和Oracle区别。因为不是DBA(勉强的理由),我还真没有认真总结过。但我的第一感觉:一个是instance,一个是Database。建Ora库和DB2的库是不一样...
  • wenzhihui_2010
  • wenzhihui_2010
  • 2013年08月05日 10:51
  • 2937

db2 函数语法详解

创建函数   SQL 函数的创建和在应用程序中的使用都很容易。CREATE FUNCTION语句定义函数的特征和逻辑,并将函数的特征和逻辑存储在 DB2 系统编目中。该操作被称为注册函数。 ...
  • pianzif
  • pianzif
  • 2014年06月20日 13:34
  • 3031
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:我用DB2的这几年(三)
举报原因:
原因补充:

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