Home > Oracle Datenbank Monitoring > Blocking Locks

Blocking Locks

Teil 7: Blocking Locks Analyse

Neben den Row Locks, die bei DML Verarbeitungen immer wieder einmal eine andere Verarbeitung blockieren, gibt es auch noch andere Sperren. Wenn einmal überhaupt nichts mehr geht, dann muss man sich diese Blocking Locks genauer ansehen.

Natürlich ist die Abfrage wieder mit einer WITH Clause um die Daten nur einmal abzuholen und damit auch eher Lesekonsistent zu sein (V$ Views sind ja nur Memory Strukturen) und selbstverständlich funktioniert das Statement sowohl für RACs als auch für Single Instances.

col locking_instance for a16
col waiting_instance for a16

WITH
myinstance as (SELECT /*+ MATERIALIZE */ inst_id, instance_name FROM gv$instance),
mylock as (SELECT /*+ MATERIALIZE */ inst_id, sid, type, request, id1, id2, lmode FROM gv$lock)
SELECT
    ih.instance_name || ' - ' ||  lh.sid        locking_instance
  , iw.instance_name || ' - ' ||  lw.sid        waiting_instance
  , DECODE (   lh.type
             , 'CF', 'Control File'
             , 'DX', 'Distr. Transaction'
             , 'FS', 'File Set'
             , 'IR', 'Instance Recovery'
             , 'IS', 'Instance State'
             , 'IV', 'Libcache Invalidation'
             , 'LS', 'LogStartORswitch'
             , 'MR', 'Media Recovery'
             , 'RT', 'Redo Thread'
             , 'RW', 'Row Wait'
             , 'SQ', 'Sequence #'
             , 'ST', 'Diskspace Transaction'
             , 'TE', 'Extend Table'
             , 'TT', 'Temp Table'
             , 'TX', 'Transaction'
             , 'TM', 'Dml'
             , 'UL', 'PLSQL User_lock'
             , 'UN', 'User Name'
             , 'Nothing-'
           ) waiter_lock_type
  , DECODE (   lw.request
             , 0, 'None'
             , 1, 'NoLock'
             , 2, 'Row-Share'
             , 3, 'Row-Exclusive'
             , 4, 'Share-Table'
             , 5, 'Share-Row-Exclusive'
             , 6, 'Exclusive'
             , 'Nothing-'
           ) waiter_mode_req
FROM
    mylock     lw
  , mylock     lh
  , myinstance iw
  , myinstance ih
WHERE    iw.inst_id = lw.inst_id
  AND ih.inst_id = lh.inst_id
  AND lh.id1     = lw.id1
  AND lh.id2     = lw.id2
  AND lh.request = 0
  AND lw.lmode   = 0
  AND (lh.id1, lh.id2) IN ( SELECT id1,id2
                            FROM   mylock
                            WHERE  request = 0
                            INTERSECT
                            SELECT id1,id2
                            FROM   mylock
                            WHERE  lmode = 0
                          )
ORDER BY lh.sid;