使用Explain分析SQL

第一篇

概述

如果您发现某一条 SQL 语句的运行情况比较糟。那么您该怎么做呢？DB2 提供了很多不同的工具，用来确切地发现是什么妨碍了性能。每个工具都略有不同，但是它们都会研究解释 SQL 正在执行什么的 Explain。

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

正被访问的表
正被使用的索引
何时连接数据
何时排序数据

Explain 还捕获更复杂的信息，在细究 SQL 正在发生什么时，该信息非常有用：

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

理解 Explain 信息

要理解 Explain 信息，您需要掌握的最重要概念是 timeron。timeron 是 DB2 优化器使用的计量单位，用来计量完成查询的时间和资源数量。timeron 综合了时间、CPU 利用率、I/O 和其它因素。由于这些参数值是变化的，因此执行某个查询所需的 timeron 数量是动态的，每次执行该查询所需的 timeron 都不同。timeron 还是一个发明出来的计量单位，没有公式可用来将 timeron 的数量转换成执行一次查询所需的时间（以秒表示）。您可以使用 timeron 作为计量单位，以确定某个查询执行路径是否比另一个快。在每一次编译时，如果执行查询所需的 timeron 数量相差了 10 或 20，那么您无须担心，因为 CPU 活动、磁盘活动或数据库使用情况的变化无疑会导致这种情形的发生。

SQL 转换

首先必须编译由数据库执行的所有 SQL。通过将查询简化成 DB2 优化器随后可以分析的代数语句（还记得学习SQL之前需要学习的关系代数吗？），可以完成该工作。该代数语句称为查询图模型（query graph model）（也称语法树），在整个优化过程中都将使用该代数语句。下图列出了优化及在执行每个查询前解析该查询要经历的各个阶段。

优化过程的最终输出是存取方案。存取方案是 DB2 执行查询时将采用的路径和步骤。所有 Explain 工具显示的都是这类信息。如果第一次接触到存取方案，您会觉得它非常复杂，但是经过一些实践，您就会发现它们非常易于理解和分析。

可以用下列三种形式之一来检查方案：

可视化的树模型：用 GUI 显示存取方案，这允许您点击树中不同的部分，以向下钻取更多详细信息。树是一系列具有不同颜色的符号构成的，每个符号都有特定的含义，它们是以树状图形式显示的。可以使用 DB2 Visual Explain 工具来查看可视化的树模型。对于初学者而言，该工具是最方便的工具，因为它每次只显示较少的信息；此外，使用 GUI 来查找您需要的有关具体某一部分的信息也很容易。
文本树模型：方案是作为一个基于 ASCII 文本的树状图模型显示的。SQL 查询中的所有不同部分都被绘制成不同的文本形状，然后使用文本行将各个部分连接起来。树的形状与可视化树模型中显示的形状相同。如果 SQL 非常复杂，那么人们就很难读懂文本模型，因为文本行会换行，从而使该模型变形。如果您正在使用简单的文本编辑器（如 UNIX 下的 vi）查看文本输出，那么经常会发生这种情况。如果您将文件导入功能较佳的文本编辑器，那么查看图就很方便了。文本树模型是使用 db2exfmt、dynexpln 和 db2expln 显示的，我们将在后续页中介绍它们。
文本信息：文本信息包含有关查询如何执行，以及优化器用来确定存取方案的统计信息和字段选择的所有具体信息。根据所使用的 Explain 工具，该信息可以非常详细。输出的结尾包含了表统计信息的摘要，从而使您能理解优化器的假定方案和优化数据是什么。通过下钻查询的各个组件，可以在 Visual Explain 工具中找到 Explain 输出的文本部分中的所有信息。

Visual Explain

Visual Explain 工具是从 DB2 控制中心（Control Center）调用的。要使用该工具，请打开 DB2 控制中心，找到并打开您希望对其运行 SQL 的数据库。用鼠标右键单击数据库名，然后选择 Explain SQL，如下图所示。（DB2 V10以后控制中心已经被Data Studio取代，Data Studio中同样保留了Visual Explain）

新弹出的菜单会要求您输入您希望 Explain 的 SQL。菜单上还有其它一些可用选项：

