文档章节

DB2优化之:db2expln的使用及实例分析

Goopand
 Goopand
发布于 2015/04/21 16:15
字数 1614
阅读 28
收藏 1
点赞 0
评论 0

1、准备实验环境

我们创建了一个模拟tpch(数据库工业标准测试,http://www.tpc.org/tpch/specs.asp)测试的数据库,库中一共有3张数据表,分别是:

part      产品部件表

supplier  供应商表

partsupp  产品供应商关联表

其中part表中含有200000条数据,partsupp表中含有800000条数据,supplier表中含有10000条数据

1)  我们为如上的3张表分别建立如下的索引:

create index part_idx1 on tpcd.part(p_partkey,p_size);

create index partsupp_idx1 on tpcd.partsupp(ps_partkey, ps_supplycost, ps_suppkey);

create index supp_idx1 on tpcd.supplier(s_suppkey);

 

 

2)  建立索引后,我们收集一下相关的统计信息,在db2cmd中执行如下的命令:

runstats on table tpcd.part with distribution and detailed indexes all;

runstats on table tpcd.partsupp with distribution and detailed indexes all;

runstats on table tpcd.supplier with distribution and detailed indexes all;

 

 

分别对PART,  PARTSUPP,  SUPPLIER运行以下命令,确保runstats已经成功执行:

db2 “select card,npages,stats_time from syscat.tables where tabname=’PART’”

 

CARD                 NPAGES               STATS_TIME

-------------------- -------------------- -----------------------------------------------------------

 200000                 7616               2008-08-21-17.20.22.828000

 

 

其中,CARD为该表的记录数,NPAGES为该表所占有的存储空间(页数)STATS_TIME为收集统计信息的时间。



2、发现问题

1)  我们有如下的一个SQL语句:

 

select

    count(*)

from

         tpcd.part,

         tpcd.partsupp,

         tpcd.supplier

where

         p_partkey = ps_partkey

         and s_suppkey = ps_suppkey

    and p_size = 30

         and ps_suppkey = 9988@

 

 

目前,该SQL的运行速度不理想,我们希望通过调优提高这个SQL语句的执行效率。

首先,我们为了记录这条查询语句执行的时间,运行如下SQL文件,记录一个时间:

文件名:lab.sql

values current timestamp@

 

select       

  count(*)

from

  tpcd.part,       

  tpcd.partsupp,        

  tpcd.supplier

where       

  p_partkey = ps_partkey 

  and s_suppkey = ps_suppkey       

  and p_size = 30     

  and ps_suppkey = 9988@

 

values current timestamp@

 

 

db2cmd中运行:

db2 -td@ -vf  lab.sql

 

 

得到结果如下:

1

--------------------------

2009-01-04-15.09.25.281000

条记录已选择。

 

select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988

1

-----------

1

  1 条记录已选择。

 

values current timestamp

1

--------------------------

2009-01-04-15.09.33.359000

 

  1 条记录已选择。

 

 

通过前后时间对比,我们发现这个SQL运行了大约6秒钟(不同的机器性能可能有差异)



3、分析问题

1)  为了了解这个SQL的执行过程,我们开始分析它的执行计划,在db2cmd中运行:

db2expln -d tpcd -f lab.sql -t -z @ -g > lab-before.exp

 

可以用文本编辑器打开lab-before.exp,下面,我们详细解读其中的执行计划:如图1所示

 

分析:执行计划是倒树状的结构,首先对part表、partsupp表和supplier表进行索引扫描,然后对partpartsupp表的索引扫描结果进行NLJOIN(嵌套循环连接),再将结果与supplier表的索引扫描结果进行HSJOINHASH连接),再进行排序,最后返回查询结果。

其中黄色标记部分,我们发现执行part表的索引扫描花费较大(1261.42个单位),且扫描结果(3810行)与我们的最终期望结果(1)差距较大,执行NLJOIN的花费(7443.881261.4215.1451=6167.31个单位),因此我们认为这里partpartsupp表建立的索引是影响查询效率的因素。



4、解决问题

1)  在仔细分析的问题之后,我们尝试来解决这个问题,我们规划了一个新的索引方案,我们建立新的索引:

drop index part_idx1;

create index part_idx1 on tpcd.part(p_size,p_partkey);

drop index partsupp_idx1;

create index partsupp_idx1 on tpcd.partsupp(ps_suppkey,ps_partkey, ps_supplycost );

drop index supp_idx1;

create index supp_idx1 on tpcd.supplier(s_suppkey);

 

 

我们改变了part表和partsupp表的索引顺序

2)  建立索引后,我们再收集一下相关的统计信息,在db2cmd中执行如下的命令:

runstats on table tpcd.part with distribution and detailed indexes all;

runstats on table tpcd.partsupp with distribution and detailed indexes all;

runstats on table tpcd.supplier with distribution and detailed indexes all;

 

 

3)  下面,我们再执行一下原来的SQL,在db2cmd中执行:

db2 connect to tpcd

db2 –td@ -vf  lab.sql

1

--------------------------

