如何快速定位当前最消耗资源的应用程序
from : http://www.db2china.net/home/space.php?uid=34945&do=blog&id=31207
如何快速定位当前最消耗资源的应用程序？
如何快速定位当前最消耗资源的应用程序？
由于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
where b.pkgname=d.bname
and c.SPECIFICNAME=d.SPECIFICNAME
and c.PROCSCHEMA=d.ROUTINESCHEMA
and b.pkgname='P9051069' and b.SECTNO=1
4、解除
force application （494）
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞0Ctrl+Enter 发表
作者其他文章
评论 0 · 赞 0
评论 0 · 赞 1
评论 0 · 赞 0
评论 0 · 赞 1
评论 0 · 赞 0
添加新评论0 条评论