db2EXPLAIN分析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
=============================||
转⾃：www.searchdatabase/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_
-u user password -w l
Query:
（附件中）
Results:
sql_1_（附件中）
db2expln
db2expln 是命令⾏下的解释⼯具，和前⾯介绍的 Visual Explain 功能相似。通过该命令可以获得⽂本形式的查询计划。命令如清单2 所⽰:
清单2. db2expln 命令sql2000数据库在哪个文件夹
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\
-z @ -output D:\temp\sql_1_ –g
Query:
（附件中）
Results:
sql_1_（附件中）
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\ > D:\temp\sql_2_
Query:
（附件中）
Results:
sql_2_（附件中）
通过 -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\ -r d:\temp\sql_3_
Query:
（附件中）
Results:
sql_3_（附件中）
对于执⾏db2batch时⼀些详细的设置可以通过-o参数指定，也可以在SQL⽂件中指定，譬如本例中在SQL⽂件中使⽤了下⾯的配置参数 :
--#SET ROWS_FETCH -1 ROWS_OUT 5 PERF_DETAIL 1 DELIMITER @ TIMESTAMP
其中ROWS_FETCH和ROWS_OUT定义了从查询的结果集中读取记录数和打印到输出⽂件中的记录数，PERF_DETAIL设置了收集性能信息的级别，DELIMITER则指定了多个查询间的间隔符。
提⾼DB2查询性能的常⽤⽅法
原⽂出处：www.ibm/developerworks/cn/db2/zones/performance/
=============================||
转⾃：www.db2china/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%，超过的话将不给出创建索引的建议。
=============================||
转⾃： blog.sina/s/blog_780a4e2e01016mln.html
⼤家读过古龙先⽣的七种武器吗？他们各有各的妙⽤，各有各的⼈⽣道理在其中。DB2中也有这么七种武器，它们也是⽤途不同，⽤好了威⼒⽆穷。
俺所谓的七种武器就是DB2的七个命令：
第⼀种，霸王，db2pd。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论