监控Oracle系统中锁的常用脚本-技术开发专区

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

作者：wxy0327 编辑：魏国亮 2006-06-26 19:40

【IT168 技术文档】把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;
/

关注我们