TX - row lock contention enq

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 7 Apr 2009 16:07:42 -0500
Message-ID: <effc058d0904071407p3bdc2632t11faeb6421c04b1_at_mail.gmail.com>

Hello Listers,

This morning one of the processes that runs regularly was much slower than usual. I was checking into it. I ran a query related to lock during the half hour interval and I got outputs like below:

---

SELECT session_id

  2  ,  lock_type
  3  ,  mode_held
  4  ,  mode_requested
  5  ,  blocking_others
  6  ,  lock_id1
  7  FROM       dba_lock l
  8  WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread')

  9 /

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1

---------- ------------ ---------- ---------- --------------------
----------------------------------------
       980 Transaction  Exclusive  None       Not Blocking         786455
       980 DML          Row-X (SX) None       Not Blocking         225989
      1017 Transaction  Exclusive  None       Not Blocking         655361
      1017 DML          Row-X (SX) None       Not Blocking         225984
      1030 DML          Row-X (SX) None       Not Blocking         833361
      1053 Transaction  Exclusive  None       Not Blocking         196653
      1053 DML          Row-X (SX) None       Not Blocking         225984
      1070 Transaction  Exclusive  None       Not Blocking         131074
      1070 DML          Row-X (SX) None       Not Blocking         225989
      1085 CT           Exclusive  None       Not Blocking         2
      1099 Temp Segment Row-X (SX) None       Not Blocking         6
      1100 RS           Row-S (SS) None       Not Blocking         25
      1100 Control File Row-S (SS) None       Not Blocking         0
      1100 XR           Null       None       Not Blocking         4

14 rows selected.

SQL>
SQL> / SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1

---------- ------------ ---------- ---------- --------------------
----------------------------------------
       980 Transaction  Exclusive  None       Not Blocking         786455
       980 DML          Row-X (SX) None       Not Blocking         225989
      1017 Transaction  Exclusive  None       Not Blocking         655361
      1017 DML          Row-X (SX) None       Not Blocking         225984
      1053 Transaction  Exclusive  None       Not Blocking         196653
      1053 DML          Row-X (SX) None       Not Blocking         225984
      1070 Transaction  Exclusive  None       Not Blocking         131074
      1070 DML          Row-X (SX) None       Not Blocking         225989
      1085 CT           Exclusive  None       Not Blocking         2
      1099 Temp Segment Row-X (SX) None       Not Blocking         6
      1100 RS           Row-S (SS) None       Not Blocking         25
      1100 Control File Row-S (SS) None       Not Blocking         0
      1100 XR           Null       None       Not Blocking         4

13 rows selected.

SQL>
SQL> / SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1

---------- ------------ ---------- ---------- --------------------
----------------------------------------
       936 Transaction  Exclusive  None       Not Blocking         458782
       936 DML          Row-X (SX) None       Not Blocking         490339
       936 DML          Row-X (SX) None       Not Blocking         229801
      1085 CT           Exclusive  None       Not Blocking         2
      1099 Temp Segment Row-X (SX) None       Not Blocking         6
      1100 RS           Row-S (SS) None       Not Blocking         25
      1100 Control File Row-S (SS) None       Not Blocking         0
      1100 XR           Null       None       Not Blocking         4

8 rows selected.

---

 I got the object names using LOCK_ID1 based on the query. Based on the results returned I could not come to any conclusion. I also generated statspack report and looked into it:

---

                 Snap Id      Snap Time      Sessions Curs/Sess
                       --------- ------------------- -------- ---------
Begin Snap:     22738 07-Apr-09 10:30:56       160       4.0
  End Snap:     22739 07-Apr-09 11:00:04       166       4.5
   Elapsed:               29.12 (mins)
   DB Time:              125.69 (mins)

......
.....

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait

Class

------------------------------ ------------ ----------- ------ ------
----------
enq: TX - row lock contention         1,294       3,703   2862   49.1
Applicatio
CPU time                                     1,856          24.6
db file sequential read             328,221       1,253      4   16.6   User
I/O
direct path read                    138,770         583      4    7.7   User
I/O
db file scattered read                8,885          71      8    0.9   User

I/O
....

---

Usually the remaining four show up in the top 5. This time I see TX row lock contention which conforms with what Grid control was showing; it showed wait on application for the SQLs running.

Is there a way to find out which process or user was blocking and what was being waited on.

ver 10.202.

Thanks.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 07 2009 - 16:07:42 CDT