Timeron 和 SQL 翻译
Timeron
为了分析解释信息，您需要了解的最重要的事情就是 timeron 的概念。Timeron 是 DB2 Optimizer 使用的一种度量单位，用于计量查询完全执行所需的时间和资源数量。timeon 是时间、CPU 占用率、磁盘 I/O 和其他一些因素的综合。由于这些参数的值不断变化，执行一个查询所需的 timeon 数量也是动态的，每次执行都有所不同。
Timeron 也是一种创造出来的度量单位，因此，没有什么公式可以将执行一个查询所需的 timeron 数转换成秒数。除此之外，timeron 可以帮助您确定一种查询执行途径是否比另一种更快。(若执行一个查询所需的 timeron 数在两次编译之间相差 10 或 20 timeron，不必担心，这可能仅仅是由于 CPU 活动、磁盘活动或数据库使用情况发生了变化。)
SQL 翻译
对一个数据库执行任何 SQL 语句之前，必须首先准备 SQL 语句。在此过程中，SQL 语句会简化为代数语句，DB2 Optimizer 随后可对此代数语句进行分析。这条代数语句就是所谓的查询图解模型，在整个优化过程中发挥作用。图 6 展示了在执行 SQL 查询之间，必要的优化及解析步骤。
图 6. SQL 翻译流程
优化流程的最终输出是一个访问计划。访问计划是 DB2 用以执行 SQL 语句的途径和步骤。这是由所有可用解释工具显示的信息。乍看上去，访问计划似乎非常复杂。但在具备了一定经验之后，您会很快发现它们实际上非常容易阅读和分析。
激活 Visual Explain
只要收集到了全面解释和/或解释快照数据，关于数据收集实现和方法的信息就会记录在 EXPLAIN_INSTANCE 解释表中。您可随时通过 Explained Statement History 窗口查看此信息。要激活 Explained Statement History 窗口，可在 Control Center 中高亮显示适当的数据库，并在 Control Center 菜单中选择 Selected > Show Explained Statement History 即可。图 7 展示了在为一条 SQL 语句收集了解释快照数据之后，Explained Statement History 窗口的外观。
图 7. Explained Statement History 窗口
一旦打开了 Explained Statement History 窗口，就可以使用 Visual Explain 来分析为任何记录收集的解释快照数据，这些数据的显示方法是，高亮显示一条记录，并在 Explained Statement History 窗口的主菜单中选择 Statement > Show Access Plan。图 8 展示了以这种方法为以下查询创建的 Access Plan 窗口(此查询可在随 DB2 提供的 SAMPLE 数据库上执行)：
SELECT * FROM EMPLOYEE, DEPARTMENT WHERE WORKDEPT=DEPTNO
图 8. Access Plan Graph 窗口
另一方面，还可为新查询收集解释快照数据，相应的访问计划可通过在 Explained Statement History 窗口的主菜单中选择 Statement > Explain Query... 显示出来。在选中这些菜单项时，Explain Query Statement 窗口将打开，并提示您为查询输入文本。图 9 展示了以一个简单的查询填充后的 Explain Query Statement 窗口。
图 9. Explain Query Statement 窗口
Access Plan Graph 窗口中展示的访问计划的每个组件都可供单击，单击后即可看到关于该组件的详细信息。例如，若选中图 8 所示的访问计划中的操作符，则 Operator details 窗口中将显示如图 10 所示的详细信息。
图 10. Operator details 窗口
在分析一个访问计划以定位性能瓶颈时，最好尝试单击所有不同的对象类型，以便充分了解已有的查询信息。
Visual Explain 组件
您可能已经注意到，Access Plan 窗口中提供的输出(参见图 9)由层次化图形构
成，表示处理为指定查询选定的访问计划时所必需的不同组件。计划中的各组件都显示为一种称为节点 的图形对象。可存在两种类型的节点：
操作符(Operator)。操作符节点用于确定是否必须在数据上执行一项活动，或者通过表或索引生成的输出。
操作对象(Operand)。操作对象节点用于确定对其进行操作的实体(例如，表可以是一个表扫描操作符的操作对象)。
操作对象
典型情况下，操作对象节点用于确定表、索引和表队列(表队列用于使用了内部分区并行操作的情况下)，它们在层次图中的符号分别是矩形(表)、菱形(索引)和平行四边形(表队列)。图 11 给出了表和索引操作对象的示例。
图 11. 表和索引操作对象
操作符
另一方面，操作符节点用于确定从插入操作到索引或表扫描的一切活动。操作符节点在层次图中的符号为椭圆形，表示数据的访问方法、表的连接方法，以及其他一些因素，例如是否要执行排序操作等。表 6 列出了访问计划层次图中较为常见的操作符。
表 6. 常见 Visual Explain 操作符
| 操作符 |
所执行的操作 |
| CMPEXP |
计算表达式。（仅用于调试模式。） |
| DELETE |
从表中删除行。 |
| EISCAN |
扫描用户定义的索引，产生一系列简化的行。 |
| FETCH |
使用指定的记录标识符从表中获取列。 |
| FILTER |
通过应用一个或多个谓词过滤数据。 |
| GENROW |
生成一个行表。 |
| GRPBY |
按指定列或函数的公共值组织行，并对集合函数求值。 |
| HSJOIN |
显示一个散列连接，其中一个或多个表在连接列上是混编的。 |
| INSERT |
向表中插入行。 |
| IXAND |
对两个或多个索引扫描所得到的行标识符（RID）进行 AND 运算。 |
| IXSCAN |
使用可选的启动/停止条件扫描表索引，产生有序的行流。 |
| MSJOIN |
显示合并连接，其中外部和内部表必须按连接谓词的顺序排列。 |
| NLJOIN |
显示嵌套循环连接，为外部表中的各行访问内部表一次。 |
| PIPE |
翻译行。（仅用于调试模式。） |
| RETURN |
将查询返回的数据显示给用户。 |
| RIDSCN |
扫描一个行标识符（RID）列表，该列表是从一个或多个索引中获得的。 |
| RPD |
远程计划的操作符。与 V8 中的 SHIP 操作符极为类似（之前版本中的 RQUERY 操作符），惟一的不同在于它不包含 SQL 或 XQuery 语句。 |
| SHIP |
从远程数据库源中检索数据。在联合系统中使用。 |
| SORT |
按特定类的顺序排序行，可以选择消除重复条目。 |
| TBSCAN |
通过直接从数据页中读取所有数据而检索行。 |
| TEMP |
将数据存储在临时表中以便读回（很可能要读回多次）。 |
| TQUEUE |
在数据库代理之间传输表数据。 |
| UNION |
串联来自多个表的行流。 |
| UNIQUE |
消除特定列值重复的行。 |
| UPDATE |
更新表中的行。 |
| XISCAN |
扫描 XML 表的索引。 |
| XSCAN |
在一个 XML 文档节点子树中导航。 |
| XANDOR |
允许为多个 XML 索引应用 ANDed 和 ORed 谓词。 |
图 12 中展示
了一些更为常见的操作对象的示例。在这个示例中，执行了三种不同的行动：两个表执行了表扫描，两个数据集使用散列连接算法连接。
图 12. 一些常见操作符
连接符和 RETRUN 操作符
箭头说明数据从一个节点流向另一个节点的方式，它将层次图中的所有节点连接在一起，RETURN 操作符通常用于终止这一过程。RETURN 操作符表示最终结果集产生，并包含关于查询的汇总信息以及所完成的 SQL 语句返回的内容。使用 RETURN 显示的 timeron 值表示按 timeron 度量的时间总长度，是完成查询所必需的时间。图 13 展示了 RETURN 操作符的一个示例。
图 13. RETURN 操作符
影响查询性能的因素
数据库环境的配置方式和用于准备查询的查询优化级别对于查询的准备方式、执行方式有着重大的影响。
配置参数值
Visual Explain 可迅速汇总影响查询编译的所有参数，并在一个汇总窗口中显示出来。这个窗口就称为 Optimization Parameters 窗口，通过在 Access Plan Graph 窗口的主菜单中选择 Statement > Show Optimization Parameters 可调用此窗口。图 14 展示了 Optimization Parameters 窗口在激活时的外观。
图 14. Optimization Parameters 窗口
Optimization Parameters 窗口中包含的部分配置参数包括：
AVG_APPLS(平均应用程序)：此参数表示为数据库并发运行的应用程序平均数量。DB2 使用此信息来确定排序空间和缓冲池使用得有多么频繁，并确定查询能够使用的空间有多少。
SORTHEAP(排序堆)：排序堆是执行排序时可用的内存空间数量。若排序需要的内存多于排序堆中可用内存，则部分排序数据将不得不分页到磁盘上(这会对性能造成严重的负面影响)。
LOCKLIST(锁列表)：该参数表示 DB2 可用于存储各应用程序的锁定信息的内存数量。若锁列表空间过小，则 DB2 可能必须逐步提交(escalate)部分锁，以便为应用程序具有的所有锁腾出空间。
MAXLOCKS(最大锁列表百分比)：该参数控制整个锁列表空间中有百分之多少的空间可为一个应用程序所有。若一个应用程序具有过多的开放锁，从而试图占用过多的内存，DB2 将提交部分锁，以释放锁列表中的空间。
NUM_FREQVALUES(频繁值数)：DB2 Runstats 实用工具使用频繁值数来控制 DB2 将在内存中保留多少使用频率最高的值。优化器使用该信息来确定 WHERE 子句中的一个谓词将消耗结果集的多少百分比。
NUM_QUANTILES(数据分位数)：DB2 Runstats 实用工具使用分位数来控制为列数据捕获多少分位。增加分位数将给予 DB2 关于数据库中数据分布情况的更多信息。
DBHEAP(数据库堆)：数据库堆控制数据库对象信息的可用内存量。对象包括索引、缓冲池和表空间。事件监控器和日志缓冲区信息也存储在这里。
CPUSPEED (CPU 速度)：计算机的 CPU 速度。若此值设置为 -1，则 DB2 使用 CPU 速度度量程序来确定恰当的设置。
BUFFPAGE 和缓冲池大小：优化器可在优化数据中使用的缓冲池大小。增加或减少缓冲池大小会对访问计划产生显著影响。
所用优化级别
影响为查询生成访问计划的方式的最重要因素就是优化级别，优化级别用于为此任务做准备。该信息告诉 DB2 Optimizer 要付出多少努力、使用什么技术来确定解决查询的最佳访问计划。较高的级别将使优化器使用更为复杂的算法和代数分析 —— 因而也需要花费更多的时间 —— 来生成最终计划。
有 7 个可用的优化级别，各级别使用所有可用规则和统计信息的不同子集。可用的优化级别包括：
0 —— 使用最少的优化
1 —— 所用的优化程度大致与 DB2/6000 V1 相同，此外还增加了一些 V1 中没有的低成本特性
2 —— 使用优化级别 5 的特性，但简化了连接算法
3 —— 执行中等数量的优化，与 DB2 for MVS/ESA 的查询优化特征相似
5 —— 使用大量优化，利用 Heuristic Rules(若未另行指定，这将是默认优化级别)
7 —— 使用无 Heuristic Rules 的大量优化
9 —— 使用所有可用优化技术
在确定要使用的最佳优化级别时，以下指导原则可给予您很大的帮助：
- 为需要很少的优化、极其依赖主键索引搜索或极简单的连接(例如，非常简单的 OLTP)的查询使用优化级别 0 或 1。
- 若查询比较简单，仅包含少量表和设计表的索引的连接(例如，OLTP)，则为查询使用优化级别 1。
- 对包含复杂 OLTP 的工作负载或包含多个表上许多复杂连接的报告(例如，混合的 OLTP 和报告)，应使用优化级别 5。
对于需要大量数据统计数据分析、且运行时间较长(超过 1 分钟)的查询(例如，非常复杂的数据研究或决策支持)，使用优化级别 9。DB2 Optimizer 需要花费较长的时间来生成访问计划，但访问计划中改进的价值往往超出产生计划所花费的额外时间。
对 SQL 进行故障排除的最终思考
已经有许多书籍专门探讨如何改进 SQL 性能这个话题，本文不可能面面俱到地介绍查询性能调优。但在您开始使用 Visual Explain 对执行状况不佳的查询进行故障排除时，应牢记一些要点：
未充分地利用索引。查询是否按您的希望使用了索引?应确保未在您理所当然地认为应该具有索引的表上进行表扫描。此问题可通过查看查询的访问计划图表轻松应对。若确实存在索引，则检查基数或索引键的顺序。情况未必与您预计的相同。
表基数和 'SELECT *' 的使用。有时，由于您要返回的列数，DB2 Optimizer 会判定扫描整个表的速度更快。有可能表非常小，也有可能扫描索引并返回大量行(即返回表中的所有列)的效率很低。尝试仅返回那些您确实需要的列。查看查询各部分返回的列，观察您是否确实需要这些列，并观察这是否是表扫描发生的原因。同样，考虑使用索引中包含的列。
优化级别设置过低。许多 DBA 将优化级别降低到 1，以缩短查询准备所需时间。有的时候，将优化级别增加到 5 将允许优化器找到更佳的访问计划，而您不必创建一个新索引来改进性能。您在 Explain Query Statement 窗口中选择为查询生成解释信息时(参见图 9)，可轻松在 Visual Explain 工具中调整该值。还可在 Command Line Processor 中设置该值，执行以下命令即可：
SET CURRENT QUERY OPTIMIZATION [0|1|2|3|5|7|9]