Kill Blocking Job entfernt Blocking Sessions

Kill-Blocker-Job erkennt und entfernt Blocking-Sessions

Der Kill-Blocker-Job dient dazu, um Blocking-Sessions in der Oracle Datenbank zu erkennen und eventuell existierende „Blocker“ zu identifizieren und per E-Mail zu melden.

Dabei kann festgelegt werden, in welchen Intervallen die Blocking-Sessions gemeldet werden und ob sie automatisch gekillt werden oder nicht.

In diesem Beispiel wurde definiert, dass alle Blocking-Sessions die länger als 30 Sekunden blockieren, automatisch identifiziert werden und per E-Mail an mehrere eMail Adressen gemeldet werden.

Weiters versucht der Kill-Blocker-Job die Blocker-Sessions automatisch zu killen.

1) Tablespace BLOCKMON erstellen

Für den neuen User BLOCKMON kann optional ein Tablespace erstellt werden. In unserem Fall erstellen wir einen neuen Tablespace.

create tablespace BLOCKMON datafile size 100M autoextend on maxsize 5G;

2) User BLOCKMON erstellen

create user BLOCKMON identified by PASSWORDDEFINIEREN default tablespace BLOCKMON;

Dem User BLOCKMON müssen folgende, notwendige Rechte vergeben werden:

GRANT CREATE SESSION TO BLOCKMON;
GRANT SELECT ANY DICTIONARY TO BLOCKMON;
GRANT CREATE PROCEDURE,CREATE SEQUENCE,CREATE VIEW,CREATE TABLE TO BLOCKMON;
ALTER USER BLOCKMON QUOTA UNLIMITED ON USERS;
GRANT "RESOURCE" TO BLOCKMON ;
ALTER USER BLOCKMON DEFAULT ROLE "RESOURCE";
GRANT CREATE JOB TO BLOCKMON ;
GRANT ALTER SYSTEM TO BLOCKMON ;
GRANT UNLIMITED TABLESPACE TO BLOCKMON ;

3) Tabellen als User BLOCKMON erstellen

Als nächster Schritt müssen folgende Tabellen erstellt werden.
In diesen Tabellen werden die Details zu den Blockern gespeichert – wie zum Beispiel die Kill-Logdaten und Lock-Logdaten.

3-1- DDL für die Tabelle KILLLOG

CREATE TABLE "BLOCKMON"."KILLLOG" 
   (    "SNAP" NUMBER, 
    "TS" TIMESTAMP (6), 
    "INST_ID" NUMBER, 
    "SID" NUMBER, 
    "STMT" VARCHAR2(4000 BYTE)
   ) 
  TABLESPACE "BLOCKMON" ;

3-2- DDL für die Tabelle LOCKLOG

  CREATE TABLE "BLOCKMON"."LOCKLOG" 
   (    "INST_ID" NUMBER, 
    "ADDR" RAW(8), 
    "KADDR" RAW(8), 
    "SID" NUMBER, 
    "TYPE" VARCHAR2(2 BYTE), 
    "ID1" NUMBER, 
    "ID2" NUMBER, 
    "LMODE" NUMBER, 
    "REQUEST" NUMBER, 
    "CTIME" NUMBER, 
    "BLOCK" NUMBER, 
    "SNAP" NUMBER, 
    "TS" TIMESTAMP (6)
   ) 
  TABLESPACE "BLOCKMON" ;

Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu Problem kommen, dass einige Spalten fehlen. Daher muss in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen werden und in den Tabellen die fehlenden Spalten ergänzt werden.

drop table locklog;
create table locklog as (select s.*,1 snap,sysTIMESTAMP ts from gv$lock s where 1=0);

