概要分析 SQL 过程

文档选项 未显示需要 JavaScript 的文档选项 样例代码

级别： 初级

Sundip Pradhan (sundipp@hotmail.com), SQL 编译器开发，DB2 Universal Database, IBM Toronto Lab
Serge Rielau (srielau@ca.ibm.com), SQL 编译器开发，DB2 Universal Database, IBM Toronto Lab

2004 年 7 月 01 日

复杂应用程序或批量处理可能执行几百，甚至几千个 SQL 过程，因而难以用传统的监控方法来进行调优。本文介绍了一个基于 Java 的 GUI 工具，它与 DB2® Universal Database ™ Stinger 结合起来使用，用于发现运行中的应用程序所调用的 SQL 过程，对这些过程进行概要分析，并对应每个过程的源代码显示所收集的数据。那么，应用程序开发人员或 DBA 就可以高效地找到并调优消耗资源的语句或算法。

简介

复杂应用程序或批量处理可能执行几百，甚至几千个 SQL 过程。由于所产生的计划数量太多，仅通过分析优化器计划来调优该环境是不可行的。而且，即使是最优的 SQL，如果调用过于频繁，也会导致运行缓慢。本文介绍了一种基于 Java 的 GUI 工具，用于发现运行中的应用程序所调用的 SQL 过程。该工具概要分析这些过程，并在每个过程的源代码中显示所收集的数据。所有这一切都只需单击几次鼠标，就可允许应用程序开发人员或 DBA 高效地找到并调优消耗资源的语句或算法。

回页首

初始安装

既可以将 SQL Procedure Profiler（或简称为 SQL PL Profiler）工具作为 applet 安装，并通过 Web 浏览器来访问，也可以将之作为单独的应用程序安装。

要将该工具安装为 applet，您需要：

• Web 服务器（例如， www.apache.org上的 Apache）。
• 带有 Java 支持的 DB2 UDB Stinger。
• 启用 Java swing 插件的浏览器。
• 用于 Windows® 用户的登录配置文件中的 .java.policy 文件（例如，该策略文件应存在于 C:\Documents and Settings\<username>\.java.policy 中）。它包含在策略目录中。
• 安装有 DB2 JDBC。

满足这些先决条件之后，就下载 Applet 版本的 SQL Procedure Profiler。下载之后，解压该文件并将文件夹移至 Web 服务器的主 HTML 子目录下（对于 Apache，就是 htdocs 下）。

要在本地机器上启动 applet，只需在浏览器中输入 URL： http://localhost/profiler。如果在不同的机器上启动 applet，就要进行适当的替换。

要将该工具安装为单独的 Java 应用程序，您需要：

• Java 1.4 或更高版本。
• 带有 Java 支持的 DB2 UDB Stinger。
• 安装有 DB2 JDBC。

下载 Application 版本的 SQL Procedure Profiler，并进行解压。将下载目录包含于 CLASSPATH 中。通过从命令行窗口输入以下命令，启动 SQL Procedure Profiler 应用程序：

java gui.SysMain

图 1. Profiler GUI

启动该工具之后，您需要提供连接参数，包括：

• 该工具所操作的数据库名称。
  （除非该工具在 disconnected 模式下工作，这在稍后将进行描述，否则该数据库就是您需要进行概要分析的数据库。）
• 数据库所驻留的服务器名称或 URL。
• DB2 实例进行通信的端口号。
• 某用户的用户名和口令，该用户拥有足够的权限启动和操作语句事件监视器，以及创建模式，而 SQL Profiler 将在该模式中保存其本地数据。

提供了所需信息之后，就单击 Connect按钮。

连接一旦成功，您就会获得显示成功的消息，如 图 2所示。单击 OK之后，将会看到按钮（Start、Clear）和单选按钮已被启用。

回页首

指定应用程序

该工具连接数据库之后，您就可以选择是概要分析该数据库上运行的所有应用程序，还是概要分析特定的连接。

若要从 DB2 命令中心（Command Center）或 CLP 等交互式连接获得应用程序 ID，只需执行该连接下的“VALUES application_id()”。然后，单击 Yes单选按钮，并从下拉列表选择应用程序 ID，如 图 3所示。

对于到 DB2 所驻留机器的本地连接，应用程序 ID 为 *LOCAL.DB2.<#######>。对于远程连接，要将 *LOCAL 替换为十六进制表示的客户端机器的 IP 地址。

