latch:transaction allocation

今天遇到一个sql，发生了大量的134的latch，134的latch是transaction allocation。数据库版本是9208.

该sql是：

SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,
       s.SQL_HASH_VALUE,
       l.id1,
       l.id2,
       l.lmode,
       l.request,
       l.type
  FROM V$LOCK l, v$session s
WHERE (l.id1, l.id2, l.type) IN
       (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   and l.SID = s.SID
   and exists (select sw.seq#,
               sw.sid || ',' || s.serial# sids,
               s.username,
               s.SQL_HASH_VALUE
          from v$session_wait sw, v$session s, dba_objects a
         where sw.sid = s.sid
           and sw.event in ('latch free', 'enqueue')
           and s.status = 'ACTIVE'
           and sw.STATE = 'WAITING'
           and s.username is not null
           and sw.seconds_in_wait > 30
           and (a.object_id = s.row_wait_obj# or
               a.data_object_id = s.row_wait_obj#))
ORDER BY l.id1, l.request;

SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,

s.SQL_HASH_VALUE,

l.id1,

l.id2,

l.lmode,

l.request,

l.type

FROM V$LOCK l, v$session s

WHERE (l.id1, l.id2, l.type) IN

(SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

and l.SID = s.SID

and exists (select sw.seq#,

sw.sid || ',' || s.serial# sids,

s.username,

s.SQL_HASH_VALUE

from v$session_wait sw, v$session s, dba_objects a

where sw.sid = s.sid

and sw.event in ('latch free', 'enqueue')

and s.status = 'ACTIVE'

and sw.STATE = 'WAITING'

and s.username is not null

and sw.seconds_in_wait > 30

and (a.object_id = s.row_wait_obj# or

a.data_object_id = s.row_wait_obj#))

ORDER BY l.id1, l.request;

据客户反映，这个sql之前没有出现问题，只是昨天做了修改，增加了对v$session表的关联。昨天的sql：

SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,
       s.SQL_HASH_VALUE,
       l.id1,
       l.id2,
       l.lmode,
       l.request,
       l.type
  FROM V$LOCK l
WHERE (l.id1, l.id2, l.type) IN
       (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   and exists (select sw.seq#,
               sw.sid || ',' || s.serial# sids,
               s.username,
               s.SQL_HASH_VALUE
          from v$session_wait sw, v$session s, dba_objects a
         where sw.sid = s.sid
           and sw.event in ('latch free', 'enqueue')
           and s.status = 'ACTIVE'
           and sw.STATE = 'WAITING'
           and s.username is not null
           and sw.seconds_in_wait > 30
           and (a.object_id = s.row_wait_obj# or
               a.data_object_id = s.row_wait_obj#))
ORDER BY l.id1, l.request;