Get：允许您打开存储 SQL 的文本文件。
Save：将正在使用的 SQL 语句保存到文本文件。
Query number：允许您为查询分配具体的标号。
Query tag：查询的字母标记。
Optimization level：设置查询的优化级别（请参阅优化级别）。
Populate columns：该选项将把存取方案信息存储在数据库中的一系列表中。这允许您以后无须重新编译 SQL 语句就能查看存取方案和解释信息。

一旦成功执行了 SQL，将会打开一个新菜单，它将向您显示该查询的存取方案。还将创建一个更小的标记为 Overview 的窗口。该窗口利用蓝色矩形框来突出显示您正在查看的那部分存取方案。当您正在使用非常大而且复杂的查询，并且很难知道当前正在查看哪一部分时，该功能就很便捷。主窗口的左边有一个滚动条，它允许您控制视图的缩放。这只会影响视图大小，而不会为您提供任何其它信息。

可以单击存取方案的每个组成部分，以显示有关该部分的更多详细信息。不妨尝试单击所有不同的对象类型，以充分了解可以利用的查询信息。

Visual Explain 组件

要使用 Visual Explain 工具，您需要理解少量的 Visual Explain 组件。本页中所有的组件说明都以下面的示例为基础：

表对象节点

表对象表示查询部分正在使用的表。可以下钻该对象，以查看表的统计信息。然后可以更深入地下钻该信息，以获取索引、群集和引用列数据。在本示例中，图中有两个表对象：TBC.SALESFACT 和 TBC.PRODUCT。

索引对象节点

索引对象表示索引的名称。索引对象始终被附加在正在扫描的表上。在本示例中，索引名是 MAR1，它位于表 TBC.MARKET 上。

运算符节点

运算符节点对象表示一个操作，该操作是正在对指向它的对象执行的。这些操作包括：排序和检索数据、连接表或结果集以及扫描数据。每个操作都明确地进行了标号。在本示例中，正在执行三种不同的操作。对其中两个表执行表扫描操作，对一个索引执行扫描操作，以及使用散列连接算法对两个数据集进行连接。

返回节点

最后一个对象是结果集对象。该对象包含有关查询以及从已完成 SQL 返回什么内容的摘要信息。对象的 timeron 值表示完成查询所需的以 timeron 计量的时间总量。

查询信息

有许多重要参数会对查询如何执行以及 DB2 如何编译查询产生极大影响。

参数值

Visual Explain 工具将影响查询编译的所有参数都汇总到了一个菜单中。基于文本的工具将经过汇总的参数值列表放在了输出文件的开头部分。各个参数的完整说明可在 DB2 文档 Administration Guide: Performance 中的数据库管理器和数据库配置一节（请参阅参考资料）找到。

下面是优化参数菜单选项：

下面是优化参数列表：

现在让我们详细地研究这些参数。

AVG_APPLS（平均应用程序数）：该参数表示将在数据库上并发运行的应用程序的平均数量。DB2 使用该信息来确定排序空间和缓冲池的工作强度，以及查询可能会使用多少空间。
SORTHEAP（排序堆）：排序堆是内存中可以用于执行排序的空间量。如果排序所需内存比可用的排序堆多，那么必须将部分排序数据分页转储到磁盘。这会对性能有较大的负面影响，因此排序应当尽量在内存中进行。
LOCKLIST（锁列表）：该参数表示可供 DB2 用来为每个应用程序存储锁定信息的内存量。如果锁列表空间十分小，那么 DB2 就必须升级某些锁，以便有空间能够容纳应用程序持有的所有锁。
MAXLOCKS（最大的锁列表百分比）：该参数控制了某个应用程序可以占用整个锁列表空间的百分比。如果某个应用程序由于拥有太多打开的锁而试图耗尽太多内存时，DB2 就将升级一些锁，以释放锁列表中的空间。
NUM_FREQVALUES（频率值的数目）：DB2 Runstats 实用程序使用频率值的数目来控制 DB2 保存在内存中的最大频率值有多少个。优化器使用该信息来确定 WHERE 子句中的谓词将消除的总结果集的百分比。
NUM_QUANTILES（数据分位点的数量）：DB2 Runstats 实用程序使用分位点的数量来控制捕获多少列数据的分位点。增加分位点的数量将为 DB2 提供更多有关数据库中数据分布的信息。
DBHEAP（数据库堆）：数据库堆控制可用于数据库对象信息的内存量。这些对象包括：索引、表、缓冲池和表空间。事件监视器和日志缓冲区信息也存储在这里。
CPUSPEED（CPU 速度）：该参数是计算机的速度。如果将该值设为 -1，那么 DB2 使用 CPU 速度度量程序来确定正确的设置。
BUFFPAGE 和缓冲池大小：优化器可在其优化数据过程中使用的可用缓冲池的大小。增加或减少缓冲池大小会对存取方案产生显著影响。

优化级别

影响存取方案的最重要参数是优化级别。该字段告诉优化器应当花费多少开销以及使用什么技术来确定存取方案。级别越高，会使优化器使用的算法和代数分析越复杂，并使用更多时间来生成最终方案。

每个优化级别所使用的是整个规则和统计信息的不同部分。有关这些级别十分详尽的描述可在 DB2 文档 SQL Reference 第 5 章“Statements”的“Set Current Query Optimization”一节（请参阅参考资料）中找到。下面简要介绍了每个优化级别。

0 — 使用最小优化
1 — 使用与 DB2/6000 V1 大致类似的优化程度，再加上 V1 中没有的其它一些低成本功能
2 — 使用与优化级别 5 相同的功能，但是简化了连接算法
3 — 执行适中优化；类似于 DB2 for MVS/ESA 的查询优化特征
5 — 使用大量优化；带有 Heuristic 规则（缺省值）
7 — 使用大量优化；不带 Heuristic 规则
9 — 使用所有可用的优化技术

一般经验法则是，针对不同的查询工作负载使用下列优化级别：

非常简单的 OLTP：0 或 1。适用于那些不太需要优化并且主要依赖于主键索引搜索或非常简单的连接的查询。
OLTP：1。适用于涉及少量表和使用表上的索引进行连接的简单查询。
混合的 OLTP 和报告：5。适用于涉及复杂 OLTP 或报告的工作负载，而 OLTP 或报告涉及多个表上的许多复杂连接。
非常复杂的数据挖掘或决策支持：9。那些需要进行大量数据统计信息分析，并且长时间（超过 1 分钟）运行的查询应当使用最高级别的优化。该优化将花费比较长的时间，但是，存取方案中的改进所产生的影响通常会超过额外编译时间所产生的影响。

Explain 表格式化命令

db2exfmt 工具用于处理插入到 Explain 表中的信息。DB2 使用这些表来记录有关 SQL 不同组成部分的所有信息以及优化信息。当您使用 Visual Explain 工具时，会自动为您创建这些表。但如果您最初使用文本工具，那么您首先必须运行一个DB2自带的称为EXPLAIN.DDL的脚本来创建这些表。该DDL脚本位于您所连接的 db2 实例主目录下的 misc 子目录中。运行它将为您创建所有Explain表和索引。否则会出现下面的错误：

执行下面的脚本就能避免上面的出错消息了：


db2 connect to databaseName
db2 -tvf EXPLAIN.DDL

db2exfmt 工具的不同之处在于，它使用已经捕获的信息，然后用一种易于读取的格式来表示它。该工作完成后，填充 Explain 表。

捕获 Explain 信息

捕获 Explain 信息的最简单方法是将 SQL 存储在文本文件中，然后可以从 DB2 命令行处理器（CLP）（在 Windows 中）或从命令行（在 UNIX 或 Linux 中）运行它。DB2 有一个变量 CURRENT EXPLAIN MODE，它确定是否捕获 Explain 信息。可以对该变量进行设置，使得每当执行查询时，就让 DB2 填充 Explain 表。下面是执行这一工作的适当命令：


db2 SET CURRENT EXPLAIN MODE [no | yes | explain]

下面是 CURRENT EXPLAIN MODE 可能使用的值：

NO: 该值是缺省值；查询正常执行
YES: 查询正常执行，同时捕获 Explain 信息
EXPLAIN: 不执行查询，但是将 Explain 信息填入表

下面是一个示例：

使用工具

db2exfmt 有许多选项，这些选项允许您选择您希望使用的 Explain 语句。您还可以对显示哪些信息进行控制。最简单的方法是全部使用缺省值，这样就会使该工具返回有关最近解释过的语句的信息：

