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_at_gmail.com> To: <oracle-l_at_freelists.org> 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 Received on Mon Mar 21 2005 - 23:30:52 CST