3-3- DDL für die Tabelle SESSIONLOG

  CREATE TABLE "BLOCKMON"."SESSIONLOG" 
   (    "INST_ID" NUMBER, 
    "SADDR" RAW(8), 
    "SID" NUMBER, 
    "SERIAL#" NUMBER, 
    "AUDSID" NUMBER, 
    "PADDR" RAW(8), 
    "USER#" NUMBER, 
    "USERNAME" VARCHAR2(30 BYTE), 
    "COMMAND" NUMBER, 
    "OWNERID" NUMBER, 
    "TADDR" VARCHAR2(16 BYTE), 
    "LOCKWAIT" VARCHAR2(16 BYTE), 
    "STATUS" VARCHAR2(8 BYTE), 
    "SERVER" VARCHAR2(9 BYTE), 
    "SCHEMA#" NUMBER, 
    "SCHEMANAME" VARCHAR2(30 BYTE), 
    "OSUSER" VARCHAR2(30 BYTE), 
    "PROCESS" VARCHAR2(24 BYTE), 
    "MACHINE" VARCHAR2(64 BYTE), 
    "PORT" NUMBER, 
    "TERMINAL" VARCHAR2(30 BYTE), 
    "PROGRAM" VARCHAR2(48 BYTE), 
    "TYPE" VARCHAR2(10 BYTE), 
    "SQL_ADDRESS" RAW(8), 
    "SQL_HASH_VALUE" NUMBER, 
    "SQL_ID" VARCHAR2(13 BYTE), 
    "SQL_CHILD_NUMBER" NUMBER, 
    "SQL_EXEC_START" DATE, 
    "SQL_EXEC_ID" NUMBER, 
    "PREV_SQL_ADDR" RAW(8), 
    "PREV_HASH_VALUE" NUMBER, 
    "PREV_SQL_ID" VARCHAR2(13 BYTE), 
    "PREV_CHILD_NUMBER" NUMBER, 
    "PREV_EXEC_START" DATE, 
    "PREV_EXEC_ID" NUMBER, 
    "PLSQL_ENTRY_OBJECT_ID" NUMBER, 
    "PLSQL_ENTRY_SUBPROGRAM_ID" NUMBER, 
    "PLSQL_OBJECT_ID" NUMBER, 
    "PLSQL_SUBPROGRAM_ID" NUMBER, 
    "MODULE" VARCHAR2(64 BYTE), 
    "MODULE_HASH" NUMBER, 
    "ACTION" VARCHAR2(64 BYTE), 
    "ACTION_HASH" NUMBER, 
    "CLIENT_INFO" VARCHAR2(64 BYTE), 
    "FIXED_TABLE_SEQUENCE" NUMBER, 
    "ROW_WAIT_OBJ#" NUMBER, 
    "ROW_WAIT_FILE#" NUMBER, 
    "ROW_WAIT_BLOCK#" NUMBER, 
    "ROW_WAIT_ROW#" NUMBER, 
    "TOP_LEVEL_CALL#" NUMBER, 
    "LOGON_TIME" DATE, 
    "LAST_CALL_ET" NUMBER, 
    "PDML_ENABLED" VARCHAR2(3 BYTE), 
    "FAILOVER_TYPE" VARCHAR2(13 BYTE), 
    "FAILOVER_METHOD" VARCHAR2(10 BYTE), 
    "FAILED_OVER" VARCHAR2(3 BYTE), 
    "RESOURCE_CONSUMER_GROUP" VARCHAR2(32 BYTE), 
    "PDML_STATUS" VARCHAR2(8 BYTE), 
    "PDDL_STATUS" VARCHAR2(8 BYTE), 
    "PQ_STATUS" VARCHAR2(8 BYTE), 
    "CURRENT_QUEUE_DURATION" NUMBER, 
    "CLIENT_IDENTIFIER" VARCHAR2(64 BYTE), 
    "BLOCKING_SESSION_STATUS" VARCHAR2(11 BYTE), 
    "BLOCKING_INSTANCE" NUMBER, 
    "BLOCKING_SESSION" NUMBER, 
    "FINAL_BLOCKING_SESSION_STATUS" VARCHAR2(11 BYTE), 
    "FINAL_BLOCKING_INSTANCE" NUMBER, 
    "FINAL_BLOCKING_SESSION" NUMBER, 
    "SEQ#" NUMBER, 
    "EVENT#" NUMBER, 
    "EVENT" VARCHAR2(64 BYTE), 
    "P1TEXT" VARCHAR2(64 BYTE), 
    "P1" NUMBER, 
    "P1RAW" RAW(8), 
    "P2TEXT" VARCHAR2(64 BYTE), 
    "P2" NUMBER, 
    "P2RAW" RAW(8), 
    "P3TEXT" VARCHAR2(64 BYTE), 
    "P3" NUMBER, 
    "P3RAW" RAW(8), 
    "WAIT_CLASS_ID" NUMBER, 
    "WAIT_CLASS#" NUMBER, 
    "WAIT_CLASS" VARCHAR2(64 BYTE), 
    "WAIT_TIME" NUMBER, 
    "SECONDS_IN_WAIT" NUMBER, 
    "STATE" VARCHAR2(19 BYTE), 
    "WAIT_TIME_MICRO" NUMBER, 
    "TIME_REMAINING_MICRO" NUMBER, 
    "TIME_SINCE_LAST_WAIT_MICRO" NUMBER, 
    "SERVICE_NAME" VARCHAR2(64 BYTE), 
    "SQL_TRACE" VARCHAR2(8 BYTE), 
    "SQL_TRACE_WAITS" VARCHAR2(5 BYTE), 
    "SQL_TRACE_BINDS" VARCHAR2(5 BYTE), 
    "SQL_TRACE_PLAN_STATS" VARCHAR2(10 BYTE), 
    "SESSION_EDITION_ID" NUMBER, 
    "CREATOR_ADDR" RAW(8), 
    "CREATOR_SERIAL#" NUMBER, 
    "ECID" VARCHAR2(64 BYTE), 
    "TS" TIMESTAMP (6), 
    "SNAP" NUMBER, 
    "INFO" VARCHAR2(100 BYTE)
   ) 
  TABLESPACE "BLOCKMON" ;

Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu dem Problem kommen, dass einige Spalten fehlen. Daher muss hier in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen und in den Tabellen die fehlenden Spalten ergänzt werden.

drop table sessionlog;
create table sessionlog as (select s.*,sysTIMESTAMP ts,1 snap,cast('x' as  VARCHAR2(100)) info  from gv$session s where 1=0);

3-4- DDL für die Tabelle SQLLOG

  CREATE TABLE "BLOCKMON"."SQLLOG" 
   (    "INST_ID" NUMBER, 
    "SQL_TEXT" VARCHAR2(1000 BYTE), 
    "SQL_FULLTEXT" CLOB, 
    "SQL_ID" VARCHAR2(13 BYTE), 
    "SHARABLE_MEM" NUMBER, 
    "PERSISTENT_MEM" NUMBER, 
    "RUNTIME_MEM" NUMBER, 
    "SORTS" NUMBER, 
    "LOADED_VERSIONS" NUMBER, 
    "OPEN_VERSIONS" NUMBER, 
    "USERS_OPENING" NUMBER, 
    "FETCHES" NUMBER, 
    "EXECUTIONS" NUMBER, 
    "PX_SERVERS_EXECUTIONS" NUMBER, 
    "END_OF_FETCH_COUNT" NUMBER, 
    "USERS_EXECUTING" NUMBER, 
    "LOADS" NUMBER, 
    "FIRST_LOAD_TIME" VARCHAR2(19 BYTE), 
    "INVALIDATIONS" NUMBER, 
    "PARSE_CALLS" NUMBER, 
    "DISK_READS" NUMBER, 
    "DIRECT_WRITES" NUMBER, 
    "BUFFER_GETS" NUMBER, 
    "APPLICATION_WAIT_TIME" NUMBER, 
    "CONCURRENCY_WAIT_TIME" NUMBER, 
    "CLUSTER_WAIT_TIME" NUMBER, 
    "USER_IO_WAIT_TIME" NUMBER, 
    "PLSQL_EXEC_TIME" NUMBER, 
    "JAVA_EXEC_TIME" NUMBER, 
    "ROWS_PROCESSED" NUMBER, 
    "COMMAND_TYPE" NUMBER, 
    "OPTIMIZER_MODE" VARCHAR2(10 BYTE), 
    "OPTIMIZER_COST" NUMBER, 
    "OPTIMIZER_ENV" RAW(2000), 
    "OPTIMIZER_ENV_HASH_VALUE" NUMBER, 
    "PARSING_USER_ID" NUMBER, 
    "PARSING_SCHEMA_ID" NUMBER, 
    "PARSING_SCHEMA_NAME" VARCHAR2(30 BYTE), 
    "KEPT_VERSIONS" NUMBER, 
    "ADDRESS" RAW(8), 
    "TYPE_CHK_HEAP" RAW(8), 
    "HASH_VALUE" NUMBER, 
    "OLD_HASH_VALUE" NUMBER, 
    "PLAN_HASH_VALUE" NUMBER, 
    "CHILD_NUMBER" NUMBER, 
    "SERVICE" VARCHAR2(64 BYTE), 
    "SERVICE_HASH" NUMBER, 
    "MODULE" VARCHAR2(64 BYTE), 
    "MODULE_HASH" NUMBER, 
    "ACTION" VARCHAR2(64 BYTE), 
    "ACTION_HASH" NUMBER, 
    "SERIALIZABLE_ABORTS" NUMBER, 
    "OUTLINE_CATEGORY" VARCHAR2(64 BYTE), 
    "CPU_TIME" NUMBER, 
    "ELAPSED_TIME" NUMBER, 
    "OUTLINE_SID" NUMBER, 
    "CHILD_ADDRESS" RAW(8), 
    "SQLTYPE" NUMBER, 
    "REMOTE" VARCHAR2(1 BYTE), 
    "OBJECT_STATUS" VARCHAR2(19 BYTE), 
    "LITERAL_HASH_VALUE" NUMBER, 
    "LAST_LOAD_TIME" VARCHAR2(19 BYTE), 
    "IS_OBSOLETE" VARCHAR2(1 BYTE), 
    "IS_BIND_SENSITIVE" VARCHAR2(1 BYTE), 
    "IS_BIND_AWARE" VARCHAR2(1 BYTE), 
    "IS_SHAREABLE" VARCHAR2(1 BYTE), 
    "CHILD_LATCH" NUMBER, 
    "SQL_PROFILE" VARCHAR2(64 BYTE), 
    "SQL_PATCH" VARCHAR2(30 BYTE), 
    "SQL_PLAN_BASELINE" VARCHAR2(30 BYTE), 
    "PROGRAM_ID" NUMBER, 
    "PROGRAM_LINE#" NUMBER, 
    "EXACT_MATCHING_SIGNATURE" NUMBER, 
    "FORCE_MATCHING_SIGNATURE" NUMBER, 
    "LAST_ACTIVE_TIME" DATE, 
    "BIND_DATA" RAW(2000), 
    "TYPECHECK_MEM" NUMBER, 
    "IO_CELL_OFFLOAD_ELIGIBLE_BYTES" NUMBER, 
    "IO_INTERCONNECT_BYTES" NUMBER, 
    "PHYSICAL_READ_REQUESTS" NUMBER, 
    "PHYSICAL_READ_BYTES" NUMBER, 
    "PHYSICAL_WRITE_REQUESTS" NUMBER, 
    "PHYSICAL_WRITE_BYTES" NUMBER, 
    "OPTIMIZED_PHY_READ_REQUESTS" NUMBER, 
    "LOCKED_TOTAL" NUMBER, 
    "PINNED_TOTAL" NUMBER, 
    "IO_CELL_UNCOMPRESSED_BYTES" NUMBER, 
    "IO_CELL_OFFLOAD_RETURNED_BYTES" NUMBER, 
    "SNAP" NUMBER, 
    "TS" TIMESTAMP (6)
   ) 
  TABLESPACE "BLOCKMON" 
 LOB ("SQL_FULLTEXT") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu dem Problem kommen, dass einige Spalten fehlen. Daher muss hier in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen und in den Tabellen die fehlenden Spalten ergänzt werden.

