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 echten 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).

Beispiel für detaillierte Blocking Session - RAC tauglich

Keine Sorge, die Query funktioniert auch wenn Sie eine Single Instance haben und selbstverständlich auch bei allen Datenbank Editionen.

 

col "Level" for a5
col "Session" for a20
col "Blocked by" for a10
col "Blocking Object" for a20
col "Sec Waiting" for 12
col event for a30
col "current SQL_ID for a13
col "current SQL" for a20
col "previous SQL_ID" for a13
col "previous SQL" for a20

WITH allsessions AS
(SELECT /*+ MATERIALIZE */
        distinct sid || '@' || inst_id as sid, username,
                 blocking_session || decode (blocking_session,null,null, '@' || blocking_instance) as blocking_session,
                 seconds_in_wait, event, row_wait_obj#, sql_id, prev_sql_id
   FROM gv$session
),
mysessions as
(SELECT /*+ MATERIALIZE */ LEVEL as MyLevel, a.*
   FROM allsessions a
  WHERE sid in (select blocking_session FROM allsessions) or blocking_session is not null
CONNECT BY PRIOR sid = blocking_session
   START WITH blocking_session IS NULL
),
mysql AS
(SELECT /*+ MATERIALIZE */
        distinct sql_id, substr(regexp_replace(sql_text, '( [[:space:]]+)|([[:cntrl:]])',' '),1,300) as sql_text
   FROM gv$sqlarea
  WHERE sql_id in (SELECT sql_id FROM mysessions
                    UNION ALL
                   SELECT prev_sql_id FROM mysessions)
),
myobjects AS
(SELECT /*+ MATERIALIZE */ object_id, owner ||'.'||object_name || '('|| object_type ||')' as "OBJECT"
   FROM dba_objects
  WHERE object_id in (SELECT distinct row_wait_obj# FROM mysessions)
)
SELECT to_char(s.myLEVEL) as "Level",
       s.sid || ': ' || s.username "Session",
       s.blocking_session "Blocked by",
       o."OBJECT" as "Blocking Object",
       s.seconds_in_wait "Sec Waiting",
       s.event,
       s.sql_id as "current SQL_ID",
       s1.sql_text as "current SQL",
       s.prev_sql_id "previous SQL_ID",
       s2.sql_text as "previous SQL"
FROM mysessions s left outer join myobjects o on (s.row_wait_obj# = o.object_id )
                   left outer join mysql s1 on (s.sql_id = s1.sql_id)
                   left outer join mysql s2 on (s.prev_sql_id = s2.sql_id)
order by 1;


Level Session              Blocked by Blocking Object  Sec Waiting EVENT
----- -------------------- ---------- ---------------- ----------- ------------------------------
current SQLID current SQL          previous SQL_ previous SQL
------------- -------------------- ------------- --------------------
1     35@1: SCOTT                                              3441 SQL*Net message from client
4mgra877tf9jt update foo set x=x+1 4mgra877tf9jt update foo set x=x+1
2     266@1: SCOTT          35@1       SCOTT.FOO(TABLE)        3428 enq: TX - row lock contention
051vutd55hy06 update foo set x=266 3jnnn1s0zxt2t update bar set x=x-1
2     16@1: SCOTT          35@1       SCOTT.FOO(TABLE)         3416 enq: TX - row lock contention
1kt0knwzvbyfv update foo set x=16  1kt0knwzvbyfv update foo set x=16
3     269@1: SCOTT          266@1      SCOTT.BAR(TABLE)        3421 enq: TX - row lock contention
4f3ncwknwr79n update bar set x=269 4f3ncwknwr79n update bar set x=269