监控Oracle系统中锁的常用脚本

出处: http://wxy0327.itpub.net/post/16888/126243
---------------------------------------------------------------

    把Oracle中常用的检查锁和闩的脚本总结一些。

　



1. 用于检查系统中锁的简单脚本
select s.username, s.sid, l.type, l.id1, l.id2, l.lmode, l.request, p.spid PID
from v$lock l, v$session s, v$process p
where s.sid = l.sid
and   p.addr = s.paddr
and   s.username is not null
order by id1, s.sid, request;



2. 显示数据库锁的信息
set pagesize 60
set linesize 132
select s.username username, a.sid sid, a.owner || '.' || a.object object, s.lockwait,   
       t.sql_text sql
from v$sqltext t, v$session s, v$access a
where t.address = s.sql_address
and   t.hash_value = s.sql_hash_value
and   s.sid = a.sid
and   a.owner != 'SYS'
and   upper(substr(a.object,1,2)) != 'V$';
/



3. 产生在数据库中持有的锁的报表
select b.sid, c.username, c.osuser, c.terminal,
       decode(b.id2, 0, a.object_name, 'Trans-' || to_char(b.id1)) object_name,
       b.type,
       decode(b.lmode, 0, '-Waiting-',
                       1, 'Null',
                       2, 'Row Share',
                       3, 'Row Excl',
                       4, 'Share',
                       5, 'Sha Row Exc',
                       6, 'Exclusive', 'Other') "Lock Mode",
       decode(b.request, 0, ' ',
                         1, 'Null',
                         2, 'Row Share',
                         3, 'Row Excl',
                         4, 'Share',
                         5, 'Sha Row Exc',
                         6, 'Exclusive', 'Other') "Req Mode"
from dba_objects a, v$lock b, v$session c
where a.object_id(+) = b.id1
and   b.sid = c.sid
and   c.username is not null
order by b.sid, b.id2;



4. 产生等待锁的用户的报告
column username format a15
column sid format 9990 heading sid
column type format a4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
select sn.username, m.sid, 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,
       m.id2
from v$session sn, 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 v$lock s
                                                                                           where request != 0
                                                        and s.id1 = m.id1
                                                        and s.id2 = m.id2)
       )
order by id1, id2, m.request;
spool off
clear breaks



5. 显示持有锁的会话的信息
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' fromat a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(s.username, 'Internal') username,
       nvl(s.terminal, 'None') terminal,
       l.sid || ',' || s.serial# Kill,
       u1.name || '.' || substr(t1.name, 1, 20) tab,
       decode(l.lmode, 1, 'No Lock',
                       2, 'Row Share',
                       3, 'Row Exclusive',
                       4, 'Share',
                       5, 'Share Row Exclusive',
                       6, 'Exclusive', null) lmode,
       decode(l.request, 1, 'No Lock',
                         2, 'Row Share',
                         3, 'Row Exclusive',
                         4, 'Share',
                         5, 'Share Row Exclusive',
                         6, 'Exclusive', null) request
from v$lock l, v$session s, sys.user$ u1, sys.obj$ t1
where l.sid = s.sid
and   t1.obj# = decode(l.id2, 0, l.id1, l.id2)
and   u1.user# = t1.owner#
and   s.type != 'BACKGROUND'
order by 1, 2, 5;



6. 用于鉴别系统中闩性能的脚本
column name heading "Name" format a20
column pid heading "HSid" format a3
column gets heading "Gets" format 999999990
column misses heading "Miss" format 99990
column im_gets heading "ImG" format 99999990
column im_misses heading "ImM" format 999990
column sleeps heading "Sleeps" format 99990
select n.name name, h.pid pid, l.gets gets, l.misses misses,
       l.immediate_gets im_gets, l.immediate_misses im_misses, l.sleeps sleeps
from v$latchname n, v$latchholder h, v$latch l
where l.latch# = n.latch#
and   l.addr = h.laddr(+);



7. 使用v$session wait视图来鉴别闩竞争
select event, p1text, p1, p2text, p2, seq#, wait_time, state
from v$session_wait
where sid = '&&1'
and event = 'latch free';



8. 列举用于闩竞争的信息
ttitle center 'Latch Contention Report' skip 3
col name form a25
col gets form 999,999,999
col misses form 999.99
col spins form 999.99
col igets form 999,999,999
col imisses form 999.99
select name, gets,
       misses * 100 / decode(gets, 0, 1, gets) misses,
       spin_gets * 100 / decode(misses, 0, 1, misses) spins,
       immediate_gets igets,
       immediate_misses * 100 / decode(immediate_gets, 0, 1, immediate_gets) imisses
from v$latch
order by gets + immediate_gets;
/



9. 检索闩睡眠率
col name form a18 trunc
col gets form 999,999,990
col miss form 90.9
col cspins form a6 heading 'spin | sl06'
col csleep1 form a5 heading 'sl01 | sl07'
col csleep2 form a5 heading 'sl02 | sl08'
col csleep3 form a5 heading 'sl03 | sl09'
col csleep4 form a5 heading 'sl04 | sl10'
col csleep5 form a5 heading 'sl05 | sl11'
col Interval form a12
set recsep off
select a.name, a.gets gets,
       a.misses * 100 / decode(a.gets, 0, 1, a.gets) miss,
       to_char(a.spin_gets * 100 / decode(a.misses, 0, 1, a.misses), '990.9') ||
       to_char(a.sleep6 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') cspins,
       to_char(a.sleep1 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep7 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep1,
       to_char(a.sleep2 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep8 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep2,
       to_char(a.sleep3 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep9 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep3,
       to_char(a.sleep4 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep10 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep4,
       to_char(a.sleep5 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep11 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep5
from v$latch a
where a.misses <> 0
order by 2 desc;
/







oracle表被锁，用此脚本可以找出谁锁住了表，谁在等待表2008-07-16 15:49今天测试GPS在线实时交通系统时，发现主计算节点在更新一张表时一直过不去，费了好一番周折才找到罪魁祸首。下面这段脚本是功臣。
执行这段脚本，能知道哪个数据库用户、哪台机器锁住了该表，哪个用户哪台机器在等待该资源。

SELECT   /*+ choose */
         bs.username "Blocking User", bs.username "DB User",
         ws.username "Waiting User", bs.sid "SID", ws.sid "WSID",
         bs.serial# "Serial#", bs.sql_address "address",
         bs.sql_hash_value "Sql hash", bs.program "Blocking App",
         ws.program "Waiting App", bs.machine "Blocking Machine",
         ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
         ws.osuser "Waiting OS User", bs.serial# "Serial#",
         ws.serial# "WSerial#",
         DECODE (
            wk.TYPE,
            'MR', 'Media Recovery',
            'RT', 'Redo Thread',
            'UN', 'USER Name',
            'TX', 'Transaction',
            'TM', 'DML',
            'UL', 'PL/SQL USER LOCK',
            'DX', 'Distributed Xaction',
            'CF', 'Control FILE',
            'IS', 'Instance State',
            'FS', 'FILE SET',
            'IR', 'Instance Recovery',
            'ST', 'Disk SPACE Transaction',
            'TS', 'Temp Segment',
            'IV', 'Library Cache Invalidation',
            'LS', 'LOG START OR Switch',
            'RW', 'ROW Wait',
            'SQ', 'Sequence Number',
            'TE', 'Extend TABLE',
            'TT', 'Temp TABLE',
            wk.TYPE
         ) lock_type,
         DECODE (
            hk.lmode,
            0, 'None',
            1, 'NULL',
            2, 'ROW-S (SS)',
            3, 'ROW-X (SX)',
            4, 'SHARE',
            5, 'S/ROW-X (SSX)',
            6, 'EXCLUSIVE',
            TO_CHAR (hk.lmode)
         ) mode_held,
         DECODE (
            wk.request,
            0, 'None',
            1, 'NULL',
            2, 'ROW-S (SS)',
            3, 'ROW-X (SX)',
            4, 'SHARE',
            5, 'S/ROW-X (SSX)',
            6, 'EXCLUSIVE',
            TO_CHAR (wk.request)
         ) mode_requested,
         TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
         DECODE (
            hk.BLOCK,
            0, 'NOT Blocking',           /* Not blocking any other processes */
            1, 'Blocking',               /* This lock blocks other processes */
            2, 'Global',            /* This lock is global, so we can't tell */
            TO_CHAR (hk.BLOCK)
         )
               blocking_others
    FROM v$lock hk, v$session bs, v$lock wk, v$session ws
   WHERE hk.BLOCK = 1
     AND hk.lmode != 0
     AND hk.lmode != 1
     AND wk.request != 0
     AND wk.TYPE(+) = hk.TYPE
     AND wk.id1(+) = hk.id1
     AND wk.id2(+) = hk.id2
     AND hk.sid = bs.sid(+)
     AND wk.sid = ws.sid(+)
     AND (bs.username IS NOT NULL)
     AND (bs.username <> 'SYSTEM')
     AND (bs.username <> 'SYS')
ORDER BY 1

附结果：



select sn.username,m.sid,m.type,
   decode (m.lmode,
           0,'None',
           1,'Null',
           2,'Row Share',
           3,'Row Excl',
           4,'Share',
           5,'S/Row Exel',
           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 Exel',
           6,'Exclusive',
       request,ltrim(to_char(m.request,'990'))) request,m.id1,m.id2
from v$session sn,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 v$lock s
                     where request !=0
                     and s.id1=m.id2)
       )
order by id1,id2,m.request;
/