Re: TX - index contention

I haven't got around to testing it yet, 
but I think your guess about index
block splits is correct.

If you check v$segstat and v$segstat_name
you will find that they depend on x$ksolsfts
and x$ksolsstat respectively, and miss out
a couple of rows.  One of these is called
    ITL service waits.

If you query x$ksolsstat directly for the
indexes under pressure, my guess is that
you would see waits reported against this
wait type.

I don't think it should help, but you could 
try increasing initrans on the indexes.

Is the data in your test realistic, or is there
some pattern to it that makes the test an
extreme case ? If the latter, it is possible 
that reversing the indexes may help.


Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






----- Original Message ----- 
From: "Stalin" <stalinsk@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 22, 2005 2:47 AM
Subject: enq: TX - index contention


We have been doing application load testing lately and I found enq TX
index contention being the top contender among other waits. The table
with 5 indexes, all it's index columns gets updated via an update for
every id in that table by no. of concurrent users defined by the load
testing scripts. 10046 trace had lot's of similar lines with varrying
p2 and p3 values.

WAIT #1: nam='enq: TX - index contention' ela= 3001942 p1=1415053316
p2=65547 p3=816

How can i overcome this wait without amending application behavior.

Is this caused by index splits ? 

Thanks
Stalin

10g(10.1.0.3)/solaris
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: