This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

Tuesday, June 1, 2010

Blocking session ... show table records that are blocked


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:
  1. sql from blocker session
  2. sql from waiter session
by retrieving sql_text using info from gv$session or gv$active_session_history views.

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>sql
And, 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:45
Just 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!

5 comments :

  1. Svaka cast na trudu! Htio sam isprobati tvoj sbr.sql skript, ali dobijam gresku:

    SQL> @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

    ReplyDelete
  2. Dejane,

    THX for info...
    Agrhhhhhh ... version control software!

    Now is fixed!

    ReplyDelete
  3. Hi 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;

    <>

    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.

    ReplyDelete
  4. --Posted again correcting one line.

    Hi 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.

    ReplyDelete
  5. @Joaquin Gonzalez,

    nice 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...
    ;-)

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign