ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 从v$active_session_history解决数据库异常一例

从v$active_session_history解决数据库异常一例

原创 Linux操作系统 作者:testmvb 时间:2008-12-03 17:53:01 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 一个业务122日凌晨0:35分出现异常。业务总处理超时18.99,共有3次。在系统性能负载图表上发现此段时间性能系统并行等待较高,负载不高为1.4左右。

问题分析:

察看报警日志。没有异常信息。

查询v$active_session_history, 仍然存在数据。因为有3audsid 8281618482826185828216183。对应的sid 619732730,执行如下的sql,查询003500:36之间这三个会话在等待什么。

select SESSION_ID,SAMPLE_TIME,SQL_ID,WAIT_CLASS,WAIT_TIMEblocking_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

从结果可以看到730session0:35:39 0:35:57都在等待application,执行的sqlcu5udfhyrhhsc,被进程id652的会话所阻塞。在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运行的进程,

结论:

现在可以得到结论,某个job123540秒正在执行,这个jobtbl_cust_mo加锁,造成了619732730会话的阻塞,从而造成业务超时。

找到问题点后,通知开发人员做修改后问题解决

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10522540/viewspace-504568/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-23

  • 博文量
    51
  • 访问量
    164929