US Enqueue , latch row object cache - performance issue

   one of our 9.2.0.8 EE databse suffered from US enqueue contention. Scenario was:
1. NOSPACEERRCNT in v$undostat suddenly showed 200 with high UNX% EXP% columns values (10 000 - 20 000)
2. v$session_wait showed about 30 session with enqueue US type waits and about 22 latch waits (type row cache object )

3. there was some SQ enqueue waits as well but I've increased cache value for that sequence so problem was solved

4. v$transaction showed about 100 small (few undo blocks transactions)

My question is how to drill down such issue? How 100 transactions could cause contension in 20GB undo tablespace and lead to huge performance degradation?

Can an uncommited transaction via dblink with delete on 70MB table (only 1 row satisfied via FTS on that table) and many quick commited delete statements on the same table (but different row) made such issue ?

How to drill down row cache object latch (how to find which object is it)?

Best regards.
Greg Received on Tue Aug 11 2009 - 11:35:24 CDT