Re: "enq: TX - index contention" wait during RAC Benchmark ?

Based on the symptoms presented it sounds like you may have right growing index leaf block contention. This is generally the result of using an increasing sequence for a primary key. A right growing index will prevent your application from scaling in a single instance database, let alone a clustered database (RAC) environment where its impact on scaling is exacerbated.

To speak to your questions:
Q2: There are two sections in the AWR report - "Segments by Buffer Busy Waits" and "Segments by Global Cache Buffer Busy Waits". This will probably have the index in question listed.

The ASH report should also contain this info as well. $ORACLE_HOME/rdbms/admin/ashrpt.sql
Alternatively you can look at v$active_session_history.

Q1: As I first mentioned, index contention is generally the result of bad application design and/or implementation. As an performance engineer, I would suggest to fix the application by altering its design or implementation. This may be a app code change or a combination app/database design change.

If the issue is as "simple" as the sequence issue mentioned above, the solution could be as "easy" as making two sequences - and creating a node affinity with them. Make one an increasing sequence starting at 1, and the other a decreasing sequence starting at 999999999999 (or other large number such that the keys will not collide). This will accomplish two things: 1) eliminate the right growing index contention, and 2) create an affinity of the index leaf block nodes such that there should be no "sharing" of blocks - one node will insert on the left half of the tree, and the other the right.

An alternative implementation would be to append the inst_id to the key in the application and use the same sequence. It will have the same effect. (This would also assume there is a node affinity at the application layer).

Having done many a benchmark myself, I would also suggest to analyze your benchmark implementation. Often times I have seen similar "keys" issues be a result of Loadrunner or other driver using a incremental counter for input into the application. This essentially has the same result as the sequence issue, but is caused by the data input, not by the application directly. Be mindful of creating such situations with synthetic benchmarks.

Regards,

Greg Rahn
http://structureddata.org

• Original Message -------- Subject: "enq: TX - index contention" wait during RAC Benchmark ? From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infosys.com> To: oracle-l_at_freelists.org Date: 2/28/2007 7:24 AM
  >
  > Folks
  >
  >
  >
  > During internal 2 Node RAC Benchmark OLTP Nature Transaction Runs,
  > following are the TOP Events from 2 Statspack.
  >
  > * *
  >
  > *Qs 1) How can the "enq: TX - index contention" wait be reduced?*
  >
  > *Qs 2) How to identify the index undergoing contention? Do
  > v$session_wait.p1, p2 identify the index/Object?*
  >
  >
  >
  > Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
  >
  > ~~~~~~~~ ---------- ------------------ -------- ---------
  > -------------------
  >
  > Begin Snap: 124 28-Feb-07 17:53:32 99 32.3
  >
  > End Snap: 125 28-Feb-07 18:18:08 126 27.1
  >
  > Elapsed: 24.60 (mins)
  >
  >
  >
  > Top 5 Timed Events
  > Avg %Total
  >
  > ~~~~~~~~~~~~~~~~~~
  > wait Call
  >
  > Event Waits Time (s)
  > (ms) Time
  >
  > ----------------------------------------- ------------ -----------
  > ------ ------
  >
  > gc buffer busy 625,165 11,067
  > 18 50.0
  >
  > enq: TX - index contention 70,130 3,954
  > 56 17.8
  >
  > CPU time
  > 2,197 9.9
  >
  > row cache lock 115,227 1,389
  > 12 6.3
  >
  > gc current block 2-way 923,272
  > 906 1 4.1
  >
  > -------------------------------------------------------------
  >
  >
  >
  > *Configuration*
  >
  > 10gR2
  >
  > Solaris 10
  >
  > Veritas CFS 5.0
  >
  > Banking Product
  >
  >
  >
  > Will Share Statspack etc with anybody
  >
  >
  >
  > Cheers
  >
  

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