db2 EXPLAIN分析SQL

#利用EXPLAIN分析SQL

--------------------------------------------------------------------------------------------------
SQL 的 Explain 通过图形化或基于文本的方式详细说明了 SQL 语句的每个部分是如何执行以及何时执行的。这包括以下一些基本信息：

正被访问的表 
正被使用的索引 
何时连接数据 
何时排序数据 
Explain 还捕获更复杂的信息，在细究 SQL 正在发生什么时，该信息非常有用：

表和索引基数 
正连接的表和索引的顺序 
结果集的基数 
在查询的每部分正在选择哪些字段 
排序方法 
SQL 每部分的时间范围

要理解 Explain 信息，您需要掌握的最重要概念是 timeron。timeron 是 DB2 优化器使
用的计量单位，用来计量完成查询的时间和资源数量。timeron 综合了时间、CPU 利用率、
I/O 和其它因素。由于这些参数值是变化的，因此执行某个查询所需的 timeron 数量是动态
的，每次执行该查询所需的 timeron 都不同。

#使用控制中心的EXPLAIN SQL
观察SQL的存取方案，分析优化参数，考虑优化的级别。
#使用字符工具
db2 set current explain mode [no|yes|explain]
db2exfmt
可以从命令行调用 db2expln 工具，以获得查询的存取方案。但是，该工具不返回优化器信息。
db2expln -database gzdb -statement "select * from syscat.tables" -terminal

=============================||

转自：http://www.searchdatabase.com.cn/showcontent_17217.htm

简介

　　随着DB2应用的逐渐增多，越来越多的数据库开发人员在项目开发过程中都会遇到查询过于复杂，导致性能难以接受的问题。本文将主要从一个数据库开发者的角度介绍几种常用的方法来提高 DB2查询的性能，而并不讨论如何通过配置DB2的各项参数以及调整服务器环境等方式来提高整个数据库性能的方法。系统配置等工作属于 DBA 的工作范畴，在一般的项目开发中，这对于开发人员都是透明的。本文先对 DB2 提供的几种用于提高查询性能的相关工具和命令进行介绍，然后根据笔者的工作经验介绍一些常用的技巧和方法来提高查询性能。主要集中于如何创建和维护索引、改写查询以及改变查询的实现方式，相关内容都将通过实例加以说明。
 
　　DB2提供的几种相关工具和命令

　　我们将着重介绍如何使用 Visual Explain 和 db2expln 查看动态查询的存取计划。读者可以查阅 DB2 Info Center获得有关查看静态查询存取计划的内容。

　　DB2 Visual Explain

　　DB2 提供了非常直观有效的方法来查看查询的存取计划。DB2 Visual Explain 能够获得可视化的查询计划，而 db2expln 命令则可以获得文本形式的查询计划。有了查询计划，我们就可以有针对的对查询进行优化。根据查询计划找出代价最高的扫描 ( 表扫描，索引扫描等 ) 和操作 (Join，Filter，Fetch 等 )，继而通过改写查询或者创建索引消除代价较高的扫描或操作来优化查询。

　　DB2 提供了多种方法来得到可视化查询计划。

   1. 通过 DB2 Control Center 获得可视化查询计划。如图1：

　　

　　图1. 可视化查询计划

　　可视化查询计划
 
　　点击”Explain SQL”后输入要进行分析的查询语句以及查询标号和标签，点击 Ok 按钮便可得到可视化的查询计划。此时，查询计划会被存储在系统的 Explain 表中。用户可以通过图 1 中的”Show Explained Statements History”命令获得存储在 Explain 表中的所有查询计划。

   2. 通过 Command Editor( 在 DB2 8.2 版本之前叫做 Command Center) 获得可视化的查询计划。如图 2：

　　

　　图2. 获得可视化的查询计划

　　获得可视化的查询计划

　　在主窗口输入查询并连接数据库后，点击图中所示的按钮即可得到可视化的查询计划，如图 3：

　　

　　图3. 查询计划结果

　　查询计划结果
 
　　在图 3 所示的查询计划中，还可以点击图示中的每个节点来察看详细的统计信息。譬如双击节点”FETCH(13) 21，959.75” 后将会弹出如图 4 所示的对话框：

　　

　　图4. 详细的统计信息

　　详细的统计信息
 
　　图 4 中的统计信息主要包括此 FETCH 操作的总代价，CPU，I/O 以及获得结果集中的第一行的代价。在这里，timerons 是结合了 CPU 和 I/O 代价的成本单位。此外，图 4 中还收集了其他相关信息。譬如此操作读取了哪个表的哪些列，每个谓词的选择度 (selectivity)，使用了多少 buffer 等等。

　　db2exfmt

　　db2exfmt 命令能够将 Explain 表中存储的存取计划信息以文本的形式进行格式化输出。db2exfmt 命令将各项信息更为直观的显示，使用起来更加方便。命令如清单 1 所示：

　　清单1. db2exfmt 命令
                

db2exfmt -d <db_name> -e <schema> -g T -o <output> -u <user> <password> -w <timestamp> 
Example: db2exfmt -d test_db -e user -g T -o D:\temp\sql_1_result_db2exfmt.txt
 -u user password -w l 
Query: 
                sql_1.txt（附件中） 
                Results: 
                sql_1_result_db2exfmt.txt（附件中）          

　　db2expln

　　db2expln 是命令行下的解释工具，和前面介绍的 Visual Explain 功能相似。通过该命令可以获得文本形式的查询计划。命令如清单 2 所示:

　　清单2. db2expln 命令      

   db2expln -d <db_name> -user <user> <password> -stmtfile <sql.file> 
   -z @ -output <output> -g 
Example: db2expln -d test_db -user user password -stmtfile D:\temp\sql_1.txt 
 -z @ -output D:\temp\sql_1_result_db2expln.txt –g 
Query: 
                 sql_1.txt（附件中） 
                Results: 
                 sql_1_result_db2expln.txt（附件中） 

　　db2expln将存取计划以文本形式输出，它只提供存取计划中主要的信息，并不包含每一个操作占用多少 CPU、I/O、占用 Buffer 的大小以及使用的数据库对象等信息，方便阅读。但是 db2expln 也会将各项有关存取计划的信息存入 Explain 表中，用户可以使用 db2exfmt 察看详细的格式化文本信息。

　　db2advis

　　db2advis是DB2提供的另外一种非常有用的命令。通过该命令DB2可以根据优化器的配置以及机器性能给出提高查询性能的建议。这种建议主要集中于如何创建索引，这些索引可以降低多少查询代价，需要创建哪些表或者 Materialized Query Table(MQT) 等。命令如清单 3 所示：

　　清单3. db2advis 命令       

db2advis -d <db_name> -a <user>/<password> -i <sql.file> -o <output> 
Example: db2advis -d test_db -a user/password 
 -i D:\temp\sql_2.txt > D:\temp\sql_2_result_db2advis.txt 
Query: 
                 sql_2.txt（附件中） 
                Results: 
                sql_2_result_db2advis.txt（附件中）  

　　通过 -i 指定的 SQL 文件可以包含多个查询，但是查询必须以分号分隔。这与db2expln命令不同，db2expln可以通过-z参数指定多个查询之间的分隔符。用户可以把某一个 workload 中所使用的所有查询写入 SQL 文件中，并在每个查询之前使用”--#SET FREQUENCY <num>”为其指定在这个workload中的执行频率。db2advis会根据每个查询在这个 workload 的频率指数进行权衡来给出索引的创建建议，从而达到整个workload的性能最优。

　　db2batch

　　前面介绍的工具和命令只提供了查询的估算代价，但有些时候估算代价和实际的执行时间并不是完全呈线形关系，有必要实际执行这些查询。db2batch就是这样一个 Benchmark工具，它能够提供从准备到查询完成中各个阶段所花费地具体时间，CPU时间，以及返回的记录。命令如清单4 所示：

　　清单4. db2batch命令        

 db2batch -d <db_name> -a <user>/<password> 
 -i <time_condition> -f <sql.file> -r <output> 
Example: db2batch -d test_db -a user/password 
 -i complete -f D:\temp\sql_3.txt -r d:\temp\sql_3_result_db2batch.txt 
Query: 
                 sql_3.txt（附件中） 
                Results: 
                 sql_3_result_db2batch.txt（附件中）

　　对于执行db2batch时一些详细的设置可以通过-o参数指定，也可以在SQL文件中指定，譬如本例中在SQL文件中使用了下面的配置参数 :

　　--#SET ROWS_FETCH -1 ROWS_OUT 5 PERF_DETAIL 1 DELIMITER @ TIMESTAMP

　　其中ROWS_FETCH和ROWS_OUT定义了从查询的结果集中读取记录数和打印到输出文件中的记录数，PERF_DETAIL设置了收集性能信息的级别，DELIMITER则指定了多个查询间的间隔符。

提高DB2查询性能的常用方法

原文出处：http://www.ibm.com/developerworks/cn/db2/zones/performance/

=============================||

转自：http://www.db2china.net/home/space.php?uid=29548&do=blog&id=14172

在之前的博文中说了如何去查看SQL的访问计划，当我们发现当前计划需要调整或者想看看有无优化空间时，我们可以使用db2advis工具，该工具是针对用户提供的工作负载（此工作负载非彼工作负载，你懂的，不懂也没关系。这里的工作负载就是一组SQL语句的组合）而给出的优化建议，优化建议包括创建索引，MQT，MDC以及表重分布等，优化建议以DDL的形式提供，用户直接运行即可。另外还会给出该工作负载未使用的索引及MQT等建议删除的信息。
目前个人感觉索引的建议比较有用，另外也不必删除索引吧，因为并不是只有当前的这个工作负载。

常用的有三种：
1.指定单条SQL语句
$db2advis -d cphapp -s "select * from test" -o result.out
说明：-s指定单条SQL语句用于评估并给出索引建议，需要注意的是SQL语句需要由双引号包围；-o 指定建议输出至文件。
2.针对DB2快照中捕获的一组SQL
$db2 reset monitor for database cphapp
$db2advis -d cphapp -g -p -o result.out
说明：首先重置数据库监视器，然后让应用程序运行足够长的时间，以使DB2快照可以捕获到足够的动态SQL语句，然后使用db2advis工具估计工作负载并给出建议。
-g指示从动态SQL快照获得SQL语句；-p指出将捕获的SQL语句存储在ADVISE_WORKLOAD表中（此表在运行EXPLAIN表脚本时创建）
3.用户提供工作负载文件，db2advis根据此文件给出建议
$db2advis -d cphapp -i input.sql -o result.out
说明：-i指定用于分析的工作负载文件。
工作负载文件说明：
定义SQL语句的执行频率：“--#SET FREQUENCY 100”
定义注释：“--comment“

部分参数的描述：
-m：指定返回的建议类型，包括IMCP--I（索引）M（MQT物化视图）C（MDC多维聚簇表）P（repartition），默认是I即索引。
-q schema-name：即限定工作负载中未指定模式的查询对象的模式名。
-l disk-limit：限定所建议的索引或物化视图所占的磁盘空间，单位是M。-1表示最大size；默认是数据库总大小的20%
-delim char：指定工作负载文件中的语句结束符，默认为“；”
-b tablespace-name：指定建议创建的MQT所在表空间


