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?
and o.object_id (+)= ash.CURRENT_OBJ# /
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
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 0bjvx94vnxtxgv 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 0azav296xxqcjx 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-lReceived on Thu Nov 29 2012 - 19:51:33 CET