主页 > DB2 > DB2监控语句[总结]

DB2监控语句[总结]

2010年2月14日 发表评论 查看评论

#监控执行成本最高的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;

 

#建立EXPLAIN所需要的表。

在SQLLIB目录下的MISC下:EXPLAIN.DDL

 

 

 

#查看表状态

db2 load query table DM.TM_CHNL_INV_MO

 

 

db2 "select * from table(mon_get_pkg_cache_stmt('D', NULL, NULL, -2)) as T order by total_cpu_time fetch first 10 rows only"

(查看消耗CPU时间最多的10个静态SQL语句。将参数‘D’改为‘S’,可以得到对应动态SQL语句的结果,改为NULL则得到对应所有SQL语句的结果)

原创文章,转载请注明: 转载自肚腩照明月'blog

本文链接地址: DB2监控语句[总结]

文章的脚注信息由WordPress的wp-posturl插件自动生成


  1. 本文目前尚无任何评论.

SEO Powered by Platinum SEO from Techblissonline