现在遇到的问题是不管使用单条SQL方式还是定义工作负载文件方式，db2advis不会给出创建索引的建议，可是我认为创建索引会改善查询速度，不知何故，学习。。。
此问题已经解决，原来是创建索引所分配的空间不足导致不给出任何建议，同样的SQL语句，首先使用
$db2advis -d cphapp -i stmt.db2 -m I
没有给出任何建议，但是在建议的开头有两行：
total disk space needed for initial set [0.000]MB
total disk space constrained to [967.930]MB

当我使用另一个参数时：
$db2advis -d cphapp -i stmt.db2 -l -1 -m I
给出了我预期的建议，建议创建一个索引，同样在建议的开头有两行：
total disk space needed for initial set [1453.513]MB
total disk space unconstrained

总结：使用了选项-l -1将不限制创建索引所占用的空间，默认是总空间的20%，超过的话将不给出创建索引的建议。

=============================||

转自：　http://blog.sina.com.cn/s/blog_780a4e2e01016mln.html　

大家读过古龙先生的七种武器吗？他们各有各的妙用，各有各的人生道理在其中。DB2中也有这么七种武器，它们也是用途不同，用好了威力无穷。

俺所谓的七种武器就是DB2的七个命令：

第一种，霸王枪，db2pd。

db2pd是一件真正的神器，许多DBA包括俺，就靠它活着了。它主要用于检查DB2某方面的状态和诊断DB2的问题，它来源于informix数据库的onstat工具，IBM收了informix后，从DB2的V8版本开始被引入，它最大的特点是直接从实例和数据库的共享内存中取所需数据，由于不需要获取latches和引擎资源就能干活，因此db2pd可以近似认为不会对系统性能产生影响，相当于是一个走后门不按常理出牌的冬冬，DB2中走后门的工具不只它一个，比如load工具。

它是咋样实现直接去共享内存中取数的嗫？熟悉开发的程序猿或媛们一下就能猜到了吧，就是利用了shmget()、shmat()、shmctl()这些函数呗，在Linux中用strace，AIX中用truss命令就可以跟踪的到，比方说Linux：

db2inst1@susea:~/sqllib> strace db2pd -db sample -app

-- 结果好多啊，看一下它的输出，这些函数调用暴露无疑。

…

shmget(0xbc789261, 0, 0700)                     = 60522498

shmctl(60522498, IPC_64|IPC_STAT, 0xbfe93ac4)  = 0

shmat(60522498, 0x10000000, 0)                 = 0x10000000

mmap2(NULL, 16842752, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)        = 0xaec92000

shmat(69304326, 0xa5920000, 0)                 = 0xa5920000

shmat(69271557, 0xaade0000, 0)                 = 0xaade0000

…

-- strace和truss是OS提供的问题诊断的工具，你了解的越多越好啦。

尽管db2pd无比强大，但是它也有让人头痛的地方，比方说，由于没有锁定，它提供的数据偶尔不太靠谱；它还必须在服务器端执行；截止到V95版本，db2pd拥有50多个参数，闹心的是每个小版本提供的参数都不一样，你打一个补丁，说不定就打出来一个db2pd的新功能出来；功能强大的另一面就是学习起来麻烦，咱们只能是见招拆招，遇到哪个说哪个了。所以这个命令有点让我欢喜让我忧的感觉，不过总的来说，当然还是欢喜多。

第二种，长生剑，db2exfmt。

大家学习数据库都是从SQL语句开始的吧。尽管你会讲中文、说英语、懂方言，但是抱歉，关系型数据库只听的懂SQL语言。所以你要想让数据库给你干活，你必须先学它听的懂的SQL，否则你对他说：亲，把A表的前三条记录给俺拿出来瞧瞧，你觉着数据库会理你这茬吗？他根本听不懂人话啊！在select/update/insert/delete等小有基础后，你选择一到多个关系数据库的产品来学习，这个时候，实例、数据库、表空间、锁、排序、监控器神马神马的就一窝蜂地来了。但是你有没有这样的一个感觉，学到后来，似乎又回到了起点，又是在研究select，在学SQL？以前你以实现目的为目标，现在你以高效率实现目的为目标，这就是进步。

