评论- 213&
qslice是一个win2000的工具包，能分析进程的cpu占用率，我们用于分析oracle的性能&
这是绿色的软件无需安装。
我们经常会碰到oracle的CPU占用居高不下，无法快速定位到问题SQL，就需要这个神器：qslice。
分析过程：
1、进入qslice，打开oracle进程，双击进入
&2、最大化弹出的窗口，发现连接Oracle的子线程在跳动。
&3、观察几分钟，揪出频繁出现的TID：1640
4、此时就可以按1640查询到问题SQL了
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = TO_NUMBER ('1640', 'xxxx')))
ORDER BY piece ASC
5、qslice已经无法从微软官网下载了，附上qslice的下载链接
/files/pcsky/qslice.rar
阅读(...) 评论()查看: 3102|回复: 8
求助，数据库某个进程cpu占用高
论坛徽章:3
客户管理员反映数据库cpu占用占用，查看后发现有个进程cpu占用特别高，有一条sql似乎进入了死循环，hanganalyzis分析文件：
Trace file /oracle/diag/rdbms/oa9/OA9/trace/OA9_ora_20644.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/oracle_home
System name:& & & & Linux
Node name:& & & & vdgl202
Release:& & & & 3.0.76-0.11-default
Version:& & & & #1 SMP Fri Jun 14 08:21:43 UTC 2013 (ccab990)
Machine:& & & & x86_64
VM name:& & & & VMWare Version: 6
Instance name: OA9
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 20644, image: oracle@vdgl202 (TNS V1-V3)
11:51:57.134
*** SESSION ID303.1-06-15 11:51:57.134
*** CLIENT ID)
11:51:57.134
*** SERVICE NAMESYS$USERS)
11:51:57.134
*** MODULE NAMEsqlplus@vdgl202 (TNS V1-V3))
11:51:57.134
*** ACTION NAME)
11:51:57.134
Processing Oradebug command 'hanganalyze 3'
11:51:57.147
===============================================================================
HANG ANALYSIS:
&&instances (db_name.oracle_sid): oa9.oa9
&&oradebug_node_dump_level: 3
&&analysis initiated by oradebug
&&os thread scheduling delay history: (sampling every 1.000000 secs)
& & 0.000000 secs at [ 11:51:57 ]
& && &NOTE: scheduling delay has not been sampled for 0.125530 secs& & 0.000000 secs from [ 11:51:53 - 11:51:58 ], 5 sec avg
& & 0.000000 secs from [ 11:50:57 - 11:51:58 ], 1 min avg
& & 0.000000 secs from [ 11:46:57 - 11:51:58 ], 5 min avg
&&vktm time drift history
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: &not in a wait&
& &&&Chain 1 Signature Hash: 0x673a0128
===============================================================================
Sessions in an involuntary wait or not in a wait:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
& & Oracle session identified by:
& && && && && & instance: 1 (oa9.oa9)
& && && && && && & os id: 27038
& && && && &&&process id: 23, oracle@vdgl202
& && && && &&&session id: 421
& && &&&session serial #: 22823
& & is not in a wait:
& && && && && &last wait: 3982 min 18 sec ago
& && && && && & blocking: 0 sessions
& && && && & current sql: select count(1) from(& &SELECT&&ers.request_id&&FROM newsoft.ecl_request_sheet&&ers,& &&&( select request_id,org_level,org_id,folder_id& && &from newsoft.ecl_request_sheet& && &where container_type = 0& && &start with request_id in(98058)& && & connect by prior request_id = parent_id& &&&) ersp,& &newsoft.ecl_req_role
& && && && & short stack: ksedsts()+465&-ksdxfstk()+32&-ksdxcb()+1927&-sspuser()+112&-__sighandler()&-kghrcappl()+249&-kghfrempty_ex()+133&-qerhjFreeSpace()+460&-qerhjFetch()+803&-qerflFetchOutside()+101&-rwsfcd()+103&-qeruaFetch()+574&-qersoProcessULS()+223&-qersoFetch()+6131&-subex1()+259&-subsr3()+183&-evaopn3()+2533&-expepr()+576&-evaiand()+51&-expeal()+23&-qerflRop()+39&-qerhjWalkHashBucket()+397&-qerhjInnerProbeHashTable()+700&-qersoFetch()+499&-qerjotFetch()+287&-rwsfcd()+103&-qerhjFetch()+621&-qerflFetchOutside()+101&-rwsfcd()+103&
& && && && &wait history:
& && && && &&&1.& && & event: 'db file sequential read'
& && && && && &&&time waited: 0.000031 sec
& && && && && && && &wait id: 440683& && && & p1: 'file#'=0x4
& && && && && && && && && && && && && && && & p2: 'block#'=0x37452
& && && && && && && && && && && && && && && & p3: 'blocks'=0x1
& && && && &&&* time between wait #1 and #2: 6.085476 sec
& && && && &&&2.& && & event: 'SQL*Net message to client'
& && && && && &&&time waited: 0.000003 sec
& && && && && && && &wait id: 440682& && && & p1: 'driver id'=0x
& && && && && && && && && && && && && && && & p2: '#bytes'=0x1
& && && && &&&* time between wait #2 and #3: 0.038544 sec
& && && && &&&3.& && & event: 'SQL*Net message from client'
& && && && && &&&time waited: 0.124268 sec
& && && && && && && &wait id: 440681& && && & p1: 'driver id'=0x
& && && && && && && && && && && && && && && & p2: '#bytes'=0x1
Chain 1 Signature: &not in a wait&
Chain 1 Signature Hash: 0x673a0128
-------------------------------------------------------------------------------
===============================================================================
Extra information that will be dumped at higher levels:
[level&&5] :& &1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[420]/1/421/c9f76e8/27038/SINGLE_NODE_NW/
11:51:57.159
===============================================================================
END OF HANG ANALYSIS
===============================================================================
11:51:57.159
===============================================================================
HANG ANALYSIS DUMPS:
&&oradebug_node_dump_level: 3
===============================================================================
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[420]/1/421/c9f76e8/27038/SINGLE_NODE_NW/
No processes qualify for dumping.
===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================
11:51:57.160
Oradebug command 'hanganalyze 3' console output:
Hang Analysis in /oracle/diag/rdbms/oa9/OA9/trace/OA9_ora_20644.trc
不能理解，这个会话没有等待事件，为什么会有这么高的cputime。
求职 : 认证徽章论坛徽章:71
数据库hang了？没hang的话，直接查下这个进程对应的session和process&&看看是否是active，执行了多少时间，执行的sql是什么
论坛徽章:11
弄一个AWR出来看看比较好
论坛徽章:3
lwfoon 发表于
弄一个AWR出来看看比较好
(38.81 KB, 下载次数: 27)
18:57 上传
点击文件名下载附件
论坛徽章:3
myles521 发表于
数据库hang了？没hang的话，直接查下这个进程对应的session和process&&看看是否是active，执行了多少时间， ...
没hang住，有问题的sql语句：select count(1) from( SELECT ers.request_id FROM newsoft.ecl_request_sheet ers,&&( select request_id, org_level, org_id, folder_id from newsoft.ecl_request_sheet where container_type = 0 start with request_id in(98058) connect by prior request_id = parent_id ) ersp,&&newsoft.ecl_req_role_groups erg,&&newsoft.group_users gu WHERE gu.user_id = :1 AND erg.group_id = gu.group_id AND erg.role_id = 16 AND ers.template_id = erg.request_id AND ers.container_type = 4 and nvl(ers.container_sub_type, 0) = 0 and ersp.org_level = 3 and ersp.folder_id = 0 and exists( SELECT et.request_id FROM newsoft.ecl_tasks et WHERE et.request_id = ers.request_id and et.status_id!=-1 AND et.assignee = ersp.request_id AND et.assignee_type = 0 UNION SELECT et.request_id FROM newsoft.ecl_tasks et,&&newsoft.ecl_user_position_level eupl WHERE et.request_id = ers.request_id and et.status_id!=-1 AND eupl.user_id = ersp.org_id AND et.assignee_type & 0 AND et.assignee = eupl.request_id AND et.assignee_type = eupl.posit ion_level_id AND ( eupl.del_id is null OR eupl.del_id in ( select del_grp_id from newsoft.ecl_delegate d where d.status_id = 1 and sysdate between d.start_time and nvl(d.end_time, to_date('', 'yyyy/mm/dd')) and ( d.request_id = -1 or d.request_id in ( select template_id from newsoft.ecl_request_sheet it where it.request_id = et.request_id ) ) ) ) ) and ers.folder_id=0 union SELECT ers.request_id FROM newsoft.ecl_request_sheet ers,&&( select request_id, org_level, org_id, folder_id from newsoft.ecl_request_sheet where container_type = 0 start with request_id in(98058) connect by prior request_id = parent_id ) ersp,&&newsoft.ecl_req_role_users erru WHERE erru.role_id = 16 AND ( (erru.user_type = 0 and ( erru.user_id = :2 or erru.user_id in (select request_id from newsoft.ecl_request_sheet start with request_id = :3 connect by prior parent_id = request_id) ) ) or (erru.user_type & 0 and exists(select 1 from newsoft.ecl_user_position_level p where p.request_id=erru.user_id and p.position_level_id=erru.user_type and p.user_id= :4 )) ) AND ers.template_id = erru.request_id AND ers.container_type = 4 and nvl(ers.container_sub_type, 0) = 0 and ersp.org_level = 3 and ersp.folder_id = 0 and exists( SELECT et.request_id FROM newsoft.ecl_tasks et WHERE et.request_id = ers.request_id and et.status_id!=-1 AND et.assignee = ersp.request_id AND et.assignee_type = 0 UNION SELECT et.request_id FROM newsoft.ecl_tasks et,&&newsoft.ecl_user_position_level eupl WHERE et.request_id = ers.request_id and et.status_id!=-1 AND eupl.user_id = ersp.org_id AND et.assignee_type & 0 AND et.assignee = eupl.request_id AND et.assignee_type = eupl.position_level_id AND ( eupl.del_id is null OR eupl.del_id in ( select del_grp_id from newsoft.ecl_delegate d where d.status_id = 1 and sysdate between d.start_time and nvl(d.end_time, to_date('', 'yyyy/mm/dd')) and ( d.request_id = -1 or d.request_id in ( select template_id from newsoft.ecl_request_sheet it where it.request_id = et .request_id ) ) ) ) ) and ers.folder_id=0 )
求职 : 认证徽章论坛徽章:71
stone1988xu 发表于
没hang住，有问题的sql语句：select count(1) from( SELECT ers.request_id FROM newsoft.ecl_request_sh ...
知道是这个sql引起的话，查下这个sql的执行计划
论坛徽章:25
优化 06m95m2hsmvmn 这个sql 吧.
论坛徽章:3
myles521 发表于
知道是这个sql引起的话，查下这个sql的执行计划
执行计划没什么问题，相同的语句，在很多项目里头跑，仅仅是有个用户出现了问题，代码上有做了准备，跟踪一些操作。
最想知道的是，为什么一条sql没latch竞争，会一直占用cputime持续几个钟头，看起来就像死循环一样。
论坛徽章:3
跑个10046，看看等待在哪里
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号2013年4月 Oracle大版内专家分月排行榜第二2013年3月 Oracle大版内专家分月排行榜第二2010年5月 Oracle大版内专家分月排行榜第二
2013年1月 Oracle大版内专家分月排行榜第三2011年12月 Oracle大版内专家分月排行榜第三2010年8月 Oracle大版内专家分月排行榜第三2010年4月 Oracle大版内专家分月排行榜第三2010年3月 Oracle大版内专家分月排行榜第三
2017年6月 Oracle大版内专家分月排行榜第三2017年3月 Oracle大版内专家分月排行榜第三2006年12月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。}