Blocking detector for RAC

Job details

Name:	Blocking detector for RAC
Platform:	Oracle
Category:	Cluster and Replication
Description:	Checks if a session is waiting on a TX (transaction) lock. (RAC only)
Long description:	Task checks if a session is waiting on a TX (transaction) lock. (RAC only)
Version:	2.8
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’ & iscluster=‘1′ & eleven_and_above=‘1′ & _priv_read_gv_lock = ‘1′ & _priv_read_gv_session = ‘1′ & _priv_read_gv_sqlarea = ‘1′ & _priv_read_dba_objects = ‘1′]

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 gv$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 and detect transaction lock blockages in Oracle Real Application Clusters (RAC).
Why: This job is critical as it helps in identifying sessions that are waiting on transaction (TX) locks in a RAC environment. Prompt detection aids in mitigating database performance issues caused by blocked transactions which can potentially escalate into more severe database lockups affecting multiple users and applications.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT s.inst_id, s.sid, l.type, l.id1 FROM gv$lock l JOIN gv$session s ON l.sid = s.sid WHERE l.request > 0 ORDER BY l.ctime DESC;

Job Implementation Details

“Object: blocking_detector_rac” – The main object and focal point of this job implementation.
“Description: Checks if a session is waiting on a TX (transaction) lock. (RAC only)” – Provides a brief overview of the job’s function.

CREATE OR REPLACE PROCEDURE blocking_detector_rac(taskid IN NUMBER) IS
... (Procedure Implementation Omitted for Brevity)
END blocking_detector_rac;

Dependencies and Scheduled Executions

Scheduled to run at minute intervals of every 4 minutes from 6 AM to 6 PM every day using the cron format setting.
Dependencies include access to several Oracle dynamic performance views such as gv$LOCK, gv$SESSION, gv$SQLAREA, and DBA_OBJECTS, ensuring that necessary permissions are granted to access these components.
Creates and utilizes database tables like “dbw_lock_rac” and “dbw_lock_rac_history” for recording and historical tracking of session locks.

Job Configuration and Parameters

“Warning threshold locked time” – Defines the time in seconds that a session should be locked before triggering a warning state (default is 300 seconds or 5 minutes).
“Include background processes” – Boolean parameter to determine whether locks associated with background processes should be tracked (default is NO).
“Alarm threshold locked time” – Configures the time in seconds a session can remain locked before escalating to an alarm state (default is 7200 seconds or 2 hours).

Reporting Structure

The job provides insightful reports through presentations titled ‘Current locks’ and ‘Blocking history’ which display ongoing and historical lock information.

Tables and Presentation Details

Presentation Title	Description	SQL Call
Current locks	Shows current blocking locks with details like instance name, session ID, lock mode, object ID, and object name.	SELECT /+ RULE / i.instance_name		‘ inst_id(‘		t1.inst_id		‘)’ “Instance name”,t1.session_id “Sid”, t1.locked_mode “Locked mode”,to_char(t2.object_id) “Object ID”, t2.object_name “Object name” FROM gv$locked_object t1 JOIN dba_objects t2 ON t1.object_id = t2.object_id JOIN gv$instance i ON t1.inst_id = i.inst_id ORDER BY 4,1 ASC
Blocking history	Visualizes history data concerning blocked sessions and objects, highlighting vital information collected in the procedure.	SELECT w_sql “Statement”, b_obj_owner “Owner”, b_obj_name “Name”, count() “Count”, max(HISTR_DATE) “Last date” FROM dbw_lock_rac_history GROUP BY w_sql, b_obj_owner, b_obj_name UNION ALL SELECT w_sql “Statement”, b_obj_owner “Owner”, b_obj_name “Name”, count() “Count”, max(HISTR_DATE) “Last date” FROM dbw_lock_rac GROUP BY w_sql, b_obj_owner, b_obj_name ORDER BY 5 DESC

This job configuration and setup reflect a robust system aimed at maintaining database performance and reliability, particularly in clustered environments where transaction locks can have widespread impacts.