Undo Latch Contention

Here is the SQL to find sessions causing UNDO Latch Contention:

select

   swh.seq#,

   sess.sid,

   sess.username username,

   swh.event     event,

   swh.p1,

   swh.p2

from

   v$session               sess,

   v$session_wait_history  swh

where

   sess.sid = 798

and

   sess.sid = swh.sid

order by

   swh.seq#;

Sample Output:

     SEQ#     SID      USERNAME        EVENT                                                  P1                      P2
---------- -------    --------------- ------------------------------------   --------------       ----------
         1        798     ORCL               latch free                                              5.0440E+17        127
         2        798     ORCL               latch free                                              5.0440E+17        127
         3        798     ORCL               latch free                                              5.0440E+17        127
         4        798     ORCL               latch free                                              5.0440E+17        127
         5        798     ORCL               latch: cache buffers chains           5.0440E+17        122
   


5 rows selected.