当学会了SQL，你就学会了如何对DB2呼来喝去，你只管开心地告诉DB2你想要什么，但是你有木有关心过DB2？它是怎么样完成你交给它的任务？它是轻松加愉快地完成的，还是它累到吐血了？当大多数人只关心你飞的有多高时，只有好基友会关心你飞的累不累。

db2exfmt就是这样一个DB2的好基友，好武器，它帮咱做这样一件事：它会告诉你的SQL如果交给DB2去做，DB2会怎样去实现它，这个实现的过程说明就是SQL执行计划。做为DBA，必须关注那些核心SQL语句的执行计划，并将它们小心维护好，这是DBA最核心的工作之一。db2exfmt直接处理已收集并存储在解释表中的解释数据或快照信息，在解释表中查询信息、格式化结果，并生成一份执行计划的报告。

第三种，孔雀翎，db2dart。

db2dart是一个凶悍的武器，dart的含义是Database Analyses Report Tools，简称dart，而dart刚好是飞标的意思，咱们的第三种武器孔雀翎也是一种暗器，我给大家先讲讲这个段子，哈哈，你们不会反对吧？《孔雀翎》讲的是一位大哥要去报仇，可是坏蛋的武功那个高强啊，这大哥心里没底啊！于是他找他的一个朋友去借天下无敌的武器孔雀翎，这个暗器其实失传很久了，他朋友随便弄了个铁家伙就说这是孔雀翎，大哥信了，就去找坏蛋了，因为怀揣孔雀翎，大哥很有信心，反正打你不过，老子就放孔雀翎！经过一场大战终于毙了那个坏蛋，回来后还孔雀翎时，被他朋友告知真相。要说他那朋友也挺孙子哈，万一大哥掉链子肿么办呢。这个故事也挺教育我们DBA啊，你能把数据库技术学好，有这个信心很重要呢！

故事讲完，回来接着说，db2dart主要用于数据库分析诊断，它的功能强劲，日常运维最常用的是利用它诊断表空间高水位问题，但它的本事可不仅这些，它涉及到许多方面，当数据库损坏而又没有备份时，db2dart也是咱们的最后一颗救命草。某省移动公司（为了给面子，俺就不点名了）就遭遇到了这样的倒霉事，没备份，bad page了，最后用了db2dart大招，花了一周的时间，将数据基本恢复了。

db2dart不爽的地方是，它在使用时，要求数据库必须是deactivate的，也就是说这个工具是事后诸葛亮型的，数据库活着的时候不能用！死着的才行，这是和其他工具区别显著的地方。传说db2dart还有一些未公开的参数，即使被800指挥着客户用了，过一段时间也会自动失效，这更增加了这个工具的神秘感。

第四种，碧玉刀，db2advis。

OLTP系统如何提高性能，怎样才能让你的DB2跑的更快？窃以为有两条最重要的原则：1、降低数据规模；2、合理的索引。有人说物理设计啥啥的都很重要啊！先不和你抬杠，这些问题在后面都会说到的。第1条很好理解，胡司令在N年前就说过了：想当初，老子的队伍才开张，总共才有十几个人七八条枪。哈哈！如果你的数据库数据规模很小，比如就十几条记录七八张表，那你根本不必关心怎么去优化它，再二的DBMS产品也是运转如飞的，你优化的本事根本没有用武之地，如何降低数据规模？哪些数据有用，哪些数据可以迁出？这需要你对业务了如指掌，不懂业务的DBA，不是好的数据库管理员哦。

db2advis就是帮你完成第2条工作目标的武器，合理的索引设计是性能强悍的必要条件之一。有同学认为性能优化就是建索引，其实不然，光知道建有啥用？删索引、引导优化器使用合理的索引也同样重要，db2advis会替你支招，其中就包括创建、删除的建议，甚至还有MQT等，老少爷们大家走着瞧吧，关于db2advis广告之后，后面更加精彩！

