db2 监控脚本（）

DB2 V9性能监控脚本
#监控缓冲池命中率 
select substr(db_name,1,30) as db_name,substr(bp_name,1,30) as 


bp_name,data_hit_ratio_percent,index_hit_ratio_percent,total_hit_ratio_percent from sysibmadm.bp_hitratio where bp_name 


not like 'IBMSYSTEM%'; 
select  * from sysibmadm.bp_hitratio 
#监控PACKAGE_CACHE大小 
with dbcfg1 as (select int(value) as pckcachesz from sysibmadm.dbcfg where name='pckcachesz') 
select pckcachesz as "Package Cache Size",pkg_cache_lookups as "Lookups",pkg_cache_inserts as 


"Inserts",pkg_cache_num_overflows as "Overflows", 
100*pkg_cache_size_top /(pckcachesz*4096) as "%PKG Cache alloc" from dbcfg1,sysibmadm.snapdb; 


#监控执行成本最高的sql语句 
select agent_id,rows_selected,rows_read from sysibmadm.snapappl fetch first 10 rows only; 


#监控运行最长的sql语句 
select substr(appl_name,1,15) as Appl_name,elapsed_time_min as "Elapsed Min.",appl_status as "Status ",substr


(authid,1,10) as auth_id, 
substr(inbound_comm_address,1,15) as "IP Address",substr(stmt_text,1,30) as "SQL Statement" 
from sysibmadm.long_running_sql order by 2 desc; 


select * from sysibmadm.long_running_sql order by 2 desc; 


#监控sql准备和预编译时间最长的sql语句 
average_execution_time_s sql语句平均执行时间 
prep_time_ms   最长的sql预编译时间 
prep_time_precent 预编译时间占整个执行时间的百分比 
select num_executions,average_execution_time_s,prep_time_ms,prep_time_precent,substr(stmt_text,1,40) as "SQL_Text" 
from sysibmadm.query_prep_cost where average_execution_time_s>0 order by prep_time_precent desc; 


#监控执行次数最多的sql语句 
Select num_executions "Num Execs",average_execution_time_s as "Avg Time(sec)",stmt_sorts as "Num 


Sorts",sorts_per_execution as "Sorts Per Stmt", 
substr(stmt_text,1,35) as "SQL Stmt" from sysibmadm.top_dynamic_sql where num_executions>0 order by 1 desc fetch first 5 


rows only; 


Select num_executions "Num Execs",average_execution_time_s as "Avg Time(sec)",stmt_sorts as "Num 


Sorts",sorts_per_execution as "Sorts Per Stmt", 
substr(stmt_text,1,35) as "SQL Stmt" from sysibmadm.top_dynamic_sql where num_executions>0 order by 2 desc fetch first 5 


rows only; 




#监控排序次数最多的sql语句 
select stmt_sorts,sorts_per_execution,substr(stmt_text,1,60) as stmt_text from top_dynamic_sql order by stmt_sorts fetch 


first 5 rows only; 


#监控LOCK WAIT时间 
select substr(ai.appl_name,1,20) as appl_name,substr(ai.primary_auth_id,1,10) as auth_id,ap.lock_waits as 


lock_waits,ap.lock_wait_time /1000 as "Total Wait(s)", 
(ap.lock_wait_time/ap.lock_waits) as "Avg Wait(ms)" from sysibmadm.snapappl_info ai,sysibmadm.snapappl ap 
where ai.agent_id=ap.agent_id and ap.lock_waits>0; 


#监控LOCK chain 可以定位谁持有锁，谁在等待锁 


select substr(ai_h.appl_name,1,10) as "Hold App",substr(ai_h.primary_auth_id,1,10) as "Holder",substr(lw.appl_name,1,10) 


as "Wait App", 
substr(lw.authid,1,10) as "Waiter",lw.lock_mode,lw.lock_object_type,substr(lw.tabname,1,10) as "TabName",substr


(lw.tabschema,1,10) as "Schema", 
timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time)) as "waiting(s)" 
from sysibmadm.lockwaits lw,sysibmadm.snapappl_info ai_h where lw.agent_id_holding_lk=ai_h.agent_id; 




