性能调优步骤
明确问题->收集数据->分析数据->细化、定位问题->优化
环境: db2 9.7, suse linux 11 问题：%usr CPU高，大约99%，db2sysc进程使用的最多
收集数据
---系统层面 vmstat 1 10 > vmstat.1 ps -elf > pself.1 ps aux > psaux.1 iostat 1 10 > iostat.1
---数据库层面 db2 connect to <db_name> db2pd -eve > db2pd_eve.out db2pd -edus interval=120 > db2pd_edu_120.out db2pd -stack all > db2pd_stack.out db2 -v get monitor switches > get_monitor.out #db2 -v update monitor switches using bufferpool on sort on table on uow on statement on sort on lock on db2 -v get snapshot for applications on <db_name> > snap_applications.log db2 -v get snapshot for database on <db_name> > snap_db.log db2 -v get snapshot for all on <db_name> > snap_all.log db2 -v reset monitor all #db2 -v update monitor switches using bufferpool off sort off table off uow off statement off sort off lock off db2 -v terminate
分析数据
对数据的访问是通过SQL语句执行的，绝大数性能问题都是SQL本身问题
动态语句：JDBC、DB2命令行
静态语句：存储过程、嵌入式SQL程序
细化定位查找SQL
1、可以通过mon_get_pkg_cache_stmt（监控数据库包告诉缓存中的动态和静态SQL语句）接口查询
举例：
a)查找平均最消耗CPU时间的前5条SQL语句
db2 -v "select member,section_type,total_cpu_time/num_exec_with_metrics as avg_cpu_time,executable_id from table(mon_get_pkg_cache_stmt('D',null,null,-2) ) as t where t.num_exec_with_metrics <> 0 order by avg_cpu_time fetch first 5 rows only"
total_cpu_time 表示在DB2中耗用的CPU时间总计，单位微秒
num_exec_with_metrics 表示在收集度量值情况下执行的SQL语句次数
executable_id 表示SQL语句的HASH值
之后根据HASH值找到具体的SQL语句
db2 -v "select stmt_text from table(MON_GET_PKG_CACHE_STMT) ( NULL,executable_id,NULL,-2))
#b)查找消耗最多CPU时间的前10条SQL语句
SELECT MEMBER,SECTION_TYPE ,varchar(stmt_text,200) as statement,num_exec_with_metricsas numExec, TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as AVG_CPU_TIME,TOTAL_CPU_TIMEFROM TABLE(MON_GET_PKG_CACHE_STMT( 'D', NULL, NULL, -2)) as T WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME descfetch first 10 rows only
c)占用CPU较多的动态SQL语句
db2 "select TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as AVG_CPU_TIME, NUM_EXEC_WITH_METRICS, substr(STMT_TEXT,1,100) as STMT_TEXT from table( mon_get_pkg_cache_stmt (null,null,null,null)) as T where T.NUM_EXEC_WITH_METRICS <> 0 order by AVG_CPU_TIME DESC"
2、可以通过db2pd -edus interval=120 来收集120秒之内每个edu占用的CPU时间，结合其他的信息，比如db2pd -eve、application snapshot的输出，来看一下edu对应的应用，以及应用执行的SQL语句是什么，针对这些SQL语句来优化。
3、通过快照查询SQL
收集快照脚本：
db2 connect to <db_name>
db2 -v get snapshot for all on <db_name> > snap.out
db2 -v reset monitor all
db2 -v terminate
找到Application SNAPSHOT显示sort、rows read高的SQL
性能调优
查找PAT树来解决问题
首先使用visual Explain或者db2exfmt来分析这些高代价SQL语句。
随后使用Design Advisor工具来重新设计这些SQL语句。
db2expln和db2advis命令使用举例：
db2expln命令分析SQL性能：
db2iyx@tsywdb:~/sqllib/misc>db2expln -d DB_NAME -q "SQL"-g -o sql01.sql
参数
-g 执行计划图
-d指定数据库名
-q是一个SQL语句，也可以用-f指定某个保存了SQL的文件，
-o可以输出到文件。
db2advis命令优化SQL语句：
db2iyx@tsywdb:~/sqllib/misc> db2 list tables for all|grep EXPLAIN_INSTANCE
db2iyx@tsywdb:~/sqllib/misc> db2 -tvf EXPLAIN.DDL
db2iyx@tsywdb:~/sqllib/misc> db2 list tables for all|grep EXPLAIN_INSTANCE
EXPLAIN_INSTANCE DB2IYX T 2017-02-21-13.26.42.705551
db2advis命令需要创建explain表才能使用，而且DB2对于SQL执行计划的explain表，需要手工创建。
db2iyx@tsywdb:~/sqllib/misc> db2advis -d whsunecm -s "SQL" –g -o sql02.sql