Monday, July 26, 2010

Blocking session ... show table records that are blocked (Part II)

In one of mine previous posts that deal with blocking sessions (Blocking session ... show table records that are blocked), I have shown how to identify row which is blocked.

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!

2 comments :

  1. Hi Damir,
    first of all,thanks for such interesting and valuable article.
    I noticed you are applying decode function on v$lock in c_blockers cursor,
    so I was thinking maybe using v$lock_type view
    could be better instead.
    I think v$lock_type is available from 10gR1 , but the content of the view in 10gR1 is different than the one in 11gR2,
    where is aprox. 30-40 new lock type comparing to the ones that were in 10g.
    cheers,
    toni

    ReplyDelete
    Replies
    1. Tony
      THX, appresciated.
      Will try to implement this "new feature" when I get the time ...

      Delete

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign