In meinem Artikel „Diagnose von Locks in Oracle-Datenbanken“ von 2009 hatte ich bereits auf das bei der Oracle-Software mitgelieferte Script „utllockt.sql“ hingewiesen. Dieses Script berücksichtigt jedoch nur Locks auf einer DB-Instanz. Im Cluster-Betrieb kann jedoch eine Verkettung von Locks über mehrere Instanzen hinweg entstehen.
In diesem, kurzen Beitrag möchte ich einen Weg vorstellen, die Funktionalität von „utllockt.sql“ mit reinem SQL und RAC-fähig zu bauen.
Bild: Mike Gabelmann / flickr / CC-BY-NC-SA
utllockt fürs RAC
Das original-Script kommt noch aus alten Zeiten vor RAC und nutzte Tabellen zur Zwischenspeicherung von Daten aus dem Data Dictionary.
Dank der WITH-Clause ist die Zwischenspeicherung nicht mehr nötig – die Tabellen werden als Table Expression nachgebildet. Das hat den zusätzlichen Charme, dass der Account, über den man das SQL ausführt, kein „CREATE TABLE“-Recht benötigt.
with BLOCKERS as(
select inst_id waiting_instance
, sid waiting_session
, blocking_instance
, blocking_session
from gv$session
where blocking_instance is not null and blocking_session is not null
)
, LOCK_TREE as(
select * from BLOCKERS
union all
select blocking_instance, blocking_session, null, null
from BLOCKERS
minus
select waiting_instance, waiting_session, null, null
from BLOCKERS
)
select lpad(' ', 2*(level-1)) || l.waiting_session wsid
, l.waiting_instance winst
, s.status
, substr( q.sql_text, 1, 30 ) sql_text
, coalesce( s.sql_id, s.prev_sql_id ) sql_id
, s.osuser
, s.username
, substr(s.program,1,20) program
, substr(s.module,1,20) module
, s.action
from LOCK_TREE l
, gv$session s
, gv$sql q
where s.sid = l.waiting_session
and s.inst_id = l.waiting_instance
and q.sql_id (+) = coalesce( s.sql_id, s.prev_sql_id )
and q.inst_id (+) = s.inst_id
connect by prior l.waiting_session = l.blocking_session
and prior l.waiting_instance = l.blocking_instance
start with l.blocking_session is null;
Voilá! Viel Spaß beim Ausprobieren!
Eine für SQL*Plus formatierte Version dieses Scripts gibt es im „SQL-Zauberkasten“ auf GitHub zum Download.
Anmerkung: ich habe für Joins und Hierarchie in voller Absicht die Oracle- der ANSI-Syntax vorgezogen. Bei ausgiebigen Tests in Oracle 11.2 habe ich damit die effizienteren Ausführungspläne bekommen.
Bonus: „Kill Switch“
Als Extra für diejenigen, die das Script eher in einem GUI-Tool als auf der Kommandozeile ausführen, gibt es hier noch eine Variante, die zu jeder gefundenen Session gleich das passende „ALTER SYSTEM KILL …“ ausgibt. Es wird noch folgende Zeile in das untere SELECT eingefügt:
, 'ALTER SYSTEM KILL SESSION ' || '''' || s.SID || ', ' || s.serial#
|| ', @' || s.inst_id ||'''' || ' IMMEDIATE;' kill_session_stmt
