테이블 단편화(Fragmentation)

테이블 단편화란 Reorg를 진행해야하는 가장 큰 이유중에 하나입니다. 

 

테이블 단편화의 이슈로 발생할 수 있는 문제점은 아래와 같습니다.

1.성능저하

2.불필요한 disk 용량 증가

 

이 문제점을 해결하기 위해서 정기적으로 Reorg작업을 수행합니다.

 

Reorg 대상 확인

가장먼저 Reorg 대상을 확인해야 합니다.

(통계정보가 최신화 되어 있으면 정확한 분석이 가능합니다. 혹, 영향도가 있다면 테이블 row 전체를 count하여 진행하여도 됩니다.)

 

아래는 스키마단위로 통계정보를 수동으로 수집하는 명령어입니다. 15%의 sample을 이용하여 통계정보를 재생성하는 명령어입니다.

exec dbms_stats.gather_schema_stats('NEWMPDB', estimate_percent=>15);

DBA_TABLES 테이블을 참조하여 블록낭비가 얼만큼 되고 있는지 확인할 수 있습니다.

 

출처 : docs.oracle.com/database/121/REFRN/GUID-6823CD28-0681-468E-950B-966C6F71325D.htm#REFRN20286

ALL_TABLES

사용하는 컬럼에 대해서 설명입니다.

num_rows(전체 rows 수)-  테이블이 가지고 있는 데이터 row의 수입니다. count를 통해서 사용해도 무방합니다.

avg_row_len(평균 row의 길이(bytes단위) - 통계분석을 통해 근사값으로 사용을 권장합니다.

blocks_needed - 산술적으로 필요한 blocks수를 계산한 값입니다.

Frag Block - dba_blocks상에서 blocks와 blocks_needed의 차이를 구한 컬럼입니다. 이 값이 클 수록 단편화 현상이 심한 테이블인 것을 활인할 수 있습니다.

 

select table_name,
           blocks, num_rows, avg_row_len, pct_free,
           ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) as "blocks_needed"
           ,blocks - (ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100)))) as "Frag Block"
           ,last_analyzed
      from dba_tables
      where owner in ('스키마이름') and blocks is not null
      and blocks > 10
      order by 7 desc

 

 

실제 SEGMENT의 용량도 체크하여 줍니다. 이것은 Reorg 전 후 용량 변화를 비교하기 위함입니다.

 

SELECT OWNER,SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE='TABLE' 
and owner='스키마이름'
and blocks > 10
ORDER BY OWNER DESC, MB DESC;

 

 

 

REORG 수행

Reorg의 방법은 여러가지가 있습니다.

1.CTAS

2.EXP ->  TRUNCATE -> IMP

3.alter table move tablespace & index rebuild

4.Shrink

 

저는 3번인 alter table move tablespace 방법을 소개하겠습니다. 테이블을 다시 Tablespace에 옮김으로써 단편화 현상을 해소하고 용량을 줄일 수 있습니다. 만약 index가 생성된 테이블이라면 index는 unusable 상태로 변하기 때문에 꼭 index rebuild 작업도 같이 병행해야 합니다.

 

MOVE TABLE 스크립트

SELECT 'ALTER TABLE ' || OWNER || '.' || SEGMENT_NAME || ' MOVE TABLESPACE ' || TABLESPACE_NAME || ';' 
FROM DBA_SEGMENTS
where segment_name in (
<테이블 명>
)

 

INDEX REBUILD 스크립트

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE '||TABLESPACE_NAME||';'
FROM DBA_INDEXES
WHERE OWNER IN(<스키마 명>) AND STATUS ='UNUSABLE';

 

쿼리는 where 절을 변형함으로써 다양하게 사용 가능합니다.

 

 참고 : semode.tistory.com/232