drop table sqllog;
create table sqllog as (select s.*,1 snap,sysTIMESTAMP ts from gv$sql s where 1=0);

3-5- DDL für die Tabelle SQLTEXTLOG

  CREATE TABLE "BLOCKMON"."SQLTEXTLOG" 
   (    "INST_ID" NUMBER, 
    "ADDRESS" RAW(8), 
    "HASH_VALUE" NUMBER, 
    "SQL_ID" VARCHAR2(13 BYTE), 
    "COMMAND_TYPE" NUMBER, 
    "PIECE" NUMBER, 
    "SQL_TEXT" VARCHAR2(64 BYTE), 
    "SNAP" NUMBER
   ) 
  TABLESPACE "BLOCKMON" ;

Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu dem Problem kommen, dass einige Spalten fehlen. Daher muss hier in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen und in den Tabellen die fehlenden Spalten ergänzt werden.

drop table sqltextlog;
create table sqltextlog as (select s.*,1 snap from gv$sqltext s where 1=0);

3-6- DDL für die Tabelle TEST

  CREATE TABLE "BLOCKMON"."TEST" 
   (    "N" NUMBER
   ) 
  TABLESPACE "BLOCKMON" ;

4) Mehrere VIEWS als User BLOCKMON erstellen

4-1- DDL for View V_BLOCKED_SESSIONS

