 | 级别： 初级 Ramakrishna Kolluru, SeeCommerce.com
2003 年 3 月 01 日 为了调优 SQL 以及确定应该创建什么索引来提高性能，本文描述了一项技术，用于分析 DB2 通用数据库语句事件监视器的输出。此外，本文还提供了一个样本程序。
本文专门针对 IBM® DB2® Universal Database™ Linux、UNIX® 和 Windows® 版。
简介
对于数据库管理员，调优数据库常常是一项挑战。调优应用程序是一种方法，但在大多数生产系统中 DBA
很少甚至不能更改源代码，因此限制了他们调优应用程序的能力。这在 DBA 使用第三方工具时尤为如此。所以，通常最有效的调优方法是解决问题的根源，即从 SQL 语句本身入手。通常通过查找哪些 SQL
语句消耗的资源最多来获得最佳性能，然后决定采取一定的措施来减少资源消耗。
通常，在第一次安装数据库时，会将其性能调至最优，但随着时间的流逝，一些常用的东西开始变得越来越慢。这在拥有大量数据的系统（譬如决策支持系统）中尤为如此。用户开始看到如锁升级、全表扫描以及排序这样的因素造成性能下降，这些操作往往会迫使系统访问磁盘而不是访问内存。当出现这种情况时，最好检查一下 SQL，看看可以做哪些改进。
本文旨在解决的问题是：针对通常的活动，调优正在用于访问数据库的 SQL。为了简化如何监控应用程序中 SQL 语句的问题，我编写了一个 Java 程序
DB2Trace.java ，帮助您在使用 DB2 通用数据库事件监视器时，确定哪些
SQL 语句消耗的资源最多。
DB2 事件监视器是 DB2 通用数据库 Linux、UNIX 和 Windows 版所带的工具。每当数据库中发生所指定的事件，该工具就将数据写到文件或命名管道，它是一种用来跟踪数据库中特定活动的方式。
DB2Trace.java 程序从语句事件监视器获取输出，然后将其插入到数据库的 DB2TRACE
表中。然后，可以使用 SQL 查询来分析数据，为有问题的 SQL 语句创建相应的索引，或者另外做一些更改以提高性能。
准备好之后，现在
下载代码，自己尝试一下。
先决条件
本文所描述的程序用 DB2 通用数据库企业版 V7.2.2 和 JAVA 1.2.2
测试过。为了支持 JDBC 2.0，JAVA 版本应为 1.2 或更高。
虽然在 UNIX 上测试过该程序，但最好还是在 Windows 上运行该程序，这样肯定不需要做任何更改。注：下面所显示的命令是针对
UNIX 命令 shell 环境，对于 Windows 环境，需要做适当的修改。
运行事件监视器
首先，必须创建事件监视器，运行监视器来收集将要分析的数据。在下面这些步骤中，用您自己的数据库名替代
dbname，用您自己的用户标识替代
username，用您自己的密码替代
password。注：
rkmon是该示例所使用的事件监视器的名称，您可以用其它任何名称来替代它。
- 打开一个新的 DB2 命令行处理器会话，然后执行以下 DB2 UDB 命令：
db2 => connect to
dbname user
username using
password
db2 => update monitor switches using statement on
db2 => create event monitor rkmon for statements write to file '/tmp'
db2 => set event monitor rkmon state=1
|
使该会话一直处于打开状态，直到这些数据库活动完成。请确保
/tmp 目录有足够大的空间来保存跟踪文件。这里选择
/tmp 目录是因为所有用户都可以访问该目录；但也可以选择使用其它目录（请确保对于该目录，DB2
有访问权限）。目录的大小取决于用户想要捕获的 SQL 语句的数目。一开始，最好设为 500 MB。
- 执行正常的数据库活动，直到您想监控的时段结束。这一监控阶段可以是问题产生时期，也可以是通常的数据库活动过程。在
/tmp 目录下，您应该可以看到一组扩展名为“
.evt ”的文件。这些文件就是您的事件监视器文件。
- 回到在步骤 1 中所打开的会话，然后发出以下语句：
db2 => set event monitor rkmon state=0
db2 => terminate
|
- 在平常的命令提示符下，执行以下命令：
$ db2evmon -path /tmp > sqltrace.txt
|
在单个文件
sqltrace.txt 中会有所有已捕获的 SQL 语句及其细节。
运行
DB2Trace.java
现在您已经在
sqltrace.txt 文件中收集好数据。下一步将使您能分析这些数据。
-
将
DB2Ttrace.java 程序下载
至
/tmp 目录。下面这个清单 1 是
DB2Trace.java 程序的副本。
清单 1. DB2Trace.java
/*(c) Copyright IBM Corp. 2003 All rights reserved. */
/* */
/*This sample program is owned by International Business Machines */
/*Corporation or one of its subsidiaries ("IBM") and is copyrighted */
/*and licensed, not sold. */
/* */
/*You may copy, modify, and distribute this sample program in any */
/*form without payment to IBM, for any purpose including developing, */
/*using, marketing or distributing programs that include or are */
/*derivative works of the sample program. */
/* */
/*The sample program is provided to you on an "AS IS" basis, without */
/*warranty of any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL */
/*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
/*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
/*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
/*limitation of implied warranties, so the above limitations or */
/*exclusions may not apply to you. IBM shall not be liable for any */
/*damages you suffer as a result of using, modifying or distributing */
/*the sample program or its derivatives. */
/* */
/*Each copy of any portion of this sample program or any derivative */
/*work, must include a the above copyright notice and disclaimer of */
/*warranty. */
/* */
import java.io.*;
import java.sql.*;
/**
* DB2Trace program reads the output from the db2event monitor
* and inserts the data into a database.
*
* Creation Date: January 15 2003.
*/
class DB2Trace {
/**
* Usage: java DB2Trace sqltrace.txt jdbc:db2:db2mwh userid
* password
* sqltrace.txt is the input file
* jdbc:db2:db2mwh is the database jdbc url connection
* username is the user ID
* password is the password
* @param args[]
* @exception IOException, SQLException
*/
public static void main (String args[]) throws IOException,
SQLException {
FileWriter fw = new FileWriter("DB2Trace.sql");
PrintWriter pw = new PrintWriter(fw);
BufferedReader in = new BufferedReader(new
FileReader(args[0]));
String s = "";
String sqlString = "";
boolean textYes = false;
int exists = 0;
COM.ibm.db2.jdbc.app.DB2Driver driver = new
COM.ibm.db2.jdbc.app.DB2Driver();
java.sql.DriverManager.registerDriver(driver);
Connection conn =
java.sql.DriverManager.getConnection(args[1], args[2],
args[3]);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT COUNT(*) FROM
SYSCAT.TABLES WHERE TABSCHEMA=USER AND "
+ "TABNAME='DB2TRACE'");
while (rset.next()) {
exists = rset.getInt(1);
}
if (exists > 0)
stmt.executeUpdate("DROP TABLE DB2TRACE");
stmt.executeUpdate("CREATE TABLE DB2TRACE( OPERATION
VARCHAR(30),SQLTXT VARCHAR(32000),"
+ "STARTTIME VARCHAR(30),STOPTIME VARCHAR(30) ,"
+ "EXECTIME VARCHAR(20),"
+ "USRCPU VARCHAR(20)," + "SORTS
VARCHAR(20)," + "TOTSORTTIME VARCHAR(20)) IN TEST ");
stmt.executeUpdate("CREATE INDEX DB2TRACE_CPU ON
DB2TRACE(USRCPU)");
PreparedStatement p = conn.prepareStatement("INSERT INTO
DB2TRACE(OPERATION,SQLTXT,EXECTIME,"
+"
STARTTIME,STOPTIME,USRCPU,SORTS,TOTSORTTIME)
VALUES(?,?,?,?,?,?,?,?)");
while ((s = in.readLine()) != null) {
if (s.startsWith(" Operation: ")) {
p.setString(1, s.substring(13, s.length()));
}
if (s.startsWith(" Text :")) {
textYes = true;
p.setString(2, s.substring(13, s.length()));
}
if (s.startsWith(" Exec Time:")) {
p.setString(3, s.substring(14, s.length() - 7));
}
if (s.startsWith(" Start Time: ")) {
p.setString(4, s.substring(14, s.length()));
}
if (s.startsWith(" Stop Time: ")) {
p.setString(5, s.substring(14, s.length()));
}
if (s.startsWith(" User CPU:")) {
p.setString(6, s.substring(13, s.length() - 7));
}
if (s.startsWith(" Sorts:")) {
p.setString(7, s.substring(8, s.length()));
}
if (s.startsWith(" Total sort time:")) {
p.setString(8, s.substring(18, s.length()));
if (textYes == true) {
p.executeUpdate();
textYes = false;
}
}
}
pw.close();
stmt.close();
p.close();
}
}
|
- 按如下编译该程序：
$ javac -classpath /export/home/seecomm/sqllib/java/db2java.zip:. DB2Trace.java
|
注：在该示例中，使用了 JDBC 2.0。使用了
sqllib/java12 目录下的
db2java.zip 。缺省情况下，DB2 V8 使用 JDBC 2.0；然而，对于 DB2 V7，运行文件
usejdbc2.bat （Windows）或
usejdbc2 （UNIX）。
- 在该示例中，所使用的应用程序驱动程序需要将该数据库编目在 DB2 数据库目录中。这个应用程序驱动程序或胖客户机属于 JDBC 的第 2 类驱动程序。这意味着必须在客户的工作站上安装应用程序开发客户机或 CAE。
- 请确保创建了 TEST 表空间（其页面为 32 KB）、用于排序的系统临时表空间（其页面为 32 KB）以及与这些表空间相关联的缓冲池（其页面为 32 KB）。在运行该程序之前，应该先创建这些表空间和缓冲池。用以下的 DB2 命令创建表空间和缓冲池：
CREATE Bufferpool TESTBP SIZE 1000 PAGESIZE 32 K
|
（先停止实例，然后再启动实例，从而激活该缓冲池，并使它与表空间相关联。）
CREATE REGULAR TABLESPACE TEST PAGESIZE 32 K MANAGED BY
SYSTEM USING ('/export/seecomm/') EXTENTSIZE 16 OVERHEAD
24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL TESTBP
|
- 现在准备运行该程序：
$cd /tmp
$ java -classpath
/export/home/seecomm/sqllib/java/db2java.zip:. DB2Trace
sqltrace.txt jdbc:db2:db2mwh username password
|
sqltrace.txt 是输入文件。
jdbc:db2:db2mwh 是这个数据库的 JDBC URL 连接。
username 是用户标识。
password 是密码。
在这一步，每条 SQL 语句的 SQL 文本、执行时间、启动时间、停止时间、用户 CPU
时间、排序以及总排序时间都被捕捉到数据库表 DB2TRACE 中。
|
|
分析输出
由于 DB2Trace 程序已经在数据库表（DB2TRACE）中存储了所需要的信息，因此可以查询该表以确定“讨厌”且耗时的 SQL 语句。
需要确定四类 SQL 语句。在执行以下查询以确定这些语句之前，在数据库配置参数中，至少要为应用程序堆大小（
applheapsz ）分配
256 个页面。
- 按照执行时间降序排列执行耗时最长的 SQL 语句。为了确定这些语句，使用下面的
SQL SELECT 语句：
select sqltxt, exectime "ExecutionTime(sec)" from db2trace
where operation not in ('Static Commit',
'Static Rollback', 'Prepare', 'Open',
'Describe', 'Compile')
order by decimal (exectime) desc
fetch first 10 rows only
|
- 按照频率降序排列执行次数最多的 SQL 语句。可以用下面这条查询来确定这些语句：
select distinct(sqltxt),count(*) Count from db2trace
where operation not in ('Static Commit', 'Static Rollback',
'Prepare', 'Open', 'Describe', 'Compile')
group by sqltxt
order by count(*)desc
fetch first 10 rows only
|
- 按照 CPU 时间降序排列最耗 CPU 时间的 SQL 语句。用下面这条查询来确定这些语句：
select sqltxt ,usrcpu "UserCPU(sec)" from db2trace
where operation not in ('Static Commit',
'Static Rollback', 'Prepare', 'Open',
'Describe', 'Compile')
order by usrcpu desc
fetch first 10 rows only
|
- 按照总排序时间降序排列排序时间最长的 SQL 语句。用下面这条查询找到这些语句：
select sqltxt ,totsorttime "TotalSortTime(ms)" from db2trace
where operation not in ('Static Commit',
'Static Rollback', 'Prepare', 'Open',
'Describe', 'Compile')
order by decimal(totsorttime) desc
fetch first 10 rows only
|
捕获每一类中的 SQL 语句，并将它们放在
tune.sql 文件中。将下面这行插入到该文件中，这样可以更改工作负载中每条语句的执行频率：
这里的 <x> 表示随后要执行 SQL 语句的次数。您的
tune.sql 文件类似于这样：
--#SET FREQUENCY 100
SELECT COUNT (*) FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS';
--#SET FREQUENCY 1
SELECT AVG (BONUS), AVG (SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT ORDER BY WORKDEPT;
|
将这些 SQL 语句复制到 tune.sql 之后，检查任何 SQL 语句的 WHERE
子句中是否具有参数标志符（?）。将参数标志符改为适当的数据类型值，以便在没有任何错误的情形下执行
SQL 语句。
为了确定哪些索引可能提高性能，按如下执行索引顾问程序：
$cd /tmp
$db2advis -d test -i tune.sql -t 0 -o tuneidx.sql
|
所有推荐的索引将放置在文件
tuneidx.sql 中。编辑该文件，在文件开始处添加一条连接语句：
connect to dbname user userid using password;
|
在该文件末尾添加下面这行：
现在可以运行该文件以创建推荐的索引：
$db2 -tf tuneidx.sql -z tuneidx.log
|
其中，
tuneidx.log 捕获
tuneidx.sql 的所有输出。
结束语
现在，数据库的性能与原来应该大不相同。可以将 DB2TRACE 表中所收集的统计信息输入到电子表格中，这些信息将有助于您更好地了解 SQL
语句到底在使用哪些资源。对 SQL 语句进行调优之后，为了达到更佳性能，还可以调优数据库和数据库管理器参数。
下载代码
| 描述 | 文件类型 | 文件大小 | 下载方式 | | DB2Trace.java | java | 5 KB | HTTP
|
 我应该选择哪种下载
方式？
下载 FAQ
关于作者 | 
| |
Ramakrishna Kolluru 是位于加利福尼亚州帕洛阿尔托的 SeeCommerce.com
的高级数据库工程师。他通过了 Oracle 9i 和 DB2 7.1 的 DBA 认证。SeeCommerce 是供应链性能管理方面的佼佼者。可以通过
rkolluru@seecommerce.com与他联系。
|
对本文的评价
| |
