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)