Blockers performance tuning queries

select name,open_mode,log_mode,host_name,database_role,to_char(startup_time,'dd/mm/yyyy hh:mm:ss')startup_time from v$database,v$instance;


SQL> -- Show all BLOCKERS and who they are blocking(BLOCKEES)...
SQL>
set lines 110 wrap on echo off
SQL> SQL> column id1 noprint
SQL> column id2 noprint
SQL> col TYPE format a10
SQL> col blockedby format a10
SQL> col Usr format a20
SQL> column minheld format 99999.9 heading "Mins|Held"
SQL>
SQL> select /*+ ORDERED */
  2         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
  3         l.sid, '         ' blockedby,
  4         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
  5         substr(s.module,1,30) module
  6    from v$lock l, v$session s, v$parameter p
  7   where l.block > 0
  8     and l.sid = s.sid
  9     and p.name = 'parallel_server' and p.value='FALSE'
 10  union
 11  select /*+ ORDERED */
 12         '  Blockee' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
 13         l.sid, substr(to_char(b.sid),1,9) blockedby,
 14         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
 15         substr(s.module,1,30) module
 16    from v$lock l, v$session s, v$parameter p,
 17         (select sid, id1, id2 from v$lock where block > 0) b
 18   where l.block = 0
 19     and l.sid = s.sid
 20     and l.id1 = b.id1
 21     and l.id2 = b.id2
 22     and p.name = 'parallel_server' and p.value='FALSE'
 23  union
 24  select /*+ ORDERED */
 25         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
 26         l.sid, '         ' blockedby,
 27         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
 28         substr(s.module,1,30) module
 29    from v$lock l, v$session s, v$parameter p
 30   where l.block > 0
 31     and l.sid = s.sid
 32     and l.lmode = 6
 33     and l.type = 'TX'
 34     and p.name = 'parallel_server' and p.value='TRUE'
 35  union
 36  select /*+ ORDERED */
 37         '  Blockee', rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
 38         l.sid, substr(to_char(b.sid),1,9) blockedby,
 39         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
 40         substr(s.module,1,30) module
 41    from v$lock l, v$session s, v$parameter p,
 42         (select bl.sid, bl.id1, bl.id2 from v$lock bl
 43           where bl.block > 0
 44             and bl.lmode = 6
 45             and bl.type = 'TX') b
 where l.block > 0
 46   47     and l.sid = s.sid
 48     and l.id1 = b.id1
 49     and l.id2 = b.id2
 50     and l.lmode = 0
 51     and l.type = 'TX'
 52     and p.name = 'parallel_server' and p.value='TRUE'
 53   order by 1 desc, 8, 9;
--
-- Lock Types:
--   TM(DML)
--   TX(transaction enqueue)
--   UL(user supplied)
-- Lock Modes:
--   0 None
--   1 Null(NULL)
--   2 Row-S(SS)
--   3 Row-X(SX)
--   4 Share(S)
--   5 S/Row-X(SSX)
--   6 Exclusive(X)

enq: TX - row lock contention

This is indicative of a session waiting for a row lock held by another session; the amount of wait time associated with this wait event is excessive and can be responsible for performance issues observed in the application. TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

There are several situations of TX enqueue:

Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

To solve this you would have the first session already holding the lock perform a COMMIT or ROLLBACK.

Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

To solve this again you have the first session already holding the lock perform a COMMIT or ROLLBACK.

Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

For which SQL currently is waiting on:

select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in ('WAITING') and wait_class != 'Idle' and event='enq: TX - row lock contention' and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is:

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session;

ORA-00054: resource busy and acquire with NOWAIT specified

How to check Blocking session /Locks -- oracle 10g

SELECT oracle_username || ' (' || s.osuser || ')' username, s.SID || ',' || s.serial# "SID,SESSION#"
, owner || '.' || object_name OBJECT, object_type,
DECODE( l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') status
, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode)) mode_held
FROM v$locked_object v, dba_objects d, v$lock l, v$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.SID
ORDER BY oracle_username,session_id;

To identify the locked rows, use the below query:

select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#
, row_wait_row#, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('<object_name_from_above_query>');


select name,open_mode,log_mode,host_name,database_role,to_char(startup_time,'dd/mm/yyyy hh:mm:ss')startup_time from v$database,v$instance;


SQL> -- Show all BLOCKERS and who they are blocking(BLOCKEES)...

set lines 110 wrap on echo off
 column id1 noprint
 column id2 noprint
 col TYPE format a10
 col blockedby format a10
 col Usr format a20
column minheld format 99999.9 heading "Mins|Held"

     select /*+ ORDERED */
         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
           l.sid, '         ' blockedby,
          s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
          substr(s.module,1,30) module
     from v$lock l, v$session s, v$parameter p
   where l.block > 0
     and l.sid = s.sid
     and p.name = 'parallel_server' and p.value='FALSE'
 union
  select /*+ ORDERED */
         '  Blockee' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
        l.sid, substr(to_char(b.sid),1,9) blockedby,
         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
        substr(s.module,1,30) module
    from v$lock l, v$session s, v$parameter p,
        (select sid, id1, id2 from v$lock where block > 0) b
  where l.block = 0
     and l.sid = s.sid
    and l.id1 = b.id1
     and l.id2 = b.id2
   and p.name = 'parallel_server' and p.value='FALSE'
   union
  select /*+ ORDERED */
         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
         l.sid, '         ' blockedby,
        s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
         substr(s.module,1,30) module
    from v$lock l, v$session s, v$parameter p
    where l.block > 0
     and l.sid = s.sid
     and l.lmode = 6
     and l.type = 'TX'
    and p.name = 'parallel_server' and p.value='TRUE'
  union
  select /*+ ORDERED */
         '  Blockee', rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
         l.sid, substr(to_char(b.sid),1,9) blockedby,
         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
       substr(s.module,1,30) module
  from v$lock l, v$session s, v$parameter p,
        (select bl.sid, bl.id1, bl.id2 from v$lock bl
          where bl.block > 0
            and bl.lmode = 6
            and bl.type = 'TX') b
 where l.block > 0
 and l.sid = s.sid
    and l.id1 = b.id1
    and l.id2 = b.id2
     and l.lmode = 0
     and l.type = 'TX'
      and p.name = 'parallel_server' and p.value='TRUE'
    order by 1 desc, 8, 9;
--
-- Lock Types:
--   TM(DML)
--   TX(transaction enqueue)
--   UL(user supplied)
-- Lock Modes:
--   0 None
--   1 Null(NULL)
--   2 Row-S(SS)
--   3 Row-X(SX)
--   4 Share(S)
--   5 S/Row-X(SSX)
--   6 Exclusive(X)



ALTER SYSTEM KILL SESSION 'SID,SESSION' ;

To simulate the occurrence of this error for testing/learning purposes, do as below in 2 sessions:

Session 1:
SQL> conn scott/tiger;

Connected.

SQL> create table lock_test (x number);

Table created.

SQL> insert into lock_test values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> update lock_test set x=500;

1 row updated.

-- Do not commit.

Session 2:
SQL> conn scott/tiger;

Connected.

declare y number;

begin

select x into y from lock_test for update nowait;

end;

/
ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-06512: at line 4


column username format a15
column sid format 999990 heading SID
column type format a4
column lmode format 990 heading HELD
column request format 990 heading REQ
column id1 format 99999990
column id2 format 99999990
break on id1 skip 1 dup
select
sn.username,
sn.sid,
sn.sql_id,
m.type,
decode(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'S/Row Excl',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
decode(m.request, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'S/Row Excl',
6, 'Exclusive',
request, ltrim(to_char(m.request, '990'))) request,
m.id1 id1,
m.id2 id2,
m.block,
sn.logon_time,
sn.seconds_in_wait,
sn.program
from
ops$oracle.v_session sn,
ops$oracle.v_lock m
where
(sn.sid = m.sid and m.request != 0)
or (sn.sid = m.sid and m.request = 0 and lmode != 4
and (id1,id2) in (select s.id1, s.id2 from ops$oracle.v_lock s where request != 0 and s.id1 = m.id1 and s.id2 = m.id2))