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

  • From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 28 Feb 2007 13:17:51 -0800

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@xxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2007