행위

DBA LOCKS

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 4월 4일 (화) 13:50 판 (→‎Lock 문제를 조회하는 뷰는 ?)
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
thumb_up 추천메뉴 바로가기

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner
  from gv$locked_object l, dba_objects o
 where l.object_id = o.object_id;
select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner
     , 'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session
  from v$locked_object l, dba_objects o, v$session s
 where l.object_id = o.object_id
   and l.session_id = s.sid;
select sid, serial#
 from gv$session
 where sid = 0000;
alter sysetm kill session '';
select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine
     , s.program,s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value
     , q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time
     , o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec
  from v$lock l, v$session s, v$process p, v$sqlarea q1, v$sqlarea q2, dba_objects o
 where l.lmode in (3,5,6) and l.type in ('TX','TM','UL')
   and l.sid=s.sid and s.paddr=p.addr(+)
   and s.sql_hash_value=q1.hash_value(+)
   and s.prev_hash_value=q2.hash_value(+)
   and l.id1=o.object_id(+)
   and l.ctime> &secs
 order by l.ctime;
  • Metalink Note:29787.1 for information about lock types/modes.
Note:15476.1
3.   Which lock modes are required for which table action?
The following table describes what lock modes on DML enqueues are actually
gotten for which table operations in a standard Oracle installation.
 Operation                Lock Mode LMODE Lock Description
------------------------- --------- ----- ----------------
 Select                     NULL     1    null
 Select for update          SS       2    sub share
 Insert                     SX       3    sub exclusive
 Update                     SX       3    sub exclusive
 Delete                     SX       3    sub exclusive
 Lock For Update            SS       2    sub share
 Lock Share                 S        4    share
 Lock Exclusive             X        6    exclusive
 Lock Row Share             SS       2    sub share
 Lock Row Exclusive         SX       3    sub exclusive
 Lock Share Row Exclusive   SSX      5    share/sub exclusive
 Alter table                X        6    exclusive
 Drop table                 X        6    exclusive
 Create Index               S        4    share
 Drop Index                 X        6    exclusive
 Truncate table             X        6    exclusive

1 Lock 호환성 작동 방식[편집]

  • 잠금 모드의 호환성은 일반적으로 다음 매트릭스로 표시됩니다.
-----------------------------------------------------
       NULL    SS      SX      S       SSX     X
-----------------------------------------------------
 NULL  YES     YES     YES     YES     YES     YES
   SS  YES     YES     YES     YES     YES     no
   SX  YES     YES     YES     no      no      no
    S  YES     YES     no      YES     no      no
  SSX  YES     YES     no      no      no      no
    X  YES     no      no      no      no      no

2 Lock 문제를 조회하는 뷰는 ?[편집]

2.1 V$SESSION_WAIT[편집]

  • 세션이 리소스를 기다리고 있을 때 대기열에 넣기 대기 이벤트에서 대기 중인 것을 찾을때
  • 예시)
SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
- SID identifier of session holding the lock
- P1, P2, P3 determine the resource when event = 'enqueue'
- SECONDS_IN_WAIT gives how long the wait did occurs

2.2 V$SESSION[편집]

  • 세션 정보 및 행 잠금 정보
- SID, SERIAL#  : 세션의 식별자
- LOCKWAIT : 락 대기 대기 주소, 그렇지 않으면 null
- ROW_WAIT_OBJ# 우리가 기다리고 있는 객체의 식별된 객체 (object identified of the object we are waiting on)
 (object_id of dba_objects)
- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# 락이 발생된 행 블록 내의 file_id , block_id, 로우 위치

2.3 V$LOCK[편집]

  • 시스템의 모든 잠금 목록
- SID identifier of session holding the lock
- TYPE, ID1 and ID2 determine the resource
- LMODE and REQUEST indicate which queue the session is waiting on, as follows:
  LMODE > 0, REQUEST = 0 owner
  LMODE = 0, REQUEST > 0 acquirer
  LMODE > 0, REQUEST > 0 converter
- CTIME time since current mode was converted (see Note 223146.1)
- BLOCK are we blocking another lock
  BLOCK = 0 non blocking
  BLOCK = 1 blocking others

2.4 DBA_LOCK or DBA_LOCKS[편집]

  • formatted view on V$LOCK ($ORACLE_HOME/rdbms/admin/catblock.sql 로 생성 )
- SESSION_ID == SID in V$LOCK
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
- LAST_CONVERT == CTIME of V$LOCK
- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK

2.5 V$TRANSACTION_ENQUEUE[편집]

  • subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)

2.6 V$ENQUEUE_LOCK[편집]

  • subset of V$LOCK for the system resources only and blocked TX resources only. (same description as for the V$LOCK view)

2.7 DBA_DML_LOCKS[편집]

  • subset of the V$LOCK for the DML (TM) locks only
(created via $ORACLE_HOME/rdbms/admin/catblock.sql
- same description as the DBA_LOCK view)

2.8 V$LOCKED_OBJECT[편집]

  • DBA_DML_LOCKS 와 동일한 정보이지만 롤백 및 세션 정보와 연결됨
- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
- OBJECT_ID object being locked
- SESSION_ID session id
- ORACLE_USERNAME oracle user name
- OS_USER_NAME OS user name
- PROCESS OS process id
- LOCKED_MODE lock mode

2.9 V$RESOURCE[편집]

  • 시스템에서 현재 잠긴 모든 리소스 목록. list of all the currently locked resources in the system.
  • 각 행은 V$LOCK에 있는 하나 이상의 행과 연관될 수 있습니다.
- TYPE, ID1 and ID2 determine the resource

2.10 DBA_DDL_LOCKS[편집]

  • DBA_DDL_LOCKS 각 DDL 락 에 대한 로우를 가지고 있음. 또한 DDL 잠금에 대한 각 미해결 요청에 대해 하나의 행. DBA_LOCK 뷰와 동일한 설명 이고 DBA_LOCKS의 하위 집합

2.11 DBA_WAITERS[편집]

  • 락 대기 중인 각 세션에 대한 정보를 검색하는 뷰 ($ORACLE_HOME/rdbms/admin/catblock.sql 로 생성 )
- WAITING_SESSION waiting session
- HOLDING_SESSION holding session
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
- MODE_HELD lock type held
- MODE_REQUESTED lock type requested

2.12 DBA_BLOCKERS[편집]

  • 블럭킹 세션을 제공하는 뷰 ($ORACLE_HOME/rdbms/admin/catblock.sql 로 생성 )
- HOLDING_SESSION holding session
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;
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
/
set linesize 80 
set pagesize 66 
column lmode heading 'Lock|Held' format a4 
column request heading 'Lock|Req.' format a4 
column username  format a10  heading "Username" 
column tab format a30 heading "Table Name" 
:column LAddr heading "ID1 - ID2" format a16 
column Lockt heading "Lock|Type" format a4 

select nvl(S.USERNAME,'Internal') username
     , decode(command, 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20), 'Rollback Segment')) tab
     , decode(L.LMODE,1,'NoLk', 2,' RS ', 3,' RX ', 4,'  S ', 5,' SRX', 6,'  X ','NONE') lmode
     , decode(L.REQUEST,1,'NoLk', 2,' RSh ', 3,' RX ', 4,'  S ', 5,' SRX', 6,'  X ','NONE') request
     , l.id1||'-'||l.id2 Laddr, l.type Lockt 
  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,1)  
   and U1.USER# = T1.OWNER# 
   and S.TYPE != 'BACKGROUND' 
  order by 1,2,5 
/
set lines 200
set pagesize 66
break on Kill on sid on  username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
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 a30 heading "Table Name"
column owner format a9
:column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command format a25
column sid format 990

select nvl(S.USERNAME,'Internal') username, L.SID, nvl(S.TERMINAL,'None') terminal
     , decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
:command||' - ???') COMMAND,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
:l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
                    'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
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,1) 
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
order by 1,2,5
/
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$'
/
clear columns
clear computes
clear breaks

set linesize 240
set pagesize 40

column sid format 99999 heading "SID"
column locktype format A9 heading "Lock Type"
column lockreq format A9 heading "Lock     |Requested"
column lockheld format A9 heading "Lock Held"
column timeheld format 9999999 heading "Time Held|(minutes)"
column block format a5 heading "Block"


select /* RULE */ l.sid sid,
       decode(l.type,'TM','DML','TX','Trans','UL','User',l.type) locktype,
       decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive') lockreq,
       decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive') lockheld,
       l.ctime/60 timeheld,
       decode(l.block,0,'No',1,'Yes') block 
  from gv$lock l
 where l.request != 0 or l.block != 0
 order by l.id1, l.lmode desc, l.ctime desc
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DBA_LOCKS&oldid=8078"