第五种，多情环，db2look。

多数人都知道db2look是干啥地，我觉着它是一个挺了不起的工具，是DB2数据库的特色工具，它用于生成数据库的DDL，这为异构平台搬迁数据成为可能，讲到这儿，哥不免要为DB2叹息一声，在Oracle新的版本中，已经支持跨平台的备份恢复，而DB2还只能依靠db2look+db2move这对组合来完成这类工作。

我把db2look语句写在crontab中，让它每天都定时执行一次，我让我团队里的哥们每天都去阅读它生成的文件，并与前一天对比，为什么这么做呢？这是在没有启用数据库审计的情况下的想出来的土招--我要防止数据库有不明来历的新对象产生。另外，当数据库存在identity列或Sequence对象时，可以通过db2look的生成文件观察到它们的变化趋势，这方便你对SEQ资源何时枯竭做出估计。实际运维中，建议你至少拥有一份完整的生产库db2look文件，以备不时之需。

db2look另一个猛的功能是它的mimic模式。来看这样一个应用场景：你的生产库遇到了一点麻烦，现在你希望把它复制到测试环境中排错，不幸的是测试环境非常寒酸，它达不到生产环境那样的豪华配置，你只能取一点点数据到测试库中，这时你失望地发现SQL语句在测试环境中跑的路子和生产库中完全不同，环境毕竟变化了嘛，优化器不傻，在新环境中给出来了新的SQL执行计划。为了复现生产库的SQL运行环境，这时你需要依靠db2look的mimic骗一下优化器，观察它生成的DDL脚本，尽是update掉SYSSTAT的冬冬，于是，咱们的目的达到鸟。

第六种，离别钩，db2move。

db2move用来成批地导入导出数据，想要用好db2move，需要首先对export、import、load三个实用工具非常的熟练，你要非常清楚它们的功能和行为。注意，我说的是熟练。学习分为三个境界，我讲的这三个境界可不是那个流传很广，并且文诌诌的那三个境界，神马衣带渐宽，蓦然回首，又憔悴了之类的，土人有土理论，嘿嘿。这三个境界就是：懂，会，熟！

• 懂：就是知道原理，但没实际或不需要实际去操练就算过关的学习境界；
• 会：是不仅要知道原理，还要知道如何操作，命令记不住也没关系，但是要达到会查会用的这么一个境界；
• 熟：要求你不仅是从里到外，还是从外到里，都要详细知道其原理，命令不查手册也能闭眼写出。

呀！有点跑题了。

第七种，拳头，db2diag。

谁都希望自己维护的DB2数据库能够寿与天齐，啥毛病没有，可是现实总是有点残酷的，我们的数据库总是会有发烧头疼脚痒的情况出现。db2diag就是这样一种武器，它主要用于操作DB2的运行日志db2diag.log文件，而db2diag.log文件可以帮助DBA诊断数据库出现的99%的问题，说99%，其实也是没有办法的办法，因为我们对db2diag.log文件的依赖程度很高，谁让IBM给的诊断手段有点略显单一呢，各位同学忍了吧。有的时候，db2diag.log有点不太容易读懂，这需要你经常看，仔细看，看的多了情况就会好一些，这个又是DB2不太如Oracle的地方了L，IBM加油吧。

其实DB2的武器铁定不只七种，比它们更狠、更猛的武器，甚至是核武器也是存在的，比如：db2untag、db2trc等，上述七种之所以进了兵器谱的排名，是因为它们常用。还有，武器再好，关键也是要看使用者，各位只有通过勤练、猛试才能发挥出武器的最大能量，驾驭好它，让它属于你自己，否则你弄个危险命令搞不好还搬起石头砸废了自己的脚哦！