DB 전체의 TX, TM LOCK 현상 확인 SCRIPT

haisins

7년 ago

create or replace view HAISINS_LOCK as
  select 
	sid session_id,
	decode(type, 
		'TX', 'Transaction',
		'TM', 'DML',
		'DX', 'Distributed Xaction',
		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,
	 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
      where  type = 'TX' 
      or type = 'TM';


drop table HAISINS_holders;

create table HAISINS_HOLDERS   /* temporary table */
(
  waiting_session   number,
  holding_session   number,
  lock_type         varchar2(26),
  mode_held         varchar2(14),
  mode_requested    varchar2(14),
  lock_table        varchar2(22),
  lock_owner        varchar2(30)
);


insert into HAISINS_holders
  select w.session_id,
        h.session_id,
        w.lock_type,
        nvl(h.mode_held, w.mode_held),
        w.mode_requested,
        o.object_name,
        o.owner
  from (select session_id, lock_type, mode_held, lock_id1, lock_id2
        from HAISINS_lock 
        where blocking_others =  'Blocking'
         and  mode_held      !=  'None'
         and  mode_held      !=  'Null'  ) h, 
      (select session_id, lock_type, mode_held, mode_requested, 
              lock_id1, lock_id2
       from HAISINS_lock
       where lock_type = 'Transaction') w, 
  HAISINS_lock a, dba_objects o
  where  w.lock_type       =  h.lock_type(+)
  and  w.lock_id1        =  h.lock_id1(+)
  and  w.lock_id2        =  h.lock_id2(+)
  and  w.session_id = a.session_id
  and  a.lock_type = 'DML'
  and  a.lock_id1 = o.object_id;
commit;

insert into HAISINS_holders
  select holding_session, null, 'None', null, null, null, null
    from HAISINS_holders
 minus
  select waiting_session, null, 'None', null, null, null, null
    from HAISINS_holders;
commit;

col lock_type format a12
col lock_owner format a8
col lock_table format a10

select  lpad(' ',3*(level-1)) || waiting_session waiting_session,
        lock_type,
        mode_requested,
        mode_held,
        lock_table, 
        lock_owner
 from HAISINS_holders
connect by  prior waiting_session = holding_session
  start with holding_session is null;

select * from HAISINS_holders;

100

create or replace view HAISINS_LOCK as

select

sid session_id,

decode(type,

'TX', 'Transaction',

'TM', 'DML',

'DX', 'Distributed Xaction',

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,

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

where type = 'TX'

or type = 'TM';

drop table HAISINS_holders;

create table HAISINS_HOLDERS /* temporary table */

(

waiting_session number,

holding_session number,

lock_type varchar2(26),

mode_held varchar2(14),

mode_requested varchar2(14),

lock_table varchar2(22),

lock_owner varchar2(30)

);

insert into HAISINS_holders

select w.session_id,

h.session_id,

w.lock_type,

nvl(h.mode_held, w.mode_held),

w.mode_requested,

o.object_name,

o.owner

from (select session_id, lock_type, mode_held, lock_id1, lock_id2

from HAISINS_lock

where blocking_others = 'Blocking'

and mode_held != 'None'

and mode_held != 'Null' ) h,

(select session_id, lock_type, mode_held, mode_requested,

lock_id1, lock_id2

from HAISINS_lock

where lock_type = 'Transaction') w,

HAISINS_lock a, dba_objects o

where w.lock_type = h.lock_type(+)

and w.lock_id1 = h.lock_id1(+)

and w.lock_id2 = h.lock_id2(+)

and w.session_id = a.session_id

and a.lock_type = 'DML'

and a.lock_id1 = o.object_id;

commit;

insert into HAISINS_holders

select holding_session, null, 'None', null, null, null, null

from HAISINS_holders

minus

select waiting_session, null, 'None', null, null, null, null

from HAISINS_holders;

commit;

col lock_type format a12

col lock_owner format a8

col lock_table format a10

select lpad(' ',3*(level-1)) || waiting_session waiting_session,

lock_type,

mode_requested,

mode_held,

lock_table,

lock_owner

from HAISINS_holders

connect by prior waiting_session = holding_session

start with holding_session is null;

select * from HAISINS_holders;