Re: Fw: "enq: TX - index contention" wait during RAC Benchmark ?
- From: "Rajeev Prabhakar" <rprabha01@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Feb 2007 15:58:31 -0500
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:
