最近与同事交流有关DB2的一些问题，突然受到一些启发，所以编写了8个采集及分析数据库性能瓶颈的脚本与广大博友分享一起共同进步。在部署以下脚本时建议把它们配置到profile里面去，并把该脚本所在的目录改成只具备读和执行的权限~
具体脚本如下：
1、check_btneck.sh #检查db可能存在的瓶颈
--------------------------------------------------------------------------------------------------------------------
#This script create by fengzhh at 20140909
#It was used by check db bottle neck,such as catalog heap,bufferpool hit,bufferpool speed,log writte speed and so on
db2 connect to rptdb >>/dev/null 2>&1
echo "No1:检查编目缓冲区是否正常，正常情况下命中率大于98%"
db2 "select cast(((1-cat_cache_inserts/double(cat_cache_lookups))*100) as numeric(5,2))||'%' as catalog_hit_ratio from sysibmadm.snapdb"
echo "No2:检查包缓冲区是否正常，正常情况下命中率大于98%"
db2 "select cast(((1-pkg_cache_inserts/double(pkg_cache_lookups))*100) as numeric(5,2))||'%' as package_hit_ratio from sysibmadm.snapdb"
echo "No3:检查缓冲池异步写入是否正常，正常情况下异步写入百分比应大于95%"
db2 "select substr(bp_name,1,15) as bp_name,percent_writes_async from sysibmadm.bp_write_io where bp_name not like 'IBMSYS%'"
echo "No4:检查缓冲区平均读、写响应时间，正常情况下读\写响应时间应小于5ms"
db2 "select substr(r.bp_name,1,20) as bpname,average_read_time_ms,average_write_time_ms from sysibmadm.bp_read_io as r,sysibmadm.bp_write_io as w where r.snapshot_timestamp=w.snapshot_timestamp and r.bp_name=w.bp_name and r.bp_name not like 'IBMSYS%'"
echo "No5:检查日志写入速度平均响应时间，正常情况下读\写响应时间应小于3ms"
db2 "select (log_write_time_ns/1000000)/num_log_write_io as average_log_write_ms from sysibmadm.snapdb"
echo "当前日志使用情况如下："
db2 "select int(total_log_used/1024/1024) as "LogUsed_MB",int(total_log_available/1024/1024) as "Log_Free_MB",int(tot_log_used_top/1024/1024) as "MaxLogUsed_MB",int(sec_log_used_top/1024/1024) as "SecUsed_Max_MB" from sysibmadm.snapdb"
echo "No6:检查排序堆是否正常，正常情况下溢出百分比应小于1%"
db2 "select concat(char(cast((sort_overflows/double(total_sort_time))*100 as numeric(4,2))),'%') as sort_overflow_ratio from sysibmadm.snapdb"
##
-----------------------------------------------------------------------------------------------------------------
2、check_hottab #检查db内前10个热表
-----------------------------------------------------------------------------------------------------------------
#This script create by fengzhh at 20140909
#It was used by check db hot table,it was sorted by hot read or hot writen
db2 connect to rptdb >>/dev/null 2>&1
echo "No1:获取Top10读热表"
db2 "select substr(tabname,1,8) as tbname,rows_read,rows_written from sysibmadm.snaptab order by rows_read desc,rows_written desc fetch first 10 rows only"
echo "No2:获取Top10写热表"
db2 "select substr(tabname,1,8) as tbname,rows_read,rows_written from sysibmadm.snaptab order by rows_written desc,rows_read desc fetch first 10 rows only"
##
-------------------------------------------------------------------------------------------------------------------------------
3、check_hottab_sql
#根据前一个脚本得出的热表检查导致热表产生的详细SQL
--------------------------------------------------------------------------------------------------------------------------------
#This script create by fengzhh at 20140909
#It was used by get the special table's dynamic sql or static sql
db2 connect to rptdb >>/dev/null 2>&1
echo "please input tabname which you want to check:"
echo "--------------表名请输入大写-----------------"
read tbname
echo "No1:获取动态导致表事务繁忙的动态SQL"
db2 "select substr(stmt_text,1,200) as stmt_text,rows_read,ROWS_WRITTEN from sysibmadm.snapdyn_sql where translate(cast(substr(stmt_text,1,50) as varchar(55))) like '%${tbname}%' order by rows_read desc,ROWS_WRITTEN desc"
echo "No2:获取动态导致表事务繁忙的静态SQL"
db2 "select substr(PKGSCHEMA,1,8) as pkgschema,substr(PKGNAME,1,8) as pkgname,substr(SECTNO,1,2) as sectno,substr(TEXT,1,200) as sqltext from syscat.statements where translate(cast(substr(text,1,32672) as varchar(32672))) like '%${tbname}%'"
##
----------------------------------------------------------------------------------------------------------------------------
4、check_hottbs_sql
#检查导致热表空间的前10个SQL
-----------------------------------------------------------------------------------------------------------------------------
#This script create by fengzhh at 20140909
#It was used by check the special tablespace hot table
db2 connect to rptdb >>/dev/null 2>&1
echo "please input the id of tablespace which you read to check:"
echo "----------------------------------------------------------"
read tbsid
echo "No1:获取Top10读热表"
db2 "select substr(tabname,1,8) as tbname,rows_read,rows_written from sysibmadm.snaptab where tabname in(select tabname from syscat.tables where TBSPACEID=${tbsid}) order by rows_read desc fetch first 10 rows only"
echo "No2:获取Top10写热表"
db2 "select substr(tabname,1,8) as tbname,rows_read,rows_written from sysibmadm.snaptab where tabname in(select tabname from syscat.tables where TBSPACEID=${tbsid}) order by rows_written desc fetch first 10 rows only"
##
--------------------------------------------------------------------------------------------------------------------------------
5、check_top10_sql
#检查前10个最消耗资源的SQL
---------------------------------------------------------------------------------------------------------------------------------
#This script create by fengzhh at 20140909
#It was used by get the Top10 dynamic sql"
db2 connect to rptdb >>/dev/null 2>&1
echo "No1:获得执行次数最多的TOP10SQL"
db2 "select substr(stmt_text,1,20),NUM_EXECUTIONS,TOTAL_USR_CPU_TIME,TOTAL_EXEC_TIME,SORT_OVERFLOWS from sysibmadm.snapdyn_sql order by NUM_EXECUTIONS desc fetch first 10 rows only"
echo "No2:获得平均执行时间最长的TOP10SQL"
db2 "select substr(stmt_text,1,20) as stmt,average_execution_time_s,num_executions from sysibmadm.top_dynamic_sql order by average_execution_time_s desc fetch first 10 rows only"
echo "N03:获得占用CPU时间最长的TOP10SQL"
db2 "select substr(stmt_text,1,20),TOTAL_USR_CPU_TIME,TOTAL_EXEC_TIME,SORT_OVERFLOWS,NUM_EXECUTIONS from sysibmadm.snapdyn_sql order by total_usr_cpu_time desc fetch first 10 rows only"
echo "No4:获得排序最多的TOP10SQL"
db2 "select substr(stmt_text,1,20) as stmt_text,stmt_sorts,SORT_OVERFLOWS,TOTAL_USR_CPU_TIME,NUM_EXECUTIONS from sysibmadm.snapdyn_sql order by stmt_sorts desc fetch first 10 rows only"
echo "No5:获得排序溢出最多的TOP10SQL"
db2 "select substr(stmt_text,1,20) as stmt_text,SORT_OVERFLOWS,stmt_sorts,TOTAL_USR_CPU_TIME,NUM_EXECUTIONS from sysibmadm.snapdyn_sql order by SORT_OVERFLOWS desc fetch first 10 rows only"
echo "No6:获得预执行时间TOP10SQL"
db2 "select substr(stmt_text,1,20) as stmt_text,prep_time_ms,prep_time_percent,num_executions,average_execution_time_s from sysibmadm.query_prep_cost where average_execution_time_s>0 order by prep_time_percent desc fetch first 10 rows only"
echo "N07:获得写最多的TOP10SQL"
db2 "select substr(stmt_text,1,20),ROWS_WRITTEN,ROWS_READ,NUM_EXECUTIONS from sysibmadm.snapdyn_sql order by ROWS_WRITTEN desc fetch first 10 rows only"
##
---------------------------------------------------------------------------------------------------------------------------------
6、check_lckwait
#检查是否存在锁等，如果存在则捕获具体造成锁等SQL
----------------------------------------------------------------------------------------------------------------------------------
#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
--------------------------------------------------------------------------------------------------------------------------
7、check_dynaccessplan
#生成相关问题SQL的访问计划（可分析动态和静态SQL）
--------------------------------------------------------------------------------------------------------------------------
#it was created by fengzhh at 20140909
#it was used by analysis accessplan
db2 connect to cbusdb >>/dev/null 2>&1
echo "please put your sql in your file then input full path which your file:"
echo "-----------------------------------------------------------------------"
read me
if [ $me <> '' ];
then
db2expln -d cbusdb -i -g -f "${me}" -t
else
echo "Error,You input info is no correct"
fi
##
-----------------------------------------------------------------------------------------------------------
8、check_staticaccessplan
#生成相关问题SQL的访问、计划（只分析相关静态package的访问计划）
------------------------------------------------------------------------------------------------------------
#it was created by fengzhh at 20140909
#it was used by analysis accessplan
db2 connect to cbusdb >>/dev/null 2>&1
echo "please put your pkgschema and pkgname:"
echo "-----------------------------------------------------------------------"
read pkgs
read pkgn
db2expln -d xbusdb -i -g -c $pkgs -p $pkgn -s 0 -t
----------------------------------------------------------------------------------------------------------
阅读(5240) | 评论(0) | 转发(0) |
