语句性能监测

DB2 UDB 提供了丰富的工具，以便用户分析定位问题，本文仅讨论如何利用DB2的语句事件监测器来了解应用系统中SQL语句的执行情况。

首先，我们来看一下如何使用DB2的语句事件监测器，以SAMPLE数据库为例。

1． 连接数据库

在DB2命令窗口输入命令db2 "connect to sample"

2． 创建语句事件监测器

db2 "create event monitor smevm for statements write to file 'c:\smevm'"
其中smevm是监测器名称，c:\smevm 存放监测结果的路径，如不存在，请先创建。

3． 激活事件监测器

db2 "set event monitor smevm state=1"
之后数据库会记录所有的语句执行状况，注意，激活事件监测器后数据库系统的性能会受到影响，特别是对OLTP的系统，影响更明显，切记监测完成之后要关闭事件监测器。

4． 关闭事件监测器

db2 "set event monitor smevm state=0"

5． 监测结果输出

db2evmon-db sample -evm smevm >smevm.out
结果为文本形式，示例如下：
5) Statement Event ...
Appl Handle: 12
Appl Id: *LOCAL.DB2.030421054225
Appl Seq number: 0001
Record is the result of a flush: FALSE
Type : Dynamic
Operation: Prepare
Section : 201
Creator : NULLID
Package : SQLC2D01
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : select * from staff
Start Time: 04/21/2003 13:59:45.405297
Stop Time: 04/21/2003 13:59:45.607771
Exec Time: 0.202474 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 1
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

如果是动态SQL语句，你可以在Text：看到具体的语句，如果是静态SQL语句，你不可以在Text：看到具体的语句，可通过Package 及Section知道运行的是哪个程序包中的第几条语句，要查看具体的语句，可用另外的工具db2expln。

性能分析

由于语句事件监测器输出结果是文本形式，在监测结果巨大的情况下，分析语句执行情况将变得比较困难，本文作者提供了一种方法，先将结果处理一下，然后把处理后的结果装入DB2的表中，用SQL语句分析，可很容易地得到，比如：执行时间最长的语句，语句的最长、最短、平均执行时间，不同语句的执行频度等等。

转换工具源代码如下：
/***********************************************************
Program name : cvemv.c
Written by : Zhang Li Min
Date : 2003.1.26
Function : Convert output of statements event monitorThe result can be loaded into a table and for analysis
************************************************************/
#define KEYNUMBER 23
#include <stdio.h>
#include <string.h>

int SearchText(char *str);

int main(int argc, char ** argv)
{
FILE *fp_in,*fp_out;
char buffer[32767];
char *ptr ="abc" ;
int i;
int sequence=0;
char *oper="sadf";
char *value="skdf";
char rec[KEYNUMBER][32767];
if(argc !=3)
{
printf("Usage:cvevm input output\n");
exit(1);
}
fp_in = fopen(argv[1],"r");
fp_out = fopen(argv[2],"w");
if((fp_in == NULL) || (fp_out == NULL))
{
printf("open file failure\n");
exit(1);
}
while(!feof(fp_in))
{
fgets(buffer,sizeof(buffer),fp_in);
if(feof(fp_in))
break;
ptr = strstr(buffer,"Statement Event");
if(ptr)
{
sequence++;
while(1)
{
fgets(buffer,sizeof(buffer),fp_in);
if(feof(fp_in)) break;
if (!strncmp(buffer," sqlstate",11))
{
break;
}
oper = strtok(buffer,":");
if(oper)
{
i = SearchText(oper);
if(i > 0){
value = strtok(NULL,":");
if(value)
{
value[strlen(value)-1] = 0;
strcpy(rec[i],value);
}
}
}
}
fprintf(fp_out,"%d",sequence);
for (i=0;i<KEYNUMBER;i++) {
if(i == 10)
{
rec[i][11] = 0;
}
if(i == 12)
{
rec[i][12] = 0;
}
if(i == 13)
{
rec[i][10] = 0;
}
if (i == 9)
{
fputc('"',fp_out);
fprintf(fp_out,"%s",rec[i]);
fputc('"',fp_out);
fputc(',',fp_out);
}
else
{
fprintf(fp_out,"%s,",rec[i]);
}
}

fprintf(fp_out,"\n");
}
}
fclose(fp_in);
fclose(fp_out);
}

