監控Oracle系統中鎖的常用指令碼

season0891發表於2010-08-16
come from:廣告.com/blog/504321

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;
/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/90618/viewspace-671073/,如需轉載,請註明出處,否則將追究法律責任。

相關文章