Oracle SQL - SELECT blocks index query and blocks DML sessions

Question

We have a very strange lock happening in production. We installed a PL / SQL script that detects objects that were blocked for more than 5 seconds and sent us an email alert.

Below is the cursor of this script:

select l.sid, trunc(l.id1 / power(2, 16)) rbs,
    bitand(l.id1, to_number('ffff', 'xxxx')) + 0 slot,
    l.id2, l.lmode, l.request, l.ctime, l.block,
    substr(v.osuser, 1, 12) osuser,
    substr(v.machine, 1, 15) machine,
    substr(v.module, 1, 12) module,
    decode(v.blocking_session_status||l.block, 'VALID0',
           dbms_rowid.rowid_create(1, v.row_wait_obj#, v.row_wait_file#,
                       v.row_wait_block#, v.row_wait_row#), '.') lrow,
    o.object_name,
    decode(v.sql_id, null, v.prev_sql_id, v.sql_id) sql_id,
    o.owner
from v$lock l, v$session v, all_objects o
where l.sid = v.sid
  and v.row_wait_obj# = o.object_id(+)
  and l.ctime > 5 and l.type = 'TX' and (l.request = 6 or l.block = 1)
order by 2, 3, 4, 8 desc, 7 desc;

And today we received a warning about blocking:

SID  TRANS-ID        L-TYPE  CTIME  BLOCK OSUSER     MACHINE         MODULE        SQLID            ROWID            OBJECT
---- --------------- ------- ------ ----- ---------- --------------- ------------ ------------- ------------------ --------------------
669  132,11,40475    6/0     70     1     userpr1     serv1023       userpr1-00002 fbnhs4gd9a7yn .                  IDX_005
1133 132,11,40475    0/6     62     0     userpr1     serv1023       userpr1-00000 f0gm2rx85qjja AAAgOuAAFAAD04TAAW ITEMST
924  132,11,40475    0/6     53     0     userpr1     serv1023       userpr1-00002 f0gm2rx85qjja AAAgOuAAFAAD04TAAW ITEMST
927  132,11,40475    0/6     27     0     userpr1     serv1023       userpr1-00001 f0gm2rx85qjja AAAgOuAAFAAD04TAAW ITEMST

So, from the above, you can see that the sqlid of the fbnhs4gd9a7ynsession 669has an index lock IDX_005and blocks the remaining sessions.

Now for the most bizarre part:

• SQLID fbnhs4gd9a7ynis just a SELECT query (not even SELECT FOR UPDATE)
• The index IDX_005has no connection to the table ITEMST, but it blocks the remaining 3 sessions that update ITEMSTanyways

, : [1] ITEMST?

​​Oracle? Oracle 11.2.0.4 Enterprise Edition, .

Answer 1

sql_id, , , .

SID 669, , ITEMST, , update, , 669 SELECT . update ( INSERT - ), , . , , , .