주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

데이터파일 단편화 진단

문서유형ㅣ장애해결

분야ㅣ모니터링/점검

적용제품버전ㅣTibero6, Tibero7

문서번호ㅣTMOTS032

 

현상

Tibero 운영 중 sys.log에 다음과 같은 에러가 빈번하게 발생합니다.

ec=ERROR_TX_CANT_ALLOC_EXT(-21004)
No more extent available in tablespace 'TIBERO_TS'.

해당 에러는 테이블스페이스에서 extent를 할당할 수 없는 상황에서 발생합니다.

 

 

원인

해당 에러는 단순히 물리적인 공간이 부족할 때뿐만 아니라, 공간은 충분하나 사용 가능한 형태가 아닐 때도 발생합니다.

1. 테이블스페이스 용량 부족 (정상적인 경우)

테이블스페이스의 전체 용량이 부족하여 extent를 할당할 수 없는 경우입니다.

 

2. 데이터 파일 단편화(Fragmentation)

테이블스페이스 내부의 free 영역이 단편화되어 연속된 공간이 부족한 경우입니다.

지속적인 테이블 생성 및 삭제가 발생하면 free 공간이 다음과 같이 분산됩니다.

  • [사용중] [FREE] [사용중] [FREE] [사용중] [사용중] [FREE]

이 경우 전체 free space는 충분하지만, 연속된 큰 공간이 부족하여 extent 할당이 실패합니다.

 

Tibero 에서는 세그먼트가 커질 수록 점점 더 큰 Extent를 할당합니다. 

세그먼트 크기요청하는 extent 크기
1M 이하64K (16 block)
64M 이하1M (128 block)
1G 이하8M (1024 block)
1G 이상64M (8192 block)

 

즉, 세그먼트가 커질수록 Tibero에서 더 큰 Extent를 배정하려고 하며, 큰 연속 공간이 필요해집니다.

Tablespace 단편화가 진행 되면, free space 전체 사이즈는 충분하지만, 연속된 큰 공간이 없어서 extent 할당에 실패할 수 있습니다.

1. 가장 큰 적정 extent 크기를 요청
2. 실패 시 절반 크기로 재시도 (마지막 최소 extent 까지 재시도)
3. 마지막 최소 extent 할당 시 free block 이 없으면 할당 실패 및 에러 발생

 

TABLESPACE 단편화 확인 SQL

WITH free AS (
    SELECT blocks
    FROM dba_free_space
    WHERE tablespace_name = 'USR'
)
SELECT
    SUM(CASE WHEN blocks >= 8192 THEN 1 END) AS cnt_8192,
    ROUND(SUM(CASE WHEN blocks >= 8192 THEN floor(blocks/8192)*8192*8/1024/1024 END),2) AS mb_8192,

    SUM(CASE WHEN blocks >= 4096 THEN 1 END) AS cnt_4096,
    ROUND(SUM(CASE WHEN blocks >= 4096 THEN floor(blocks/4096)*4096*8/1024/1024 END),2) AS mb_4096,

    SUM(CASE WHEN blocks >= 2048 THEN 1 END) AS cnt_2048,
    ROUND(SUM(CASE WHEN blocks >= 2048 THEN floor(blocks/2048)*2048*8/1024/1024 END),2) AS mb_2048,

    SUM(CASE WHEN blocks >= 1024 THEN 1 END) AS cnt_1024,
    ROUND(SUM(CASE WHEN blocks >= 1024 THEN floor(blocks/1024)*1024*8/1024/1024 END),2) AS mb_1024,

    SUM(CASE WHEN blocks >= 512 THEN 1 END) AS cnt_512,
    ROUND(SUM(CASE WHEN blocks >= 512 THEN floor(blocks/512)*512*8/1024/1024 END),2) AS mb_512,

    SUM(CASE WHEN blocks >= 256 THEN 1 END) AS cnt_256,
    ROUND(SUM(CASE WHEN blocks >= 256 THEN floor(blocks/256)*256*8/1024/1024 END),2) AS mb_256,

    SUM(CASE WHEN blocks >= 16 THEN 1 END) AS cnt_16,
    ROUND(SUM(CASE WHEN blocks >= 16 THEN floor(blocks/16)*16*8/1024/1024 END),2) AS mb_16
FROM free;



  CNT_8192    MB_8192   CNT_4096    MB_4096   CNT_2048    MB_2048   CNT_1024    MB_1024    CNT_512     MB_512    CNT_256     MB_256     CNT_16      MB_16
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       1.69          1       1.72          1       1.73          1       1.74          1       1.75          1       1.75          1       1.75

  • CNT_8192

    8192 blocks(= 64MB 정도) 이상의 연속 free extent 가 몇 개인가를 나타냅니다.

    값이 0 → 64MB 크기의 연속 free 공간이 하나도 없음 → 대형 세그먼트 확장 불가

     

  • MB_8192

    8192블록 단위로 사용할 수 있는 free 공간의 총합입니다.(단위를 MB로 환산)

    (예: 5라면 → 64MB 단위 free space 를 합친 usable space가 총 5MB 정도)

 

해당 쿼리로 알 수 있는 부분은 다음과 같습니다.

  1. 현재 TABLESPACE에 큰 연속 free 공간이 있는가?

  2. 세그먼트가 다음 extent 할당 시 실패 할 가능성이 있는가?

  3. free space 는 많지만 조각나 있어서 TBR-21004 발생할 가능성이 큰가?

  4. 테이블/인덱스 MOVE 가 필요한 단편화 상태인가?

 

 

해결

TBR-21004 (ERROR_TX_CANT_ALLOC_EXT) 를 조치하기 위해서는 2가지 방법으로 조치가 가능합니다.

데이터 파일 추가 / 및 기존 데이터 파일 사이즈 늘리기

