Table/Index 에 대한 move를 통한 reorg 작업

move를 통한 reorg 작업을 간단히 소개하겠습니다.

** 운영중인 상황에서는 MOVE를 사용해서는 안됨(REORG 작업으로 처리해야됨.

    ==> DBMS_REDEFINITION 패키지를 이용한 처리 참조:  https://argolee.tistory.com/19

 

 

1.REORG란?

 

복잡하고 불필요하게 공간을 차지하고 있는 오브젝트들을 재편성하여

액세스 되는 블록을  감소시키는 등 (ROW Migration, ROW chaining 현상 완화)

용량 확보 및 SQL 쿼리의 성능 향상을 꾀하는 작업

 

REORG 작업의 필요성

 

1) 요구되는 전체 I/O 의 최소화

 

2) 낭비된 디스크 공간의 복구

=>HWM 감소시켜 datafile resize 작업 가능

 

3) 과다하게 확장된 스페이스의 교정 작업

 

 

*HWM란?

=>SEGMENT에서 사용한 '적' 이 있는 BLOCK 과 아예 없는 BLOCK간의 경계선

테이블에 데이터가 insert 될 때 할당된 블록들 중 Free Block에 row가 들어감

마지막 까지 사용됐던 block이 HWM로 남는데

만약 데이터가 삭제(delete) 되어도 HWM는 변경되지 않음.

 

문제점

1) 풀스캔 시, 쿼리 수행 시간이 길어짐

=>사용자가 테이블을 풀 스캔 할 때 위와 같이 데이터가 있는 3번까지의 블록을 스캔하는 게 아니라

HWM아래쪽 영역인 1~5번까지의 블록을 스캔하기 때문

2) append hint를 사용한 insert 등 특정 insert 시 공간 낭비

=> 특정 조건의 INSERT 시 HWM아래의 영역에 빈 공간이 있어도 HWM 위쪽으로 데이터가 추가되어

아래쪽 영역이 낭비됨

* 사진 출처: http://tocsg.tistory.com/33 [투씨에스지 기술 블로그]

 

3) RESIZE 불가

 

 

=>10GB 중 6GB만 사용하는 테이블스페이스이므로

TOTALSPACE를 7GB로 RESIZE가 가능해야함

 

ALTER TABLESPACE TS_REORG RESIZE 7G;

 

==>실사용량은 6GB지만 HWM가 7GB 보다 더 높게 설정되어있다는 의미

 

 

 

2. REORG 작업 절차

 

1)REORG 대상 TABLESPACE에 있는SEGMENT 조회

 

 

 

 

해당 테이블스페이스에 6종류의 SEGMENT가 있음을 확인함

SEGMENT_TYPE을 확인하는 이유는 TYPE마다 MOVE 해주는 방법이 다르기 때문

 

추가로

 long column을 가진 TABLE 조회

 

 

=>LONG column 을 가진 Table은 일반적인 Table 과는 달리 move 가 안되므로 export/import 해줘야함

 

 

2)  MOVE 쿼리 추출

 

1)  TABLE

 

SELECT 'ALTER TABLE ' || OWNER || '.' || SEGMENT_NAME || ' MOVE TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS  

WHERE TABLESPACE_NAME ='TS_NPT_DEV_D'

AND SEGMENT_TYPE='TABLE';

 

 

 

2)   TABLE PARTITION

 

SELECT 'ALTER TABLE ' || OWNER || '.' || SEGMENT_NAME ||' MOVE ' || REPLACE(SEGMENT_TYPE,'TABLE','')||' ' || PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS  

WHERE TABLESPACE_NAME = 'TS_NPT_DEV_D'

AND SEGMENT_TYPE IN ('TABLE PARTITION','TABLE SUBPARTITION');

 

 

 

3)   INDEX

 

SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS   

WHERE TABLESPACE_NAME='TS_NPT_DEV_D'

AND SEGMENT_TYPE = 'INDEX';

 

 

 

4)  INDEX PARTITION

 

SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD ' || REPLACE(SEGMENT_TYPE,'INDEX','')|| ' '||PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME ||';'

FROM DBA_SEGMENTS    

WHERE TABLESPACE_NAME='TS_NPT_DEV_D'

AND SEGMENT_TYPE IN ('INDEX PARTITION','INDEX SUBPARTITION');

 

 

*참고사항

1) TABLE과 INDEX의 경우, MOVE 와 REBUILD 의 차이가 있음

2) PARTITION의 경우 MOVE 뒤에 해당 테이블의 어떤 PARTITION을 MOVE 할 지 지정해야 하기 때문에

PARTITION_NAME 정보가 추가로 붙음

 

 

5) LONG column TABLE

 

OS> EXPDP TABLES=NPT_DW_DEV.PLAN_TABLE DIRECTORY=DATA_PUMP_DIR DUMPFILE=0110_DW.dmp LOGFILE=0110_DW.log

OS> IMPDP  DIRECTORY=DATA_PUMP_DIR DUMPFILE=0110_DW.dmp logfile=0110_imp_dw.log tablespaces=TS_NPT_DEV_D           remap_schema=NPT_DW_DEV:NPT_DW_DEV

 

*LONG은 EXPORT/IMPORT 방법으로만 MOVE 가능

 

6) LOB SEGMENT

 

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME ||' MOVE LOB (' ||COLUMN_NAME||')' || ' STORE AS (TABLESPACE TS_REORG);'

FROM   DBA_LOBS

WHERE  SEGMENT_NAME IN (SELECT SEGMENT_NAME

        FROM   DBA_SEGMENTS

        WHERE  TABLESPACE_NAME='TS_NPT_DEV_D'

        AND    SEGMENT_TYPE LIKE 'LOB%');

        

*참고사항

1)  LOB의 경우 속한 테이블과는 별도의 SEGMENT로 생성이 되기 때문에  TABLE 밖에 생성이 된다.

    그래서 MOVE 할 경우, LOB 테이블의 해당 LOB COLUMN 자체를 옮겨주어야 함

 

2)  세그먼트를 별도로 가지는 LOB과 같은 Data type은 

비구조적,비정형적인 특성의 사이즈가 큰 데이터셋을 보관하기 위한 용도로 사용됨.

테이블의 lob column엔 실제 데이터가 저장된 segment를 가리키는 포인트 주소값만 갖고 있음

 

*LOB SEGMENT 확인

1) LOB TABLE 생성 후 METADATA 확인

 

2) LOB_TEST 테이블 SEGMENT와 TEXT (LOB SEGMENT) 별도로 생성된 것 확인

 

 

 

3) MOVE 후 TABLESPACE RESIZE

MOVE를 통한 REORG 작업 완료 후엔 위와 같이 HWM가 내려오고

블락 내부의 빈공간들이 재편성됨

 

=> TABLESPACE RESIZE 후 작업 종료