[转]DB2监控sql

ck1987

　　Ø 监控执行成本最高的SQL语句()
Db2 select AGENT_ID, ROWS_SELECTED, ROWS_READ, from sysibmadm.APPLICATION_PERFORMANCE;
Ø 监控运行时间最长的SQL语句
db2 "select ELAPSED_TIME_MIN, APPL_STATUS, AGENT_ID from sysibmadm.long_running_sql order by ELAPSED_TIME_MIN desc fetch first 5 rows only"
　　Db2 "select * from sysibmadm.TOP_DYNAMIC_SQL order by AVERAGE_EXECUTION_TIME_S desc fetch first 5 rows only";
Ø 监控SQL准备和预编译时间最长的SQL语句
db2 "select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, PERF_TIME_PERCENT from sysibmadm.QUERY_PERF_COST order by NUM_EXECUTIONS DESC“
Ø 监控执行次数最多的SQL语句
Db2 "select * from sysibmadm.TOP_DYNAMIC_SQL order by NUM_EXECUTIONS desc fetch first 5 rows only";
Ø 监控排序次数最多的的SQL语句
Db2 select STMT_SORTS, SORTS_PER_EXECUTION,substr(STMT_TEXT,1,60) as STMT_TXT from sysibmadm.TOP_DYNAMIC_SQL order by STMT_SORTS desc fetch first 5 rows only;
Ø 监控引起锁等待的SQL语句
Db2 select AGENT_ID, substr(STMT_TEXT,1,60) as STMT_TEXT, STMT_ELAPSED_TIME_MS from table (SNAPSHOT_STATEMENT('sample', -1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table (SNAPSHOT_LOCKWAIT('SAMPLE',-1))as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY) order by STMT_ELAPSED_TIME_MS DESC
　　转载请注明源自www.DB2China.net,请保留版权.
原文地址：http://www.db2china.net/club/thread-7500-1-1.html