Enqueue dump and a clarification

From: Vivek <Vivek_at_1800FLOWERS.com>
Date: Mon, 4 Dec 2000 09:32:45 -0500
Message-Id: <10700.123524@fatcity.com>

Hi:

  I know some of my friends in this group think that dumps and trying to read them is a waste of time. For others I have a question. I had an enqueue situation in my place. I can find and did find the info in the v$session_wait and all the related views to findout that the enqueue was a TX, the Rollback segment number and Slot number etc. 2 sessions were waiting on TX for a shared lock. I thought will do an enqueue dump. This is the snippet of the enqueue dump.

c1d3385c TX-008b003c-00004184 U 0 0 0 0 0 1 64
[c1fb3b64,c1fb3b64]

         [bbd62074,bbd62074] [c1d33874,c1d33874] [c1d2df4c,c1d2ded4]
   lock     que session        resource hold wait ser link
   ----------------------------------------------------------------------
   bbd6206c OWN c0621494 (-1067314028)      34e    X NLCK 14312


[c1d33864,c1d33864]

   c1d2df44 WAT c050fb14 (-1068434668)      14b NLCK    S 11903


[c1d2ded4,c1d3386c]

   c1d2decc WAT c0568f14 (-1068069100)      1f3 NLCK    S 8599

[c1d3386c,c1d2df4c]

When I go and see in the v$session wait the sessions that are waiting are in TX - Level 4. Usually its for an ITL (Most of the times). My question is those which have a WAT status in the above dump are they waiting for the ITL slots (TX lock with Status S). If it were waiting with a Status of X then its waiting for a specific row and its a row lock. The parameter points to Rollback segment 139 wrap# 60. When I dumped the value in it I found that its a undo header. So does this mean that the ITL slots got full in a block and a transaction was chosen randomly and these transactions are waiting on it? Because here I see 2 transactions waiting on the Rollback segment slot (331,499)

Any help is appreciated. Received on Mon Dec 04 2000 - 08:32:45 CST