Blocking Locks

Procedure Call

blocking_lock_alert (#DBID#, #FREQUENCY#, #ALERTVALUE#, #ALERTSTRING#)

Description

In Ignite 6.4, this alert will provide notification of locking problems in the selected databases. Based on that notification, you can drill into the locking problems within the Ignite GUI to determine the root cause of the issues, i.e. the blocking sessions.

PL/SQL code for Blocking_Lock_Alert procedure

CREATE OR REPLACE PROCEDURE blocking_lock_alert (

p_db_id IN NUMBER,

p_frequency IN NUMBER,

p_alert_value OUT NUMBER,

p_alert_msg OUT VARCHAR2) IS

sSQL VARCHAR2(2000) := '';

sDBName VARCHAR2(100);

nCompareIEDX NUMBER := concap.convert_from_date(SYSDATE - p_frequency/1440);

nLockTime INTEGER;

BEGIN

-- get the database name

SELECT name INTO sDBName FROM cond WHERE dbid = p_db_id;

-- get blocking locks info

sSQL := 'select nvl(sum(tt.qp/100),0) timesecs ';

sSQL := sSQL || 'from consw_'||p_db_id||' sw, contt_'||p_db_id||' tt, conev_'||p_db_id||' ev ';

sSQL := sSQL || 'where sw.iedx = CONCAP.CONVERT_FROM_DATE(TRUNC(tt.iedx, ''MI'')) ';

sSQL := sSQL || 'and sw.iedx > :1 ';

sSQL := sSQL || 'and blee is not null ';

sSQL := sSQL || 'and sw.keeq = ev.id ';

sSQL := sSQL || 'and ev.blockee = ''Y'' ';

EXECUTE IMMEDIATE sSQL INTO nLockTime USING nCompareIEDX;

p_alert_value := nLockTime;

p_alert_msg := 'Locking problems were found on the ' || sDBName || ' database which has caused '

|| nLockTime || ' seconds of wait time.';

EXCEPTION

WHEN NO_DATA_FOUND THEN

p_alert_value := 0;

p_alert_msg := ' ';

END blocking_lock_alert;

lock_alert;

/

For more information about Writing Custom Alerts, see PG110.