Home > Oracle Datenbank Monitoring > Blocking Sessions

Blocking Sessions

Teil 1: Blocking Sessions

Die Aufgabenstellung ist eigentlich einfach: Gibt es Sessions, die andere blockieren, und wenn ja, was sind die blockierten Sessions?

Die notwendige Information findet sich in der View V$SESSION.

Aber Vorsicht!

  • Als so genannte "Performance View" basiert sie wie all die anderen V$-Views auf den X$-Tabellen, die keine echte Tabellen sind sondern (in diesem Fall) Memorystrukturen. Daher sind sie allesamt nicht lesekonsistent.
  • Für den Fall, dass man einen RAC nutzt, muß man auf GV$SESSION zugreifen um auch blockierende Sessions von anderen Instanzen zu sehen. Freundlicherweise stehen die GV$ Views auch bei Single Instance Datenbanken zur Verfügung - somit kann man mit den GV$-Views sowohl RAC als auch Single Instance Datenbanken abdecken.
  • Aus Performance Gründen sollte man in RAC Environments nicht mehrfach auf die gleiche View zugreifen, da dies zu vielen - unnötigen - Instance zu Instance Kommunikationen führt.

Daher ist der erste Ansatz ...

SELECT lpad(' ',level) || username AS username,
        inst_id, sid, serial#, terminal, event, sql_id, status,
        seconds_in_wait, blocking_session
   FROM gv$session s
   START WITH blocking_session IS NULL
      AND EXISTS
      (SELECT 1
          FROM gv$session i_s
          WHERE i_s.blocking_session = s.sid
            AND i_s.blocking_instance = s.inst_id
      )
   CONNECT BY blocking_session = PRIOR sid
          AND blocking_instance = PRIOR inst_id;

... zum Scheitern verurteilt.

Dieses Script sollte alle blockierten Sessions eingerückt unter den Blockierenden auflisten. Der Execution-Plan dieser Query würde die View aber mehrfach lesen: Jeder Durchlauf sieht andere Sessions, daher geht die Sache - besonders unter Last - schief. 

Die Lösung: WITH!

col username_ form a12 head Username
col osuser_ form a12 head "OS user"
col event_ form a32 head Event
col status_ form a12 head Status
col sid_ form 99999 head SID
col blocker_ form 9999999 head Blocker
col sql_id_ form a14 head SQL_id
col terminal_ form a8

WITH v_session AS (
   SELECT username,
          osuser,
          inst_id,
          sid,
          serial#,
          terminal,
          event,
          sql_id,
          status,
          seconds_in_wait,
          blocking_instance,
          blocking_session
      FROM gv$session)
SELECT  lpad(' ',level) || username AS username_,
        osuser osuser_,
        inst_id,
        sid AS sid_,
        serial#,
        terminal AS terminal_,
        event AS event_,
        sql_id,
        status,
        seconds_in_wait,
        blocking_instance,
        blocking_session AS blocker_
   FROM v_session s
   START WITH blocking_session IS NULL
      AND EXISTS (
         SELECT 1
            FROM v_session i_s
            WHERE i_s.blocking_session = s.sid
              AND i_s.blocking_instance = s.inst_id)
   CONNECT BY blocking_session = PRIOR sid
          AND blocking_instance = PRIOR inst_id
/

Durch die WITH-Klausel wird der Optimizer mit dem Holzhammer gezwungen, zuerst die GV$SESSION zu lesen, und dann den Rest. Damit liefert dieses Statement auch unter Last korrekten Output.

Die vielen Aliase sind notwendig, um im SQL*Plus die Zeilenlänge auf 162 zu halten (in der Defaulteinstellung wäre der Output weit über 200 Spalten lang und schwerer lesbar).