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) Memory-Strukturen. Daher sind sie allesamt nicht lesekonsistent.
- Für den Fall, dass man einen RAC nutzt, muss 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 Spalten-Aliases sind notwendig, um im SQL*Plus die Zeilenlänge auf 162 zu halten (in der Default-Einstellung wäre der Output weit über 200 Spalten lang und schwerer lesbar).
Beispiel für eine detaillierte Liste der Blocking Sessions
Keine Sorge, beide Queries funktionieren 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