안녕하세요
며칠 전에 처음으로 작업했던
 
move를 통한 reorg 작업을 간단히 소개하겠습니다.
 
물론 운영중인 상황에서는 MOVE를 통한 작업은 힘들고
개발자와 협의 후 진행해야 한다는 점 꼭 참고하시면서 읽어주세요
 
 
 

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 후 작업 종료

 
감사합니다 :)