Re: 'enq: TX - row lock contention' and 10046 trace

From: kyle Hailey <kylelf_at_gmail.com>
Date: Thu, 29 Nov 2012 10:51:33 -0800
Message-ID: <CADsdiQgeS+oZy8AxT6DDOaddGt4m+OPyEiq5xw=L5HZGy5Z23w_at_mail.gmail.com>



A timeline tool would be cool.
For this situation why do you want a timeline?

If the locks are all on indexes, likely they are either unique key, foreign key or bitmap issues.

You can query ASH and get an idea.

select

       substr(event,0,20)                  lock_name,
       ash.session_id                      waiter,
       mod(ash.p1,16)                     lmode,
       ash.p2                                   p2,
       ash.p3                                   p3,
       o.object_name                      object,
       o.object_type                        otype,
       CURRENT_FILE#                filen,
       CURRENT_BLOCK#           blockn,
       ash.SQL_ID                          waiting_sql,
       BLOCKING_SESSION         blocker
       --,ash.xid
from
         v$active_session_history ash,
         all_objects o
where
           event like 'enq: %'

   and o.object_id (+)= ash.CURRENT_OBJ# /

Here is what the three different cases looked like on my system: uniq index

ST    EVENT                  SID  LM     P2   P3 OBJ   OTYPE  FN BLOCKN
SQL_ID         BSID
----- ---------------------- --- --- ------ ---- ----- ----- --- ------ --
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0
bjvx94vnxtxgv   158
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0
bjvx94vnxtxgv   158
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0
bjvx94vnxtxgv   158
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0
bjvx94vnxtxgv 158

FK

ST    EVENT                  SID  LM     P2   P3 OBJ   OTYPE  FN BLOCKN
SQL_ID         BSID
----- ---------------------- --- --- ------ ---- ----- ----- --- ------ --
10:41 enq: TX - row lock c 144 4 179681 7074 CHILD TABLE 1 60954 ahm7c9rupbz9r 1
10:41 enq: TX - row lock c 144 4 179681 7074 CHILD TABLE 1 60954 ahm7c9rupbz9r 1
10:41 enq: TX - row lock c 144 4 179681 7074 CHILD TABLE 1 60954 ahm7c9rupbz9r 1

bitmap

ST    EVENT                  SID  LM     P2   P3 OBJ   OTYPE  FN BLOCKN
SQL_ID         BSID
----- ---------------------- --- --- ------ ---- ----- ----- --- ------ --
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0
azav296xxqcjx   144
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0
azav296xxqcjx   144
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0
azav296xxqcjx   144
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0
azav296xxqcjx 144

On Thu, Nov 29, 2012 at 8:27 AM, Rich <richa03_at_gmail.com> wrote:

> Hi List,
> This is 10.2.0.4 on Solaris 10.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 29 2012 - 19:51:33 CET

Original text of this message