OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c dml 중 인덱스 drop 시 발생사항 확인
오라클 19c 환경에서 t1이라는 테이블에 t1_ix1이라는 인덱스를 사용하는 dml 트랜잭션이 실행중인 경우
다른 세션에서 해당 인덱스를 drop 하려고 하면 어떻게 되는지 확인해봄
샘플 테이블 생성
|
1
2
3
|
SQL> create table t1 (col1 number, col2 number);
Table created.
|
샘플 데이터 삽입 및 커밋
|
1
2
3
4
5
6
7
|
SQL> insert into t1 select object_id, object_id+1 from dba_objects;
22965 rows created.
SQL> commit;
Commit complete.
|
샘플 인덱스 생성
|
1
2
3
|
SQL> create index t1_ix1 on t1(col1);
Index created.
|
dml(delete)할 대상 데이터 확인
|
1
2
3
4
5
6
|
--set autot on
SQL> select count(*) from t1 where col1 <= 10;
COUNT(*)
----------
9
|
xplan_display cursor 또는 set auto trace로 플랜 확인하면서 dml 실행
|
1
2
3
|
SQL> delete /*+gather_plan_statistics */ t1 where col1 <= 10;
9 rows deleted.
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
SQL>
set lines 200 pages 1000
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID 7jw15xq6mv2pc, child number 0
-------------------------------------
delete /*+gather_plan_statistics */ t1 where col1 <= 10
Plan hash value: 2776990808
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 15 |
| 1 | DELETE | T1 | 1 | | | | | 0 |00:00:00.01 | 15 |
|* 2 | INDEX RANGE SCAN| T1_IX1 | 1 | 9 | 117 | 2 (0)| 00:00:01 | 9 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / T1@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"DEL$1")
INDEX(@"DEL$1" "T1"@"DEL$1" ("T1"."COL1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"<=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (cmp=2) "T1".ROWID[ROWID,10], "COL1"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[DEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[DEL$1]]></s></h></f></q>
54 rows selected.
|
t1_ix1 인덱스를 사용해서 dml을 하고 있음
B 세션에서 해당 인덱스 drop 시도
|
1
2
3
4
5
|
SQL> drop index t1_ix1;
drop index t1_ix1
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
|
ora-00054 에러 발생함
C 세션에서 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
2 TM 23318 0 3 0 0 0 23318 0
2 TX 262169 443 6 0 0 4 25 443
|
2번 세션에 TM lock ID1 23318(T1) 에 대해 LMODE 3 (row-X (SX))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
이 상태에서 ddl_lock_timeout 파라미터를 이용해 B 세션 DDL 실행 후 100초간 대기하게 한 후 lock 재확인
B 세션 ddl_lock_timeout 파라미터 설정 후 ddl 재실행
|
1
2
3
4
5
6
|
SQL> alter session set ddl_lock_timeout = 100;
Session altered.
SQL> drop index t1_ix1;
(100초간 대기중)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
2 TM 23318 0 3 0 1 0 23318 0
2 TX 262169 443 6 0 0 4 25 443
23 TM 23318 0 0 6 0 0 23318 0
|
2번 세션에 TM lock ID1 23318(T1) 에 대해 LMODE 3 (row-X (SX))가 걸려있고,
TM lock ID1 23318(T1) 에 대해 BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
23번 세션의 TM lock ID1 ID1 23318(T1) 에 대해 LMODE 값이 0 (none)이고 REQUEST가 6 으로 되어있음
(TM lock에 LMODE 6 (exclusive (X))을 걸기 위해 대기중이라는 뜻)
(dml 트랜젝션 완료(commit or rollback) 후 lock 해소됨)
그림에 의하면 LMODE 6 (exclusive (X)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
해당 인덱스 dml을 하지 않고 있는 경우 확인
A 세션에서 rollback
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
dml(delete)할 대상 데이터 확인
|
1
2
3
4
5
6
|
--set autot on
SQL> select count(*) from t1 where col1 <= 10;
COUNT(*)
----------
9
|
xplan_display cursor 또는 set auto trace로 플랜 확인하면서 dml 실행
(full 힌트 수행하여 index를 의도적으로 타지 않게 함)
|
1
2
3
|
SQL> delete /*+gather_plan_statistics full(t1) */ t1 where col1 <= 10;
9 rows deleted.
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
SQL>
set lines 200 pages 1000
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
SQL_ID ckuw38ads1knq, child number 0
-------------------------------------
delete /*+gather_plan_statistics full(t1) */ t1 where col1 <= 10
Plan hash value: 775918519
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | | | 14 (100)| | 0 |00:00:00.01 | 79 |
| 1 | DELETE | T1 | 1 | | | | | 0 |00:00:00.01 | 79 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 9 | 117 | 14 (0)| 00:00:01 | 9 |00:00:00.01 | 50 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / T1@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"DEL$1")
FULL(@"DEL$1" "T1"@"DEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"<=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (cmp=2; rowset=256) "T1".ROWID[ROWID,10], "COL1"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
2 - DEL$1 / T1@DEL$1
- full(t1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[DEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[DEL$1]]></s></h></f></q>
61 rows selected.
|
인덱스를 타지 않고 full scan 함
B 세션에서 해당 인덱스 drop 시도
|
1
2
3
4
5
|
SQL> drop index t1_ix1;
drop index t1_ix1
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
|
ora-00054 에러 발생함
C 세션에서 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
2 TM 23318 0 3 0 0 0 23318 0
2 TX 196621 436 6 0 0 3 13 436
|
2번 세션에 TM lock ID1 23318(T1) 에 대해 LMODE 3 (row-X (SX))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
이 상태에서 ddl_lock_timeout 파라미터를 이용해 B 세션 DDL 실행 후 100초간 대기하게 한 후 lock 재확인
B 세션 ddl_lock_timeout 파라미터 설정 후 ddl 재실행
|
1
2
3
4
5
6
|
SQL> alter session set ddl_lock_timeout = 100;
Session altered.
SQL> drop index t1_ix1;
(100초간 대기중)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
2 TM 23318 0 3 0 1 0 23318 0
2 TX 196621 436 6 0 0 3 13 436
23 TM 23318 0 0 6 0 0 23318 0
|
2번 세션에 TM lock ID1 23318(T1) 에 대해 LMODE 3 (row-X (SX))가 걸려있고,
TM lock ID1 23318(T1) 에 대해 BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
23번 세션의 TM lock ID1 ID1 23318(T1) 에 대해 LMODE 값이 0 (none)이고 REQUEST가 6 으로 되어있음
(TM lock에 LMODE 6 (exclusive (X))을 걸기 위해 대기중이라는 뜻)
(dml 트랜젝션 완료(commit or rollback) 후 lock 해소됨)
(인덱스를 사용할때와 동일한 결과)
그림에 의하면 LMODE 6 (exclusive (X)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
결론 :
인덱스가 존재하는 경우에 테이블 dml 중 인덱스를 drop 하려는 경우 ORA-00054 메세지가 발생함
이 경우 dml이 끝난 뒤 인덱스를 drop 하거나 ddl_lock_timeout 파라미터를 이용해서 자동으로 기다리고 삭제하는 방법을 사용해야함
참조 : https://positivemh.tistory.com/848
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 11.2.0.4 에러 : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 오라클에서 테이블에 ddl(partition exchange, add column 등) 작업을 할때 해당 테
positivemh.tistory.com
https://positivemh.tistory.com/906
오라클 19c 테이블 move 시 발생하는 lock 확인
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 테이블 move 시 발생하는 lock 확인 테이블 용량을 줄이거나 특정 테이블스페이스의 여유공간이 부족 할때 move 명령을(reorg
positivemh.tistory.com
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 무료 모니터링 툴 ASH Viewer 설치 가이드 (0) | 2023.12.11 |
|---|---|
| 오라클 19c 인터벌 파티션 테이블 생성 (0) | 2023.12.08 |
| 오라클 19c 인덱스 Sparse 발생시 공간 재사용 확인 (0) | 2023.11.30 |
| 오라클 19c 인덱스 Skew 발생시 공간 재사용 확인 (0) | 2023.11.22 |
| DBeaver를 이용해 오라클 21c XE 접속 가이드 (0) | 2023.11.21 |