#监控锁内存使用 
with dbcfg1 as (select float(int(value)*4096) as locklist from sysibmadm.dbcfg where name='locklist'),dbcfg2  as (select 


float(int(value)*4096) as maxlock from sysibmadm.dbcfg where name='maxlock') 
select  dec((lock_list_in_use/locklist)*100,4,1) as "%Lock List",dec((lock_list_in_use/(locklist*(maxlocks/100))


*100),4,1) as "% to Maxlock", 
appls_cur_cons as "Number of Cons",lock_list_in_use/appls_cur_cons as "Avg Lock Mem Per Con (bytes)" 
from dbcfg1,dbcfg2,sysibmadm.snap.db; 




#监控锁升级、死锁和锁超时 
select substr(ai.appl_name,1,10) as Application,substr(ai.primary_auth_id,1,10) as AuthID,int(ap.locks_held) as 


"#Locks", 
int(ap.lock_escals) as "Escalations",int(ap.lock_timeouts) as "Lock TimeOuts",int(ap.deadlocks) as "Deadlocks", 
int(ap.int_deadlock_rollbacks) as "Dlock Victim",substr(inbound_comm_address,1,15) as "IP Address" 
from sysibmadm.snapappl ap,sysibmadm.snapappl_info ai where ap.agent_id=ai.agent_id; 


#监控全表扫描的SQL 
select substr(authid,1,10) as authid,substr(appl_name,1,20) as appl_name,percent_rows_selected from 


sysibmadm.appl_performance; 


#检查PAGE CLEANERS 是否足够 
with db_snap as (select float(pool_drty_pg_steal_clns) as pg_steal,float(pool_drty_pg_thrsh_clns) as chg_pg_thrsh,float


(pool_lsn_gap_clns) as softmax, 
float(pool_drty_pg_steal_clns + pool_drty_pg_thrsh_clns +pool_lsn_gap_clns) as total_clns from sysibmadm.snapdb) 
select dec((pg_steal/total_clns)*100,4,1) as "% Steals ",dec((chg_pg_thrsh/total_clns)*100,4,1) as "%Threshold",dec


((softmax/total_clns)*100,4,1) as "% softmax" 
from db_snap; 


#检查PREFETCHER 是否足够 
with bp_snap as (select substr(bp_name,1,30) as 


bp_name,unread_prefetch_pages,pool_async_data_reads+pool_async_index_reads as async_reads, 
pool_temp_data_p_reads+pool_temp_index_p_reads as total_reads from sysibmadm.snapbp where bp_name not like 'IBMSYSTEM%') 
select bp_name,unread_prefetch_pages,dec(100*(total_reads - async_reads)/total_reads,5,2) as "% Synch Reads",dec(100* 


unread_prefetch_pages/total_reads,5,2) as "% unread pages" from bp_snap; 


#监控数据库内存使用 
select pool_id,pool_secondary_id,pool_cur_size,pool_watermark from sysibmadm.snapdb_memory_pool 




#监控日志使用情况 
select int(total_log_used/1024/1024) as "Log Used (Mb)",int(total_log_available/1024/1024) as "Log Space Free(Mb)", 
int((float(total_log_used)/float(total_log_used+total_log_available))*100) as "Pct Used",int(tot_log_used_top/1024/1024) 


as "Max Log Used (Mb)", 
int(sec_log_used_top/1024/1024) as "Max Sec. Used (Mb)",int(sec_logs_allocated) as "Secondaries" from sysibmadm.snapdb; 


#监控占有日志空间最旧的交易 
select substr(ai.appl_status,1,20) as "Status",substr(ai.primary_auth_id,1,10) as "Authid",substr(ai.appl_name,1,15) as 


"Appl Name", 
int(ap.UOW_LOG_SPACE_USED/1024/1024) as "Log Used (M)",int(ap.appl_idle_time/60) as "Idle for(min)",ap.appl_con_time as 


"Connected Since" 
from sysibmadm.snapdb db,sysibmadm.snapappl ap,sysibmadm.snapappl_info ai where ai.agent_id=db.APPL_ID_OLDEST_XACT and 


ap.agent_id=ai.agent_id; 




监控存储路径 
select substr(type,1,20) as type ,substr(path,1,50) as path from sysibmadm.dbpaths order by type; 


监控表空间使用情况 
db2 "select substr(tbsp_name,1,18),tbsp_type,tbsp_free_size_kb/1024 as "tbsp_free_size(MB)",tbsp_utilization_percent 


from sysibmadm.tbsp_utilization;
回复 引用 报告