SQL PL Profiler 介绍
Sundip Pradhan, SQL 编译器开发，DB2 Universal Database, IBM Toronto Lab
Serge Rielau, SQL 编译器开发，DB2 Universal Database, IBM Toronto Lab
2004 年 7 月
复杂应用程序或批量处理可能执行几百，甚至几千个 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 应用程序，您需要：
下载 Application 版本的 SQL Procedure Profiler，并进行解压。将下载目录包含于 CLASSPATH 中。通过从命令行窗口输入以下命令，启动 SQL Procedure Profiler 应用程序： java gui.SysMain
图 1. Profiler GUI
 启动该工具之后，您需要提供连接参数，包括：
- 该工具所操作的数据库名称。
（除非该工具在 disconnected 模式下工作，这在稍后将进行描述，否则该数据库就是您需要进行概要分析的数据库。） - 数据库所驻留的服务器名称或 URL。
- DB2 实例进行通信的端口号。
- 某用户的用户名和口令，该用户拥有足够的权限启动和操作语句事件监视器，以及创建模式，而 SQL Profiler 将在该模式中保存其本地数据。
提供了所需信息之后，就单击 Connect按钮。 图 2. 成功消息
 连接一旦成功，您就会获得显示成功的消息，如 图 2所示。单击 OK之后，将会看到按钮（Start、Clear）和单选按钮已被启用。 指定应用程序
该工具连接数据库之后，您就可以选择是概要分析该数据库上运行的所有应用程序，还是概要分析特定的连接。 若要从 DB2 命令中心（Command Center）或 CLP 等交互式连接获得应用程序 ID，只需执行该连接下的“VALUES application_id()”。然后，单击 Yes单选按钮，并从下拉列表选择应用程序 ID，如 图 3所示。 图 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
图 4. 可选的事件监视器元素
 现在，应该通过打开事件监视器来启动概要分析了。可通过单击 Start按钮来完成，这将产生成功对话框，确认已启动事件监视器，如 图 5所示。从此刻起，事件监视器将记录所执行的每条 SQL 语句的有关信息。这可以是已经在运行的应用程序，例如批作业，也可以是您此刻所调用的过程。 图 5. 事件监视器消息
 注意，事件监视器信息十分详细，如果短时间内执行了许多快速的 SQL 语句，那么事件监视器的信息量就会快速增长。同时还要注意，打开事件监视器会给 DB2 带来一定的性能影响。交互进行概要分析的过程一旦结束，或者您认为已经捕获了足量的批作业，就可以单击 Stop 按钮。 Clear按钮清除并设置环境，以便运行概要分析器的新会话。您在收到报表之前，无需按该按钮。 选择过程和模式
事件监视器停止之后，SQL Procedure Profiler 完成的第一件事就是找到所捕获的所有 SQL 过程。现在，您可以按模式级别或单个过程，选择对哪些过程感兴趣。 图 6. 模式选择的下拉组合框
 选择“All”模式查看所有过程，或者选择所期望的一个子集。然后，将需要概要分析器（profiler）进行报告的过程移至“Selected procedures”框中。 图 7. 列举过程的列表框
 报表
生成报表
选择过程之后，您就可以在标为“Report”的文本字段中为报表输入一个名称。该报表名应该与您以前所运行并存档的报表名不同。现在，单击 Generate Report 将生成一个报表，该报表将在另一个选项卡窗格中显示。可在数据库保存报表，以便将来参考。为此，您要选中“Save Report”框，该复选框在默认情况下是选中的。
生成报表可能要花费较长一段时间。还请耐心等待。其结果决不会让您白费时间。 搜索报表
通过切换至 Search Report 选项卡窗格，可以查看存档的报表。单击 Refresh查看可用报表。 图 8. 用于查看报表的选项卡窗格
 如果有可用的报表，“View Report”就会被启用。 图 9. 显示可用报表的列表框
 选择一个报表，并单击 View Report来查看。 查看报表
示例报表如 图 10中所示。 图 10. 示例报表
 在 Display Item Menu 中撤销选定可以隐藏报表中的列。 图 11. 隐藏或显示列的 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 命令窗口中执行下列步骤。
- 通过用户 ID 连接要进行概要分析的数据库，该用户 ID 应具有创建事件监视器的权限。
- 输入命令
db2 –tvf startEventMon.sql （请参阅附录 A.1），以创建并打开语句事件监视器。现在，该事件监视器将在整个数据库上收集数据。若要定制事件监视器，例如仅在指定应用程序上收集数据，请参考 SQL Reference 手册。 - 要停止语句事件监视器，就运行
db2 –tvf stopEventMon.sql （请参阅附录 A.2）。 - 然后，运行导出脚本
db2 –tvf export.sql ，以导出统计数据。该脚本将生成多个文件，包括事件监视器数据和某些 DB2 目录视图。
在运行概要分析器 applet 或概要分析器应用程序的客户端机器上，执行下列步骤：
- 通过用户 ID 连接任何数据库，该用户 ID 拥有创建模式的足够权限。SQL Procedure Profiler 将在所选择的数据库中存储其本地数据。
- 从服务器下载导出脚本所生成的文件（上面第 4 步中），并将当前工作目录更改为这些文件的所在位置。然后，运行
doImport.bat ’（对于 Windows）或 doImport.sh （对于 UNIX），以将数据导入表中。 - 现在，启动 SQL Procedure Profiler。
- 使用第 1 步所选择的用户 ID 进行连接。
- 选中 Import 复选框。这将启用‘Load’按钮。
图 12. 选中 Import的同时启用了 Load按钮
 - 单击 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 过程中的瓶颈极其有用。 附录
用于导入和导出概要分析器数据的脚本
startEventMon.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;
|
stopEventMon.sql 的脚本
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;
|
doImport.bat 和 doImport.sh 的脚本
db2 -tvf dropRemoteTbl.sql
echo "Drop completed"
db2 -tvf import.sql
echo "Import completed"
|
dropRemoteTbl.sql 的脚本
DROP TABLE REMOTE_STMTS;
DROP TABLE REMOTE_STATEMENTS;
DROP TABLE REMOTE_ROUTINES;
DROP TABLE REMOTE_ROUTINEDEP;
|
参考资料
下载
|
| Name |
|  |
| Size |
| |
| Download method |
| | |
| sqlplprofilerApplet.zip |
| |
| 76.3 KB |
| |
| HTTP |
| | | | | | | | | | | | | | |
| sqlplprofilerApp.zip |
| |
| 74.5 KB |
| |
| HTTP |
| | | | | | | | | | | | | | |
| sourceApplet.zip |
| |
| 204 KB |
| |
| HTTP |
| | | | | | | | | | | | | | |
| sourceApp.zip |
| |
| 200 KB |
| |
| HTTP |
| | | | | | | | | | | | | |
| 
1707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