CREATE OR REPLACE FORCE VIEW "BLOCKMON"."V_BLOCKED_SESSIONS" ("TIMESTAMP", "SNAP", "INFO", "INST_ID", "SID", "USERNAME", "WAIT_CLASS",

"EVENT", "SECONDS_IN_WAIT", "SQL_ID", "SQL_TEXT") AS 
  select se.ts timestamp,se.snap,se.info,se.inst_id,se.sid,se.username,se.wait_class,se.event,se.seconds_in_wait,sq.sql_id,sq.sql_text
from sessionlog se,sqllog sq
where info='blocked'
and se.snap=sq.snap(+)
and se.sql_id=sq.sql_id(+);

4-2- DDL for View V_BLOCKING_DATA

CREATE OR REPLACE FORCE VIEW "BLOCKMON"."V_BLOCKING_DATA" ("SNAP", "INST_ID", "SID", "SQL_ID", "INFO", "WAIT_CLASS", "EVENT",

"SECONDS_IN_WAIT", "TIMESTAMP") AS 
  select se.snap,se.inst_id,se.sid,se.sql_id,se.info,se.wait_class,se.event,se.seconds_in_wait,se.ts timestamp from sessionlog se, killlog

ki,sqllog sq
where se.snap=ki.snap
and se.snap=sq.snap(+)
and se.inst_id=ki.inst_id
and se.sid=ki.sid
and se.sql_id=sq.sql_id(+);

4-3- DDL for View V_KILLED_SESSIONS

  CREATE OR REPLACE FORCE VIEW "BLOCKMON"."V_KILLED_SESSIONS" ("TIMESTAMP", "SNAP", "INFO", "INST_ID", "SID", "USERNAME", "WAIT_CLASS",

"EVENT", "SECONDS_IN_WAIT", "SQL_ID", "SQL_TEXT") AS 
  select ki.ts timestamp,ki.snap,se.info,ki.inst_id,ki.sid,se.username,se.wait_class,se.event,se.seconds_in_wait,sq.sql_id,sq.sql_text
