神马文学网监控 DB2 活动之使用解释工具分析SQL
来源：百度文库 编辑：神马文学网 时间：2024/04/25 15:27:01
将一条 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 语句或与语句交互的数据库，都应收集并检查解释数据，弄清楚您的更改对性能产生了怎样的效果(如果有效果的话)。
解释表
必须首先创建一组特殊的表，即解释表，之后才能捕获解释信息。表 4 列出了所用的各解释表以及各表设计用于容纳的信息。
表 4. 解释表
典型情况下，解释表用于数据库开发之中，协助应用程序数据，但不会在应用程序代码较为稳定的生产数据库中。出于这方面的原因，它们不会随系统目录表一起作为数据库创建过程的一部分而创建。相反，解释表必须在要应用解释工具的数据库中手动创建，之后才能使用解释工具。幸运的是，使用 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
- db2expln
之前您已经了解到，在包含嵌入式 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 工具。
如您所见，可用于显示全面解释数据和解释快照的不同工具有着很大的差异，无论是在复杂性方面还是在功能方面。表 5 总结了几种可用工具，并强调了各工具的特征。要使解释工具发挥出最好的效果，您应在选择工具时考虑您的环境和需求。
表 5. 可用解释工具的比较