Re: enq: tx - contention on selects

On Sep 16, 7:48 am, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
> > Oracle 10G R2 on HPUX 64 bit.
>
> > I see a lot of occurrences in our application waiting on the event
> > enq: TX - contention.
>
> > What is puzzling me, is that these waits are all generally on select
> > statements (at least accoring to TK Prof).  If I grep through the
> > trace file I see waits like the one below for instance:
>
> > WAIT #33: nam='enq: TX - contention' ela= 619813 name|mode=1415053316
> > usn<<16 | slot=5832740 sequence=570213 obj#=33133100 tim=6991129188755
>
> > Could any one offer me any advice on what each of the numbers in this
> > line mean, ie how do I decode name|mode, slot, sequence, obj# into
> > something meaningful so I can get to the bottom of what is causing
> > these waits?
>
> > Thanks,
>
> > Stephen.
>
> I have managed to work some of this stuff out.  The name|mode is
> decimal, so you convert to hex:
>
> 1415053316 -> 54580004
>
> The first two bytes give the type of Enq and the final number gives
> the lock mode.
>
> In this case 54 = T and 58 = X, give TX, and the 4 means its a share
> lock.
>
> obj# allows you to look up the object name on sys.obj$
>
> So the remaining question is what do the other parameters mean - are
> they something to do with the undo segment in use by the transaction?
>
> When I see this sort of wait, the system is under pretty heavy load -
> could this problem be related to insufficient ITL slots in the undo
> segments or objects?  I am not sure if that even makes any sense, as I
> didn't think a select required an ITL slot?- Hide quoted text -
>
> - Show quoted text -

A select for update would require an ITL slot. Distributed transaction also take RBS entries.

A share mode of 4 usually results from no free ITL slot available, a unique key insert, or a wait to update a bitmap index.

Also verify that the select is not going against data affected by updates to the partent in a FK relationship where no index exists to support the FK.

HTH -- Mark D Powell -- Received on Tue Sep 16 2008 - 08:44:49 CDT