Blocking detector


Job details

Name: Blocking detector
Platform: Oracle
Category: Performance
Description: Checks if a session is waiting on a TX (transaction) lock.
Long description: Task checks if a session is waiting on a TX (transaction) lock.
Version: 3.0
Default schedule: 0,4,8,12,16,20,24,28,32,36,40,44,48,52,56 6-18 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’]

Parameters

Name Default value Description
warning threshold locked time 300 Waiting time threshold (WARNING) on transaction lock for a session (in sec. --> 300 sec. = 5 min.).
include background processes NO Check for transaction locks for background processes (session without username value i v$session performance view).
alarm threshold locked time 7200 Waiting time threshold (ALARM) on transaction lock for a session (in sec. --> 7200 sec = 2 hours).

Job Summary

select /*+ RULE */ t1.sid as "Sid", t2.username as "Username", t1.type as "Type", t1.lmode as "Lock mode", decode(t1.block ,0,'NO','YES') as "Blocker", decode(t1.request,0,'NO','YES') as "Waiter", t2.program as "Program", t2.action as "Action", t2.osuser as "OS user", 'alter system kill session '||''''||t2.sid||','||t2.serial#||'''' as "DDL to kill session" from v$lock t1, v$session t2 where (t1.request > 0 or t1.block > 0) and t1.sid = t2.sid order by t1.block desc;

Technical Details

Implementation Details

Dependencies

SQL Tables and Procedures

SQL Object Description
“dbw_lock_#instance~id#” Records current sessions waiting due to blocking.
“dbw_lock_history_#instance~id#” Maintains historical data of blockings and locks.
“blocking_detector_#instance~id#” Procedure to identify and log blocking sessions based on specified thresholds.

Reports

By incorporating such monitoring tools, dbWatch Control Center ensures that the Oracle databases under its care operate optimally, with minimal downtimes and performance issues due to database locks.