Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fw: "enq: TX - index contention" wait during RAC Benchmark ?

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

From: Rajeev Prabhakar <rprabha01_at_gmail.com>
Date: Wed, 28 Feb 2007 15:58:31 -0500
Message-ID: <2ba656800702281258i6c05afbdrb0519250af05b619@mail.gmail.com>


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_at_yahoo.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 28 2007 - 14:58:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US