DB2性能问题快速诊断手册

本文旨在提供在生产环境出现性能问题时快速定位问题的一种方法，本方法使用于V9.7.06以上版本，请在执行本文中提供的脚本时确认您的数据库版本~

注:以下脚本在AIX下ksh环境运行ok~

一、检查锁等

1、检查是否有锁等

#check lockwait

Time1=`date +%Y%m%d%H%M%S`

echo "please enter the check databasename:"

read dbname

echo "The start time:`date +%Y%m%d%H%M%S`"|tee -a lockwait_$Time1.log

lock=`db2pd -db $dbname -locks wait showlocks|grep 'W'`

if [ $lock <> '' ]; then

echo "Successfullly,catched lockwait info,please check file db2pd.out"|tee -a lockwait_$Time1.log

db2pd -d $dbname -locks wait showlocks -transactions -applications -dynamic -file db2pd.out  >>/dev/null 2>&1

echo "method1:vi输出文件db2pd.out,根据locks的tranhdl->transactions的apphandl->applications的C-StmUID和L-StmUID->dynamicSQL的StmUID找到相应SQL文"|tee -a lockwait_$Time1.log

echo "method2:db2 get snapshot for application applid agentid,You can use snapshot,Get the apphandl SQL"|tee -a lockwait_$Time1.log

else

echo "No catch lockwait info!!!"|tee -a lockwait_$Time1.log

fi

echo "The end time:`date +%Y%m%d%H%M%S`"|tee -a lockwait_$Time1.log

-------------------------------------------------------------

2、抓取造成锁等的SQL,根据locks的tranhdl->transactions的apphandl->applications的C-StmUID和L-StmUID->dynamicSQL的StmUID找到相应SQL文

db2pd -d $dbname -locks wait showlocks -transactions -applications -dynamic -file db2pd.out

二、检查应用缓慢check_slowly

3、检查执行次数最多的10个SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别打开monitor

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "Display exe_num and exe_time,order by exe_num decs"

db2 -x "select num_executions,average_execution_time_s,substr(stmt_text,1,200) as stmt from sysibmadm.top_dynamic_sql order by num_executions desc fetch first 10 rows only"

4、抓取平均执行时间最长的10个SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "Display avg_exe_time and exe_num,Order by avg_exe_time desc"

db2 -x "select average_execution_time_s,num_executions,substr(stmt_text,1,200) as stmt from sysibmadm.top_dynamic_sql order by average_execution_time_s desc fetch first 10 rows only"

5、执行排序最多的10个SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "The key:stmt_sorts,sorts_per_execution,stmt_text"

db2 "select stmt_sorts,sorts_per_execution,stmt_text as stmt_text from SYSIBMADM.top_dynamic_sql order by stmt_sorts desc fetch first 20 rows only"

6、执行时间最长的10个SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "The key:agent_id,elapsed_time_min,appl_status,stmt_text"

db2 -x "select agent_id,elapsed_time_min,appl_status,substr(stmt_text,1,200) from sysibmadm.long_running_sql order by 2 desc fetch first 10 rows only"

7、监控预执行时间最长的10个SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "The Key:num_executions,average_execution_time_s,prep_time_ms,prep_time_percent,stmt_text"

db2 -x "select num_executions,average_execution_time_s,prep_time_ms,prep_time_percent,substr(stmt_text,1,200) from sysibmadm.query_prep_cost where average_execution_time_s>0 order by prep_time_percent desc fetch first 10 rows only"

8、监控执行等待最长时间的TOP10SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "The key:appl_id,total_wait_time,pool_read_time, pool_write_time, log_disk_wait_time, lock_wait_time"

db2 -x "select application_handle,total_wait_time,pool_read_time, pool_write_time, log_disk_wait_time, lock_wait_time from table(mon_get_connection(NULL,-1) ) order by total_wait_time" 

8、监控执行成本最高的10个SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "The Key: agent_id,rows_selected,rows_read"

db2 -x "select agent_id,rows_selected,rows_read from sysibmadm.snapappl order by 3 desc fetch first 10 rows only"

9、监控全表扫描成TOP10SQL

#Get TOP10 SQL 先检查monitor是否为on 否则需要在回话级别设置monitor为on

#db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

db2 get monitor switches

echo "The Key: authid,agent_id,appl_name,percent_rows_selected"

db2 "select substr(authid,1,10) as authid,agent_id,substr(appl_name,1,20) as appl_name,percent_rows_selected from sysibmadm.appl_performance where percent_rows_selected > 90 order by percent_rows_selected"

10、监控前10个大事务

db2 -x "SELECT application_handle,rows_returned,tcpip_send_volume FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t ORDER BY rows_returned DESC fetch first 10 rows only"

11、监控当前及历史事务日志空间利用情况

db2 "select int(total_log_used/1024/1024) as "LogUsed_Meg",int(total_log_available/1024/1024) as "Log_Space_Free_Meg",int((float(total_log_used)/float(total_log_used+total_log_available))*100) as "PctUsed",int(tot_log_used_top/1024/1024) as "MaxLogUsed_Meg",int(sec_log_used_top/1024/1024) as "MaxSecUsed_Meg" from sysibmadm.snapdb"

三、检查error及deadlock

#check dbsys error and deadlock

echo "------------------------------------------------"

Time1=`date +%Y%m%d%H%M%S`

echo "please enter the  start time such as 2014-07-01"

read time1

echo "please enter the end time such as 2014-07-03"

read time2 

echo "###############################################################"

echo "The start time:`date +%Y%m%d%H%M%S`"|tee -a db2error_$Time1.log

lock=`db2diag -level error -time ${time1}:${time2} -count|awk '{print $2}'`

lock1=`db2diag -level error -time ${time1}:${time2}|grep -i dead`

if [ $lock -ne 0 ]; then

    echo "Warnning!!!Catched error infomation has been put db2error.log,please check file db2error.log"|tee -a db2error_$Time1.log

    db2diag -level error -time ${time1}:${time2} >>db2error_$Time1.log

else

    echo "No error infomation was catched"|tee -a db2error_$Time1.log

fi

echo "###############################################################"

if [ $lock1 <> '' ];then

    echo "checked deadlock,please insure the info from db2error.log!!!"|tee -a db2error_$Time1.log

else

    echo "No checked deadlock!!!"|tee -a db2error_$Time1.log

fi

echo "The end time:`date +%Y%m%d%H%M%S`"|tee -a db2error_$Time1.log

以上完.