解一个标准规则的集合──DB2优化器

日期:2008-6-4作者:海燕来源:赛迪网

DB2   优化器   成本   存取路径   CPU成本   I/O成本   SQL语句   

  和Oracle数据库一样,DB2数据库里面也是通过优化器来分析你的SQL,生成它认为最优的执行计划(Access Plan)。DB2的优化器实际上是一个标准规则集合,一般来说我们只要告诉DB2要检索什么,而不是如何检索。

  那么DB2的优化器是根据什么来判断SQL的最优存取路径呢?

  DB2的优化器是基于成本的优化器,也就是CBO(Cost Based Optmizer)。也就是说DB2 优化器会应用查询成本公式,该公式对每条可能的存取路径的四个因素进行评估和权衡:CPU 成本、I/O 成本、DB2 系统目录中的统计信息和实际的 SQL 语句。

  那么我们来简单看一下DB2的优化器的工作流程:

  1. DB2的优化器,在接收到SQL语句后,会首先校验SQL的语法,确保是正确的SQL;

  2. 根据当前的系统环境信息,生成最优的执行计划来优化SQL语句;

  3. 把SQL翻译成计算机指令语言,并执行这个优化后的SQL;

  4. 返回结果,或者存储它们,以便将来的执行。
 
  在我们看来,DB2 系统目录中统计信息是让DB2优化器正确工作的一个非常重要的依据。这些统计信息向优化器提供了与正在被优化的 SQL 语句将要访问的表状态相关的信息。这些信息主要包括:

  Table--包括表的记录数、PAGE、PCTFREE以及COMPRESS等信息,相关的系统视图是:sysstat.tables、syscat.tables。

  Columns—包括COLUMNS的数量、长度、分布特征以及COMPRESS等信息,相关的系统视图是:sysstat.columns、syscat. columns。

  Index--包括是否存在索引、索引的组织(叶子页的数量和级别的数量)、索引键的离散值的数量以及是否群集索引, 相关的系统视图是:sysstat.indexes、syscat. indexes。

  其他的还有分区/节点组信息和表空间的信息,如何及时更新这些信息呢?保证DB2优化器正确的工作,在DB2里面提供了以下的办法。

  RUNSTATS与REOGCHK

  Runstats这个命令的功能主要就是收集数据库对象的状态信息,这对数据库使用合理的ACCESS PLAN是至关重要的。一般来说,以下几种情况下面,我们需要用runstats来收集统计信息:

  1. 在给表创建一个index后,我们最好做一次runstat。这个情况也是大家经常忽略的。很多时候大家在给表增加了一个index后,分析执行计划,发现没有变化,觉得很奇怪。其实这个时候,你需要做一次runstats,就可以了。在8.2里面,DB2做了很好的改进,可以避免这个问题,在创建index的时候,可以立即更新你的信息。

  2. 在对table做了一次reorg后,记得要做一次runstats。因为对表做reorg,会修改表的很多信息,比如高水位等,所以做一次runstats,可以更新统计信息。

  3. 当你的表里面的数据发生了比较大的变化,一般来说,大约表里面的数据量的10%-20%发生了变化,就应该作一次runstats。这些变化包括删除,修改,插入。对于一些非常大的表,比方在数据仓库的项目里面,某些事实表非常巨大。这个时候,完整的对一个大表作runstats可能花费时间相当大,DB2 8.1里面支持我们对这些大表作抽样,比方说只对20%的数据作runstats,这样的话,一般来说也能保证得到正确的执行计划。当然首先要确保这个表里面的数据最好分布比较均匀。

  4. 当你在分区(DPF)数据库里面使用了REDISTRIBUTE DATABASE PARTITION GROUP这个命令,那么就需要用runstats来收集新的统计信息。

  RUNSTATS命令的语法如下:

  如果表名为DB2INST1.STAFF,表上有索引,则可以用下面的例子完成RUNSTATS命令:

db2 runstats on table db2inst1.staff with
distribution and detailed indexes all

  在实际的项目里面,对于变化比较大的表,需要我们定时对数据库做runstats,一般来说runstats和reorg可以结合起来做,首先对表作reorg,然后做runstats,最后REBIND数据库根据最新的统计信息生成合适的统计计划。

  值得注意的是,如果我们要处理的表数据量是快速变化的,比如在电信移动行业,需要在月末进行处理的汇总表。在不长的时间范围内数据量变化特别大,从而使得RUNSTATS 得到的统计信息不准确,原因是这些统计信息只是某个时间点的信息。您可以用这条语句来把表修改为volatile。

alter table table_name volatile cardinality

  这样优化器将考虑使用索引扫描而不是表扫描。无论统计信息如何,优化器将使用索引扫描而不是使用表扫描。

  IBM的文档里面还提供了REORGCHK这个命令,可以根据统计公式计算表是否需要重整。

  比如可以分为对系统表和用户表两部分分别进行REORGCHK:

  (1)针对系统表进行REORGCHK:

