快速定位当前最消耗CPU的应用程序

字数 5149阅读 2755评论 5赞 5

如何快速定位当前最消耗资源的应用程序？

由于v9.5后应用连接以线程显示不再以进程显示，所以 ps -ef 不能直接获得应用程序进程

思路： 1、先定位高CPU线程 2、再由线程定位应用程序

1、通过db2pd -edus 获得最高CPU线程 -edus 是应用程序启动以来的统计不是每次的计数，所以需要配合UOW Executing进行过滤

EDU ID TID Kernel TID EDU Name USR SYS

========================================================================================================================================

48886 48886 89391349 db2agent (testdb) 0 2400.313141 635.205920

50415 50415 79233133 db2agent (testdb) 0 0.021561 0.011889

46008 46008 122748981 db2agent (testdb) 0 2432.397296 637.073038

2、通过db2 list application show detail |grep "UOW Executing" |grep 46008 定位出appl name,IP和appl handle

CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status Status Change Time DB Name DB Path

Handle Agents partition number pid/thread

-------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------- -------------------------------------------------------------- ----- ---------- ---------------- --------------- ------------------------------ -------------------------- -------- --------------------

U1_BUSI JoYing.Search.HotelS 494 99.48.212.125.8136.131210082624 07256 1 0 46008 UOW Waiting 2014-01-02 14:06:24.300444 testdb /db2home/db2inst1/db2inst1/NODE0000/SQL00001/

3、根据appl handle获得当前运行的静、动态sql

select agent_id,stmt_type,stmt_operation,package_name,SECTION_NUMBER,a.stmt_text,rows_read,rows_written,query_cost_estimate,query_card_estimate,

stmt_sorts,total_sort_time,sort_overflows,stmt_start,stmt_Stop-stmt_start as duration,stmt_usr_cpu_time_ms,stmt_elapsed_time_ms,

pool_data_l_reads,pool_data_p_reads,pool_index_l_reads,pool_index_p_reads,

pool_temp_data_l_reads,pool_temp_data_p_reads,dbpartitionnum

from SYSIBMADM.SNAPSTMT a

where agent_id=494

--order by 2,3;

order by stmt_usr_cpu_time_ms desc ,pool_data_l_reads desc,pool_data_l_reads;

--定位静态语句，所属存储过程，该存储过程的哪句话

select b.*,c.PROCSCHEMA,c.PROCNAME from

syscat.STATEMENTS b, syscat.PROCEDURES c,syscat.ROUTINEDEP d