enq: TX - row lock contention“等待事件的处理(二)
0 00
0 00 2723168908 6 Idle
0 661 WAITING
发现enq: TX - row lock contention
sql>select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock
where block=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME
BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
----------
135 TX 131073 1761 0 6 1840
0
136 TX 131073 1761 0 6 198
0
138 TX 131073 1761 6 0 1852
1
---查看sid 138 在做什么
SQL> select sid,event,SQL_ADDRESS,SQL_ID from v$session where
sid=138;
SID EVENT
----------
----------------------------------------------------------------
SQL_ADDRESS SQL_ID
---------------- -------------
138 SQL*Net message from client
00
去看看具体等待什么吧
SQL> select sid,event,SQL_ADDRESS,SQL_ID from v$session
where sid in (7879,8167);
SID EVENT SQL_ADDRESS
SQL_ID
----- --------------------------------------------------
---------------- -------------
7879 enq: TX - row lock contention
C000000CCE8A76F0 08qruv04r2v6d
8167 enq: TX - row lock contention
C000000CCE8A76F0 08qruv04r2v6d
看看sql语句是什么
SQL> select SQL_ID,SQL_TEXT from v$sql
2* where sql_id = '08qruv04r2v6d'
-----查看等待的对象
SQL> SELECT owner, object_name, subobject_name, value
2 FROM v$segment_statistics
3 WHERE statistic_name='row lock waits'
4 AND value > 0
5 ORDER BY 4 DESC;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME VALUE
------------------------------ ----------
SCOTT TX_EG
方法二：
SQL> SELECT row_wait_obj#,
2 row_wait_file#,
3 row_wait_block#,
4 row_wait_row#
5 FROM v$session
6 WHERE sid=135;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
54999 4 485 0
SQL> select object_name from dba_objects where object_id in (54999);
OBJECT_NAME
---------------------------------------------------------------------
-----------
TX_EG
SQL> select sid,serial# from v$session where sid=5364;
SID SERIAL#
----- ----------
5364 24841
--杀会话
alter system kill session '5364,24841';
-------查找对应的操作
系统进程号：
SQL> select paddr from v$session where sid= 5364;
PADDR
----------------
C000000D2194FCD0
SQL> select spid from v$process where addr='C000000D2194FCD0';
SPID
------------------------
19669
整合为一句：
Select spid from v$process where addr in(select paddr from v$session
where sid= xxxx;)
SQL> ! ps -ef | grep 19669
orapp0 19669 1 0 Jul 20 0:03 oraclePP0
(DESCRIPTION=(LOCAL=NO)(SDU=32767))
和客户应用管理人员确认后，执行alter system kill session '5364,24841';
命令没能杀掉该会话，只好在操作系统层面通过kill -9 19669杀掉进程，结束会话