[Oracle] 조각화된 테이블의 재구성 기준 및 방법

데이터베이스 월간 점검항목 중에 조각화된 테이블을 재구성하는 것은 테이블의 성능을 최적화하고 저장 공간을 효율적으로 사용하는 데 중요한 작업입니다. 블록 수가 많은 테이블은 데이터 액세스 성능에 큰 영향을 미칠 수 있기 때문에, 재구성을 통해 성능을 최적화할 수 있습니다. 조각화 비율이 높을수록 데이터 액세스 성능이 저하될 가능성이 크기 때문에 조각화된 테이블을 재구성하는 적절한 시기를 판단하는 것은 중요합니다.

1. 블록 수 기준
   테이블을 재구성할 블록 수 기준을 설정할 때 다음과 같은 접근 방법을 고려할 수 있습니다
   • 1000 블록 이상: 중간 크기 이상의 테이블로, 주기적인 재구성이 필요할 수 있습니다.
   • 5000 블록 이상: 큰 테이블로, 조각화가 성능에 미치는 영향이 클 수 있습니다.
   • 10000 블록 이상: 매우 큰 테이블로, 성능 최적화를 위해 재구성이 필요할 가능성이 높습니다.
2. 조각화 비율
   일반적으로 조각화 비율이 20-30%를 초과하면 재구성을 고려해야 합니다. 이 기준은 데이터베이스의 사용 패턴, 테이블 크기, 성능 요구 사항 등에 따라 다를 수 있습니다.
   • 20% 이하: 보통 조각화가 심하지 않으므로 큰 성능 문제가 없다면 재구성을 하지 않아도 됩니다.
   • 20-30%: 조각화가 중간 정도이며, 이 시점에서 테이블의 성능을 주기적으로 모니터링하고 필요에 따라 재구성을 고려할 수 있습니다.
   • 30% 이상: 조각화가 심한 상태이므로, 성능 저하가 발생할 가능성이 높습니다. 이 경우에는 테이블을 재구성하는 것이 좋습니다.

테이블이 조각화되었는지 확인하고 Oracle에서 테이블 조각화를 제거하는 절차를 설명하겠습니다.

1. 테이블 통계 수집
2. 테이블 크기 확인
3. 조각화 제거
4. 테이블 통계 재수집
5. 테이블 크기 재확인

1. 테이블 통계 수집

최근에 테이블 통계를 수집한 경우 다음 단계로 건너뛰고, 그렇지 않으면 테이블 통계를 수집하세요.

EXEC dbms_stats.gather_table_stats(ownname => 'OWNER_NAME', tabname => 'TABLE_NAME', cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

2. 테이블 크기 확인

dba_segments 테이블에서 지정된 테이블의 세그먼트 크기를 확인하고, dba_tables 테이블에서 지정된 테이블의 실제 테이블 크기, 조각화 크기 및 조각화 비율을 확인합니다.

col SIZE_in_GB for 99.990 heading "Segment Size|in GB";
col owner for a10 heading "Owner";
col table_name for a30 heading "Table Name";
col TOTAL_SIZE_MB for 999999999999 heading "Total Size|in MB";
col ACTUAL_SIZE_MB for 999999999999 heading "Actual Size|in MB";
col FRAGMENTED_SPACE_MB for 999999999999 heading "Fragmented Space|in MB";
col FRAGMENTED_RATIO for 999.90 heading "Fragment(%)";
define v_owner = 'OWNER_NAME';
define v_table = 'TABLE_NAME'
SELECT SUM(bytes)/1024/1024/1024 AS SIZE_in_GB
FROM   dba_segments
WHERE  owner = '&v_owner'
AND    segment_name='&v_table';
SELECT owner
     , table_name
     , blocks
     , num_rows
     , avg_row_len
     , ROUND(((blocks*8*1024)/1024/1024),0) AS TOTAL_SIZE_MB
     , ROUND((num_rows*avg_row_len/1024/1024),0) AS ACTUAL_SIZE_MB
     , ROUND(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) AS FRAGMENTED_SPACE_MB
     , ROUND(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024))/((blocks*8*1024)/1024/1024)*100, 2) AS FRAGMENTED_RATIO
FROM   dba_tables
WHERE  owner = '&v_owner'
AND    table_name = '&v_table';

3. 조각화 제거

아래 쿼리를 사용하여 테이블에서 조각화를 제거하십시오.

alter table <table_name> move;

테이블의 인덱스 재구축합니다.

alter index <index_name> rebuild online;

4. 테이블 통계 재수집

1단계의 “테이블 통제 수집” 쿼리를 재실행합니다.

5. 테이블 크기 재확인

2단계의 “테이블 크기 확인” 쿼리를 재실행하여 기존 데이터와 비교합니다.