[Oracle] 테이블 단편화 정보 확인 (리오그 대상 식별법)

 

오라클 DB를 사용하다보면 단편화 때문에 한 번씩 리오그를 통한 효율화를 해줘야 하는 상황이 찾아옵니다.

 

 

1. 단편화가 발생하는 이유

오라클은 1개에 8KBytes인 블록 단위로 데이터를 저장하는데 아래와 같은 변화 과정을 거칩니다.

 

아래 그림의 흰 바탕의 네모 하나를 블록이라고 할때,

 

1) 초기 블록 구조

 

2) 데이터 Insert

 

3) 중간 데이터 Delete

 

 

 

 

자연스런 Insert와 Delete의 과정을 거치면서 Block 중간에 빈 공간이 존재하게 됩니다.

이러한 빈 공간은 리오그(새 테이블을 만들어 데이터를 모두 이동해 단편화된 Block을 합치는 방법)를 통해

빈 공간 없이 효율화할 수 있습니다.

 

참고 링크 - https://subbak2.tistory.com/14

[Oracle 11g~] 온라인 테이블 redefinition (reorg)

 

 

 

2. 단편화 정보 확인 방법

 

블록의 빈 공간을 분석할 수 있는 프로시저를 오라클에서는 기본적으로 제공합니다.

 

바로 DBMS_SPACE 패키지의 SPACE_USAGE 프로시저입니다.

오라클 참고 링크 - 

https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68113

 

1) 테이블 단편화 정보 확인

declare
   l_fs1_bytes number;
   l_fs2_bytes number;
   l_fs3_bytes number;
   l_fs4_bytes number;
   l_fs1_blocks number;
   l_fs2_blocks number;
   l_fs3_blocks number;
   l_fs4_blocks number;
   l_full_bytes number;
   l_full_blocks number;
   l_unformatted_bytes number;
   l_unformatted_blocks number;
begin
   dbms_space.space_usage(
      segment_owner      => 'HR',                   -- 1. Owner 입력
      segment_name       => 'EMPLOYEE',             -- 2. 테이블 이름 입력
      segment_type       => 'TABLE',                -- 3. 세그먼트 종류 입력 (여기서는 테이블)
      fs1_bytes          => l_fs1_bytes,
      fs1_blocks         => l_fs1_blocks,
      fs2_bytes          => l_fs2_bytes,
      fs2_blocks         => l_fs2_blocks,
      fs3_bytes          => l_fs3_bytes,
      fs3_blocks         => l_fs3_blocks,
      fs4_bytes          => l_fs4_bytes,
      fs4_blocks         => l_fs4_blocks,
      full_bytes         => l_full_bytes,
      full_blocks        => l_full_blocks,
      unformatted_blocks => l_unformatted_blocks,
      unformatted_bytes  => l_unformatted_bytes
   );
   dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
   dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
   dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
   dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
   dbms_output.put_line('Full Blocks = '||l_full_blocks||'
   Bytes = '||l_full_bytes);
end;
/

SEGMENT의 OWNER, NAME, TYPE을 정보에 맞게 입력해서 실행하면 

이러한 결과가 나옵니다. 

FS는 Free Space의 약자로

 

FS1 : 0~24%

FS2 : 25~49%

FS3 : 50~74%

FS4 : 75~99%

Full Blocks : 100%

 

각각의 %만큼 공간을 사용중임을 의미합니다.

여기서 Free Space가 얼마이든 각 Block은 8KBytes이므로 (8,192 Bytes)

Blocks * 8,192가 각 Bytes의 값으로 나옵니다. (4 * 8,192 = 32,768)

 

이 결과에 FS1~FS3인 블록이 많이 나온다면 

리오그를 했을때 용량이 많이 줄어준다는 것을 예측할 수 있습니다.

 

 

2) 인덱스 단편화 정보 확인

declare
   l_fs1_bytes number;
   l_fs2_bytes number;
   l_fs3_bytes number;
   l_fs4_bytes number;
   l_fs1_blocks number;
   l_fs2_blocks number;
   l_fs3_blocks number;
   l_fs4_blocks number;
   l_full_bytes number;
   l_full_blocks number;
   l_unformatted_bytes number;
   l_unformatted_blocks number;
begin
   dbms_space.space_usage(
      segment_owner      => 'HR',                   -- 1. Owner 입력
      segment_name       => 'EMP_DEPARTMENT_IX',    -- 2. 인덱스 이름 입력
      segment_type       => 'INDEX',                -- 3. 세그먼트 종류 입력 (여기서는 인덱스)
      fs1_bytes          => l_fs1_bytes,
      fs1_blocks         => l_fs1_blocks,
      fs2_bytes          => l_fs2_bytes,
      fs2_blocks         => l_fs2_blocks,
      fs3_bytes          => l_fs3_bytes,
      fs3_blocks         => l_fs3_blocks,
      fs4_bytes          => l_fs4_bytes,
      fs4_blocks         => l_fs4_blocks,
      full_bytes         => l_full_bytes,
      full_blocks        => l_full_blocks,
      unformatted_blocks => l_unformatted_blocks,
      unformatted_bytes  => l_unformatted_bytes
   );
   dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
   dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
   dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
   dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
   dbms_output.put_line('Full Blocks = '||l_full_blocks||'
   Bytes = '||l_full_bytes);
end;
/

 

인덱스의 경우에도 SEGMENT NAME과 TYPE만 바꿔서 넣어주면 됩니다.

 

일반적으로 인덱스의 경우 효율화의 효과가 더 크기 때문에 

인덱스를 먼저 확인하는 것이 좋습니다.

(리오그가 아닌 인덱스 리빌드 등으로 효율화 가능)

 

 

위 프로시저는 DB Guide Net의 예시를 참고했습니다.

(참고 링크 - http://www.dbguide.net/db.db?boardConfigUid=9&boardIdx=51&boardStep=1&boardUid=12934&categoryUid=216&cmd=view)