Oracle

March 6, 2012

Blocking_Instance and Blocking_Session values in GV$SESSION could be wrong

Filed under: Uncategorized — srivenu @ 10:26 am

Recently in one of our RAC systems (64bit – 10.2.0.4.0 on AIX 5.3), I observed several sessions waiting on “enq: TX – row lock contention”.
The waiters locked out at the following update


UPDATE 	CUST
SET 	UPDATED_DATE = :1 
WHERE 	CUSTID = :2

I tried to find out the enqueue holders


select 	blocking_instance, blocking_session, inst_id, sid
from 	gv$session 
where 	event = 'enq: TX - row lock contention'
order	by 1, 2, 3, 4
/


                                     Inst
BLOCKING_INSTANCE BLOCKING_SESSION   ance    Sid
----------------- ---------------- ------ ------
                1              489      1    962
                1              489      3    642
                1              840      3    777
                1              875      1    953
                1              875      3    883
                1              986      3    928
                1              986      3    971
                1             1042      1    821
                1             1079      3    533

                2              495      2    762
                2              495      3    729
                2             1079      3    604

                4              841      4    733
                4              887      4   1005
                4              968      3    916
                4             1029      3    581

Sid 1079 on Instance 1 is one of the enqueue holders.

And following are the details of sid 1079 on instance 1


col sid head "Sid" form 99999
col pid head "PID" form 9999
col serial# form 99999 head "Ser#"
col username head "DB User" form a8
col program head "Program" form a10
col module head "Module" form a10
col action head "Client|Action" form a12
col machine head "Client|Machine" form a15
col cprocess head "Client|Process|ID" form a9
col osuser head "Client|OS User" form a7 
col spid head 'Oracle|Back|ground|Process|ID' form a8


select 	s.sid, s.serial#, p.pid, s.process cprocess, s.osuser osuser,
	s.program, s.module, s.action, s.machine machine, s.username username, p.spid
from 	v$session s,
	v$process p
where 	s.paddr(+)=p.addr
and	sid = 1079
order 	by 1
/

                                                                                                  Oracle
                                                                                                  Back
                    Client                                                                        ground  
                    Process   Client                        Client       Client                   Process 
   Sid   Ser#   PID ID        OS User Program    Module     Action       Machine         DB User  ID      
------ ------ ----- --------- ------- ---------- ---------- ------------ --------------- -------- --------
  1079      1    18 13541454  oracle  oracle@abc                         abcora1racprod           13541454
                                      def1racpro
                                      d (DBW0)

The holder is the DBW0 process on instance 1
Why does a DBW0 process hold up a foreground session on a TX enqueue on a user table?
Having experienced this issue several times in the past, i decided to put in a small note to warn others not to take the Blocking_Instance and Blocking_Session values from GV$SESSION view at face value.

I normally use the following sql to find out the holders of “enq: TX – row lock contention”


col wi head "Wai|ti|ng|In|St|an|ce" form 9999
col wu head "Waiting|User" form a10
col wsid head "Waiting|Sid" form 99999
col wser head "Waiting|Ser#" form 99999

col hi head "Ho|ld|ing|In|St|an|ce" form 9999
col hu head "Holding|User" form a10
col hsid head "Holding|Sid" form 99999
col hser head "Holding|Ser#" form 99999

select  hs.inst_id hi, hs.username hu, hs.sid hsid, hs.SERIAL# hser, 
	ws.inst_id wi, ws.username wu, ws.sid wsid, ws.SERIAL# wser, 
	t.xidusn, t.xidslot, t.xidsqn, t.status
from 	gv$session ws, gv$session hs, gv$transaction t
where 	hs.taddr = t.addr
and	hs.inst_id = t.inst_id
and	t.xidusn = trunc(ws.p2/65536)
and	t.xidslot = mod(ws.p2,65536)
and	t.xidsqn = ws.p3
and	ws.event like 'enq: TX%'
order 	by hs.inst_id, hs.sid, hs.SERIAL#, ws.inst_id, ws.sid
/

The output is something like this



   Ho                              Wai
   ld                               ti
  ing                               ng
   In                               In
   St                               St                             undo    undo     undo
   an Holding    Holding Holding    an Waiting    Waiting Waiting   Seg    Slot      Seq
   ce User           Sid    Ser#    ce User           Sid    Ser#    No      No       No Status
----- ---------- ------- ------- ----- ---------- ------- ------- ----- ------- -------- ------
    1 ABCDEFGH       489   16589     1 ABCDEFGH       962    7535     5      27   394378 ACTIVE
    1 ABCDEFGH       489   16589     2 ABCDEFGH       423     365     5      27   394378 ACTIVE
    1 ABCDEFGH       840   61100     2 ABCDEFGH       580    1346    97      37    12608 ACTIVE
    1 ABCDEFGH       875   64492     1 ABCDEFGH       953   40122    56      43    87618 ACTIVE
    1 ABCDEFGH       875   64492     2 ABCDEFGH       726   20828    56      43    87618 ACTIVE
    1 ABCDEFGH       967   47330     2 ABCDEFGH       815    3000    55      42   116791 ACTIVE
    1 ABCDEFGH       986   60240     2 ABCDEFGH       764   54442    71       8   212341 ACTIVE
    1 ABCDEFGH       986   60240     2 ABCDEFGH      1017    4489    71       8   212341 ACTIVE
    1 ABCDEFGH      1042   42742     1 ABCDEFGH       821   42845    58       0   100629 ACTIVE

    2 ABCDEFGH       425   14272     1 ABCDEFGH      1028   42640   113      14   291960 ACTIVE
    2 ABCDEFGH       425   14272     4 ABCDEFGH       717   20891   113      14   291960 ACTIVE
    2 ABCDEFGH       495    8828     1 ABCDEFGH       458   28807   195      40    55394 ACTIVE
    2 ABCDEFGH       495    8828     2 ABCDEFGH       762   39933   195      40    55394 ACTIVE
    2 ABCDEFGH       706   54438     1 ABCDEFGH       960   24251   209      18     7352 ACTIVE
    2 ABCDEFGH       872   55363     4 ABCDEFGH       887   19939   212      30    21108 ACTIVE
    2 ABCDEFGH       893   60900     1 ABCDEFGH       843   38790   240      33     2689 ACTIVE

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: