SQL语句性能分析方法.doc

DB2 SQL语句性能分析方法语句性能监测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 monitor The result can be loaded into a table and for analysis*/ #define KEYNUMBER 23#include #include int SearchText(char *str);int main(int argc, char * argv) FILE *fp_in,*fp_out; char buffer32767; char *ptr =abc ; int i; int sequence=0; char *oper=sadf; char *value=skdf; char recKEYNUMBER32767; if(argc != 3) printf(Usage:cvevm input outputn); exit(1); fp_in = fopen(argv1,r); fp_out = fopen(argv2,w); if(fp_in = NULL) | (fp_out = NULL) printf(open file failuren); 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) valuestrlen(value)-1 = 0; strcpy(reci,value); fprintf(fp_out,%d,sequence); for (i=0;iKEYNUMBER;i+) if(i = 10) reci11 = 0; if(i = 12) reci12 = 0; if(i = 13) reci10 = 0; if (i = 9) fputc(,fp_out); fprintf(fp_out,%s,reci); fputc(,fp_out); fputc(,fp_out); else fprintf(fp_out,%s,reci); fprintf(fp_out,n); fclose(fp_in); fclose(fp_out); int SearchText(char *str) char *opersKEYNUMBER = 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;iKEYNUMBER;i+) if (strncmp(str,opersi,strlen(str) = 0) return (i+1); return 0;请选用C编译器将它编译成执行文件。用法如下：cvevm inputfile outputfileinputfile 是语句监测器的输出结果，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解决性能问题经过分析，你应该能够了解哪些语句是导致性能问题的根本原因，接下来针对具体的语句来分析原因。常见的语句性能问题通常由以下几个原因造成1 相关表没有合适的索引2 有合适的索引，语句执行时没有使用（用工具dynexpln, db2expln 可了解语句的具