问题描述
项目系统负载突然变大,查到top sql是一个存储过程,执行计划缺少合理的索引,SQL是个update操作,于是kill 调用的几个session 建立一个索引,结果发现kill几个用户进程后实例挂了.
专家解答
下面是alert日志,可见instance crash前出现过死锁,就是那个update sql
Thu Aug 15 11:08:04 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_24011.trc. Thu Aug 15 11:27:00 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_26527.trc. Thu Aug 15 11:27:03 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_26912.trc. Thu Aug 15 11:32:46 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_24298.trc. Thu Aug 15 11:38:14 2013 Errors in file /oracle/admin/icme/bdump/icme_pmon_3518.trc: ORA-07445: exception encountered: core dump [kggchk()+77] [SIGSEGV] [Address not mapped to object] [0x0000001BC] [] [] Thu Aug 15 11:38:17 2013 MMAN: terminating instance due to error 472 Instance terminated by MMAN, pid = 3522
VI /oracle/admin/icme/bdump/icme_pmon_3518.trc
** 2013-08-15 11:38:14.534 Process state: 0x0x224214240 freeing in-flux 220b5ec40 Child simulator hash latch level=7 child#=111 Location from where latch is held: kcbsacc: lookup dba: dba Context saved from call: 26555738 state=busy, wlstate=free waiters [orapid (seconds since: put on list, posted, alive check)]: 186 (6, 1376537894, 6) 435 (6, 1376537894, 6) 51 (6, 1376537894, 6) waiter count=3 gotten 25796245 times wait, failed first 445 sleeps 1 gotten 0 times nowait, failed: 0 Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x1bc, PC: [0x3b5d84d, kggchk()+77] *** 2013-08-15 11:38:14.767 ksedmp: internal or fatal error ORA-07445: exception encountered: core dump [kggchk()+77] [SIGSEGV] [Address not mapped to object] [0x0000001BC] [] [] ----- Call Stack Trace ----- ksedst()<ksedmp()<ssexhd()<__funlockfile()<kggchk()<kcbshlc()<kslilcr()<kslflux()<ksl_cleanup()... 看到freeing="" in-flux="" 220b5ec40="" child="" simulator="" hash="" latch="" level="7" child#="111" 应该与kslflux函数有关,看到simulator个人感觉与shared="" pool="" advice有关="" 再次崇拜老杨也有遇到过类似案例http://space.itpub.net/4227/viewspace-713527 在MOS上查到最接近的是下面这篇 note ID 443909.1 ORA-00600: internal error code, arguments: [kcbshlc_1], [71], [], [], [], [], [], [] PMON: terminating instance due to error 472 Stack Trace is similar to the following: ksedst ksedmp ssexhd sigreturn kcbshlc kslilcr ksl_cleanup ksuxfl ksuxda ksucln ksbrdp opirip
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。