[DB] ORACLE

[ORACLE] REORG, HWM, SHRINK, MOVE

mewoni 2025. 2. 24. 10:58
반응형

OS 환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.

설명 : 오라클 reorg, hwm, shrink, move 테스트

 

 

HWM(High Water Mark)

고수위 즉, 마지막까지 등록된 블록위치를 뜻합니다.

만약 데이터가 대량으로 지워지면 이전에 표시되었던 HWM은 그대로이고 실제로 사용되는 데이터는 HWM보다 훨씬 작지만 데이터를 조회시(Full Scan등) HWM 표시부분까지 읽기때문에 불필요한 DISK I/O가 발생합니다.

HWM는 관리자가 별도로 초기화하거나 축소시키지 않으면 늘어나기만 하고 줄어들지 않습니다.

이 점을 꼭 알아두어야 하는 이유는 데이터 풀스캔 시 데이터 스캔의 범위 기준이 바로 HWM 이기 때문입니다.

DB 사용 목적에 따라 차이가 있겠으나 당연히 데이터는 추가와 삭제가 발생하며, HWM를 이동시켜야 할 정도로 많아지기도 했다가 많은 공간이 비게 될 정도로 데이터를 삭제시키는 경우도 발생합니다.

그런데 데이터베이스는 이런 데이터의 많고 적음에 상관없이 HWM 까지의 데이터블록 전체를 스캔하게 됩니다.

심지어 데이터가 0건인 경우라도 HWM 가 1억 Row의 데이터가 있던 때를 기준으로 설정되어 있다면 그만큼의 탐색 시간이 소요된 뒤 0건의 조회 결과를 출력하게 됩니다.

 

이처럼 사용하지 않는 공간이 많으면 공간낭비 뿐만 아니라 조회 성능이 떨어지는 문제도 발생게 되며 

이러한 비효율적인 부분들을 제거하기 위해서 주기적으로 통계정보 등을 바탕으로 재구성해주어야 할 테이블이나 인덱스를 확인해주는 것이 필요합니다.

 

Delete, Truncate 차이점

 

HWM 에 관해서만 보더라도 delete는 기존에 할당된 영역 및 HWM 의 위치가 그대로인 반면, 

truncate 는 HWM 의 위치를 초기화시키고, MINEXTENT 설정값만큼의 공간만 남긴 뒤 모두 할당을 해제합니다.

따라서 이후 동일 테이블에 똑같은 SQL문으로 조회를 시도했을 때 delete 로 삭제했을 경우와 truncate 로 삭제했을 경우의 성능이 다르게 나오게 됩니다.
 
 

Test

샘플 테이블 및 데이터 생성

1
2
3
4
5
SQL> DROP TABLE HWMTEST1 PURGE;
SQL> CREATE TABLE HWMTEST1(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_HWM_COLB ON HWMTEST1(COLB); 

 

테이블 LOGGING 기능 OFF(운영서버에서는 NOLOGGING 권장안함)

(NOLOGGING 후 FULL BACKUP 을 받아야하기때문)

1
2
3
SQL> ALTER TABLE HWMTEST1 NOLOGGING;
 
Table altered.

 

샘플 데이터 삽입(500만)

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
SQL> 
DECLARE
TYPE tbl_ins IS TABLE OF HWMTEST1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
END;
/
 
PL/SQL procedure successfully completed.
 

해당 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('JSH','HWMTEST1');
 
PL/SQL procedure successfully completed.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST1','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB          15360       120
HWMTEST1           88064       688

 

블록확인

1
2
3
4
5
6
7
8
9
10
11
SQL> COL TABLE_NAME FOR A16
SQL> 
SELECT TABLE_NAME,
   NUM_ROWS, 
   BLOCKS
FROM DBA_TABLES 
WHERE TABLE_NAME = 'HWMTEST1';
 
TABLE_NAME       NUM_ROWS    BLOCKS
---------------- ---------- ----------
HWMTEST1        5000000     87617

 

실제사용 블럭확인

1
2
3
4
5
6
7
8
9
SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
 
      USED
----------
     87567

     

샘플 데이터 삭제(400만)

1
2
SQL> DELETE HWMTEST1 WHERE ROWNUM<=4000000;
SQL> COMMIT;

혹시나 undo ts가 작아서 한번에 삭제가 안된다면 아래 방법 사용

1
2
3
4
5
6
7
8
SQL> 
BEGIN
FOR I IN 1 .. 8 LOOP
DELETE HWMTEST1 WHERE ROWNUM<=500000;
COMMIT;
END LOOP;
END;
/

 

테이블 LOGGING 기능 ON

1
2
3
SQL> ALTER TABLE HWMTEST1 LOGGING;
 
Table altered.

 

해당 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('JSH','HWMTEST1');
 
PL/SQL procedure successfully completed.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST1','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB          15360       120
HWMTEST1           88064       688

 

블록확인

1
2
3
4
5
6
7
8
9
10
11
SQL> COL TABLE_NAME FOR A16
SQL> 
SELECT TABLE_NAME,
   NUM_ROWS, 
   BLOCKS
FROM DBA_TABLES 
WHERE TABLE_NAME = 'HWMTEST1';
 
TABLE_NAME       NUM_ROWS    BLOCKS
---------------- ---------- ----------
HWMTEST1        1000000     87617

 

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
 
      USED
----------
     17518

 

DELETE 작업 후 테이블 및 인덱스 용량과 DBA_TABLE및 DBA_SEGMETNS의 BLOCK 수는 

DELETE 작업 전과 동일하지만(87617) 실제 사용 BLOCK은 17518로 줄어든것을 확인할 수 있습니다.

 

이는 HWM 까지 사용하고 있다고 표시하고 있기 때문이며, 이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면 HWM를 줄여주는 것이 좋습니다.

 

* HWM 줄여주는 방법 1

SHRINK

SHRINK 제약사항

- UNDO SEGMENTS

- TEMPORARY SEGMENTS

- CLUSTERED TABLE

- TABLE WITH A COLUMN OF DATATYPE LONG

- LOB INDEXES

- IOT MAPPING TABLES AND IOT OVERFLOW SEGMENTS

- TABLES WITH MVIEWS WITH ON COMMIT

- TABLES WITH MVIEWS ARE BASED ON ROWIDS

- Function Base Index

- nologging, parallel 수행 안됨

- 대용량 테이블일 경우 속도가 느리다.

 

SHRINK 실행

1
2
3
4
SQL> ALTER TABLE HWMTEST1 SHRINK SPACE;
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

 

ROW-MOVEMENT 활성화를 먼저 해줘야 SHRINK 사용이 가능함

1
2
3
SQL> ALTER TABLE HWMTEST1 ENABLE ROW MOVEMENT; 
 
Table altered.

 

SHRINK 다시 실행

1
2
3
SQL> ALTER TABLE HWMTEST1 SHRINK SPACE;
 
Table altered.

 

해당 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('JSH','HWMTEST1');
 
PL/SQL procedure successfully completed.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST1','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB          15360       120
HWMTEST1           17696    138.25

 

블록확인

1
2
3
4
5
6
7
8
9
10
11
SQL> COL TABLE_NAME FOR A16
SQL> 
SELECT TABLE_NAME,
   NUM_ROWS, 
   BLOCKS
FROM DBA_TABLES 
WHERE TABLE_NAME = 'HWMTEST1';
 
TABLE_NAME       NUM_ROWS    BLOCKS
---------------- ---------- ----------
HWMTEST1        1000000     17518

 

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
 
      USED
----------
     17518

SHRINK 작업 후 DBA_TABLES에서 조회한 BLOCKS와 실제 사용 BLOCK가 동일한 것을 확인할 수 있습니다.

 

하지만 인덱스(IDX_HWM_COLB)는 용량이 줄어들지 않은 것으로 보이는데 인덱스까지 같이 SHRINK 하려면 CASCADE옵션을 사용하면 인덱스까지 같이 SHRINK 가 진행됩니다.

1
2
3
SQL> ALTER TABLE HWMTEST1 SHRINK SPACE CASCADE;
 
Table altered.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST1','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB           2024    15.8125
HWMTEST1           17696    138.25

CASCADE 옵션을 사용한뒤 인덱스(IDX_HWM_COLB)도 용량이 줄어든걸 확인 할 수 있습니다.

 

SHRINK 작업결과 요약

 

 

* HWM 줄여주는 방법2

ALTER TABLE MOVE 실행

해당 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('JSH','HWMTEST2');
 
PL/SQL procedure successfully completed.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST2','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB          15360       120
HWMTEST2              88064       688

 

블록확인

1
2
3
4
5
6
7
8
9
10
11
SQL> COL TABLE_NAME FOR A16
SQL> 
SELECT TABLE_NAME,
   NUM_ROWS, 
   BLOCKS
FROM DBA_TABLES 
WHERE TABLE_NAME = 'HWMTEST2';
 
TABLE_NAME       NUM_ROWS    BLOCKS
---------------- ---------- ----------
HWMTEST2        5000000     87617

 

실제사용 블럭확인

1
2
3
4
5
6
7
8
9
SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST2;
 
      USED
----------
     87567

     

샘플 데이터 삭제(400만)

1
2
SQL> DELETE HWMTEST2 WHERE ROWNUM<=4000000;
SQL> COMMIT;

혹시나 undo ts가 작아서 한번에 삭제가 안된다면 아래 방법 사용

1
2
3
4
5
6
7
SQL> BEGIN
FOR I IN 1 .. 8 LOOP
DELETE HWMTEST2 WHERE ROWNUM<=500000;
COMMIT;
END LOOP;
END;
/

 

테이블 LOGGING 기능 ON

1
2
3
SQL> ALTER TABLE HWMTEST2 LOGGING;
 
Table altered.

 

해당 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('JSH','HWMTEST2');
 
PL/SQL procedure successfully completed.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST2','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB          15360       120
HWMTEST2              88064       688

 

블록확인

1
2
3
4
5
6
7
8
9
10
11
SQL> COL TABLE_NAME FOR A16
SQL> 
SELECT TABLE_NAME,
   NUM_ROWS, 
   BLOCKS
FROM DBA_TABLES 
WHERE TABLE_NAME = 'HWMTEST2';
 
TABLE_NAME       NUM_ROWS    BLOCKS
---------------- ---------- ----------
HWMTEST2        1000000     87617

 

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST2;
 
      USED
----------
     17518

 

DELETE 작업 후 테이블 및 인덱스 용량과 DBA_TABLE및 DBA_SEGMETNS의 BLOCK 수는 동일하지만

실제 사용 BLOCK은 17518로 줄어든것을 확인할 수 있고 이는 HWM 까지 사용하고 있다고 표시하고 있기 때문입니다.

이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면 HWM를 줄여주는 것이 좋습니다.

 

MOVE용 임시 테이블 스페이스 생성

1
2
3
SQL> CREATE TABLESPACE MOVE_IMSI DATAFILE '/oracle/app/oracle/oradata/movets01.dbf' SIZE 1G;
 
Tablespace created.

 

테이블 새로 생성한 테이블 스페이스로 HWMTEST2 테이블 MOVE

1
2
3
SQL> ALTER TABLE HWMTEST2 MOVE TABLESPACE MOVE_IMSI;
 
Table altered.

 

테이블 기존 테이블 스페이스로 이동

1
2
3
SQL> ALTER TABLE HWMTEST2 MOVE TABLESPACE MOVE_IMSI;
 
Table altered.

 

인덱스 조회

1
2
3
4
5
6
7
8
SQL> 
SELECT TABLE_NAME, INDEX_NAME, STATUS 
FROM DBA_INDEXES
WHERE TABLE_NAME = 'HWMTEST2';
 
TABLE_NAME     INDEX_NAME            STATUS
---------------- ------------------------------ --------
HWMTEST2     IDX_HWM_COLB            UNUSABLE

인덱스의 상태가 UNUSABLE 임

 

인덱스 REBUILD

1
2
3
SQL> ALTER INDEX IDX_HWM_COLB REBUILD;
 
Index altered.

 

Index rebuild 이유

Table의 Index 컬럼에 update와 작은 delete 발생 시 Index data는 지워지지 않으며 오히려 오라클 내부적으로 현재 이 인덱스 데이터는 삭제된 것이다라는 마킹만 하게 됩니다.

Update시에도 Update전의 Index데이터는 지워졌다 마킹해 놓고 Update Index 데이터를 Insert하는 방식으로 동작합니다.

삭제되었다 마킹된 부분은 공간을 재활용할 필요가 있을 경우 오라클에 의해 정리됩니다.

인덱스 컬럼에 대량의 Update, Delete가 빈번히 발생할 경우 실제 인덱스 사이즈에 비해 인덱스 세그먼트의 크기가 매우 커지게 되고 이러한 현상에 의해 Index B-Tree 구조의 밸런스가 붕괴되어  단편화가 발생해서 다른 리프 노드에 비해 루트 블록과의 거리가 더 멀거나 가까운 리프노드가 생길 수 있습니다.

위의 이유로 일반적으로 Index rebuild를 해주어야 합니다.

 

*참고

delete 작업 때문에 인덱스가 불균형 (Unbalanced) 상태에 놓일 수 있다고 설명한 자료들을 볼 수 있는데

아래 그림처럼 다른 리프 노드에 비해 루트 블록과의 거리가 더 멀거나 가까운 리프노드가 생길 수 있다는 설명인데 

B*Tree 인덱스에서 이런 현상은 절대 발생하지 않습니다. 

B*Tree 인덱스의 'B'가 'Balanced'의 약자임을 기억해야 합니다.

'Balanced'는 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음을 의미합니다.

즉 루트로부터 모든 리프 블록까지의 높이 (height)는 항상 같습니다.

- 친절한 SQL 튜닝 83p -

 

Oracle Docs 내용

테이블을 이동하면 테이블에서 행의 rowid가 변경되는데 이로 인해 테이블의 인덱스 상태가 UNUSABLE이 되고 인덱스를 사용하여 테이블에 액세스하는 DML은 ORA-01502 오류를 발생시킵니다. 그래서 테이블의 인덱스를 삭제하거나 rebuild 해야합니다.

마찬가지로, 테이블에 대한 통계는 유효하지 않으며 테이블을 이동 한 후 새 통계를 수집해야 합니다.

테이블에 LOB열이 포함 된 경우이 명령문을 사용 하여 사용자가 명시 적으로 지정하는 LOB데이터 및 LOB인덱스 세그먼트 (이 테이블과 연관된)와 함께 테이블을 move할 수 있습니다.. 지정하지 않으면 기본값은 LOB데이터 및 LOB인덱스 세그먼트를 move 하지 않는 것입니다.

 

해당 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('JSH','HWMTEST2');
 
PL/SQL procedure successfully completed.

 

용량확인

1
2
3
4
5
6
7
8
9
10
SQL> COL SEGMENT_NAME FOR A16
SQL> 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST2','IDX_HWM_COLB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
IDX_HWM_COLB           2048        16
HWMTEST2              18432       144

 

블록확인

1
2
3
4
5
6
7
8
9
10
11
SQL> COL TABLE_NAME FOR A16
SQL> 
SELECT TABLE_NAME,
   NUM_ROWS, 
   BLOCKS
FROM DBA_TABLES 
WHERE TABLE_NAME = 'HWMTEST2';
 
TABLE_NAME       NUM_ROWS    BLOCKS
---------------- ---------- ----------
HWMTEST2        1000000     17717

 

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST2;
 
      USED
----------
     17542

SHRINK에 비해 MOVE 사용시 BLOCK이 덜 감소한것을 확인 할 수 있습니다.

 

MOVE 작업결과 요약

 

 

참조 : http://tocsg.tistory.com/33

http://opendatabase.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%ED%85%8C%EC%9D%B4%EB%B8%94-%EC%B6%95%EC%86%8C-oracle-table-shrink-alter-table-tablename-shrink

 

오라클 테이블 축소 / oracle table shrink / alter table table_name shrink

오라클 테이블 축소 / oracle table shrink / alter table table_name shrink TOM 슨상님이 주신 방법으로 테이블을 만들도록 하자 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1132417600346069010 9999999 이라고

opendatabase.tistory.com

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1132417600346069010

 

The fastest way of creating a huge table for testing - Ask TOM

Test Case: 4 different technique for creating a huge table for testing Sandro, September 03, 2008 - 9:00 am UTC This is my test case with four different technique for creating a huge table for testing (reproducible in 10g - in 9i only 1, 2 and 3 - in 8i on

asktom.oracle.com:443

http://clipper0317.tistory.com/41

 

Altering Tables

You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints. You cannot d

docs.oracle.com

https://positivemh.tistory.com/350

 

오라클 reorg, hwm, shrink, move 설명 및 테스트

OS환경 : Oracle Linux6.8(64bit) DB 환경 : Oracle Database 11.2.0.4 설명 : 오라클 reorg, hwm, shrink, move 테스트 HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치임 만약 데이터가 대량으로 지워지면 이

positivemh.tistory.com

반응형
저작자표시 비영리 동일조건