from killlog ki,sessionlog se,sqllog sq
where ki.snap=se.snap
and ki.inst_id=se.inst_id
and ki.sid=se.sid
and se.info='blocker'
and se.snap=sq.snap(+)
and se.sql_id=sq.sql_id(+);

5) SEQUENCES als User BLOCKMON erstellen

DDL for Sequence SEQ

CREATE SEQUENCE  "BLOCKMON"."SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 607041 CACHE 20 NOORDER NOCYCLE ;

6) PROZEDUR WATCHDOG als User BLOCKMON erstellen

DDL for Procedure WATCHDOG

set define off;  
 
create or replace procedure BLOCKMON.watchdog as
snapid number;
rsid number;
sqlid varchar2(100);
stm varchar2(4000);
msg varchar2(32000);
blkdmsg varchar2(32000);
blkdsql varchar2(32000);
crlf varchar2(100):=chr(13)||chr(10);
flag number;
begin
select seq.nextval into snapid from dual;
for s in (select * from gv$session where blocking_session is not null) loop
--session
  insert into sessionlog (select s1.*,systimestamp,snapid,'blocked' from gv$session s1 where inst_id=s.inst_id and sid=s.sid);


 insert into sessionlog  (select s1.*,systimestamp,snapid,'blocker' from gv$session s1 where inst_id=s.blocking_instance and sid=s.blocking_session);


 insert into sessionlog  (select s1.*,systimestamp,snapid,'finalblocker' from gv$session s1 where inst_id=s.final_blocking_instance and sid=s.final_blocking_session);


--sql
  insert into sqllog (select s1.*,snapid,systimestamp from gv$sql s1 where sql_id=(select sql_id from gv$session s1 where inst_id=s.inst_id and sid=s.sid));


  insert into sqllog (select s1.*,snapid,systimestamp from gv$sql s1 where sql_id=(select sql_id from gv$session s1 where inst_id=s.blocking_instance and sid=s.blocking_session));


  insert into sqllog (select s1.*,snapid,systimestamp from gv$sql s1 where sql_id=(select sql_id from gv$session s1 whereinst_id=s.final_blocking_instance and sid=s.final_blocking_session));


--lock  
insert into locklog (select s1.*,snapid,systimestamp from gv$lock s1 where inst_id=s.inst_id and sid=s.sid);
insert into locklog (select s1.*,snapid,systimestamp from gv$lock s1 where inst_id=s.blocking_instance and sid=s.blocking_session);
insert into locklog (select s1.*,snapid,systimestamp from gv$lock s1 where inst_id=s.final_blocking_instance and sid=s.final_blocking_session);
--sqltext
insert into sqltextlog (select s1.*,snapid from gv$sqltext s1 where inst_id=s.inst_id and sql_id=s.sql_id);

blkdmsg:='instance:'||s.inst_id||crlf;
blkdmsg:=blkdmsg||'sid:'||s.sid||crlf;
blkdmsg:=blkdmsg||'username:'||s.username||crlf;
blkdmsg:=blkdmsg||'program:'||s.program||crlf;
blkdmsg:=blkdmsg||'module:'||s.module||crlf;
blkdmsg:=blkdmsg||'wait_class:'||s.wait_class||crlf;
blkdmsg:=blkdmsg||'evnt:'||s.event||crlf;
blkdmsg:=blkdmsg||'seconds_in_wait:'||s.seconds_in_wait||crlf;
blkdmsg:=blkdmsg||crlf;
  
flag:=0;
blkdsql:='';
for r in (select distinct piece,sql_text from sqltextlog where sql_id=s.sql_id order by piece) loop
  blkdsql:=blkdsql||r.sql_text||crlf;
  flag:=1;
end loop;
if flag=0 then 
  blkdsql:='--NONE--'||crlf;  
end if;
  
