Re: enq: tx - contention on selects

On 16 Sep, 12:48, "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 -

"tim" is the timestamp in microseconds, I believe.

HTH -g Received on Tue Sep 16 2008 - 08:11:06 CDT