2009-01-04-16.02.45.078000

  1 条记录已选择。

 

select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988

1

-----------

1

  1 条记录已选择。

 

values current timestamp

1

--------------------------

2009-01-04-16.02.45.218000

 

  1 条记录已选择。

 

 

 

通过前后时间对比,我们发现这次,这个SQL运行时间在1秒之内 (不同的机器性能可能有差异)

4)  为了进一步分析这个SQL的执行过程,我们再分析一下SQL的执行计划:

db2cmd中运行:

db2expln -d tpcd -f lab.sql -t -z @ -g > lab-after.exp

 

 

 

可以用文本编辑器打开lab-after.exp,下面,我们详细解读这个执行计划,如图2所示

 

从执行的总花费(84.817)上我们可以明显的看到优化后的效果。



5、解决方案分析

我们来看实验Sql语句的谓词部分:

p_partkey = ps_partkey

   and s_suppkey = ps_suppkey

     and p_size = 30

         and ps_suppkey = 9988@

 

DB2sql优化器在执行查询sql语句,根据谓词进行表连接查询,并不依赖于where条件中谓词的顺序,而是根据所建索引来进行先后顺序的连接。

我们再来看优化前的索引:

create index part_idx1 on tpcd.part(p_partkey,p_size);

create index partsupp_idx1 on tpcd.partsupp(ps_partkey, ps_supplycost, ps_suppkey);

create index supp_idx1 on tpcd.supplier(s_suppkey);

 

1)我们目标是尽量增大第一次或前几次join的数据量缩小幅度,所以首先要进行小表的索引扫描和连接。而这里,从业务角度来说,把业务主键放到索引的第一个位置是有意义的,但是对于优化器来说,这毫无意义。优化器会根据索引优化器会首先选择谓词:p_partkey = ps_partkey partpartsupp进行NLJOIN,而这两个表是数据量相对大的表。

2NLJOIN中外表只扫描一次,内表扫描N次,所以内表要尽量的小一些。而这里的内表partsupp800000条数据。

我们期望优化器做如下处理:

1)优化器首先根据谓词p_size = 30 ps_suppkey = 9988@进行索引扫描,缩小数据范围。

2)优化器根据谓词s_suppkey = ps_suppkeysupplierpartsupp进行表的NLJOIN。内表(partsupp)是数据量较小的一个表

所以,我们要将p_sizeps_suppkey的索引提前,建立如下索引

create index part_idx1 on tpcd.part(p_size,p_partkey);

create index partsupp_idx1 on tpcd.partsupp(ps_suppkey,ps_partkey, ps_supplycost );

create index supp_idx1 on tpcd.supplier(s_suppkey);

 

 


6、总结

使用db2expln解释工具,能够得到DB2 Sql优化器的详细Sql执行计划,通过其中的花费我们可以结合sql语句及表、索引、连接的结构进行分析,发现并定位问题,然后对sql进行改进,达到优化的目标。


本文转载自:http://blog.csdn.net/hulua2010/article/details/42026985

共有 人打赏支持
Goopand
粉丝 8
博文 180
码字总数 183387
作品 0
朝阳
DB2常用命令

常用命令 1.启动数据库 db2start 2.停止数据库 db2stop 3.连接数据库 db2 connect to oyd user db2 using pwd 4.读数据库管理程序配置 db2 get dbm cfg 5.写数据库管理程序配置 db2 up...

蚂蚁也疯狂 ⋅ 2014/01/19 ⋅ 0

DB2 故障处理的思路及一般问题的解决办法

我认为解决问题的关键在于分清问题的种类,并清楚每种问 题的解决办法。另外很多的数据库的问题都是由于错误的操作,错误的配置引起的,所以本文在解释怎么样处理问题时也会给出一些好的建议...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

db2pd和db2support

C:IBMSQLLIBBIN>db2 get instance C:IBMSQLLIBBIN>db2 get dbm cfg C:IBMSQLLIBBIN>db2 connect to sample C:IBMSQLLIBBIN>db2 "select bpname,bufferpoolid,npages,pagesize from syscat.bu......

晨曦之光 ⋅ 2012/03/09 ⋅ 0

常用的DB2命令

启动DB2服务:db2start 关闭DB2服务: db2stop 一、加载数据: 1、 以默认分隔符加载,默认为“,”号 db2 "import from btpoper.txt of del insert into btpoper" 2、 以指定分隔符“|”加载 ...

玛雅牛 ⋅ 2013/01/23 ⋅ 0

db2 command list

工作一个多月了,因为公司要用DB2数据库,所以总是努力去看这方面的书,一段时间来有点体会也总结了一些常用的DB2命令,发出来给大家分享吧!希望对大家会有所帮忙,呵呵。。 启动DB2服务:d...

jiyayun ⋅ 2013/07/19 ⋅ 0

IBM DB2 11.1与Oracle Database 12c对比

  【IT168 评论】市场形势 对于希望根据 IT 基础架构需求做出关键选择的企业来说,面临的选择从未如此 丰富。通过运用内部、云端和混合备选方案交付 IT 资源,企业能够根据自身需 求和现有...

IBM ⋅ 05/04 ⋅ 0

深入了解 IBM DB2 高级企业版

简介: IBM® DB2® Advanced Enterprise Server Edition(AESE)是将 IBM DB2 Enterprise Server Edition V9.7 与优化、开发、管理工具打包在一起销售的软件包。这是用于管理 DB2 环境关键方...

IBMdW ⋅ 2011/06/24 ⋅ 1

BigInsights -- 基于 Hadoop 的大数据分析平台

本文针对 IBM 最新开发的数据分析平台进行概要介绍并对其应用进行指导说明。随着信息技术应用范围的不断扩展,对数据进行挖掘分析的需求日益增加,但是信息量的不断增大及其应用构建的复杂性...

zoujiajun33 ⋅ 2016/10/08 ⋅ 0

linux系统安装配置DB2 10.1 数据库

DB2 是IBM公司研制的一种关系型数据库系统。DB2主要应用于大型应用系统,具有较好的可伸缩性,可支持从大型机到单用户环境,应用于OS/2、 Windows等平台下。 DB2提供了高层次的数据利用性、完...

zhangdiandong ⋅ 2014/08/26 ⋅ 0

DB2常用命令

1、 打开命令行窗口   #db2cmd 2、 打开控制中心   # db2cmd db2cc 3、 打开命令编辑器  db2cmd db2ce =====操作数据库命令===== 4、 启动数据库实例   #db2start 5、 停止数据库实例  ...

KavenSu ⋅ 2014/04/25 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

MyBatis四大核心概念

本文讲解 MyBatis 四大核心概念(SqlSessionFactoryBuilder、SqlSessionFactory、SqlSession、Mapper)。 MyBatis 作为互联网数据库映射工具界的“上古神器”,训有四大“神兽”,谓之:Sql...

waylau ⋅ 28分钟前 ⋅ 0

以太坊java开发包web3j简介

web3j(org.web3j)是Java版本的以太坊JSON RPC接口协议封装实现,如果需要将你的Java应用或安卓应用接入以太坊,或者希望用java开发一个钱包应用,那么用web3j就对了。 web3j的功能相当完整...

汇智网教程 ⋅ 42分钟前 ⋅ 0

2个线程交替打印100以内的数字

重点提示: 线程的本质上只是一个壳子,真正的逻辑其实在“竞态条件”中。 举个例子,比如本题中的打印,那么在竞态条件中,我只需要一个方法即可; 假如我的需求是2个线程,一个+1,一个-1,...

Germmy ⋅ 54分钟前 ⋅ 0

Springboot2 之 Spring Data Redis 实现消息队列——发布/订阅模式

一般来说,消息队列有两种场景,一种是发布者订阅者模式,一种是生产者消费者模式,这里利用redis消息“发布/订阅”来简单实现订阅者模式。 实现之前先过过 redis 发布订阅的一些基础概念和操...

Simonton ⋅ 今天 ⋅ 0

error:Could not find gradle

一.更新Android Studio后打开Project,报如下错误: Error: Could not find com.android.tools.build:gradle:2.2.1. Searched in the following locations: file:/D:/software/android/andro......

Yao--靠自己 ⋅ 昨天 ⋅ 0

Spring boot 项目打包及引入本地jar包

Spring Boot 项目打包以及引入本地Jar包 [TOC] 上篇文章提到 Maven 项目添加本地jar包的三种方式 ,本篇文章记录下在实际项目中的应用。 spring boot 打包方式 我们知道,传统应用可以将程序...

Os_yxguang ⋅ 昨天 ⋅ 0

常见数据结构(二)-树(二叉树,红黑树,B树)

本文介绍数据结构中几种常见的树:二分查找树,2-3树,红黑树,B树 写在前面 本文所有图片均截图自coursera上普林斯顿的课程《Algorithms, Part I》中的Slides 相关命题的证明可参考《算法(第...

浮躁的码农 ⋅ 昨天 ⋅ 0

android -------- 混淆打包报错 (warning - InnerClass ...)

最近做Android混淆打包遇到一些问题,Android Sdutio 3.1 版本打包的 错误如下: Android studio warning - InnerClass annotations are missing corresponding EnclosingMember annotation......

切切歆语 ⋅ 昨天 ⋅ 0

eclipse酷炫大法之设置主题、皮肤

eclipse酷炫大法 目前两款不错的eclipse 1.系统设置 Window->Preferences->General->Appearance 2.Eclipse Marketplace下载【推荐】 Help->Eclipse Marketplace->搜索‘theme’进行安装 比如......

anlve ⋅ 昨天 ⋅ 0

vim编辑模式、vim命令模式、vim实践

vim编辑模式 编辑模式用来输入或修改文本内容,编辑模式除了Esc外其他键几乎都是输入 如何进入编辑模式 一般模式输入以下按键,均可进入编辑模式,左下角提示 insert(中文为插入) 字样 i ...

蛋黄Yolks ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部