db2 reorgchk update statistics on table system

  (2) 针对用户表进行REORGCHK:

db2 reorgchk update statistics on table user

  需要注意的是,如果数据库中数据量比较大,这些操作一般所需时间比较长,所以尽量安排在数据库比较空闲的时候做。

db2 update db cfg using AUTO_MAINT off AUTO_TBL_MAINT
off AUTO_RUNSTATS off
 
  在DB2 8.2里面数据库可以自动进行统计信息收集,不过这样的动作还是会带来额外的负载,一般情况下面可以关掉,只在我们需要的时候运行就可以了。

  LOAD

  Load这个工具是DB2里面一个非常强大的数据迁移工具。一般用作大批量的数据插入。因为Load操作不记日志,所以效率非常好。笔者曾经在RS6000平台上面实现50-60m/s的速度Load数据。在这里我想讨论的是在DB2数据库里面如何用load来影响你的catalog视图的统计信息。

  在Load的时候使用statistics选项可以在装入的过程中生成统计数据,这些统计数据可以供优化器确定最有效的执行sql语句的方式。

  比如:

  对表和索引产生最详细的统计数据:

load from mobile_number.del of del replace into
mobile statistics yes with distribution and detailed indexes all
对表和索引都产生简略的统计:
load from mobile_number.del of del replace into
mobile statistics yes and indexes all
需要值得注意的时候在DB2 8.2新版本里面,可以这样做:
load from mobile_number.del of del replace into
mobile statistics use profile
 
  DB2 LOOK

  Db2look是DB2一个相当强大的一辅助工具,可以帮助我们从当前数据库里面把数据库结构抽取出来。在数据迁移的很多场合,我们都非常需要这个强大的工具。

  在一些场合,特别是开发库迁移到生产库,生产库迁移到开发库的环境中,为了保证SQL执行计划的一致,我们需要用db2look这个工具,利用 db2look 工具提供的抽取数据库对象统计信息的功能,把数据库的统计信息进行迁移。

  比如:

  (1) 在用户环境下提取统计信息:

  db2 runstats on table <用户表模式名>.<表名>

  db2look -d <用户数据库名> -t <表名> -m -o statis.sql

  输出文件中是对用户的 DB2 系统编目表中与该表统计信息相关的各字段值的 update 语句。

  db2 -svtf statis.sql
 
  (2) 利用用户提供的统计信息更新测试环境下测试表的统计信息。

  总结

  本文对DB2里面更新执行计划的几个常见的方法,做了一些抛砖引玉的说明。实际工作中的环境,是千差万别的,会有很多的不同。需要强调的是,在DB2数据库里面,基于成本的优化器决定着SQL的执行效率。而正确、及时地收集数据库的统计信息对于让优化器生成正确的执行计划是至关重要的。

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

评论
查看更多评论

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

DB2数据库性能诊断与调优>更多

相关推荐

  • 数据库产品巡礼:IBM DB2概览

    IBM DB2关系型数据库管理系统提供了支持多平台系统的关键技术,它具备较高的可用性和良好的性能。

  • 如何进行分布式大数据应用调优

    分布式环境通常是与数据库服务器相分离的。而DBA的工作就是监视这些环境并配置和优化数据库服务器以满足多种需求。大数据的出现加剧了DBA的问题。

  • IBM DB2将迎来30岁“生日”

    再过几天,主流数据库产品DB2就将迎来它30岁的“生日”。作为关系型数据库技术的标志性产品,DB2在过去的30年中也在伴随用户需求的变化不断地发展。

  • SQL调优之“忧”:如何进行SQL调优

    DBA们应该将自己从“我要对什么调优?”的老路上解放出来,而在指标、配置和成本方面花费一定的时间。

技术手册>更多

  • SQL Server与SharePoint技术指南

    SQL Server和Microsoft SharePoint Server二者通过一种复杂的关系被连接在一起,SQL Server可以作为SharePoint Server的后端数据存储库。有些时候管理员并没有搞清楚这二者的关系,但它的确可以帮助DBA们解决很多问题。

  • Oracle GoldenGate数据库同步技术

    Oracle Goldengate是一款实时访问、基于日志变化捕捉数据,并且在异构平台之间进行数据传输的产品。利用这个技术,Goldengate 能够实现低延迟、高性能、低影响进行连续的数据集成。

  • SQL Server十大技巧集锦

    新年干货大放送。TechTarget数据库网站总结了过去一年最实用、最受欢迎的十大SQL Server技巧,分享给各位DBA。预祝DBA们马年“马上有成功”!

  • SQL Server群集与非群集索引设计指南

    在SQL Server中,我们如何进行索引设计来提升系统的性能和对查询进行优化,是许多用户非常关注的问题,在本次技术专题中,我们将分群集索引和非群集索引设计两部分来为您详细分析。

TechTarget

最新资源
  • 安全
  • 存储
  • CIO
  • SOA
  • 云计算
  • 商务智能