回页首

使用带有概要分析器的事件监视器

打开事件监视器之前，您可以设置概要分析器（profiler），通过选择可选元素来包含可选的事件监视器元素。这些可选元素包括：

• total_sort_time
• total_sorts
• fetch_count
• rows_read
• rows_written
• int_rows_deleted
• int_rows_inserted
• int_rows_updated
• pool_data_l_reads
• pool_data_p_reads
• pool_index_l_reads
• pool_index_p_reads
• pool_temp_data_l_reads
• pool_temp_data_p_reads
• pool_temp_index_l_reads

现在，应该通过打开事件监视器来启动概要分析了。可通过单击 Start按钮来完成，这将产生成功对话框，确认已启动事件监视器，如 图 5所示。从此刻起，事件监视器将记录所执行的每条 SQL 语句的有关信息。这可以是已经在运行的应用程序，例如批作业，也可以是您此刻所调用的过程。

注意，事件监视器信息十分详细，如果短时间内执行了许多快速的 SQL 语句，那么事件监视器的信息量就会快速增长。同时还要注意，打开事件监视器会给 DB2 带来一定的性能影响。交互进行概要分析的过程一旦结束，或者您认为已经捕获了足量的批作业，就可以单击 Stop 按钮。

Clear按钮清除并设置环境，以便运行概要分析器的新会话。您在收到报表之前，无需按该按钮。

回页首

选择过程和模式

事件监视器停止之后，SQL Procedure Profiler 完成的第一件事就是找到所捕获的所有 SQL 过程。现在，您可以按模式级别或单个过程，选择对哪些过程感兴趣。

选择“All”模式查看所有过程，或者选择所期望的一个子集。然后，将需要概要分析器（profiler）进行报告的过程移至“Selected procedures”框中。

回页首

报表

生成报表

选择过程之后，您就可以在标为“Report”的文本字段中为报表输入一个名称。该报表名应该与您以前所运行并存档的报表名不同。现在，单击 Generate Report 将生成一个报表，该报表将在另一个选项卡窗格中显示。可在数据库保存报表，以便将来参考。为此，您要选中“Save Report”框，该复选框在默认情况下是选中的。
生成报表可能要花费较长一段时间。还请耐心等待。其结果决不会让您白费时间。

搜索报表

通过切换至 Search Report 选项卡窗格，可以查看存档的报表。单击 Refresh查看可用报表。

如果有可用的报表，“View Report”就会被启用。

选择一个报表，并单击 View Report来查看。

查看报表

示例报表如 图 10中所示。

在 Display Item Menu 中撤销选定可以隐藏报表中的列。

只要单击列名，就可以按指定列进行排序。对于每次的鼠标单击，排序将在升序、降序和无序之间轮流变换。若要添加附加的排序列，就要在选择附加列时，一直按住控制键。您可以通过从“Display Item Menu”列表中选择或撤销选定列，选择隐藏或显示指定的列，如 图 11中所示。

导出和关闭报表

可以通过单击 Export 导出报表并保存到文件中。文件中的字段是由‘|’加上一个 tab 分隔的。现在，可通过诸如 Microsoft Excel 的工具进一步处理所导出的报表（Data -> Get External Data -> Import Text File）。

Close 按钮将关闭当前报表。

回页首

Disconnected 模式

该工具不要求连接进行概要分析的数据库。而是可以将所有相关数据从该数据库中转移出来，该工具可以连接到另一个数据库（例如开发环境）运行，使用导入的数据。

当考虑在生产环境中运行该工具时，这种操作模式就十分有用。为了收集概要文件信息，需要从 UNIX®/Linux 中的 shell 或 Windows 上的 DB2 命令窗口中执行下列步骤。

1. 通过用户 ID 连接要进行概要分析的数据库，该用户 ID 应具有创建事件监视器的权限。
2. 输入命令 db2 –tvf startEventMon.sql （请参阅附录 A.1），以创建并打开语句事件监视器。现在，该事件监视器将在整个数据库上收集数据。若要定制事件监视器，例如仅在指定应用程序上收集数据，请参考 SQL Reference 手册。
3. 要停止语句事件监视器，就运行 db2 –tvf stopEventMon.sql （请参阅附录 A.2）。
4. 然后，运行导出脚本 db2 –tvf export.sql ，以导出统计数据。该脚本将生成多个文件，包括事件监视器数据和某些 DB2 目录视图。

