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

Vivek,

You could modify the below mentioned query per your
needs to identify index objects experiencing contention.

Regards
Rajeev

------------cut here ----------------cut here
--------------------------------
select do.object_name,
      do.object_type,
      ash.event,
      sum(ash.wait_time +
      ash.time_waited) ttl_wait_time
from v$active_session_history ash, dba_objects do
where ash.sample_time between sysdate - 60/2880 and sysdate
and ash.current_obj# = do.object_id
and ash.event like '%enq: TX - index contention%'
group by do.object_name, do.object_type,
ash.event
order by 4
------------cut here ----------------cut here
--------------------------------

On 2/28/07, Nigel Thomas <nigel_cl_thomas@xxxxxxxxx> wrote:


  (copy to list)



*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?*


Vivek

*Q2*: do you have the excellent *Oracle Wait Interface: A Practical Guide
to Performance Diagnostics & Tuning **
http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/007222729X
*<http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/007222729X>
* *which would certainly help you at the moment (It helped me last year -
belated thanks to the authors)

To simplify things you can also use statspack (level 7 IIRC) to find most
'contentious' segments across the instance (or RAC). For more information
about the wait itself see this thread:
http://freelists-180.iquest.net/archives/oracle-l/03-2005/msg00778.html "enq:
TX - index contention"

And don't forget the docs:
http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10752/instance_tune.htm#18211
 -
search for index contention.

Regards Nigel

Other related posts: