oracle diag idle wait,oracle TX等待事件的解决方法

查询数据库等待事件：

EVENT   COUNT(*)

---------------------------------------------------------------- ----------

SQL*Net message from client  1

heartbeat redo informer  1

pmon timer  1

Streams AQ: qmn slave idle wait  1

Streams AQ: waiting for time management or cleanup tasks  1

Streams AQ: qmn coordinator idle wait  1

smon timer  1

lreg timer  1

SQL*Net message to client  1

VKTM Logical Idle Wait  1

AQPC idle  1

DIAG idle wait  2

Space Manager: slave idle wait 10

rdbms ipc message 17

14 rows selected.

现有表a;

SQL> select * from a;

ID NAME

---------- ----------

2 a

SQL>

session1:--执行不提交

update a set id=1 where id=2;

SQL> update a set id=1 where id=2;

1 row updated.

SQL>

session2:--hang住

update a set id=3 where id=2;

SQL>

SQL> update a set id=3 where id=2;

查询数据库等待事件：

EVENT   COUNT(*)

---------------------------------------------------------------- ----------

enq: TX - row lock contention  1

Streams AQ: waiting for time management or cleanup tasks  1

Streams AQ: qmn coordinator idle wait  1

AQPC idle  1

heartbeat redo informer  1

pmon timer  1

smon timer  1

lreg timer  1

Streams AQ: qmn slave idle wait  1

SQL*Net message to client  1

VKTM Logical Idle Wait  1

DIAG idle wait  2

SQL*Net message from client  2

Space Manager: slave idle wait 10

rdbms ipc message 17

15 rows selected.

分析：比之前多了tx等待事件，行级锁

解决：

个人写的sql，觉得很好用，特此分享：

select  l.session_id, s.SERIAL#,o.object_name,s.PREV_SQL_ID,pr.SPID

from v$session s,v$locked_object l, dba_lock lo,dba_objects o,v$process pr

where l.session_id = s.SID

and lo.session_id=l.SESSION_ID

and lo.blocking_others not in ('Not Blocking')

and pr.ADDR=s.PADDR

and o.object_id=l.OBJECT_ID

或者

select se.sid, se.SERIAL#,lo.type,lo.ctime

from v$lock lo, v$session se

where lo.sid = se.sid

and lo.block = 1

SQL> /

SESSION_ID    SERIAL# OBJECT_NAME       PREV_SQL_ID   SPID

---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------

74  839 A

然后kill session，先不急着kill，考虑下kill的是哪个？没错，肯定是第一条执行未提交的，第二个session成功提交。kill验证

alter system kill session '74 ,839';

SQL> alter system kill session '74,839';

System altered.

SQL>

第二个窗口：

SQL> update a set id=3 where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL>

第一个窗口:

SQL> /

update a set id=1 where id=2

*

ERROR at line 1:

ORA-00028: your session has been killed

SQL>

成功kill源头，完美解决。哈哈哈，下午没白搞

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