프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat
2024
10.12
14:00

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

티스토리툴바