Recent ask from mine friend gave me an idea how to expand information with waiting SQL statement itself (show real sql that is in waiter session waiting to be run).
Regardless the solution is very easy (just add next code in the end of existing sbr.sql script):
SELECT t.sql_text
INTO l_sql
FROM gv$sqlarea t,
gv$session s
WHERE t.address = s.sql_address AND
t.hash_value = s.sql_hash_value AND
s.inst_id=rec_waiters.inst_id AND
s.SID = rec_waiters.sid AND
s.sql_hash_value != 0
;
dbms_output.put_line(lpad(chr(9),9)|| chr(9)||
'('||
rpad(l_sql,100,' ')||
')'
);
I'll show the whole solution. sbr.sql script in complete code that include current improvement:CREATE OR REPLACE procedure TOOLS.show_blocked_records (
/* $Header: show_blocked_records.prc 1.1 09/20/2010 20:45 damirv $ */
/* ---------------------------------------------------------------------------
Copyright(C) 1995-2010 TEB Informatika d.o.o.
Filename: sbr.sql (show_blocked_records)
CR/TR# :
Purpose : Find blocking records by locked session for RAC and non RAC environment
Resulting SQL shows rows which are locked and their release will continue
running blocking session.
Excellent way to find hot records (hot tables)
Date : 19.05.2010.
Author : Damir Vadas, damir.vadas@gmail.com
Remarks : Tested on 10g/11g (should work on lower versions as well)
"c_nr_cols" define number of columns (without pk columns!) of generated sql
change it to your needs
Changes (DD.MM.YYYY, Name, CR/TR#):
12.07.2010 Damir Vadas
added support for current waiter sql statement and
19.08.2010 Damir Vadas
added exception block for no_data_found case
added "substr" for l_sql parse
01.09.2010 Damir Vadas
fixed bug blockers and waiters to be 11g compatible
03.09.2010 Damir Vadas
added "Locked object is not table" for "starnge" locks in 11g
20.09.2010 Damir Vadas
Reunited logic for script/procedure (change value for b_is_script)
--------------------------------------------------------------------------- */
s_mail_text OUT VARCHAR2 )
AS
b_is_script CONSTANT BOOLEAN DEFAULT TRUE;
const_nr_cols CONSTANT PLS_INTEGER := 3;
--s_mail_text VARCHAR2(4000);
db_ver VARCHAR2(128);
db_ver2 VARCHAR2(128);
-- blockers ....
CURSOR c_blockers IS
SELECT DISTINCT
NVL(username,'BLOCKER ???') blocker_user,
gvb.sid,
gvs.serial# serial,
gvb.inst_id,
gvb.ctime ,
gvs.status STATUS,
module,
action,
decode(gvb.type,
'AE', 'Edition Lock',
'BL','Buffer hash table',
'CF','Control File Transaction',
'CI','Cross Instance Call',
'CS','Control File Schema',
'CU','Bind Enqueue',
'DF','Data File',
'DL','Direct-loader index-creation',
'DM','Mount/startup db primary/secondary instance',
'DR','Distributed Recovery Process',
'DX','Distributed Transaction Entry',
'FI','SGA Open-File Information',
'FS','File Set',
'IN','Instance Number',
'IR','Instance Recovery Serialization',
'IS','Instance State',
'IV','Library Cache InValidation',
'JQ','Job Queue',
'KK','Redo Log "Kick"',
'LS','Log Start/Log Switch',
'MB','Master Buffer hash table',
'MM','Mount Definition',
'MR','Media Recovery',
'PF','Password File',
'PI','Parallel Slaves',
'PR','Process Startup',
'PS','Parallel Slaves Synchronization',
'RE','USE_ROW_ENQUEUE Enforcement',
'RT','Redo Thread',
'RW','Row Wait',
'SC','System Commit Number',
'SH','System Commit Number HWM',
'SM','SMON',
'SQ','Sequence Number',
'SR','Synchronized Replication',
'SS','Sort Segment',
'ST','Space Transaction',
'SV','Sequence Number Value',
'TA','Transaction Recovery',
'TD','DDL enqueue',
'TE','Extend-segment enqueue',
'TM','DML enqueue',
'TO', 'Temp Object',
'TS','Temporary Segment',
'TT','Temporary Table',
'TX','Transaction',
'UL','User-defined Lock',
'UN','User Name',
'US','Undo Segment Serialization',
'WL','Being-written redo log instance',
'WS','Write-atomic-log-switch global enqueue',
'XA','Instance Attribute',
'XI','Instance Registration',
decode(substr(gvb.type,1,1),
'L','Library Cache ('||substr(gvb.type,2,1)||')', -- Library cache lock instance lock (A..P = namespace)
'N','Library Cache Pin ('||substr(gvb.type,2,1)||')', -- Library cache pin instance (A..Z = namespace)
'Q','Row Cache ('||substr(gvb.type,2,1)||')', -- Row cache instance (A..Z = cache)
'????'
),
gvb.type
) lock_type
FROM gv$lock gvb, gv$lock gvw, gv$session gvs
WHERE (gvb.id1, gvb.id2) in (
SELECT id1, id2 FROM gv$lock WHERE request=0
INTERSECT
SELECT id1, id2 FROM gv$lock WHERE lmode=0
)
AND gvb.id1=gvw.id1
AND gvb.id2=gvw.id2
AND gvb.request=0
AND gvw.lmode=0
AND gvb.sid=gvs.sid
AND gvb.inst_id=gvs.inst_id
ORDER BY CTIME desc
;
--waiters
CURSOR c_waiters (cp_blocker_waiter_sid gv$lock.sid%TYPE, cp_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
SELECT
NVL(username,'WAITER ???') waiter_user,
gvs.sid,
gvs.serial# serial,
gvs.inst_id,
gvw.ctime ,
gvs.status STATUS,
module,
action,
decode(gvw.request,
0, 'None',
1, 'NoLock',
2, 'Row-Share',
3, 'Row-Exclusive',
4, 'Share-Table',
5, 'Share-Row-Exclusive',
6, 'Exclusive',
'Nothing-'
) lock_req
FROM gv$lock gvw, gv$session gvs
WHERE 1=1
AND gvw.request>0
AND gvw.lmode=0
AND gvw.sid=gvs.sid
AND gvw.inst_id=gvs.inst_id
AND gvs.blocking_session=cp_blocker_waiter_sid
AND gvs.blocking_instance=cp_blockers_waiter_inst_id
ORDER BY CTIME desc
;
-- blocked records (this is allways one object with one locked row)
CURSOR c_blocked_objects (cp_sid IN PLS_INTEGER, cp_inst_id IN PLS_INTEGER) IS
SELECT do.owner,
do.object_name,
do.object_type,
dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) RID
FROM gv$session s
,dba_objects do
WHERE inst_id=cp_inst_id
AND sid=cp_sid
AND s.ROW_WAIT_OBJ# = do.OBJECT_ID
;
-- columns which user want to see (pk keys are excluded because they are shown by default!)
CURSOR c_cols (cp_owner IN VARCHAR2, cp_table IN VARCHAR2, cc_nr_cols IN PLS_INTEGER) IS
WITH q as (
SELECT column_name
FROM all_tab_columns
WHERE owner= cp_owner
AND table_name = cp_table
AND column_name NOT IN ( SELECT b.column_name column_name_pk
FROM all_constraints a,
all_cons_columns b
WHERE a.OWNER = cp_owner
AND a.table_name = cp_table
AND a.constraint_type='P'
AND a.constraint_name=b.constraint_name
)
ORDER BY column_id
)
SELECT q.column_name
FROM q
WHERE rownum<=cc_nr_cols
;
-- pk_key columns (always shown in the front)
CURSOR c_pkeys (cp_owner IN VARCHAR, cp_table IN VARCHAR) IS
SELECT b.column_name column_name_pk
FROM all_constraints a,
all_cons_columns b
WHERE a.owner = cp_owner
AND a.table_name = cp_table
AND a.constraint_type='P'
AND a.constraint_name=b.constraint_name
ORDER BY position
;
stmt VARCHAR2 (4000);
-- for showing current waiter sql
l_sql VARCHAR2 (4000);
l_can_obtain_locked_row boolean;
l_temp VARCHAR2(64);
-- for finding locked record in non DML blocks
l_rowid ROWID;
PROCEDURE add_message_line (p_text VARCHAR2)
IS
BEGIN
IF b_is_script THEN
dbms_output.put_line (p_text);
ELSE
s_mail_text := s_mail_text || p_text || chr(10);
END IF;
END;
FUNCTION create_SELECT_stmt (p_owner VARCHAR2,
p_object_name VARCHAR2,
p_row_id VARCHAR2,
p_nr_cols NUMBER DEFAULT const_nr_cols
) RETURN VARCHAR2
IS
BEGIN
stmt := 'SELECT ';
-- place pks cols in the front of select
FOR rec_pkeys IN c_pkeys (p_owner, p_object_name) LOOP
stmt := stmt || rec_pkeys.column_name_pk ||' , ';
END LOOP;
-- then show other "c_nr_cols" number of cols (pks are not counted)
FOR rec_cols IN c_cols (p_owner, p_object_name, const_nr_cols) LOOP
stmt := stmt || rec_cols.column_name || ' , ' ;
END LOOP;
-- remove last added " , "
IF SUBSTR (stmt,LENGTH(stmt)-2)=' , ' THEN
stmt := SUBSTR (stmt,1, (LENGTH(stmt)-3));
END IF;
stmt := stmt || ' FROM ' || p_owner ||'.'|| p_object_name ;
-- get involved rowid for this session (there is always one blocking row!)
stmt := stmt || ' WHERE rowid = ' || ''''|| p_row_id || '''' || ' ;';
return stmt;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;
BEGIN
IF b_is_script THEN
dbms_output.put_line (chr(9));
dbms_utility.db_version(db_ver,db_ver2);
dbms_output.put_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
END IF;
dbms_output.put_line (chr(9));
FOR rec_blockers IN c_blockers LOOP
add_message_line(rec_blockers.blocker_user||' ('||rec_blockers.inst_id|| ' '''||rec_blockers.sid||','||rec_blockers.serial||''')');
FOR rec_waiters IN c_waiters (rec_blockers.sid,rec_blockers.inst_id) LOOP
IF b_is_script THEN
add_message_line (chr(255)||
rpad(rec_waiters.waiter_user||' (' || rec_waiters.inst_id||' '||rec_waiters.sid||','||rec_waiters.serial||')',20,' ')||
lpad(to_char(rec_waiters.ctime),6,' ')||' sec '||
lpad(to_char(rec_waiters.lock_req),13,' ')||' '||
lpad(rec_waiters.status,15,' ')|| ' '||
lpad(to_char(nvl(rec_waiters.module,'?')),15,' ')
);
ELSE
add_message_line (chr(9)|| chr(9)||
rpad(rec_waiters.waiter_user||' (' || rec_waiters.inst_id||' '||rec_waiters.sid||','||rec_waiters.serial||')',20,' ')||
lpad(to_char(rec_waiters.ctime),6,' ')||' sec '||
lpad(to_char(rec_waiters.lock_req),13,' ')||' '||
lpad(rec_waiters.status,15,' ')|| ' '||
lpad(to_char(nvl(rec_waiters.module,'?')),15,' ')
);
END IF;
stmt := null;
FOR rec_blocked_objects IN c_blocked_objects (rec_waiters.sid,rec_waiters.inst_id) LOOP
stmt := 'SELECT ';
-- place pks cols in the front of select
FOR rec_pkeys IN c_pkeys (rec_blocked_objects.owner, rec_blocked_objects.object_name) LOOP
stmt := stmt || rec_pkeys.column_name_pk ||' , ';
END LOOP;
-- then show other "c_nr_cols" number of cols (pks are not counted)
FOR rec_cols IN c_cols (rec_blocked_objects.owner, rec_blocked_objects.object_name, const_nr_cols) LOOP
stmt := stmt || rec_cols.column_name || ' , ' ;
END LOOP;
-- remove last added " , "
IF SUBSTR (stmt,LENGTH(stmt)-2)=' , ' THEN
stmt := SUBSTR (stmt,1, (LENGTH(stmt)-3));
END IF;
stmt := stmt || ' FROM ' || rec_blocked_objects.owner ||'.'|| rec_blocked_objects.object_name ;
-- get involved rowid for this session (there is always one blocking row!)
stmt := stmt || ' WHERE rowid = ' || ''''|| rec_blocked_objects.RID || '''' || ' ;';
add_message_line(chr(9)|| stmt);
-- current sql statement support
BEGIN
SELECT substr(t.sql_text,1,3999)
INTO l_sql
FROM gv$sqlarea t,
gv$session s
WHERE t.address = s.sql_address AND
t.hash_value = s.sql_hash_value AND
s.inst_id=rec_waiters.inst_id AND
s.SID = rec_waiters.sid AND
s.sql_hash_value != 0
;
IF b_is_script THEN
add_message_line(chr(9)||
'('||
l_sql||
')'
);
ELSE
add_message_line(chr(9)|| chr(9)||
'('||
l_sql||
')'
);
END IF;
EXCEPTION
WHEN no_data_found THEN
add_message_line('Cannot retrieve SQL in this moment. Please try again!');
END;
END LOOP;
IF stmt is null THEN
FOR rec_blocked_objects IN c_blocked_objects (rec_blockers.sid,rec_blockers.inst_id) LOOP
add_message_line(chr(9)||chr(9)|| 'Locked object is not table: '||rec_blocked_objects.owner||'.'||rec_blocked_objects.object_name||' ('||rec_blocked_objects.object_type||')');
END LOOP;
END IF;
END LOOP;
END LOOP;
s_mail_text := RTRIM (s_mail_text, chr(9));
-- chr(9) is the only content
IF length(s_mail_text) IN (0,1) THEN
s_mail_text := null;
END IF;
END;
/
Quick example
Suppose we have a table xx with one column (C1) and two rows in it as:SQL>select * from xx; C1 ---------- 2 3 SQL>Here is SQL from blocker's session:
SQL>update xx set c1='1' where c1='2'; 1 row updated. SQL>And in another (waiter session) there is SQL:
SQL>update xx set c1='123';This command "hang" ... waiting for lock to be released! In third (monitoring session) we can call previously shown sbr.sql script:
SQL>@sbr DAMIRV (1 '439,2117') SYS 1 499,25445 40 sec Exclusive ACTIVE SQL*Plus SELECT C1 FROM DAMIRV.XX WHERE rowid = 'AABIYyAAKAAALONAAA' ; (update damirv.xx set c1='123' ) PL/SQL procedure successfully completed. SQL>Interpetation is very obvious. SYS session (inst_id=1, 499,25445) is blocked by DAMIRV session (inst_id=1, 439,2117). SYS session wait for the release of record:
SELECT C1 FROM XX WHERE rowid = 'AABIYyAAKAAALONAAA' ;And SYS has executed SQL (which is hanged):
update damirv.xx set c1='123'What shows all important data about any kind of lock!
Be proactive
If you incorporate mentioned sbr.sql port of code into sb.sql script in a way that create show_blocked_records function that return blocking information (rather then use dbms_output.put_line kind of output), then all you have to do is insert following lines inside existing code:-- previous code from sb.sql
...
IF bHasAny THEN -- this line already exist
add_message_line (chr(9));
show_blocked_records (s_blocked_rows);
add_message_line (s_blocked_rows);
...
-- following code from sb.sql
you'll get complete blocking monitoring system with full explanation of the problem.
The End
I have rearranged sbr.sql script to understand locking that was shown on Deadlock on Oracle 11g but Not on 10g. This is why I put part of code that shows locked object in a case that it is not table. Here is output of mine function for his example. Here oner of all objects is DAMIRV:Oracle version: 11.2.0.1.0 (11.1.0.0.0)
DAMIRV (1 '133,10')
DAMIRV 1 18,9 1596 sec Share-Row-Exc ACTIVE SQL*Plus
Locked object is not table: DAMIRV.SYS_C0022027 (INDEX)
PL/SQL procedure successfully completed.
SQL>
As you see sometimes fk index can be a power full blocker.Cheers!
