2014-02-28 – OracleBlog

今天遇到一个问题，某sql的并发度虽然已经全部获得但是只有少数几个slave干活。

alter  session enable parallel dml;
insert /*+ parallel(t 16)*/一个本地分区表t  
select /*+ full(a parallel(a 32))*/* from 远程分区表a@dblink 
where 时间>=xxxx and 时间<xxxxx;

alter session enable parallel dml;

insert /*+ parallel(t 16)*/一个本地分区表t

select /*+ full(a parallel(a 32))*/* from 远程分区表a@dblink

where 时间>=xxxx and 时间<xxxxx;

insert要求有16个并发，虽然在v$PX_SESSION看到其获得的degree是16，但是并不是这16个slave都在干活，从sqlmonito […]

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$sess […]

当Parallel DML遇到分布式事务、分区表和LOB时

latch:transaction allocation