在运行概要分析器 applet 或概要分析器应用程序的客户端机器上，执行下列步骤：

1. 通过用户 ID 连接任何数据库，该用户 ID 拥有创建模式的足够权限。SQL Procedure Profiler 将在所选择的数据库中存储其本地数据。
2. 从服务器下载导出脚本所生成的文件（上面第 4 步中），并将当前工作目录更改为这些文件的所在位置。然后，运行 doImport.bat ’（对于 Windows）或 doImport.sh （对于 UNIX），以将数据导入表中。
3. 现在，启动 SQL Procedure Profiler。
4. 使用第 1 步所选择的用户 ID 进行连接。
5. 选中 Import 复选框。这将启用‘Load’按钮。
   图 12. 选中 Import的同时启用了 Load按钮
   
   
6. 单击 Load按钮将列出生产数据库上进行概要分析的过程和模式（如 图 7 中所示）。

此时，您可以选择模式和过程（关于细节，请参阅 选择过程和模式这一节）。然后，就可以生成报表了（关于细节，请参阅 生成报表 这一节）。

回页首

分析报表

报表提供了下列信息：

• ROWNUM： 报表中所有行的顺序编号，便于进行引用。
• ROUTINESCHEMA： 创建例程的模式。
• LINE： 过程里一条语句的行号。
• NUM_ITERATION： SQL 语句的执行次数。注意，对于游标声明，该值为打开游标的次数，不包括该游标取数据的次数。
  如果迭代次数为零，就表示从不执行该语句。然而，如果迭代次数字段为空，则表示下列四种情况之一：
  
  • 该行不是 SQL 语句的开头。
  • 该语句是 OPEN、FETCH 或 CLOSE 类型的，并且 DECLARE CUSROR 语句中已经提供了数据。
  • 出于性能目的，该语句已经与另一条语句合并，并在那里进行计算。您将看到这对于压缩到 VALUES INOT 中的 SET 语句群集十分普遍。
  • 该语句在过程虚拟机（PVM）中执行。这意味着语句的执行比较普通，因而无需担心其速度。
• ELAPSED_TIME： 运行该语句所消耗的时间（以秒计算）。对于游标，这就是打开和关闭游标之间所经过的时间。还要注意，一个例程中所消耗的总时间等于所有调用例程的调用所经过的时间。主要是因为事件监控本身具有相当大的开销。然而，消耗时间（elapsed time）可以很好地显示语句和例程之间的性能对比。
• CPU（微秒）： 执行 SQL 语句消耗 CPU 的总时间（微秒级）。该值越高，该语句就越是 CPU 密集的。CPU 时间包括用户和系统 CPU。与消耗时间不同，对于声明游标的语句，CPU 时间是打开游标、所有读取操作和关闭游标的时间总和。因此，该字段比其他字段更能显示游标成本。
• TEXT： 进行概要分析的 SQL 语句的文本。
• TOTAL_SORT_TIME： 该语句已经执行的所有排序的总消耗时间（微秒级）（例如，使用 ORDER BY 子句的查询语句）。
• TOTAL_SORT： 该语句所执行的排序总数目。该元素有助于识别执行大量排序的语句。这些语句可受益于减少排序数目的附加调优，例如添加索引。
• FETCH_COUNTS： 执行游标所交付的总行数。注意，成块游标要进行预取。这意味着使用 FETCH 语句时，未到达 EOF 的游标可能在 FETCH_COUNTS 中展示比实际读取更多的行数。
• ROWS_READ： 在执行语句期间，所读取的总行数。
• ROWS_WRITTEN： 在执行插入、删除或更新语句时，在表中修改的总行数。

其他列元素有：
INT_ROWS_DELETED、INT_ROWS_INSERTED、INT_ROWS_UPDATED、POOL_DATA_L_READS、POOL_DATA_P_READS、POOL_INDEX_L_READS、POOL_INDEX_P_READS、POOL_TEMP_DATA_L_READS、POOL_TEMP_DATA_P_READS 和 POOL_TEMP_INDEX_L_READS。

关于这些语句监视器元素的细节，请参考 DB2 UDB System Monitor Guide and Reference。

回页首