下面是样本查询摘要输出：

下面是样本查询树信息：

使用 db2expln 和 dynexpln 工具

可以从命令行调用 db2expln 工具，以获得查询的存取方案。但是，该工具不返回优化器信息。dynexpln 工具对于解释那些不使用参数标记的动态查询非常有用。有关这两个工具的信息可在 DB2 文档Administration Guide: Performance 的第 7 章“SQL Explain Facility”（请参阅参考资料）中找到。

db2expln

通常使用这个实用程序来分析存储在静态 SQL 包中的 SQL 语句。但是，通过使用下列参数，还可用它来动态地执行 SQL：

-database — 要连接的数据库名称
-statement — 用引号括起语句，以执行动态的 SQL 语句
-stmtfile fileName — 包含要执行的 SQL 语句列表的文件
-terminal — 将输出定向到屏幕
-output fileName — 将输出定向到指定的文件名

下面是该工具的示例命令：


db2expln -database drew_db -statement "select * from syscat.tables" -terminal

下面是样本输出：

dynexpln

已保留 dynexpln 工具以向下兼容 DB2 早期版本。您可以使用 db2expln 的动态选项来执行 db2expln 的所有功能。在您使用 db2expln 时，将语句作为真正的动态 SQL 语句预编译，存取方案是从 SQL 高速缓存生成的。dynexpln 工具将语句作为静态 SQL 准备，它不提供精确的存取方案，并且不允许有参数标记。

SQL 故障诊断

有很多整本都是有关如何改进 SQL 的书籍，但是下面是一些要点，在您开始使用 Explain 工具时应当牢记它们。

索引使用。查询正在使用您希望的索引吗？请确保在您认为有索引的表上不会发生表扫描操作。通过查看存取方案图，很容易找到该信息。如果索引确实存在，那么进行检查，以了解索引键的基数或顺序是什么。它通常不是您所希望的。

表基数和 "SELECT *" 的使用。由于您正在返回的列数，有时候 DB2 优化器会认为扫描整个表速度更快。可能这个表非常小，或者也许扫描索引不是很有效，因此返回了大量行（这些行返回了表中的所有列）。试着只返回您实际需要的那些列。看一下查询的每个部分要返回哪些列，以了解您是否真的需要它们，并且了解这是否是发生表扫描的原因。

优化级别太低。许多 DBA 将优化级别降为 1，以减少查询准备所需的时间。有时候，将优化级别提高到 5 可以使优化器找到一种更佳的存取方案，而您又无须创建新索引即可改进性能。在解释查询时，可以在 Visual Explain 工具中方便地调整该值。通过使用下面这条命令，也可以在命令行更改它：


db2 set current query optimization [0|1|2|3|5|7|9]

转自：http://ss.hnu.cn/rjxy/kcjs/DB2/fxzd/701_teach/db2cert7014/db2cert7014/db2cert7014-4-1.html

第二篇

什么是解释工具?

　　将一条 SQL 语句提交给DB2数据库引擎进行处理时，DB2 Optimizer 会对其加以分析，以生成所谓的访问计划。各访问计划包括将用于执行该语句的策略的详细信息(例如是否使用索引;若有排序方法，需要怎样的排序方法等)。如果该 SQL 语句是在一个应用程序中编写的，则访问计划生成于预编译时(若使用了延时绑定，则在绑定时生成)，另外还会生成一个可执行形式的访问计划，它作为称为 “包” 的对象存储在系统目录中。但若语句是通过 Command Line Processor 提交的，或者语句是应用程序中的一条动态 SQL 语句(也就是说，这是一条在应用程序运行时构造的 SQL 语句)，则访问计划将在该语句发出时生成，而所生成的可执行形式则临时地存储在内存中(位于全局包缓冲区中)，而不是系统目录。(若发出了一条 SQL 语句，而全局包缓冲区中已有其可执行形式的访问计划，则已有访问计划将被重用，不会再次调用 DB2 Optimizer。)

　　为什么说这非常重要?原因在于，尽管可以使用数据库系统监控器和健康监控器来获取关于某些 SQL 操作执行的情况有多好(或多糟)的信息，但不能用这些监控器来分析单独的 SQL 语句。要执行此类分析，您必须能够捕获并查看存储于 SQL 语句的访问计划中的信息。而为了捕获并查看访问计划信息，您必须使用 DB2 9 解释工具。

