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

Purpose: The purpose of this job is to monitor the Oracle database instances for sessions that are waiting on transaction locks (TX locks).
Why: This job is critical to maintain database performance and avoid potential deadlock scenarios or prolonged wait times that can significantly affect application performance. By detecting sessions waiting on transaction locks, necessary actions can be taken to resolve the issue, ensuring smooth and efficient database operations.
Manual checking: You can check this manually in the database by issuing these SQL commands:

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

Job version: 3.0
Company: dbWatch.com
Default schedule: Every 4 minutes from 6 AM to 6 PM every day (`0,4,8,12,16,20,24,28,32,36,40,44,48,52,56 at minute 6-18 every day`)

Implementation Details

The job involves the creation of two tables (`dbw_lock_#instance~id#`, `dbw_lock_history_#instance~id#`) and a procedure (`blocking_detector_#instance~id#`) in the Oracle database.
The procedure is designed to detect blocking sessions and record these sessions’ details into `dbw_lock_#instance~id#`. Historical data is maintained in `dbw_lock_history_#instance~id#` for comparative and evidential purposes.

Dependencies

Dependencies on itself (`blocking_detector_#instance~id#`) and supportive tables (`dbw_lock_#instance~id#` and `dbw_lock_history_#instance~id#`).
Cleanup is initiated on fail, ensuring that unsuccessful or partial executions do not leave the system in an inconsistent state.

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

A set of reports are generated to provide insights into current and historical blocking events.
Reports include detailed session information such as SID, username, lock type, and actions that can be taken (such as kill commands for the sessions causing blocks).

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.