From: helter skelter on 21 Jun 2010 09:51 Hi, I've got strange problem with my db. It happend two times lately - many DML session has hung on "TX-index contention" waiting for the session which is hanging on "DFS handle lock" - with no blocker of course. Then like an avalanche...in few minutes almost all DML were hanging on dfs lock handle or TX-index contention. I tried flush buffer cache (it was working in similar problems which I had once) but it also hung on 'dfs lock handle'. When I kill session with dfs lock, another session start to wait on this event... No errors in alert log (db&asm), no traces, but I have some info from oradebug: [343]/0/344/50328/0x50f72138/28801/IGN_DMP/25/28/[771]/none .... [604]/0/605/23753/0x4e19c618/26949/IGN/411/412/[771]/none [645]/0/646/1033/0x4e1d33c0/27988/NLEAF/467/470/[1095]/none [1095]/0/1096/1/0x4e42d490/23833/LEAF/468/469//645 [771]/0/772/246/0x4e27bcf0/26727/LEAF/26/27//343 I see that many sessions are waiting for [771] which isn't waiting for anything. Found 44 objects waiting for <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> <0/772/246/0x50c40598/26727/DFS lock handle> Open chains found: Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/772/246/0x50c40598/26727/DFS lock handle> -- <0/344/50328/0x50c73bd8/28801/enq: TX - index contention> ......... ......... but I can't see any traces for PID 26727. any ideas ? rac database 10.2.0.4, asm&clusterware 11.2.0.1, rhel 5 cheers
From: Jonathan Lewis on 21 Jun 2010 10:45
I take it you're running RAC. "TX-Index contention" usually relates to a delay waiting for an index leaf block split. And "DFS lock handle" can be related to sequences (under RAC) which have been declared with the ORDER attribute set. I'd be looking for other symptoms relating to primary keys based on sequences - with all those other sessions either trying to get the nextval or trying to insert a value into the same (or adjacent) leaf block as the first "TX - index contention" session trys to do a leaf block split. I'd also be looking for a session that was trying to do a "nextval" and getting nowhere. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com "helter skelter" <helterskelter(a)gmail.com> wrote in message news:hvnqt9$1ql$1(a)mx1.internetia.pl... > Hi, > > I've got strange problem with my db. > It happend two times lately - many DML session has hung on "TX-index > contention" waiting for the session which is hanging on "DFS handle > lock" - with no blocker of course. Then like an avalanche...in few > minutes almost all DML were hanging on dfs lock handle or TX-index > contention. > I tried flush buffer cache (it was working in similar problems which I > had once) but it also hung on 'dfs lock handle'. > When I kill session with dfs lock, another session start to wait on this > event... > No errors in alert log (db&asm), no traces, but I have some info from > oradebug: > > [343]/0/344/50328/0x50f72138/28801/IGN_DMP/25/28/[771]/none > ... > [604]/0/605/23753/0x4e19c618/26949/IGN/411/412/[771]/none > > [645]/0/646/1033/0x4e1d33c0/27988/NLEAF/467/470/[1095]/none > > [1095]/0/1096/1/0x4e42d490/23833/LEAF/468/469//645 > [771]/0/772/246/0x4e27bcf0/26727/LEAF/26/27//343 > > I see that many sessions are waiting for [771] which isn't waiting for > anything. > > Found 44 objects waiting for > <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> > <0/772/246/0x50c40598/26727/DFS lock handle> > Open chains found: > Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : > <0/772/246/0x50c40598/26727/DFS lock handle> > -- <0/344/50328/0x50c73bd8/28801/enq: TX - index contention> > ........ > ........ > > but I can't see any traces for PID 26727. > any ideas ? > > > rac database 10.2.0.4, asm&clusterware 11.2.0.1, rhel 5 > > > cheers > > |