　　使用解释工具，您可以捕获并查看为特定 SQL 语句选择的访问计划的具体信息，还有可用于帮助确定编写不良的语句或数据库中弱点的性能信息。特别地，解释数据将帮助您了解 DB2 Database Manager 如何为满足查询而访问表和索引。解释数据还可用于评估采取的任何性能调优行动。实际上，只要您更改了 DB2 Database Manager 的某些方面、SQL 语句或与语句交互的数据库，都应收集并检查解释数据，弄清楚您的更改对性能产生了怎样的效果(如果有效果的话)。

解释表

　　必须首先创建一组特殊的表，即解释表，之后才能捕获解释信息。下表列出了所用的各解释表以及各表设计用于容纳的信息。

表名内容

EXPLAIN_ARGUMENT	包含所用各独立操作符的独特特征（如果存在的话）。
EXPLAIN_INSTANCE	包含所解释的 SQL 语句的源的基本信息，还有关于进行解释的环境的信息。（EXPLAIN_INSTANCE 表是所有解释信息的主要控制表。其他解释表中的各行数据显式地链接到该表中的各行。）
EXPLAIN_OBJECT	包含关于为 SQL 语句生成的访问计划所需的数据对象的信息。
EXPLAIN_OPERATOR	包含 SQL 编译器为满足 SQL 语句而需的所有操作符。
EXPLAIN_PREDICATE	包含确定特定操作符应用哪些谓词的相关信息。
EXPLAIN_STATEMENT	包含在得到不同级别的解释信息时存在的 SQL 语句文本。用户输入的原始 SQL 语句存储在该表中，另外还有 DB2 Optimizer 用于选择满足 SQL 语句的访问计划的版本。（后一种版本可能与原始版本的语句略有差异，因为 SQL Precompiler 可能已通过额外的谓词重写和/或增强了该语句。）
EXPLAIN_STREAM	包含关于各单独操作符和数据对象之间存在的输入输出数据流的信息。（数据对象本身显示于 EXPLAIN_OBJECT 表中，而数据流中涉及的操作符可在 EXPLAIN_OPERATOR 表中找到。）

　　典型情况下，解释表用于数据库开发之中，协助应用程序数据，但不会在应用程序代码较为稳定的生产数据库中。出于这方面的原因，它们不会随系统目录表一起作为数据库创建过程的一部分而创建。相反，解释表必须在要应用解释工具的数据库中手动创建，之后才能使用解释工具。幸运的是，使用 Command Line Processor 创建解释表的流程相当简单，您只要建立一个到恰当数据库的连接，并执行名为 EXPLAIN.DDL 的脚本即可，可在 DB2 9软件最初安装的 “sqllib” 目录下的 “misc” 子目录中找到此脚本。(此文件头部的注释提供了执行方法信息。)

收集解释数据
　　解释工具由多个单独的工具组成，而并非所有的工具需要的都是相同类型的解释数据。因此，可收集两种不同类型的解释数据：

全面解释数据(Comprehensive explain data)。包含关于一条 SQL 语句的访问计划的详细信息。此信息跨多个不同的解释表存储。

解释快照数据(Explain snapshot data)。包含一条 SQL 语句的当前内部表示以及所有相关信息。此信息存储在 EXPLAIN_STATEMENT 解释表的 SNAPSHOT 列中。

　　您或许已经想到，有多种收集这两种解释数据的方法。收集解释数据的可行方法包括：

执行 EXPLAIN SQL 语句

设置 CURRENT EXPLAIN MODE 特殊寄存器

设置 CURRENT EXPLAIN SNAPSHOT 特殊寄存器

在 PRECOMPILE 或 BIND 命令中使用 EXPLAIN 绑定选项

在 PRECOMPILE 或 BIND 命令中使用 EXPLSNAP 绑定选项

EXPLAIN SQL 语句
　　为单独一条动态 SQL 语句收集全面解释信息和解释快照数据的方法之一就是执行 EXPLAIN SQL 语句。该语句的基本语法是：
　　EXPLAIN [ALL | PLAN | PLAN SELECTION]
　　＜FOR SNAPSHOT | WITH SNAPSHOT＞
　　FOR [SQLStatement]
　　其中，SQLStatement 表示要为其收集解释数据和/或解释快照数据的 SQL 语句。(指定的语句必须是一条有效的 INSERT、UPDATE、DELETE、SELECT、SELECT INTO、VALUES 或 VALUES INTO SQL 语句。)
　　若在 EXPLAIN 语句中指定了 FOR SNAPSHOT 选项，则仅为指定动态 SQL 语句收集解释快照信息。另一方面，若指定的是 WITH SNAPSHOT 选项，则指定动态 SQL 语句的全面解释信息和解释快照数据均会被收集。但若未使用任一选项，则仅收集全面解释数据，而不会产生任何解释快照数据。
　　要为 SQL 语句 SELECT * FROM DEPARTMENT 收集全面解释数据和解释快照信息，可像下面这样执行 EXPLAIN 语句：
　　EXPLAIN ALL WITH SNAPSHOT FOR SELECT * FROM DEPARTMENT
　　另一方面，如果希望仅为这条 SQL 语句收集解释快照数据，可像下面这样执行 EXPLAIN 语句：
　　EXPLAIN ALL FOR SNAPSHOT FOR SELECT * FROM DEPARTMENT
　　最后，如果仅要为 SQL 语句 SELECT * FROM DEPARTMENT 收集全面解释数据，像下面这样执行 EXPLAIN 语句：
　　EXPLAIN ALL FOR SELECT * FROM DEPARTMENT
　　务必注意，EXPLAIN 语句未执行指定 SQL 语句，也未显示所收集的解释信息。要查看收集到的信息，必须使用其他解释工具(下文中将介绍这样的工具)。
CURRENT EXPLAIN MODE 和 CURRENT EXPLAIN SNAPSHOT 特殊寄存器
　　在您希望为单独一条动态 SQL 语句收集解释和/或解释快照信息时，EXPLAIN SQL 语句非常有用，尽管如此，若有大量 SQL 语句需要分析，使用这种方法将极为耗时。要为多条动态 SQL 语句收集相同的信息，一种更好的方法就是在执行一组动态 SQL 语句之前，设置所提供的一种或两种特殊解释工具寄存器。随后，语句准备好执行时，即可为所处理的各条语句收集解释和/或解释快照信息。(但在解释和/或解释快照信息收集完毕后，语句本身可能执行，也可能不会执行。)
　　以这种方式使用的两个解释工具特殊寄存器就是 CURRENT EXPLAIN MODE 特殊寄存器和 CURRENT EXPLAIN SNAPSHOT 特殊寄存器。CURRENT EXPLAIN MODE 特殊寄存器是使用 SET CURRENT EXPLAIN MODE SQL 语句设置的，CURRENT EXPLAIN SNAPSHOT 特殊寄存器是使用 SET CURRENT EXPLAIN SNAPSHOT SQL 语句设置的。SET CURRENT EXPLAIN MODE SQL 语句的基本语法是：
　　SET CURRENT EXPLAIN MODE <=>
　　[NO |
　　YES |
　　EXPLAIN |
　　REOPT |
　　RECOMMEND INDEXES |
　　EVALUATE INDEXES |
　　RECOMMEND PARTITIONINGS |
　　EVALUATE PARTITIONINGS]
　　SET CURRENT EXPLAIN SNAPSHOT SQL 语句的基本语法是：
　　SET CURRENT EXPLAIN SNAPSHOT <=> [YES | NO | EXPLAIN | REOPT]
　　可想而知，若 CURRENT EXPLAIN MODE 和 CURRENT EXPLAIN SNAPSHOT 特殊寄存器均设置为 NO，解释工具即被禁用，不会捕获任何解释数据。另一方面，若任一特殊寄存器被设置为 EXPLAIN，则相应的解释工具即被激活，每当一条动态 SQL 语句准备好执行时，就会为其收集全面解释信息或解释快照数据(若两个特殊寄存器均这样设置，则同时收集这两种信息)。但语句本身并不执行。若任一特殊寄存器被设置为 YES，行为与任一寄存器设置为 EXPLAIN 时基本相同，但存在一个重要的差异：为其收集解释信息的动态 SQL 语句会在适当的解释/解释快照数据收集完备后立即执行。
　　若 CURRENT EXPLAIN MODE 或 CURRENT EXPLAIN SNAPSHOT 特殊寄存器被设置为 REOPT，解释工具将被激活，只要一条静态或动态 SQL 语句在执行时的语句再优化过程中得到处理 —— 也就是说，在该语句中所用的主变量、特殊寄存器或参数标记的实际值可用时，解释信息或解释快照数据(若两个特殊寄存器均按此设置，则同时收集这两种信息)就会被捕获。
EXPLAIN 和 EXPLSNAP 预编译/绑定选项
　　至此，您已经看到了为动态 SQL 语句收集全面解释信息和解释快照数据的一些方法。但在很多时候，数据库应用程序由静态 SQL 语句构成，这些语句同样需要分析。那么如何使用解释工具来分析在嵌入式 SQL 应用程序中编写的静态 SQL 语句呢?要为在嵌入式 SQL 应用程序中编写的静态和/或动态 SQL 语句收集全面解释信息和/或解释快照数据，可以依靠 EXPLAIN 和 EXPLSNAP 预编译/绑定选项。
　　如您所料，EXPLAIN 预编译/绑定选项用于控制是否为在嵌入式 SQL 应用程序中编写的静态和/或动态 SQL 语句收集全面解释数据。类似地，EXPLSNAP 预编译/绑定选项用于控制是否为在嵌入式 SQL 应用程序中编写的静态和/或动态 SQL 语句收集解释快照数据。可在用于预编译包含嵌入式 SQL 语句的源代码文件的 PRECOMPILE 命令中指定这两个选项或其中之一。若使用了延迟绑定，那么可以在将应用程序的绑定文件绑定到数据库的 BIND 命令中提供这些选项。
　　可为 EXPLAIN 选项和 EXPLSNAP 选项指派的值包括 NO、YES、ALL 或 REOPT。如果两个选项均被指派以 NO 值(例如，EXPLAIN NO EXPLSNAP NO)，解释工具将被禁用，不捕获任何解释数据。另一方面，若任一选项被指派以 YES 值，解释工具将被激活，为应用程序中发现的每一条静态 SQL 语句收集全面解释信息或解释快照数据(若两个选项均按此设置，则同时收集这两种信息)。若任一选项被指派以 ALL 值，解释工具将被激活，并且为找到每一条静态 SQL 语句和每一条动态 SQL 语句收集全面解释信息或解释快照数据(若两个选项均按此设置，则同时收集这两种信息)，即便是 CURRENT EXPLAIN MODE 和/或 CURRENT EXPLAIN SNAPSHOT 特殊寄存器已设置为 NO。
　　若 EXPLAIN 或 EXPLSNAP 选项被指派以 REOPT 值，则各可再优化的递增绑定 SQL 语句的全面解释信息或解释快照数据(若两个选项均按此设置，则同时收集这两种信息)将在运行时放入解释表，即便是 CURRENT EXPLAIN MODE 和/或 CURRENT EXPLAIN SNAPSHOT 特殊寄存器已设置为 NO。
评估解释数据
　　至此为止，您一直将精力集中在收集全面解释数据和解释快照数据的不同方法上。但收集完数据后，如何查看这些数据?为了回答这个问题，您需要先看看专门设计用于以一种有意义的格式呈现解释信息的解释工具。其中包括：

