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

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

DB 환경 : Oracle Database 11.2.0.4

설명 : 오라클 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 로 삭제했을 경우의 성능이 다르게 나올 것임

테스트 :

샘플 테이블 및 데이터 생성

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 을 받아야하기때문)

SQL> ALTER TABLE HWMTEST1 NOLOGGING;
 
Table altered.

샘플 데이터 삽입(500만)

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.

HWMTEST2 테이블 생성(나중에 테스트 할 MOVE 테스트)

1	CREATE TABLE HWMTEST2 AS SELECT * FROM HWMTEST1;

해당 테이블 통계정보 수집

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

용량확인

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

블록확인

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

실제사용 블럭확인

SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
 
      USED
----------
     87567

     

샘플 데이터 삭제(400만)

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

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

SQL> 
BEGIN
FOR I IN 1 .. 8 LOOP
DELETE HWMTEST1 WHERE ROWNUM<=500000;
COMMIT;
END LOOP;
END;
/

테이블 LOGGING 기능 ON

SQL> ALTER TABLE HWMTEST1 LOGGING;
 
Table altered.

해당 테이블 통계정보 수집

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

용량확인

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

블록확인

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 확인

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 실행

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

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

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

SHRINK 다시 실행

SQL> ALTER TABLE HWMTEST1 SHRINK SPACE;
 
Table altered.

해당 테이블 통계정보 수집

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

용량확인

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

블록확인

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 확인

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옵션을 사용하면 됨.

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

용량확인

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 실행

해당 테이블 통계정보 수집

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

용량확인

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

블록확인

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

실제사용 블럭확인

SQL> 
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST2;
 
      USED
----------
     87567

     

샘플 데이터 삭제(400만)

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

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

SQL> BEGIN
FOR I IN 1 .. 8 LOOP
DELETE HWMTEST2 WHERE ROWNUM<=500000;
COMMIT;
END LOOP;
END;
/

테이블 LOGGING 기능 ON

SQL> ALTER TABLE HWMTEST2 LOGGING;
 
Table altered.

해당 테이블 통계정보 수집

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

용량확인

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

블록확인

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 확인

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용 임시 테이블 스페이스 생성

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

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

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

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

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

인덱스 조회

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

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 튜닝 83page-

Oracle Docs 내용

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

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

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

해당 테이블 통계정보 수집

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

용량확인

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

블록확인

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 확인

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

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

http://clipper0317.tistory.com/41

https://m.blog.naver.com/itperson/220878447705

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables006.htm#ADMIN11660