--kill nur final blocker
  if s.seconds_in_wait>=30 then
    for b in (select * from gv$session where inst_id=s.final_blocking_instance and sid=s.final_blocking_session) loop
--sqltext
    insert into sqltextlog (select s1.*,snapid from gv$sqltext s1 where inst_id=b.inst_id and sql_id=b.sql_id);
      
      
      stm:='alter system kill session '''||b.sid||','||b.serial#||',@'||b.inst_id||'''';
      insert into killlog values (snapid,systimestamp,b.inst_id,b.sid,stm);
msg:='will try to kill('||stm||')'||chr(10)||chr(13)||'(if this mail is not sent again, the kill has been successful)'||crlf;    
msg:=msg||crlf;
msg:=msg||'(there will be one mail per blocked session, if there is a cascading block situation, the blocks will be handled sequencially)'||

crlf;    
msg:=msg||crlf;
msg:=msg||'DETAILED INFORMATION:'||crlf;
msg:=msg||'---------------------'||crlf;
msg:=msg||crlf;
msg:=msg||'BLOCKER SESSION:'||crlf;
msg:=msg||'----------------'||crlf;
msg:=msg||'instance:'||b.inst_id||crlf;
msg:=msg||'sid:'||b.sid||crlf;
msg:=msg||'username:'||b.username||crlf;
msg:=msg||'program:'||b.program||crlf;
msg:=msg||'module:'||b.module||crlf;
msg:=msg||'wait_class:'||b.wait_class||crlf;
msg:=msg||'event:'||b.event||crlf;
msg:=msg||'seconds_in_wait:'||b.seconds_in_wait||crlf;
msg:=msg||crlf;
msg:=msg||'BLOCKED SESSION:'||crlf;
msg:=msg||'-------------------'||crlf;
msg:=msg||blkdmsg;
msg:=msg||crlf;
msg:=msg||'Current Statements:'||crlf;
msg:=msg||'-------------------'||crlf;
msg:=msg||'BLOCKER(normaly NONE):'||crlf;
flag:=0;
for r in (select distinct piece,sql_text from sqltextlog where sql_id=b.sql_id order by piece) loop
  msg:=msg||r.sql_text||crlf;
  flag:=1;
end loop;
if flag=0 then 
  msg:=msg||'--NONE--'||crlf;
end if;
msg:=msg||crlf;
msg:=msg||'BLOCKED:'||crlf;
msg:=msg||blkdsql||crlf;
msg:=msg||crlf;
msg:=msg||'historical data in schema BLOCKMON:'||crlf;
msg:=msg||'select * from blockmon.v_blocked_sessions where snap=<snap>'||crlf;
msg:=msg||'select * from blockmon.v_killed_sessions where snap=<snap>'||crlf;
msg:=msg||'Base Tables:'||crlf;
msg:=msg||'KILLLOG'||crlf;
msg:=msg||'LOCKLOG'||crlf;
msg:=msg||'SESSIONLOG'||crlf;
msg:=msg||'SQLLOG'||crlf;
msg:=msg||'SQLTEXTLOG'||crlf;

msg:=msg||crlf;

          utl_mail.send(
    sender      =>    'NAME@xyxyxy.com',
    recipients  =>  'EMPFÄNGER@xyxyxy.com, EMPFÄNGER@xyxyxy.com ',  
    cc          =>    'EMPFÄNGER@xyxyxy.com ',
    bcc         =>    '',
    subject     =>    'BLOCKER DETECTED (snap '||snapid||')',
    message     =>    msg,
    mime_type   =>    'text/plain; charset=us-ascii',
    priority    =>    NULL);
      begin
--      null;
      execute immediate stm;                                    
      exception when others then null;
      end;
      dbms_output.put_Line(stm);
    end loop;
  end if;
end loop;
end;
/

Falls man vorher testen möchte ob die Blocking Sessions richtig gekillt werden, muss man den Eintrag „execute immediate stm;“ auskommentieren.

Dadurch werden die Blocker nicht gekillt, sondern nur E-Mails verschickt mit der Info welche Session gekillt werden sollten.

begin
    null;
--      execute immediate stm;                                    
      exception when others then null;
      end;
     dbms_output.put_Line(stm);

Wie schon oben bei den einzelnen Tabellen beschrieben, kann es bei einigen Datenbank-Releases beim Insert in den folgenden Tabellen zum Problem kommen, dass einige Spalten fehlen, die aber auf den entsprechenden Datenbank-Releases in den jeweiligen gv$ Views vorhanden sind:

sqllog, sessionlog, locklog, sqltextlog

Daher muss hier in solchen Fällen die Struktur von den jeweiligen gv$ Views übernommen- und in den Tabellen die fehlenden Spalten ergänzt werden.

Als BLOCKMON:

drop table locklog;
create table locklog as (select s.*,1 snap,sysTIMESTAMP ts from gv$lock s where 1=0);

drop table sqllog;
create table sqllog as (select s.*,1 snap,sysTIMESTAMP ts from gv$sql s where 1=0);

drop table sessionlog;
create table sessionlog as (select s.*,sysTIMESTAMP ts,1 snap,cast('x' as  VARCHAR2(100)) info  from gv$session s where 1=0);

drop table sqltextlog;
create table sqltextlog as (select s.*,1 snap from gv$sqltext s where 1=0);

7) ACL EINRICHTEN

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('netacl.xml',
      'Allow usage to the UTL network packages', 'BLOCKMON', TRUE,
'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'BLOCKMON', TRUE,
'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('netacl.xml','*');
END;
/

8) Falls kein UTL_MAIL installiert -> UTL_MAIL installieren

select * from dba_objects where object_name='UTL_MAIL';        => kein UTL_MAIL installiert

installieren des UTL_MAILS:
@utlmail.sql
@prvtmail.plb


 Rechte fehlen (BLOCKMON):
 grant execute on utl_mail to BLOCKMON;

9) SMTP EINRICHTEN

SQL> alter system set smtp_out_server='mail.xyxyxy.com';
System altered.

SQL> show parameter smtp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server                      string      mail.xyxyxy.com

10)  TESTEN

Zum Testen kann eine Blocking-Session erstellt werden und getestet werden, ob der Kill-Blocker richtig eingerichtet wurde.

1- Blockingsessions erzeugen:

BLOCKMON User:
insert into TEST values(1);
commit;
update TEST set n=2;

Nun nochmal mit einem anderen User.

update BLOCKMON.TEST set n=3;

=> Blockingsessions erzeugt!!!
3- Prozedur ausführen als BLOCKMON USER (am besten ohne kill):

=> nach 30 Sekunden:
begin
watchdog;
end;
/

Falls der Test erfolgreich verläuft, kann das Killen wieder aktiviert- und der Job dafür eingerichtet werden.

11) JOB FÜR BLOCKMON USER EINRICHTEN

Nun muss noch folgender Job zum Ausführen des WATCHDOGs eingerichtet werden:

begin
dbms_scheduler.create_job(
job_name=>'BLOCKMON.WATCHDOG_JOB',
job_type=>'PLSQL_BLOCK',
job_action=>'begin watchdog;end;',
repeat_interval=>'freq=minutely',
enabled=>true);
end;
/

12) Abschließend

Durch das Einrichten des WATCHDOG_JOB wird die Oracle Datenbank laufend aktiv auf blockierende Sessions überprüft und ein aufgespührter Blocker automatisch beendet.

In der WATCHDOG Procedure (Punkt 6)  werden alle Einstellungen vorgenommen, um die Kill-Voraussetzungen für Blocking Sessions anzupassen.

0 Kommentare

Dein Kommentar

An Diskussion beteiligen?
Hinterlasse uns Deinen Kommentar!

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Time limit is exhausted. Please reload CAPTCHA.