　　db2expln

　　db2exfmt

　　Visual Explain

　　之前您已经了解到，在包含嵌入式 SQL 语句的源代码文件绑定到数据库时(无论是作为预编译流程的一部分还是在延迟绑定过程中)，DB2 Optimizer 将分析遇到的每一条静态 SQL 语句，并生成一个相应的访问计划，此访问计划随后以包的形式存储在数据库中。给定数据库名称、包名称、包创建者 ID、部分号(若指定了部分号 0，则处理包的所有部分)，db2expln 工具即可为存储在数据库系统目录中的任何包解释并说明其访问计划。由于 db2expln 工具直接处理包而非全面解释数据或解释快照数据，因而通常用来获取那些已选定用于未捕获其解释数据的包的访问计划的相关信息。但由于 db2expln 工具仅可访问已存储在包中的信息，因而只能说明所选的最终访问计划的实现，不能提供特定 SQL 语句优化方式的信息。
　　若使用额外的输入参数，db2expln 工具还可用于解释动态 SQL 语句(不包含参数标记的动态 SQL 语句)。
　　db2exfmt
　　与 db2expln 工具不同，db2exfmt 工具设计用于直接处理已收集并存储在解释表中的全面解释数据或解释快照数据。给定数据库名和其他限定信息，db2exfmt 工具将在解释表中查询信息、格式化结果，并生成一份基于文本的报告，此报告可直接显示在终端上或写入 ASCII 文件。
　　Visual Explain
　　Visual Explain 是一种 GUI 工具，它为数据库管理员和应用程序开发人员提供了查看为特定 SQL 语句选择的访问计划的图形化表示的能力。Visual Explain 允许您完成以下任务：

