诊断脚本：
http://blog.csdn.net/nanaranran/article/details/51790511
其中catblock.sql如下：
rem
rem $Header: catblock.sql 06-nov-2007.05:30:41 kquinn Exp $ blocking.sql
rem
Rem Copyright (c) 1989, 2007, Oracle. All rights reserved.
Rem NAME
Rem catblock.sql
Rem FUNCTION - create views of oracle locks
Rem NOTES
Rem MODIFIED
Rem kquinn 11/06/07 - 6440408: avoid numeric overflow
Rem kigoyal 01/06/04 - DBA_WAITERS/BLOCKERS to use "enq:%"
Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM
Rem jdavison 10/10/00 - Fix dba_lock_internal view
Rem vsaksena 07/07/00 - Optimize views DBA_BLOCKERS, DBA_WAITERS
Rem arithikr 02/10/00 - 878668: Fix DBA_WAITERS view for OPS environment
Rem sparrokk 12/17/99 - 1040651: Use v$lock defn without hint
Rem in dba_dml_locks
Rem mjungerm 06/15/99 - add java shared data object type
Rem nireland 03/17/98 - Add synonyms for DBA_WAITERS, DBA_BLOCKERS
Rem and others. #605559
Rem agardner 04/02/97 - bug #226646: remove comment
Rem jwijaya 03/19/97 - support types
Rem asurpur 04/08/96 - Dictionary Protection Implementation
Rem pgreenwa 08/14/95 - bug #293557: optimize view queries
Rem wmaimone 01/04/96 - 7.3 merge
Rem pgreenwa 05/10/95 - fix dba_lock_internal
Rem pgreenwa 04/25/95 - add new vlock columns
Rem drady 03/22/93 - merge changes from branch 1.1.312.1
Rem drady 03/18/93 - fix 154271
Rem glumpkin 10/17/92 - renamed from BLOCKING.SQL
Rem tpystyne 09/14/92 - rename sid to session_id
Rem jloaiza 07/30/92 - fix for KGL change
Rem tpystyne 05/27/92 - add dba_dml_locks and dba_ddl_locks views
Rem jloaiza 05/24/91 - upgrade for v7
Rem Loaiza 11/01/89 - Creation
Rem
/* this is an auxiliary view containing the KGL locks and pins */
create or replace view DBA_KGLLOCK as
select kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk
union all
select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype from x$kglpn;
create or replace public synonym DBA_KGLLOCK for DBA_KGLLOCK;
grant select on DBA_KGLLOCK to select_catalog_role;
/*
* DBA_LOCK has a row for each lock that is being held, and
* one row for each outstanding request for a lock or latch.
* The columns of DBA_LOCK are:
* session_id - session holding or acquiring the lock
* type - type of lock
* mode_held - mode the lock is currently held in by the session
* mode_requested - mode that the lock is being requested in by the process
* lock_id1 - type specific identifier of the lock
* lock_id2 - type specific identifier of the lock
* last_convert - time (in seconds) since last convert completed
* blocking_others - is this lock blocking other locks
*/
drop synonym DBA_LOCKS;
drop view DBA_LOCKS;
create or replace view DBA_LOCK as
select
sid session_id,
decode(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',
type) lock_type,
decode(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) mode_held,
decode(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2,
ctime last_convert,
decode(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(block)) blocking_others
from v$lock;
create or replace public synonym DBA_LOCK for DBA_LOCK;
grant select on DBA_LOCK to select_catalog_role;
create or replace public synonym DBA_LOCKS for DBA_LOCK;
/*
* DBA_LOCK_INTERNAL has a row for each lock or latch that is being held, and
* one row for each outstanding request for a lock or latch.
* The columns of DBA_LOCK_INTERNAL are:
* session_id - session holding or acquiring the lock
* type - type of lock (DDL, LATCH, etc.)
* mode_held - mode the lock is currently held in by the session
* mode_requested - mode that the lock is being requested in by the process
* lock_id1 - type specific identifier of the lock
* lock_id2 - type specific identifier of the lock
*
* NOTE: this view can be very, very slow depending on the size of your
* shared pool area and database activity.
*/
create or replace view DBA_LOCK_INTERNAL as
select
sid session_id,
decode(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',
type) lock_type,
decode(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) mode_held,
decode(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2
from v$lock /* processes waiting on or holding enqueues */
union all /* procs holding latches */
select s.sid, 'LATCH', 'Exclusive', 'None', rawtohex(laddr), ' '
from v$process p, v$session s, v$latchholder h
where h.pid = p.pid /* 6 = exclusive, 0 = not held */
and p.addr = s.paddr
union all /* procs waiting on latch */
select sid, 'LATCH', 'None', 'Exclusive', rawtohex(latchwait), ' '
from v$session s, v$process p
where latchwait is not null
and p.addr = s.paddr
union all /* library cache locks */
select s.sid,
decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body',
3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
14, 'Java Resource', 32, 'Java Data', to_char(ob.kglhdnsp))
|| ' Definition ' || lk.kgllktype,
decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
to_char(lk.kgllkmod)),
decode(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
to_char(lk.kgllkreq)),
decode(ob.kglnaown, null, '', ob.kglnaown || '.') || ob.kglnaobj ||
decode(ob.kglnadlk, null, '', '@' || ob.kglnadlk),
rawtohex(lk.kgllkhdl)
from v$session s, x$kglob ob, dba_kgllock lk
where lk.kgllkhdl = ob.kglhdadr
and lk.kgllkuse = s.saddr;
create or replace public synonym DBA_LOCK_INTERNAL for DBA_LOCK_INTERNAL;
grant select on DBA_LOCK_INTERNAL to select_catalog_role;
/*
* DBA_DML_LOCKS has a row for each DML lock that is being held, and
* one row for each outstanding request for a DML lock. It is subset
* of DBA_LOCKS
*/
create or replace view DBA_DML_LOCKS as
select
sid session_id,
u.name owner,
o.name,
decode(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'Invalid') mode_held,
decode(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'Invalid') mode_requested,
l.ctime last_convert,
decode(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(block)) blocking_others
from (select l.laddr addr, l.kaddr kaddr, /* 1040651: Defn for v$lock */
s.ksusenum sid, r.ksqrsidt type, r.ksqrsid1 id1,
r.ksqrsid2 id2, l.lmode lmode, l.request request,
l.ctime ctime, l.block block
from v$_lock l, x$ksuse s, x$ksqrs r
where l.saddr = s.addr and l.raddr = r.addr and
s.inst_id = USERENV('Instance')) l, obj$ o, user$ u
where l.id1 = o.obj#
and o.owner# = u.user#
and l.type = 'TM';
create or replace public synonym DBA_DML_LOCKS for DBA_DML_LOCKS;
grant select on DBA_DML_LOCKS to select_catalog_role;
/*
* DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
* one row for each outstanding request for a DDL lock. It is subset
* of DBA_LOCKS
*/
create or replace view DBA_DDL_LOCKS as
select s.sid session_id,
substr(ob.kglnaown,1,30) owner,
substr(ob.kglnaobj,1,30) name,
decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body',
3, 'Trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
14, 'Java Resource', 32, 'Java Data', to_char(ob.kglhdnsp)) type,
decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from v$session s, x$kglob ob, x$kgllk lk
where lk.kgllkhdl = ob.kglhdadr
and lk.kgllkuse = s.saddr
and ob.kglhdnsp != 0;
create or replace public synonym DBA_DDL_LOCKS for DBA_DDL_LOCKS;
grant select on DBA_DDL_LOCKS to select_catalog_role;
/*
* Show all the sessions waiting for locks and the session that holds the
* lock.
*/
create or replace view DBA_WAITERS
(waiting_session
,holding_session
,lock_type
,mode_held
,mode_requested
,lock_id1
,lock_id2)
as
select /*+ordered */ w.sid
,s.ksusenum
,decode(r.ksqrsidt,
'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',
r.ksqrsidt)
,decode(l.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
l.lmode)
,decode(bitand(w.p1,65535),
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(bitand(w.p1,65535)))
,r.ksqrsid1, r.ksqrsid2
from v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
where w.wait_Time = 0
and w.event like 'enq:%'
and r.ksqrsid1 = w.p2
and r.ksqrsid2 = w.p3
and r.ksqrsidt = chr(bitand(p1,4278190080)/16777215)||
chr(bitand(p1,16711680)/65535)
and l.block = 1
and l.saddr = s.addr
and l.raddr = r.addr
and s.inst_id = userenv('Instance');
create or replace public synonym DBA_WAITERS for DBA_WAITERS;
grant select on DBA_WAITERS to select_catalog_role;
/*
* Show all the sessions that have someone waiting on a lock they hold, but
* that are not themselves waiting on a lock.
*/
create or replace view DBA_BLOCKERS as
select /*+ordered */ distinct s.ksusenum holding_session
from v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
where w.wait_Time = 0
and w.event like 'enq:%'
and r.ksqrsid1 = w.p2
and r.ksqrsid2 = w.p3
and r.ksqrsidt = chr(bitand(p1,4278190080)/16777215)||
chr(bitand(p1,16711680)/65535)
and l.block = 1
and l.saddr = s.addr
and l.raddr = r.addr
and s.inst_id = userenv('Instance');
create or replace public synonym DBA_BLOCKERS for DBA_BLOCKERS;
grant select on DBA_BLOCKERS to select_catalog_role;
对脚本进行过滤：
cat catblock.sql|grep create
Rem FUNCTION - create views of oracle locks
create or replace view DBA_KGLLOCK as
create or replace public synonym DBA_KGLLOCK for DBA_KGLLOCK;
create or replace view DBA_LOCK as
create or replace public synonym DBA_LOCK for DBA_LOCK;
create or replace public synonym DBA_LOCKS for DBA_LOCK;
create or replace view DBA_LOCK_INTERNAL as
create or replace public synonym DBA_LOCK_INTERNAL for DBA_LOCK_INTERNAL;
create or replace view DBA_DML_LOCKS as
create or replace public synonym DBA_DML_LOCKS for DBA_DML_LOCKS;
create or replace view DBA_DDL_LOCKS as
create or replace public synonym DBA_DDL_LOCKS for DBA_DDL_LOCKS;
create or replace view DBA_WAITERS
create or replace public synonym DBA_WAITERS for DBA_WAITERS;
create or replace view DBA_BLOCKERS as
create or replace public synonym DBA_BLOCKERS for DBA_BLOCKERS;
可以看到这个脚本就是创建enqueue、lock相关的数据字典。
最重要的是脚本utllockt.sql，这个脚本可以列出lock等待的关系，就是等待队列的关系。
这个脚本内容如下：
drop synonym DBA_LOCKS;
drop view DBA_LOCKS;
[oracle@rac1 admin]$ more utllockt.sql
rem
rem $Header: utllockt.sql 21-jan-2003.16:21:56 bnnguyen Exp $ locktree.sql
rem
Rem Copyright (c) 1989, 2003, Oracle Corporation. All rights reserved.
Rem NAME
REM UTLLOCKT.SQL
Rem FUNCTION - Print out the lock wait-for graph in tree structured fashion.
Rem This is useful for diagnosing systems that are hung on locks.
Rem NOTES
Rem MODIFIED
Rem bnnguyen 01/21/03 - bug2166717
Rem pgreenwa 04/27/95 - fix column definitions for LOCK_HOLDERS
Rem pgreenwa 04/26/95 - modify lock_holders query to use new dba_locks f
Rem glumpkin 10/20/92 - Renamed from LOCKTREE.SQL
Rem jloaiza 05/24/91 - update for v7
Rem rlim 04/29/91 - change char to varchar2
Rem Loaiza 11/01/89 - Creation
Rem
/* Print out the lock wait-for graph in a tree structured fashion.
*
* This script prints the sessions in the system that are waiting for
* locks, and the locks that they are waiting for. The printout is tree
* structured. If a sessionid is printed immediately below and to the right
* of another session, then it is waiting for that session. The session ids
* printed at the left hand side of the page are the ones that everyone is
* waiting for.
*
* For example, in the following printout session 9 is waiting for
* session 8, 7 is waiting for 9, and 10 is waiting for 9.
*
* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
* ----------------- ---- ----------------- ----------------- -------- --------
* 8 NONE None None 0 0
* 9 TX Share (S) Exclusive (X) 65547 16
* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
*
* The lock information to the right of the session id describes the lock
* that the session is waiting for (not the lock it is holding).
*
* Note that this is a script and not a set of view definitions because
* connect-by is used in the implementation and therefore a temporary table
* is created and dropped since you cannot do a join in a connect-by.
*
* This script has two small disadvantages. One, a table is created when
* this script is run. To create a table a number of locks must be
* acquired. This might cause the session running the script to get caught
* in the lock problem it is trying to diagnose. Two, if a session waits on
* a lock held by more than one session (share lock) then the wait-for graph
* is no longer a tree and the conenct-by will show the session (and any
* sessions waiting on it) several times.
*/
/* Select all sids waiting for a lock, the lock they are waiting on, and the
* sid of the session that holds the lock.
* UNION
* The sids of all session holding locks that someone is waiting on that
* are not themselves waiting for locks. These are included so that the roots
* of the wait for graph (the sessions holding things up) will be displayed.
*/
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, 'None', null, null, null, null
from lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
这个脚本中有个解释：
* For example, in the following printout session 9 is waiting for
* session 8, 7 is waiting for 9, and 10 is waiting for 9.
*
* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
* ----------------- ---- ----------------- ----------------- -------- --------
* 8 NONE None None 0 0
* 9 TX Share (S) Exclusive (X) 65547 16
* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
列出了数据库中等待队列的关系。对此，我做了个实验：
session 1：sid 60 执行：update test set name='aa' where id=1;
session 2：sid 34 执行：update test set name='aa' where id=1; 等待
session 3：sid 36 执行：select * from test for update; 等待
session 4：sid 50 执行：select * from test for update; 等待
然后执行脚本：
SQL> @utllockt.sql
drop table lock_holders
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
drop table dba_locks_temp
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
3 rows created.
Commit complete.
Table dropped.
1 row created.
Commit complete.
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
60 None
34 Transaction Exclusive Exclusive 262156 1054
36 Transaction Exclusive Exclusive 262156 1054
50 Transaction Exclusive Exclusive 262156 1054
Table dropped.
这里把队列的信息表示的比较清除，60是blocker，而34/36/50是waiter
125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
