在AIX 5.3平台下oracle 10.2.0.3测试环境中,经常发现某些oracle进程占用很大的CPU资源,但这些进程并不存在v$session中,具体发现脚本如下:
SQL>select spid, pid, program from v$process
where addr not in (select paddr from v$session)
SPID PID PROGRAM
------------ ------------------------------------------------
1962208 18 xxxxx@xxxx (TNS V1-V3)
2039826 49 xxxxx@xxxx (TNS V1-V3)
查看系统中发现该进程占用CPU比例很高,每个进程消耗CPU大约20%资源。
以下是该问题的具体分析:
SQL> alter session set events ' immediate trace name systemstate level 266'
或者
SQL>oradebug setospid 1962208
SQL>oradebug short_stack
查找对应的 PROCESS 18,具体信息如下:
Short stack dump: ksdxfstk+002c
Dump of memory from 0x070000009E5EF668 to 0x070000009E5EF870
................................
Repeat 29 times
----------------------------------------
SO: 70000009ea7aef8, type: 3, owner: 70000009e6322a0, flag: INIT/-/-/0x00
(call) sess: cur 0, rec 0, usr 0; depth: 0
----------------------------------------
SO: 7000000941eced0, type: 16, owner: 70000009e6322a0, flag: INIT/-/-/0x00
(osp req holder)
short stack分析:
start ->main->opimai_real->sou2o->opidrv->ipiodrv->opiino->ksucrp
->kscnfy->ktsmg_register_tac+0074->sspuser ..........
sspuser()功能是给oradebug 请求的代码路径,所以我们关注ktsmg_register_tac,通过metalink查询,发现BUG:6084108与我们所发现的问题非常相似,在10.2.0.4解决该bug。
附录:
其BUG:6084108具体问题描述:
SQL>select spid, pid, program from v$process
where addr not in (select paddr from v$session)
SPID PID PROGRAM
------------ ------------------------------------------------
1962208 18 xxxxx@xxxx (TNS V1-V3)
2039826 49 xxxxx@xxxx (TNS V1-V3)
查看系统中发现该进程占用CPU比例很高,每个进程消耗CPU大约20%资源。
以下是该问题的具体分析:
SQL> alter session set events ' immediate trace name systemstate level 266'
或者
SQL>oradebug setospid 1962208
SQL>oradebug short_stack
查找对应的 PROCESS 18,具体信息如下:
Short stack dump: ksdxfstk+002c
Dump of memory from 0x070000009E5EF668 to 0x070000009E5EF870
................................
Repeat 29 times
----------------------------------------
SO: 70000009ea7aef8, type: 3, owner: 70000009e6322a0, flag: INIT/-/-/0x00
(call) sess: cur 0, rec 0, usr 0; depth: 0
----------------------------------------
SO: 7000000941eced0, type: 16, owner: 70000009e6322a0, flag: INIT/-/-/0x00
(osp req holder)
short stack分析:
start ->main->opimai_real->sou2o->opidrv->ipiodrv->opiino->ksucrp
->kscnfy->ktsmg_register_tac+0074->sspuser ..........
sspuser()功能是给oradebug 请求的代码路径,所以我们关注ktsmg_register_tac,通过metalink查询,发现BUG:6084108与我们所发现的问题非常相似,在10.2.0.4解决该bug。
附录:
其BUG:6084108具体问题描述:
PROBLEM:
--------
Intermittently, oracle process abnormally terminates due to ora-3115, then
consumed one cpu 100%. There was no v$session info so could not get session
information to track down the root cause.
STACK TRACE:
------------
ktsmg_register_tac 0074 kscnfy ksucrp opiino opiodr opidrv sou2o opimai_real
main
暂时解决办法,用操作系统命令kill -9 删除以上进程。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-609745/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-609745/