ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Locktree

Locktree

原创 Linux操作系统 作者:yyp2009 时间:2012-04-07 16:12:52 0 删除 编辑

E:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\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;

with vw_lock AS (SELECT * FROM v$lock)
,t AS (
select a.sid id1,
b.sid id2
from vw_lock a, vw_lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
)
SELECT CONNECT_BY_ROOT(id1)||SYS_CONNECT_BY_PATH(id2,'->')
FROM t
WHERE CONNECT_BY_ISLEAF=1
START WITH NOT EXISTS (SELECT NULL FROM t t2 WHERE t2.id2=t.id1)
CONNECT BY id1= PRIOR id2
/   (from newkid)

 

 

select lpad('--',decode(c.request,0,0,4))||b.username user_name,c.sid,b.inst_id,a.object_id,a.object_name,
decode(c.request,0,'BLOCKED','WAITING') STATUS,request,block,c.id1,c.id2
from dba_objects a,gv$session b,gv$lock c,gv$locked_object d
where d.OBJECT_ID=a.object_id
and c.ID2=d.XIDSQN
and c.sid=b.sid
and b.inst_id=1
and c.type='TX'

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-720627/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Activity Transaction
下一篇: non
请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1017348