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;