Re: TX Enqueue | what to do?

There are several causes of TX enqueue waits. The ITL issue that you describe comes about no. 4 in order of probability on the list, and is a fairly exotic occurrence.

The commonest cause is that you literally have two sessions trying to lock the same row.

Following on from that is the possibility that you have some foreign key indexes missing that you happen to need to support the
DML activity (particularly full row updates, or deletes at the parent end of a pk/fk) on your application.

There are other reasons, but ITL contention is a bit unlikely, and a quick and dirty fix for that is simply to set the INITRANS on the relevant objects to be larger than the number of concurrent transactions that could reasonably be expected to hit one block at any one time.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

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


"Kenneth A Kauffman" <kkauffman_at_nospam.headfog.com> wrote in message
news:zwupa.344300$0g4.9333286_at_news2.east.cox.net...


> My understanding of TX Enqueue is that it is a transaction level

wait state.


> Meaning, that within an 8k block, there may be several rows of data.

When


> one of those rows within the block is marked as locked for an oracle

> transaction, other rows are subsequently blocked from access.  This

is the


> condition of a TX Enqueue wait state.  Additional understanding

leads me to


> believe that if I have this symptom occur frequently, I would need

to adjust


> my block size to something smaller to avoid transaction contention.

Since


> Oracle 9i supports multiple block sizes at a table level, this is

not a big


> deal to do a rebuild targeting the tables in question.

>

> Here is the quesitons:

>

> 1) Would Oracle partitioning help me in this case?  It would seem to

me less


> likely if its adjacent data within the same block.

>

> 2) Is redesigning the application to use optomistic locking my only

other


> choice?  There is a lot of code time involved in doing this and also

doesn't


> guarantee the integrity as well as pessimistic locking.

>

> 3) How do I target which blocks are the "offenders" in this

condition?


>

> I see TX Enqueues as my top wait event within a set of statspack

reports


> during a LoadRunner test. If anyone has any further clarification on

this


> point or if any of my understanding is inaccurate, please do tell.

:)


>

> ken k

>

>