结束语

本文中，我展示了如何部署和使用一个基于 Java 的 SQL 概要分析（profiling）工具。该工具从运行中的应用程序收集统计数据 — 例如迭代次数、消耗时间、CPU 时间、排序时间等等，而无需提前知道所调用过程的调用结构。结果数据对于帮助您精确定位 SQL 过程中的瓶颈极其有用。

回页首

附录

用于导入和导出概要分析器数据的脚本

CREATE EVENT MONITOR STMTMON FOR STATEMENTS
    WRITE TO TABLE STMT 
    (TABLE  STMTS, INCLUDES
      (section_number, package_name, stop_time, start_time, 
      system_cpu_time, user_cpu_time, appl_id, creator, blocking_cursor, 
      cursor_name,  stmt_type, stmt_operation, fetch_count, rows_read, 
      rows_written, total_sort_time, total_sorts, INT_ROWS_DELETED, 
      INT_ROWS_INSERTED, INT_ROWS_UPDATED, POOL_DATA_L_READS, 
      POOL_DATA_P_READS, POOL_INDEX_L_READS, POOL_INDEX_P_READS, 
      POOL_TEMP_DATA_L_READS, POOL_TEMP_DATA_P_READS, 
      POOL_TEMP_INDEX_L_READS ) ) BLOCKED;
SET EVENT MONITOR STMTMON STATE = 1;

SET EVENT MONITOR STMTMON STATE = 0;
        
        

      
      

export to stmt.ixf of ixf messages msgs.txt select * from stmts;
export to routine.ixf of ixf messages msgs.txt 
    select R.ROUTINENAME, R.SPECIFICNAME, R.TEXT, 
    R.ROUTINESCHEMA, R.PARM_COUNT from SYSCAT.ROUTINES R;
export to routinedep.ixf of ixf messages msgs.txt 
    select D.ROUTINENAME, D.ROUTINESCHEMA, D.BTYPE, D.BNAME 
    from SYSCAT.ROUTINEDEP D;
export to statements.ixf of ixf messages msgs.txt 
   select ST.PKGNAME, ST.SECTNO, ST.STMTNO 
   from SYSCAT.STATEMENTS ST;

import from stmt.ixf of ixf 
	messages impmsgs.txt 
	REPLACE_CREATE into REMOTE_STMTS;
import from routine.ixf of ixf 
	messages impmsgs.txt 
	REPLACE_CREATE into REMOTE_ROUTINES;
import from routinedep.ixf of ixf 
	messages impmsgs.txt 
	REPLACE_CREATE into REMOTE_ROUTINEDEP;
import from statements.ixf of ixf 
	messages impmsgs.txt 
	REPLACE_CREATE into REMOTE_STATEMENTS;

db2 -tvf dropRemoteTbl.sql
echo "Drop completed"
db2 -tvf import.sql
echo "Import completed"

DROP TABLE REMOTE_STMTS;
DROP TABLE REMOTE_STATEMENTS;
DROP TABLE REMOTE_ROUTINES;
DROP TABLE REMOTE_ROUTINEDEP;

回页首

下载

	关于下载方法的信息

参考资料

• 您可以参阅本文在 developerWorks 全球站点上的 英文原文.
  
  
• 下载工具和源代码，分别针对 SQL PL Profiler Application 和 SQL PL Profiler Applet 的。
  
  
• 阅读 JDBC 规范或下载最新的 JDBC API。
  
  
• 在 developerWorksJava 技术专区 查找更多 Java 技术参考资料。
  

作者简介

		Sundip Pradhan 是 IBM 多伦多实验室里 DB2 UDB SQL 编译器开发小组的一名 co-op 学生。他已经取得了知识工程的硕士学位，该专业要应用数据仓库和数据挖掘等技术。

		Serge Rielau 是一名资深软件开发人员，在 IBM 多伦多实验室里负责 DB2 UDB SQL 编译器的开发。7 年以来，他一直致力于 DB2 SQL 编译器的开发，并且他还是 SQL 语言方面的专家。Serge 大部分时间都在帮助客户从其他 DBMS 迁移到 DB2，他还是 comp.databases.ibm-db2 新闻组的积极参与者。

对本文的评价

太差！ (1) 需提高 (2) 一般；尚可 (3) 好文章 (4) 真棒！(5) 建议？

回页首