DB2性能问题快速诊断手册Beta版

本文旨在提供在生产环境出现性能问题时快速定位问题的一种方法，本方法使用于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
以上完.