Lock-Diagnose im RAC

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.

Locked?

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
Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s