Re: 'enq: TX - row lock contention' and 10046 trace
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: richa03@xxxxxxxxx
- Date: Thu, 29 Nov 2012 10:51:33 -0800
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@xxxxxxxxx> wrote:
> Hi List,
> This is 10.2.0.4 on Solaris 10.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