查看用于优化 SQL 语句的数据库统计数据。

确定是否使用索引来访问表数据。(若未使用索引，Visual Explain 可帮助您确定哪些列可受益于索引。)

允许您进行 “前” “后” 对比，从而查看性能调优的效果。

获得访问计划执行的各操作的详细信息，包括各操作的预计成本。

　　但 Visual Explain 只能用于查看解释快照数据，要查看已收集并写入了解释表的全面解释数据，则必须使用 db2exfmt 工具。
　　如您所见，可用于显示全面解释数据和解释快照的不同工具有着很大的差异，无论是在复杂性方面还是在功能方面。表2总结了几种可用工具，并强调了各工具的特征。要使解释工具发挥出最好的效果，您应在选择工具时考虑您的环境和需求。
所需特征                                                                                  Visual Explain    db2exfmt               db2expln

用户界面	图形化	基于文本	基于文本
“快速但粗略的” 静态 SQL 分析	否	否	是
静态 SQL 支持	是	是	是
动态 SQL 支持	是	是	是
CLI 应用程序支持	是	是	否
详细的 DB2 Optimizer 信息可用	是	是	否
适于分析多条 SQL 语句	否	是	是

解释工具使用示例
步骤1. 创建explain表
访问计划的采集和评估是通过一些explain命令和工具实现的，在采集和评估之前需要先创建一些数据表来存放解释数据。在<db2_inst>/sqllib/misc下有个文件叫EXPLAIN.DDL
db2 connect to <db>
db2 -tvf <db2_inst>/sqllib/misc/EXPLAIN.DDL （unix和linux下注意大小写）
在当前数据库中会生成9张解释表，第2步采集的解释数据会存放到这些表中。
步骤2. 采集explain数据和explain snapshot数据
explain数据包含关于一条 SQL 语句的访问计划的详细信息。此信息跨多个不同的解释表存储。
explain shapshot数据包含一条 SQL 语句的当前内部表示以及所有相关信息。此信息存储在 EXPLAIN_STATEMENT 解释表的 SNAPSHOT 列中。
explain和explain snapshot的解释数据采集有如下几种方法：

执行 EXPLAIN SQL 语句
设置 CURRENT EXPLAIN MODE 特殊寄存器/CURRENT EXPLAIN SNAPSHOT 特殊寄存器
在 PRECOMPILE 或 BIND 命令中使用 EXPLAIN 或 EXPLSNAP 绑定选项Explain SQL：
explain语句为单条动态SQL语句收集解释数据，如
explain plan for "select * from employee"
-------------------------------------------
Current explain mode为多条动态SQL语句收集解释数据：
>db2 set current explain mode explain (打开注册变量为explain模式)
>db2 "select * from employee"
>db2 "select * from department"
>...
>db2 set current explain mode no （关闭解释模式）
-------------------------------------------
Prep（或延迟bind) ...explain为嵌入式(如SQLC等）SQL语句收集解释数据：
>db2 prep myapp.sqc explain yes explsnap yes
步骤3. 查看和评估解释数据DB2提供了如下几个查看工具,其中db2exfmt最为强大，其余两个可辅助。
　db2expln
　db2exfmt
　Visual Explaindb2exfmt -d <db> -g tic -s % -n % -# 0 -w -1 -o explain1.txt
具体命令的使用请参看DB2 command reference或信息中心
转自：http://flyskyxzb.blog.sohu.com/132985941.html

posted on 2012-11-05 14:51 xshrim 阅读(728) 评论(0) 编辑收藏举报