enq: tx - contention on selects

[stephen O'D]

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.

[stephen O'D]

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?

[gazzag]

> 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

[Mark D Powell]

> 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 --

[stephen O'D]

Mark,

Thanks for the reply

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

These are just plan selects (ie NO for update clause), but they are
part of distributed transactions. At any time, there may be about 40
of these distributed transactions occurring at once (which isn't
really a massive number). We are using undo tablespace (as opposed to
Rollback segments) - are there a limited number of slots available for
undo? Could this TX - Contention wait be caused by a transaction
attempting to get a slot of undo?

>
> 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.

I know we have no bitmap indexes in the application and as the waits
are against a select, I am still confused as to why I am seeing the TX
Contention at all, unless its something to do with undo.

>
> 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.

I don't think this is the case, but I will check for sure.

Thanks,

Stephen.

[sri...@hotmail.com]

selects across db links can wait for TX locks (mode 4).
This is the only instance in oracle where a select can be locked.

Since your problem is frequent, this could be a flaky network causing
a lot of distributed transactions to go into a pending state.
Once a transaction goes into a pending state, other sessions trying to
access that data may wait with TX mode 4.
You can monitor DBA_2PC_PENDING & DBA_2PC_NEIGHBORS.
regards
srivenu

[Jonathan Lewis]

For more notes on Srivenu's comment, here's a note I wrote
a few years back. Oracle changed the handling of distibributed
transactions around 9.2.0.5 to deal with this issue in the case
of XA processing, but the issue still exists for pure Oracle activity
across database links.

Your select can wait for another transaction if that transaction
is between the "prepare" and "commit" of a two-phase commit.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

[The Boss]

Jonathan Lewis wrote:
> For more notes on Srivenu's comment, here's a note I wrote
> a few years back. Oracle changed the handling of distibributed
> transactions around 9.2.0.5 to deal with this issue in the case
> of XA processing, but the issue still exists for pure Oracle activity
> across database links.
>
> Your select can wait for another transaction if that transaction
> is between the "prepare" and "commit" of a two-phase commit.

You forgot to include the note, here's the "missing link":

http://www.jlcomp.demon.co.uk/02_tpm.rtf

Cheers!

--
Jeroen