问题分析:
察看报警日志。没有异常信息。
查询v$active_session_history, 仍然存在数据。因为有3个audsid 82816184,82826185,828216183。对应的sid 为619,732,730,执行如下的sql,查询00:35到00:36之间这三个会话在等待什么。
select SESSION_ID,SAMPLE_TIME,SQL_ID,WAIT_CLASS,WAIT_TIME,blocking_session,blocking_session_status from v$active_session_history
where SESSION_ID IN ('639','712','730') AND sample_time between to_date('200812020035','yyyymmddhh24mi') and to_date('200812020036','YYYYMMDDHH24MI')
ORDER BY SAMPLE_TIME ASC
得到如下信息
730|2008-12-2 0:35:00.728|7cx3vtphrb4tf|User I/O|0||NO HOLDER
712|2008-12-2 0:35:04.738|7dyjxbh6qf91q|User I/O|0||NO HOLDER
639|2008-12-2 0:35:09.738|1t5baq29f974k||673||NOT IN WAIT
712|2008-12-2 0:35:10.738|f52bdqa0jtvx7||800||NOT IN WAIT
712|2008-12-2 0:35:11.738|48z0d7vd2cks4||456449||NOT IN WAIT
730|2008-12-2 0:35:12.738|bfb5gk5v1uf4b|User I/O|0||NO HOLDER
639|2008-12-2 0:35:16.748|48z0d7vd2cks4||821277||NOT IN WAIT
639|2008-12-2 0:35:34.778||Commit|0|881|VALID
639|2008-12-2 0:35:36.778||Commit|0|881|VALID
712|2008-12-2 0:35:36.778|8m3ybqh1upksx|Commit|0|881|VALID
712|2008-12-2 0:35:38.778||Commit|0|881|VALID
730|2008-12-2 0:35:39.778|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:39.778||Commit|0|881|VALID
730|2008-12-2 0:35:40.778|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:40.778|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:40.778|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:41.778|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:41.778|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:41.778|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:42.778|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:42.778|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:42.778|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:43.778|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:43.778|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:43.778|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:44.788|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:44.788|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:44.788|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:45.831|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:45.831|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:45.831|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:46.831|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:46.831|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:46.831|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:47.831|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:47.831|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:47.831|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:48.831|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:48.831|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:48.831|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:49.831|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:49.831|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:49.831|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:50.841|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:50.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:50.841|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:51.841|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:51.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:51.841|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:52.841|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:52.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:52.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:53.841|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:53.841|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:53.841|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:54.841|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:54.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:54.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:55.841|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:55.841|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:55.841|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:56.851|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:56.851|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:56.851|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:57.851|cu5udfhyrhhsc|Application|0|652|VALID
730|2008-12-2 0:35:57.851|cu5udfhyrhhsc|Application|0|652|VALID
639|2008-12-2 0:35:57.851|cu5udfhyrhhsc|Application|0|652|VALID
712|2008-12-2 0:35:58.851|1913bk4d9v6bk|User I/O|0||NO HOLDER
639|2008-12-2 0:35:58.851|bh9cq0t9ak061|User I/O|0||UNKNOWN
730|2008-12-2 0:35:58.851|b7u5rwmt7jgfh|User I/O|0||UNKNOWN
730|2008-12-2 0:35:59.851|3dj30krxqh1jy||782||NOT IN WAIT
712|2008-12-2 0:35:59.851|bfb5gk5v1uf4b|User I/O|0||NO HOLDER
639|2008-12-2 0:35:59.851|42p6qk7vrkz76||316||NOT IN WAIT
从结果可以看到730的session从0:35:39 到0:35:57都在等待application,执行的sql是cu5udfhyrhhsc,被进程id为652的会话所阻塞。在v$sql中查到这个sql_id对应的sql为
UPDATE TBL_CUST_MO T SET T.FMOCOUNT = FMOCOUNT + 1 WHERE T.FMOBILE = :B2 AND FCUSTID = :B1;
查询sid 为652的会话究竟是干什么的
select distinct program from v$active_session_history where session_id=652 and sample_time between to_date('200812020035','yyyymmddhh24mi') and to_date('200812020036','YYYYMMDDHH24MI')
结果是
oracle@d3rundb (J000)
这个是job运行的进程,
结论:
现在可以得到结论,某个job在12点35分40秒正在执行,这个job对tbl_cust_mo加锁,造成了619,732,730会话的阻塞,从而造成业务超时。
找到问题点后,通知开发人员做修改后问题解决来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10522540/viewspace-504568/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10522540/viewspace-504568/