Re: enq: tx - contention on selects

> 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? Received on Tue Sep 16 2008 - 06:48:24 CDT