Another part of "blocking session" issues are blocking session that occur undetected. If you haven't implemented proactive blocking session monitoring then this event is 100% probable to happened sooner or later.In mine previous two post on blocking session theme (Blocking session ... detect, unblock, notify and Blocking session ... detect, unblock, notify (Part II)) I have explained how to handle that problem in praxis.
Because blocking session present normal state when action in one session prevent execution of other session(s), deeper investigation on cause of this subject should be very common task of all DBA's and developers as well. After making all preventing steps in proactive reaction, last step is to find the root cause of blocking issues.
Because of mine luck, that from past experience, I wasn't involved in any serious locking "situations" where I could do something special, Oracle EBS (not locking at all because it has no foreign keys-app level of relation, smaller ERP's (where transactions was to short to see some problems at this level), this is the main reason why blocking session on record level was not part of my experience and interest. Frankly, there was one system, where I was working as DBA, where blocking sessions occur very frequently and cause big problems, but there the problem was totally wrong design where one table has records that were too much popular and this is find out very soon so no other investigation was not needed!
So I was really surprised when a friend of mine, Mirko, come to me with question: "How to see which table record is blocked by other session?". And this is the moment when this theme was born...
The problem
Test will be on table EMP. Just for easier reference let me show it's content.SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 17.11.81 5000 10
7698 BLAKE MANAGER 7839 01.05.81 2850 30
7782 CLARK MANAGER 7839 09.06.81 2450 10
7566 JONES MANAGER 7839 02.04.81 2975 20
7788 SCOTT ANALYST 7566 09.12.82 3000 20
7902 FORD ANALYST 7566 03.12.81 3000 20
7369 SMITH CLERK 7902 17.12.80 800 20
7499 ALLEN SALESMAN 7698 20.02.81 1600 300 30
7521 WARD SALESMAN 7698 22.02.81 1250 500 30
7654 MARTIN SALESMAN 7698 28.09.81 1250 1400 30
7844 TURNER SALESMAN 7698 08.09.81 1500 0 30
7876 ADAMS CLERK 7788 12.01.83 1100 20
7900 JAMES CLERK 7698 03.12.81 950 30
7934 MILLER CLERK 7782 23.01.82 1300 10
14 rows selected.
SQL>
Suppose user1 make update statement like:USER1@db1> @my_sid USER INSTANCE SID SERIAL# ------------------------------ ------------ ---------- ---------- USER1 db1 307 292 USER1@db1> update emp set ename = '????' ; 14 rows updated. USER1@db1>When user2 try to execute his update statement, he will be block:
USER2@db1> @my_sid USER INSTANCE SID SERIAL# ------------------------------ ------------ ---------- ---------- USER2 db1 308 890 USER2@db1> update emp set ename = 'NEW_NAME' where empno= 7654;running sb.sql (mentioned in one of mine previous topic) you notify that session 307 (blocker) is blocking other session 308 (waiter) :
DAMIRV@db1> @sb
Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker Inst SID Serial [sec] Lock Type Status Module
------------------------------------------------------------------------------------------------------------------------
1. USER1 1 307 292 48 Transaction INACTIVE SQL*Plus
USER2 1 308 890 42 Exclusive INACTIVE SQL*Plus
To kill first from the list, perform:
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT SESSION '307,292' IMMEDIATE;
ALTER SYSTEM KILL SESSION '307,292' IMMEDIATE;
RAC (logged on any node) :
--------------------------
declare
v_job binary_integer;
begin
DBMS_JOB.submit ( job =>v_job
,what =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''307,292'''' IMMEDIATE''; end; '
,instance=>1
);
commit;
end;
/
PL/SQL procedure successfully completed.
DAMIRV@db1>
Regarding involved commands you may find:Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker Inst SID Serial [sec] Lock Type Status Module
------------------------------------------------------------------------------------------------------------------------
1. USER1 1 307 292 48 Transaction INACTIVE SQL*Plus
USER2 1 308 890 42 Exclusive INACTIVE SQL*Plus
To kill first from the list, perform:
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT SESSION '307,292' IMMEDIATE;
ALTER SYSTEM KILL SESSION '307,292' IMMEDIATE;
RAC (logged on any node) :
--------------------------
declare
v_job binary_integer;
begin
DBMS_JOB.submit ( job =>v_job
,what =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''307,292'''' IMMEDIATE''; end; '
,instance=>1
);
commit;
end;
/
PL/SQL procedure successfully completed.
DAMIRV@db1>
- sql from blocker session
- sql from waiter session
Because case nr. 1) is harder to get (remember that commands in blocker session can continue to execute after "blocking statement" has been executed...so this sql what cause block is gone), here is an example for second case (find waiter sql).
DAMIRV@db1> @si 1 308 Instance ID = 1 SID = 308 Current SQL....have result if session is active.... SQL_TEXT IID HASH_VALUE OPTIMIZER_COST ---------------------------------------------------------------------- ---- ---------- -------------- update emp set ename = 'NEW_NAME' where empno= 7654 1 229437123 1 1 row selected. Binding values.... no rows selected Previous SQL.... SQL_TEXT IID HASH_VALUE OPTIMIZER_COST ---------------------------------------------------------------------- ---- ---------- -------------- BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 1 1029988163 0 1 row selected. IID SID_SERIAL AUDSID ORAPID DBPID APPPID USERNAME MODULE ACTION STATUS ---- ----------- ---------- ------- ------ ---------- ---------- ------------- --------------- -------- 1 308, 890 205187 32 356 1640:2780 USER2 SQL*Plus ACTIVE 1 row selected. DAMIRV@db1>sqlAnd, in this case, the result, which record is blocking this session is obvious! So by killing the session 307 on instance 1 would give green light for our waiter session to continue running.
But as I said in real life situation may be (and always is by Murphy) different and worse. Let us make situation where user1 (blocker) perform:
update emp set ename='123' where empno=7654;and then with user2 (which will become a "waiter") execute
update emp set ename='xxx';Suppose also that in the meantime, in the blocker session some others sqls are performed (as they are in real life when you run PL/SQL block). In that situation there is no way to find out rows that originally cause the block in previously described way because blocker sql has been changed, and through waiter sql we cannot determine which row is blocking it because we are updating many of them.
The solution
For that we should use another columns from gv$session (row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#) and arrange that with some foreign tables to get more readable output. The result is sbr.sql, which expose problems on record level.
/* ---------------------------------------------------------------------------
Copyright(C) 1995-2020 Vadas savjetovanje 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
Lock types can be retrieved by :
SELECT type, name, is_user, description FROM V$LOCK_TYPE ORDER BY is_user DESC, type;
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)
19.10.2010 Damir Vadas
Added "l_sql := ''; -- not null !"
09.09.2013 Damir Vadas
Adopted to write logs to tools.blocker_logs table ...
this why procedure becomes AUTONOMOUS_TRANSACTION
blocking session is if last for at least 120 seconds
added "p_write_to_table" parameter
true write blocking information to tools.blocker_logs table
false do not write
removed all VARCHAR2 and introduced CLOB (s_mail_text, l_sql)
22.01.2014 Damir Vadas
20=>30 (line rpad(rec_waiters.waiter_user||' (' || rec_waiters.inst_id||' '||rec_waiters.sid||','||rec_waiters.serial||')',20,' ')||)
31.03.2014 Damir Vadas
Fixed select statement which was run against indexes
23.08.2017 Damir Vadas
added @sash support for quick investgation and ctime for it
28.11.2017 Damir Vadas
now is all faster!
removed gv$sqlarea query
added hint /*+ CPU_COSTING
SELECT
LPAD(' ', (level-1)*2, chr(9)) || NVL(s.username, '(oracle)') || ' (@sash '|| s.inst_id || ' '|| s.sid || ' ' ||s.serial# || ' '|| ceil (s.LAST_CALL_ET/60) ||')',
s.status,
s.module,
blocking_session
FROM gv$session s
WHERE 1=1
AND level > 1
OR EXISTS (SELECT 1 FROM gv$session WHERE blocking_session = s.sid AND inst_id=s.inst_id)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
20.08.2020 Damir Vadas
Change lock output
added hint INDEX(@SEL$11 C I_CDEF2)
redisned the whole code
--------------------------------------------------------------------------- */
@sqlplus_default;
set serveroutput on size unlimited;
SET FEEDBACK OFF
declare
const_nr_cols CONSTANT PLS_INTEGER := 3;
db_ver VARCHAR2(128);
db_ver2 VARCHAR2(128);
--
-- blockers ....
CURSOR c_blockers IS
SELECT level,
gvs.inst_id,
gvs.sid,
gvs.serial#,
gvs.username blocker_user,
LPAD(' ', (level-1)*2, chr(9)) || NVL(gvs.username, '(oracle)') || ' (@si '|| gvs.inst_id || ' '|| gvs.sid || ' ' ||gvs.serial# || ' ' || ceil (gvs.LAST_CALL_ET/60) ||') ' || gvs.status || ' ,' ||lpad(to_char(nvl(gvs.module,'?')),15,' ') BLOCK_TREE,
gvs.status,
gvs.blocking_session
FROM gv$session gvs
WHERE 1=1
AND level > 1
OR EXISTS (SELECT 1 FROM gv$session WHERE blocking_session = gvs.sid AND inst_id=gvs.inst_id)
CONNECT BY PRIOR gvs.sid = gvs.blocking_session
START WITH gvs.blocking_session IS NULL
;
-- blocked records (this is allways one object with one locked row)
CURSOR c_blocked_objects (p_inst_id IN NUMBER, p_sid IN NUMBER, p_serial# IN NUMBER) 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,
s.ROW_WAIT_OBJ# ROW_WAIT_OBJ,
s.event
FROM gv$session s
,dba_objects do
WHERE inst_id=p_inst_id
AND sid=p_sid
AND serial#=p_serial#
AND s.ROW_WAIT_OBJ# = do.OBJECT_ID
;
CURSOR c_blocked_objects2 (p_inst_id IN NUMBER, p_sid IN NUMBER, p_serial# IN NUMBER) IS
SELECT do.owner,
do.object_name,
do.object_type
FROM gv$session s
,dba_objects do
WHERE blocking_instance=p_inst_id
AND BLOCKING_SESSION=p_sid
AND serial#=p_serial#
AND s.ROW_WAIT_OBJ# = -1
AND DO.OBJECT_ID = P2
;
-- 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 EXISTS (SELECT 1
FROM all_cons_columns b JOIN all_constraints a ON (b.owner = a.owner AND a.constraint_name = b.constraint_name)
WHERE 1 = 1 AND a.constraint_type = 'P' AND a.OWNER = cp_owner AND a.table_name = cp_table
)
ORDER BY column_id
)
SELECT /*+ INDEX(@SEL$11 C I_CDEF2) */ 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_cons_columns b JOIN all_constraints a ON (b.owner = a.owner AND a.constraint_name = b.constraint_name)
WHERE 1=1
AND a.constraint_type='P' AND a.OWNER = cp_owner AND a.table_name = cp_table
ORDER BY position ;
--
--stmt VARCHAR2 (4000);
-- for showing current waiter sql
--l_sql VARCHAR2 (4000);
FUNCTION add_indent_char RETURN CHAR
IS
BEGIN
RETURN chr(9);
END;
--
PROCEDURE add_message_line (p_text VARCHAR2, p_level IN NUMBER)
IS
BEGIN
dbms_output.put_line (LPAD(' ', (p_level-1)*2, add_indent_char) || p_text);
END;
--
FUNCTION get_curent_sql_stmt (p_inst_id IN NUMBER,
p_sid IN NUMBER,
p_serial# IN NUMBER
) RETURN VARCHAR2
IS
l_retval varchar2 (4000);
BEGIN
-- current sql statement support
BEGIN
SELECT
CASE
WHEN sql_fulltext is null then
CASE
WHEN s.sql_id is null then
'@sql_id2 '|| p_inst_id ||' '||s.prev_sql_id|| '*/'|| s.PREV_HASH_VALUE
ELSE
'@sql_id2 '|| p_inst_id ||' '||s.sql_id|| '/'|| t.PLAN_HASH_VALUE
END
ELSE
CASE
WHEN s.sql_id is null then
'@sql_id2 '|| p_inst_id ||' '||s.prev_sql_id|| '*/'|| s.PREV_HASH_VALUE
ELSE
'@sql_id2 '|| p_inst_id ||' '||s.sql_id|| '/'|| t.PLAN_HASH_VALUE
END || ', ' || dbms_lob.substr(sql_fulltext,80,1)
END sql_data
INTO l_retval
FROM gv$sqlarea t,
gv$session s
WHERE 1=1 AND
t.address (+)= s.sql_address AND
t.hash_value (+)= s.sql_hash_value AND
s.inst_id=p_inst_id AND
s.SID = p_sid AND s.serial#=p_serial#
;
EXCEPTION
WHEN OTHERS THEN
l_retval := SQLERRM;
END;
return l_retval;
END;
--
FUNCTION create_SELECT_stmt (p_owner VARCHAR2,
p_object_name VARCHAR2,
p_row_id rowid, --VARCHAR2,
p_event VARCHAR2,
p_blocking_session NUMBER,
p_nr_cols NUMBER DEFAULT const_nr_cols
) RETURN VARCHAR2
IS
l_retval VARCHAR2(4000);
BEGIN
CASE
WHEN p_blocking_session is null THEN
l_retval := ' -- TOP BLOCKER --';
ELSE
l_retval := 'SELECT ';
-- place pks cols in the front of select
FOR rec_pkeys IN c_pkeys (p_owner, p_object_name) LOOP
l_retval := l_retval || 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
l_retval := l_retval || rec_cols.column_name || ' , ' ;
END LOOP;
-- remove last added " , "
IF SUBSTR (l_retval,LENGTH(l_retval)-2)=' , ' THEN
l_retval := SUBSTR (l_retval,1, (LENGTH(l_retval)-3));
END IF;
-- 1.3
-- when no data is returned - no index!!!
CASE
WHEN l_retval = 'SELECT ' THEN
l_retval := 'SELECT * FROM ' || p_owner ||'.'|| p_object_name ;
-- get involved rowid for this session (there is always one blocking row!)
l_retval := l_retval || ' WHERE rowid = ' || ''''|| p_row_id || '''' || ' ;';
l_retval := l_retval || add_indent_char || '('|| p_event ||')';
--WHEN p_event ='enq: TX - row lock contention' THEN
--l_retval := 'Problem is NOT in INDEX: ' || p_owner ||'.' || p_object_name;
ELSE
null;
END CASE;
END CASE;
return l_retval;
EXCEPTION
WHEN OTHERS THEN
RETURN SQLERRM;
END;
BEGIN
add_message_line (chr(9),0);
dbms_utility.db_version(db_ver,db_ver2);
add_message_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')', 0);
add_message_line (chr(9),0);
FOR rec_blockers IN c_blockers LOOP
add_message_line(rec_blockers.BLOCK_TREE, 0);
add_message_line('('||get_curent_sql_stmt (rec_blockers.inst_id, rec_blockers.sid, rec_blockers.serial#)||')',rec_blockers.level+1);
FOR rec_blocked_objects IN c_blocked_objects (rec_blockers.inst_id,rec_blockers.sid,rec_blockers.serial#) LOOP
add_message_line(create_SELECT_stmt (rec_blocked_objects.owner, rec_blocked_objects.object_name,rec_blocked_objects.RID,rec_blocked_objects.event,rec_blockers.blocking_session),rec_blockers.level+1);
END LOOP;
END LOOP;
END;
/
SET FEEDBACK ON
The result is:SQL>@sbr Oracle version: 12.2.0.1.0 (12.2.0.1) SYS (@si 1 426 49238 58) INACTIVE , SQL*Plus (@sql_id2 1 2fyw2psbd23vx*/382799741) SYS (@si 1 244 37701 58) ACTIVE , SQL*Plus (@sql_id2 1 2fyw2psbd23vx/1783341450, update t1 set c1='A1' where c1='x') SELECT * FROM SYS.T1 WHERE rowid = 'AA8YwSAABAAAAlpAAA' ; (enq: TX - row lock contention) SYS (@si 1 250 18808 57) ACTIVE , SQL*Plus (@sql_id2 1 6n5w8gmumgu19/1783341450, update t1 set c1='x' where c1='A2') SELECT * FROM SYS.T1 WHERE rowid = 'AA8YwSAABAAAAlpAAB' ; (enq: TX - row lock contention) Elapsed: 00:00:03.22 SQL>SELECT * FROM SYS.T1 WHERE rowid = 'AA8YwSAABAAAAlpAAA'; C1 ---------- x 1 row selected.Based on blocking information from database views, script dynamically generate sql statement whose result shows which record in "waiter" sessions are waiting for release from blocking session action (commit or rollback). The only constant in script is c_nr_cols, which represent number of columns which will be included in generated sql result. Default is 3, but this exclude pk columns, which are always shown at the beginning of generated sql. In fact, script use data from gv$session, view which can help identifying records rowids of blocked sessions.
If you run generated sql result, you see records that are waiting to be "unlocked". Notice that for each session there is only one record:
DAMIRV@db1> SELECT EMPNO , ENAME , JOB , MGR FROM DAMIRV.EMP WHERE rowid = 'AAAPMQAALAAEGHUAAJ' ;
EMPNO ENAME JOB MGR
---------- ---------- --------- ----------
7654 MARTIN SALESMAN 7698
1 row selected.
DAMIRV@db1>Let us check that these results are correct. Let us see records by rowids:DAMIRV@db1> select empno, ename, job, rowid
2 from emp
3 where rowid in ('AAAPMQAALAAEGHUAAJ');
EMPNO ENAME JOB ROWID
---------- ---------- --------- ------------------
7654 MARTIN SALESMAN AAAPMQAALAAEGHUAAJ
1 row selected.
DAMIRV@db1>Then by pk values:DAMIRV@db1> select empno, ename, job, rowid
2 from emp
3 where empno = 7654;
EMPNO ENAME JOB ROWID
---------- ---------- --------- ------------------
7654 MARTIN SALESMAN AAAPMQAALAAEGHUAAJ
1 row selected.
DAMIRV@db1> As you can see these queries generate the same outputs! Because blocking session was updating all the records in EMP table (ENAME column), this is the record that really represent blocked record (were unable to be processed in waiter session). Of course there were some other ways to see the blocking row but shown method present "out of the box" solution for any case.
To show complexity of the problem, here is one "real life" output.
Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker Inst SID Serial [sec] Lock Type Status Module
------------------------------------------------------------------------------------------------------------------------
1. PETR 2 648 18149 1295 Transaction INACTIVE ?
BIBE 2 651 14393 551 Share-Table INACTIVE ?
SAIG 4 732 16425 67 Exclusive INACTIVE ?
TAB 2 736 6976 11 Share-Table INACTIVE ?
ANRU 2 682 4379 38 Exclusive INACTIVE ?
BORO 2 725 4665 31 Share-Table INACTIVE ?
2. BORO 2 725 4665 150 Transaction ACTIVE ?
BIBE 2 651 14393 552 Share-Table ACTIVE ?
TAB 2 736 6976 12 Share-Table ACTIVE ?
ANRU 2 682 4379 38 Exclusive ACTIVE ?
SAIG 4 732 16425 68 Exclusive ACTIVE ?
BORO 2 725 4665 31 Share-Table ACTIVE ?
3. SAIG 4 732 16425 66 Transaction ACTIVE ?
BIBE 2 651 14393 552 Share-Table ACTIVE ?
SAIG 4 732 16425 68 Exclusive ACTIVE ?
TAB 2 736 6976 12 Share-Table ACTIVE ?
ANRU 2 682 4379 38 Exclusive ACTIVE ?
BORO 2 725 4665 31 Share-Table ACTIVE ?
PETR (2 '648,18149')
BIBE 2 651,14393 554 sec Share-Table ACTIVE ?
SELECT FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
SAIG 4 732,16425 70 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
TAB 2 736,6976 27 sec Share-Table ACTIVE ?
SELECT FROM TAB.SCP_BROJ_DOK WHERE rowid = 'AAAT6/AALAAAcduAAA' ;
ANRU 2 682,4379 53 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
BORO 2 725,4665 58 sec Share-Table ACTIVE ?
SELECT FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ; BORO (2 '725,4665')
BIBE 2 651,14393 579 sec Share-Table ACTIVE ?
SELECT FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
SAIG 4 732,16425 94 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
TAB 2 736,6976 51 sec Share-Table ACTIVE ?
SELECT FROM TAB.SCP_BROJ_DOK WHERE rowid = 'AAAT6/AALAAAcduAAA' ;
ANRU 2 682,4379 77 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
BORO 2 725,4665 84 sec Share-Table ACTIVE ?
SELECT FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ;
ILBA 1 751,4084 4 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
SAIG (4 '732,16425') BIBE 2 651,14393 616 sec Share-Table ACTIVE ?
SELECT FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
SAIG 4 732,16425 131 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
ANRU 2 682,4379 114 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
ILBA 1 751,4084 39 sec Exclusive ACTIVE ?
SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
BORO 2 725,4665 131 sec Share-Table ACTIVE ?
SELECT FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ;
IVBD 1 642,6661 52 sec Exclusive ACTIVE ?
SELECT GODINA , RBR , DATKNJ FROM TAB.P_DNEVNIK WHERE rowid = 'AAAUwzAAHAABxFoAAA' ;
TAB 2 736,6976 27 sec Share-Table ACTIVE ?
To kill first from the list, perform:
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT SESSION '648,18149' IMMEDIATE;
ALTER SYSTEM KILL SESSION '648,18149' IMMEDIATE;
RAC (logged on any node) :
--------------------------
declare
v_job binary_integer;
begin
DBMS_JOB.submit ( job =>v_job
,what =>'begin execute immediate ''ALTER SYSTEM DISCONNECT SESSION ''''648,18149'''' IMMEDIATE''; end; '
,instance=>2
);
commit;
end;
/
This is automated message so, do not reply to this mail.
Regards,
Your auto DBA d.o.o.
Zagreb, 01.06.2010 08:50:45Just to mention that for delete statements sbr.sql cannot generate blocked records!The end
Using this approach in blocking session issues, lead you to find "hot spot" tables or "hot spot" records in tables that present critical resources for your applications.On Oracle Metalink you may find interesting document ID 15476.1-"FAQ about Detecting and Resolving Locking Conflicts" which widely covers locking subjects and presents comprehensive knowledge for studding this issue deeper.
Just for your notes, ID 729727.1-"Detecting blocking Locks in 10.2 and above", which shows a way to find locking issues, is not working on Oracle 11.2, what is not in mine cases.
Now I really think that blocking session theme is covered from all points of view. If you find something that is still not, please speak out and I'll try to present the solution.
Until then...as allways...
Cheers!
Svaka cast na trudu! Htio sam isprobati tvoj sbr.sql skript, ali dobijam gresku:
ReplyDeleteSQL> @sbr
rpad(rec_waiters.waiter_user,10,' ')||
*
ERROR at line 141:
ORA-06550: line 141, column 24:
PLS-00201: identifier 'REC_WAITERS.WAITER_USER' must be declared
ORA-06550: line 139, column 7:
PL/SQL: Statement ignored
Dejane,
ReplyDeleteTHX for info...
Agrhhhhhh ... version control software!
Now is fixed!
Hi Damir,
ReplyDeleteIn 11R2 (don't know if R1) if you want to know which records are locked, you can use one cursor with "for update skip locked" and another one without it:
create table test as select rownum r from dual connect by level <= 10;
update test set r=r*10 where mod(r,2)=0;
<>
declare
cursor c_locked is select rownum row_num, t.* from test t for update skip locked;
cursor c_free is select rownum row_num, t.* from test t;
r_locked c_free%rowtype;
r_free c_free%rowtype;
begin
open c_locked;
open c_free;
loop
if r_free.row_num is not null and r_free.row_num < r_locked.row_num then
fetch c_free into r_free;
else
fetch c_locked into r_locked;
fetch c_free into r_free;
end if;
exit when c_free%notfound;
if r_free.row_num != r_locked.row_num then
dbms_output.put_line(r_free.row_num);
end if;
end loop;
close c_locked;
close c_free;
end;
/
It's just a 10-minute code, so it might contain error but I think the idea is ok.
This way you don't have to write too much code.
Regards,
Joaquin Gonzalez.
--Posted again correcting one line.
ReplyDeleteHi Damir,
In 11R2 (don't know if R1) if you want to know which records are locked, you can use one cursor with "for update skip locked" and another one without it:
create table test as select rownum r from dual connect by level <= 10;
update test set r=r*10 where mod(r,2)=0;
--change to another session
declare
cursor c_locked is select rownum row_num, t.* from test t for update skip locked;
cursor c_free is select rownum row_num, t.* from test t;
r_locked c_free%rowtype;
r_free c_free%rowtype;
begin
open c_locked;
open c_free;
loop
if r_free.row_num is not null and r_free.row_num < r_locked.row_num then
fetch c_free into r_free;
else
fetch c_locked into r_locked;
fetch c_free into r_free;
end if;
exit when c_free%notfound;
if r_free.row_num != r_locked.row_num then
dbms_output.put_line(r_free.row_num);
end if;
end loop;
close c_locked;
close c_free;
end;
/
It's just a 10-minute code, so it might contain error but I think the idea is ok.
This way you don't have to write too much code.
Regards,
Joaquin Gonzalez.
@Joaquin Gonzalez,
ReplyDeletenice example of new features in 11g database.
But still there are some processes that cannot be skipped (suppose the number must be in order) so there is not too help for that.
However, seems that locking problem might be a history in next incoming versions...
;-)