int SearchText(char *str)
{
char *opers[KEYNUMBER] = {
" Appl Handle",
" Type",
" Operation",
" Section",
" Creator",
" Package",
" Cursor ",
" Cursor was blocking",
" Text",
" Exec Time",
" Number of Agents created",
" User CPU",
" System CPU",
" Fetch Count",
" Sorts",
" Total sort time",
" Sort overflows",
" Rows read",
" Rows written",
" Internal rows deleted",
" Internal rows updated",
" Internal rows inserted",
" sqlcode"};
int i;
for(i=0;i<KEYNUMBER;i++)
{
if (strncmp(str,opers[i],strlen(str)) == 0)
{
return (i+1);
}
}
return 0;
}
请选用C编译器将它编译成执行文件。
用法如下：
cvevm inputfile outputfile
inputfile 是语句监测器的输出结果，outputfile是处理过后的文件名

建表语句如下：
CREATE TABLE STATEMENTS(

SEQUENCE INT,
APPLHANDLE INT,
Type CHAR(32),
Operation CHAR(18),
Section INT,
Creator CHAR(32),
Package CHAR(18),
Cursor CHAR(18),
Cursorwasblocking CHAR(5),
Text VARCHAR(2048),
StartTime CHAR(200),
StopTime CHAR(200),
ExecTime dec(14,6) ,
NumofAgents INT,
UserCPU dec(14,6),
SystemCPU dec(14,6),
FetchCount INT,
Sorts INT,
Totalsorttime INT,
Sortoverflows INT,
Rowsread INT,
Rowswritten INT,
rowsdeleted INT,
rowsupdated INT,
rowsinserted INT,
sqlcode INT
);

分析步骤
1． 连接数据库，建表。
2． 装入数据（例：db2 load from sm.dat of del replace into statements）
3． 分析列出执行时间最长的语句
select exectime, text from statements order by exectime desc fetch first 10 row only

计算语句的平均、最快、最慢执行时间
select avg(exectime) as avg, min(exectime) as min, max(exectime) as max , text from statements group by text

按语句执行时间分类
with t(type,text) as (select case when exectime <0.001 then 'very fast' when exectime < 0.005 then 'fast' when exectime < 0.01 then 'middle'

when exectime < 0.020 then 'slow' else 'very slow' end as type ,text from statements where operation='Close') select type,count(*),text from t group by text,type

列出读记录操作最多的语句
select rowread, text from statements order by rowread desc fetch first 10 row only

解决性能问题

经过分析，你应该能够了解哪些语句是导致性能问题的根本原因，接下来针对具体的语句来分析原因。

常见的语句性能问题通常由以下几个原因造成
相关表没有合适的索引
有合适的索引，语句执行时没有使用（用工具dynexpln, db2expln 可了解语句的具体执行策略）
解决方法：
创建相关的索引
可能是相关表没有做统计，或数据变化后没有再做统计，重新对相关表运行RUNSTATS，如果是静态SQL语句，在运行统计之后，要做REBIND；另外要考虑的是语句使用的优化级别，数据库有配置参数DFT_QUERYOPT可控制数据库级别的默认优化级别，对于通过CLI/ODBC接口访问数据库的应用，可在配置文件db2cli.ini中用关键字DB2OPTIMIZATION来控制，对于静态SQL，在做BIND时由参数QUERYOPT控制优化级别。

good article, thanks for share!

以后要向你学习了,呵呵

谢谢分享 顶!!!

db2 8.1中好象有SQL直接查询了。不用这么复杂了。