Der Kill-Blocker-Job dient dazu, um Blockingsessions zu erkennen und die existierenden Blocker zu identifizieren und diese per E-Mail zu melden. Dabei kann definiert werden, wann diese gemeldet werden und ob sie automatisch gekillt werden oder nicht.
In diesem Beispiel wurde definiert, dass alle Blocker, die länger als 30 Sekunden blockieren, identifiziert werden und per E-Mail an definierte Adressen gemeldet werden. Weiters versucht der Kill-Blocker-Job die Blocker automatisch zu killen.
1) Tablespace BLOCKMON erstellen
Für einen neuen User BLOCKMON, der erstellt wird, kann hier 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 diverse Rechte vergeben:
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
Danach müssen folgende Tabellen erstellt werden. In diesen Tabellen werden die Details zu den Blockern gespeichert. Wie zB.:. Kill-Logdaten und Lock-Logdaten.
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“ ;
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“ ;
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 locklog;
create table locklog as (select s.*,1 snap,sysTIMESTAMP ts from gv$lock s where 1=0);
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“ ;
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);
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)) ;
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);
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“ ;
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);
6- DDL für die Tabelle TEST
CREATE TABLE „BLOCKMON“.“TEST“
( „N“ NUMBER
)
TABLESPACE „BLOCKMON“ ;
4) VIEWS als User BLOCKMON erstellen
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(+);
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(+);
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 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 where
inst_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 Blocker auch 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 wird.
begin
null;
— execute immediate stm;
exception when others then null;
end;
dbms_output.put_Line(stm);
Wie auch schon bei den einzelnen Tabellen beschrieben, kann es bei einigen Datenbank-Releases beim Insert in den folgenden Tabellen zu dem 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 Blockingsession erstellt warden und somit 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;
/
=> Wenn erfolgreich, dann kann das Killen wieder aktiviert und der Job dafür eingerichtet werden.
11) JOB FÜR BLOCKMON USER EINRICHTEN
Nun muss nur noch ein Job 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;
/
