Monitoren van locks in een RAC-database

Dit script geeft de ‘waiters’ en de ‘lockers’ aan per instance. Veel gebruik van gemaakt.

set pagesize 10000
set echo off
set feedback on
set linesize 500
set recsep off

column h_user format a11
column h_mach format a6
column h_stat format a6
column h_inst format a6
column h_sid format 9999
column h_object format a30
column h_mode format a9
column w_inst format a6
column w_sid format 9999
column w_srt_lock format a21
column w_srt_req format a13
column w_user format a11

SELECT s.username h_user
, substr(s.machine,9,14) h_mach
, substr(s.status,1,1) h_stat
, decode(lh.inst_id
,”) h_inst
, lh.sid h_sid
, decode(lh.lmode, 2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’) h_mode
, decode(lw.inst_id
, ”) w_inst
, lw.sid w_Sid
, decode ( lh.type,
‘MR’, ‘Media_recovery’,
‘RT’, ‘Redo_thread’,
‘UN’, ‘User_name’,
‘TX’, ‘Transaction’,
‘TM’, ‘Dml’,
‘UL’, ‘PLSQL User_lock’,
‘DX’, ‘Distrted_Transaxion’,
‘CF’, ‘Control_file’,
‘IS’, ‘Instance_state’,
‘FS’, ‘File_set’,
‘IR’, ‘Instance_recovery’,
‘ST’, ‘Diskspace Transaction’,
‘IV’, ‘Libcache_invalidation’,
‘LS’, ‘LogStaartORswitch’,
‘RW’, ‘Row_wait’,
‘SQ’, ‘Sequence_no’,
‘TE’, ‘Extend_table’,
‘TT’, ‘Temp_table’,
‘Nothing-‘ ) w_srt_lock
, decode ( lw.request,
0, ‘None’,
1, ‘NoLock’,
2, ‘Row-Share’,
3, ‘Row-Exclusive’,
4, ‘Share-Table’,
5, ‘Share-Row-Exclusive’,
6, ‘Exclusive’,
‘Nothing-‘ ) w_srt_req
FROM gv$lock lw
, gv$lock lh
, gv$session s
WHERE lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request= 0
AND lw.lmode= 0
AND lh.sid = s.sid
AND lh.inst_id = s.inst_id
AND (lh.id1,lh.id2) in
SELECT id1,id2 FROM gv$lock WHERE request=0
SELECT id1,id2 FROM gv$lock WHERE lmode=0

By | 2007-09-18T23:26:27+00:00 September 18th, 2007|Categories: RAC|0 Comments

About the Author:

Leave A Comment