# 데이터 파일 추가
ALTER TABLESPACE USR ADD DATAFILE '/path/usr02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;


# 기존 데이터 파일 사이즈 늘리기

ALTER DATABASE DATAFILE '/path/usr01.dbf' RESIZE 30G;

 

Table 내부 단편화가 심한 경우에 사용 (Table Move + Index Rebuild)

# Table Move
ALTER TABLE 테이블명 MOVE TABLESPACE USR;

# Index Rebuild


alter system set _INDEX_BUILD_USING_FULL_SCAN=Y;
ALTER INDEX 인덱스명 REBUILD;
alter system set _INDEX_BUILD_USING_FULL_SCAN=N;

 

참고

아래 SQL은 USR Tablespace 에 대한 단편화 진단을 할 수 있는 SQL입니다.

SET SERVEROUTPUT ON
DECLARE
   -- 결과 저장 변수
   cnt_8192 NUMBER; mb_8192 NUMBER;
   cnt_4096 NUMBER; mb_4096 NUMBER;
   cnt_2048 NUMBER; mb_2048 NUMBER;
   cnt_1024 NUMBER; mb_1024 NUMBER;
   cnt_512  NUMBER; mb_512  NUMBER;
   cnt_256  NUMBER; mb_256  NUMBER;
   cnt_16   NUMBER; mb_16   NUMBER;

   v_msg VARCHAR2(4000);
BEGIN
   -- Free Space 요약 조회
   WITH free AS (
       SELECT blocks
       FROM dba_free_space
       WHERE tablespace_name = 'USR'
   )
   SELECT
       SUM(CASE WHEN blocks >= 8192 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 8192 THEN floor(blocks/8192)*8192*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 4096 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 4096 THEN floor(blocks/4096)*4096*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 2048 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 2048 THEN floor(blocks/2048)*2048*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 1024 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 1024 THEN floor(blocks/1024)*1024*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 512 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 512 THEN floor(blocks/512)*512*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 256 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 256 THEN floor(blocks/256)*256*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 16 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 16 THEN floor(blocks/16)*16*8/1024/1024 END),2)
   INTO
       cnt_8192, mb_8192,
       cnt_4096, mb_4096,
       cnt_2048, mb_2048,
       cnt_1024, mb_1024,
       cnt_512 , mb_512 ,
       cnt_256 , mb_256 ,
       cnt_16  , mb_16
   FROM free;

   DBMS_OUTPUT.PUT_LINE('=== 테이블스페이스 단편화 자동 분석 결과 (USR) ===');
   DBMS_OUTPUT.PUT_LINE('');

   DBMS_OUTPUT.PUT_LINE('64MB(8192 block) 연속공간: ' || cnt_8192 || '개,  총 ' || mb_8192 || ' MB');
   DBMS_OUTPUT.PUT_LINE('32MB(4096 block) 연속공간: ' || cnt_4096 || '개,  총 ' || mb_4096 || ' MB');
   DBMS_OUTPUT.PUT_LINE('16MB(2048 block) 연속공간: ' || cnt_2048 || '개,  총 ' || mb_2048 || ' MB');
   DBMS_OUTPUT.PUT_LINE('');

   -- 분석 로직 -----------------------------------------

   -- ① 가장 큰 64MB 단위 판단
   IF cnt_8192 = 0 THEN
       v_msg := v_msg || '* 64MB 이상의 연속 free space 없음 → 대형 세그먼트 확장 위험 가능성 높음)' || chr(10);
   ELSE
       v_msg := v_msg || '* 64MB 이상의 연속 free space 존재 → 대형 세그먼트 확장 가능' || chr(10);
   END IF;

   -- ② 32MB 단위
   IF cnt_4096 = 0 THEN
       v_msg := v_msg || '* 32MB 연속 free space 없음 → 중대형 세그먼트 확장 시 위험' || chr(10);
   END IF;

   -- ③ free space 는 많으나 조각화 여부
   IF cnt_8192 = 0 AND cnt_4096 = 0 AND cnt_2048 > 0 THEN
       v_msg := v_msg || '* free space 는 있지만 큰 조각이 없어 심각한 단편화 상태' || chr(10);
   END IF;

   -- ④ 실제 데이터파일 부족 여부(참고용)
   IF mb_16 < 50 THEN
       v_msg := v_msg || '* 전체 free space 가 50MB 미만 → 실제 공간 부족 가능성도 있음' || chr(10);
   END IF;

   -- ⑤ 최종 위험도 평가
   DBMS_OUTPUT.PUT_LINE('--- 단편화 종합 진단 ---');
   IF cnt_8192 = 0 AND cnt_4096 = 0 THEN
       DBMS_OUTPUT.PUT_LINE('>> 위험도: ★★★ (높음)');
   ELSIF cnt_8192 = 0 AND cnt_4096 > 0 THEN
       DBMS_OUTPUT.PUT_LINE('>> 위험도: ★★ (중간)');
   ELSE
       DBMS_OUTPUT.PUT_LINE('>> 위험도: ★ (낮음)');
   END IF;

   DBMS_OUTPUT.PUT_LINE('');
   DBMS_OUTPUT.PUT_LINE(v_msg);

END;
/

 

[수행결과]

64MB(8192 block) 연속공간: 1개,  총 1.69 MB
32MB(4096 block) 연속공간: 1개,  총 1.72 MB
16MB(2048 block) 연속공간: 1개,  총 1.73 MB

--- 단편화 종합 진단 ---
>> 위험도: ★ (낮음)

* 64MB 이상의 연속 free space 존재 → 대형 세그먼트 확장 가능
* 전체 free space 가 50MB 미만 → 실제 공간 부족 가능성도 있음

 

 

관련 대상