ORACLE 청크 공간 재사용 테스트, clob

Admin

ORACLE 청크 공간 재사용 테스트, clob

돼지와함께춤을 2025. 12. 30. 10:14

개요

업무 배치 수행 중 특정 테이블스페이스의 사용량이 비정상적으로 급증하여, 데이터파일을 사전에 추가하지 못하고 TS_FULL 상황이 발생한 사례가 있었다.
일반적으로 데이터파일의 MAX_SIZE는 30GB로 설정해 두고, 필요 시 점진적으로 확장하는 방식으로 운영하고 있었으나, 해당 배치는 매시간 수행될 때마다 테이블스페이스 사용량이 갑자기 30GB를 초과하는 증가 폭을 보였다.

문제가 된 배치의 처리 방식은 다음과 같다.

기존 데이터를 전량 DELETE
이후 신규 데이터를 일괄 INSERT
신규 데이터의 총 데이터량은 기존 데이터와 유사한 수준

데이터의 총량이 유사함에도 불구하고 테이블스페이스 사용량이 지속적으로 증가했다는 점에서,
기존 데이터 삭제에도 반환된 공간(블록 또는 LOB 청크)이 재사용되지 못하고 누적되고 있다고 판단.

특히 대상 테이블에는 CLOB 컬럼이 포함되어 있었으며,
일반 컬럼과는 다른 LOB 세그먼트의 공간 관리 방식(청크 단위) 이 이 문제의 핵심 원인일 가능성이 높다고 보았다.

이에 따라 글에서는 다음을 확인하기 위해 여러 테스트 케이스를 구성하였다.

CLOB 세그먼트에서 DELETE 이후 공간(청크)이 실제로 재사용되는지
블록 단위 공간 재사용과 LOB 청크 재사용의 차이
LOB RETENTION 설정이 청크 재사용에 미치는 영향

일반 세그먼트의 블록(Block) 과 LOB 세그먼트의 청크(Chunk) 차이를 먼저 정리할 필요가 있다.

데이터 블록(Block) 은 Oracle 데이터베이스에서 I/O의 최소 단위이자,
일반 테이블과 인덱스 데이터가 저장되는 가장 기본적인 저장 단위이다.

CLOB, BLOB과 같은 LOB 컬럼은 일반 테이블 블록에 직접 데이터가 저장되지 않는다.

테이블 본체에는 LOB Locator 만 저장
실제 LOB 데이터는 별도의 LOB 세그먼트에 저장됨
이 LOB 세그먼트에서 사용하는 최소 저장 단위가 청크(Chunk) 이다

최종 결론 먼저..

(우선 DB버전과 세부 파라미터 차이로 인해 결과는 각각 다를 수 있다)

DELETE로 인해 발생한 더티 블록( 아직 COMMIT이 안되었거나, 디스크에 반영이 되지 않고 버퍼에 남아있는상태 )이나,
커밋 이후에도 Free 상태로 전환되지 않은 LOB 청크는 재사용되지 않는다.

일부 자료에서는 의도적으로 Full Scan을 수행하여 블록 클린아웃을 유도한다는 설명이 있으나,
이는 LOB 청크 개념에서는 예외에 해당한다.

또한 클린아웃(Cleanout) 이란,
트랜잭션 완료 후 트랜잭션 슬롯, Lock 정보 등 트랜잭션 메타데이터를 정리하는 작업이며,
공간 재사용(Free Block / Free Chunk)과는 직접적인 관련이 없다.

본 테스트 결과를 종합해 보면,
LOB UNDO_RETENTION 설정과 ASSM 관리 방식의 조합에 의해 청크가 Free Chunk 상태로 전환된 이후에야 재사용이 가능해 보인다.

추가로,

LOB UNDO_RETENTION = NONE 으로 설정할 경우 DELETE 이후 비교적 빠른 재사용이 가능했으나,
테스트 결과 100% 재사용되지는 않았고,
소폭이나마 HWM(High Water Mark)은 계속 상승하는 현상이 관찰되었다.

그 결과,
가용 테이블스페이스 용량과 실제 데이터량이 매우 타이트한 환경에서는,
여전히 TS_FULL 이 발생할 수 있었다.

UNDO_RETENTION과 재사용의 관계

LOB UNDO_RETENTION 이 테스트 결과에 영향을 미친 이유는

SecureFile LOB은 일반 컬럼과 다른 UNDO 구조를 가진다.

SecureFile LOB은 자체적인 LOB Undo 버퍼(LOB 버전 저장 영역) 를 보유한다

따라서 LOB 관련 RETENTION 파라미터는

LOB 세그먼트 내부에 저장되는 LOB 버전이 얼마나 유지될지를 결정한다

해당 retention 기간이 경과해야만 해당 영역은 Free Chunk 로 전환되어 재사용이 가능해진다

유의할점은

일반 컬럼 데이터 블록의 Free Space 재사용은 일반적인 UNDO_RETENTION 파라미터와 직접적인 관계가 없다

SecureFile LOB은 일반 Undo 세그먼트를 사용하지 않는다(UNDO를 위한 UNDO_TABLESPACE가 따로있음)

즉, 일반 컬럼과 LOB는 UNDO 저장 구조 자체가 다르다.

SECUREFILE LOB 세그먼트
  ├── LOB Data
  ├── LOB Index
  └── LOB Undo 영역 (LOB 버전 저장 공간)

테스트케이스

TABELSPACE_MAXSIZE : 30G

CASE A : 배치 내부 DELETE와 INSERT후 COMMIT

- 신규데이터 용량 14.xxG

- 신규데이터 용량 29.xxG

CASE B : 배치 중간 DELETE 후 COMMIT명시 후 INSERT

- 신규데이터 용량 14.xxG

- 신규데이터 용량 29.xxG

CASE C : DELETE와 INSERT를 배치(트랜잭션) 분리, 각 배치간 간격은 텀 없이 바로실행, 시간텀, 테이블 FULL스캔후 바로 실행한다

- 신규데이터 용량 14.xxG, 시간텀 x
- 신규데이터 용량 14.xxG, delete 배치 후 FULL SCAN후 바로 실행

- 신규데이터 용량 14.xxG, delete 배치 후 6시간 이상 대기 후 실행
- 신규데이터 용량 29.xxG, 시간텀 x
- 신규데이터 용량 29.xxG, delete 배치 후 FULL SCAN후 바로 실행

- 신규데이터 용량 29.xxG, delete 배치 후 6시간 이상 대기 후 실행

- 신규데이터 용량 29.xxG, delete 배치 후 체크포인트 후 실행

CASE D : LOB RETENTION 을 NONE으로 설정하여 CASE B, C를 실행한다

테스트케이스별 결과 요약

CASE A : 배치 내부 DELETE와 INSERT후 COMMIT

A-1 : 신규데이터 약 14.XX GB

결과 : 1회차 배치 HWM 14.XX 2회차 배치부터는 28.XX 이며 3회차 이상에도 문제없이 저장한다

A-2 : 신규데이터 약 29.XX GB

결과 : 1회차 배치 HWM 29.XX 2회차 배치부터는 TS_FULL발생한다, 당연히 COMMIT되지 않은 DELETE데이터 (DIRTY 블럭)은 재사용할수 없음으로 보인다

CASE B : 배치 중간 DELETE 후 COMMIT명시 후 INSERT

B-1 : 신규데이터 약 14.XX GB

결과 : 1회차 배치 HWM 14.XX 2회차 배치부터는 28.XX 이며 3회차 이상에도 문제없이 저장한다, 단 DELETE블럭이 커밋되었어도 HWM이 늘어남으로 재사용하지 못한것으로 보인다

B-2 : 신규데이터 약 29.XX GB

결과 : 1회차 배치 HWM 29.XX 2회차 배치부터는 TS_FULL발생한다, DELETE COMMIT된 블록,청크 재사용이 필요한 상황에도 free block, chunk가 되지 않음으로 보임

CASE C : DELETE와 INSERT를 배치(트랜잭션) 분리, 각 배치간 간격은 텀 없이 바로실행, 시간텀, 테이블 FULL스캔후 바로실행한다

C-1 : 신규데이터 약 14.XX GB, 텀 X

C-2 : 신규데이터 약 14.XX GB, FULL스캔 후 바로 실행 텀 X

결과 : 1회차 배치 HWM 14.XX 2회차 배치부터는 28.XX 이며 3회차 이상에도 문제없이 저장한다, 단 FULL SCAN이 일어나도 HWM이 늘어남으로 재사용하지 못한것으로 보인다

C-3 : 신규데이터 약 14.XX GB, 삭제 후 6시간 ~ 하루정도 텀을두고 INSERT

결과 : 1회차 배치 HWM 14.XX 2회차 배치 14.XX 이며 청크 재사용 추정

C-4 : 신규데이터 약 29.XX GB, 텀 X

결과 : 1회차 배치 HWM 29.XX 2회차 배치부터는 TS_FULL발생한다,DELETE COMMIT된 블록,청크 재사용이 필요한 상황에도 free block, chunk가 되지 않음으로 보임

C-5 : 신규데이터 약 29.XX GB, FULL스캔 후 바로 실행 텀 X

결과 : 1회차 배치 HWM 29.XX 2회차 배치부터는 TS_FULL발생한다, FULL SCAN이 일어나도 재사용하지 못한것으로 보인다

C-6 : 신규데이터 약 29.XX GB, 삭제 후 6시간 ~ 하루정도 텀을두고 INSERT

결과 : 1회차 배치 HWM 29.XX 2회차 배치에도 비슷한 HWM유지 INSERT정상 재사용 추정

C-7 : 신규데이터 약 29.XX GB, 삭제 후 체크포인트 실행 후 INSERT

결과 : 1회차 배치 HWM 29.XX 2회차 배치부터는 TS_FULL발생한다

CASE D : LOB RETENTION 을 NONE으로 설정하여 CASE B, C를 실행한다

A-1 : 신규데이터 약 14.XX GB

결과 : 1회차 배치 HWM 14.XX 2회차 배치부터는 28.XX 이며 3회차 이상에도 문제없이 저장한다

A-2 : 신규데이터 약 29.XX GB

결과 : 1회차 배치 HWM 29.XX 2회차 배치부터는 TS_FULL발생한다, 당연히 COMMIT되지 않은 DELETE데이터 (DIRTY 블럭)은 재사용할수 없음으로 보인다

B-1 : 신규데이터 약 14.XX GB

결과 : 1회차 배치 HWM 14.XX 2회차 배치도 14.XX 소폭상승한 HWM을 형성한다, RETENTION NONE으로 바로 재사용가능함을 보임

B-2 : 신규데이터 약 29.XX GB

결과 : 1회차 배치 HWM 29.XX 2회차 배치는 FULL 혹은 29.XX 소폭상승한 HWM을 형성한다, 100% 재사용에는 미치지 못하는것으로 보임

C-1 : 신규데이터 약 14.XX GB, 텀 X

결과 : 1회차 배치 HWM 14.XX 2회차 배치도 14.XX 소폭상승한 HWM을 형성한다, RETENTION NONE으로 바로 재사용가능함을 보임

C-4 : 신규데이터 약 29.XX GB, 텀 X

결과 : 1회차 배치 HWM 29.XX 2회차 배치는 29.XX 소폭상승한 HWM을 형성한다, 100% 재사용에는 미치지 못하는것으로 보임

추가 : 테이블 풀스캔이 블록,청크 클린아웃을 발현하는지?

CASE C 풀스캔 테스트시 풀스캔 전, 후 SESSTAT 스냅샷을 비교하여 클린아웃을 유추

결과 : 블록클린아웃이 되나 청크 클린아웃은 발현되지 않음, 단 클린아웃은 재사용과는 관련 없는 메커니즘

테스트 진행

CASE A : DELETE와 INSERT 를 한트랜잭션에서 수행

DECLARE
  -- 변수 선언
  ...
  
BEGIN
  -- 테이블의 모든 데이터를 삭제
  DELETE FROM [테이블명]

  -- 약15G 데이터를 삽입
  FOR i IN 1..1600000 LOOP
    
  -- 임의 값 생성
  변수명 := '값';
  ...

  -- 데이터 삽입
  INSERT INTO [테이블명] (
      COL, ...) 
  VALUES (
     변수명, ...
  );
  END LOOP;

  -- 트랜잭션 커밋
  COMMIT;  
END;

-- ==== 테이블 스페이스, 주 용량 세그먼트 확인

SELECT BYTES /1024 /1024 /1024, A.*

FROM DBA_SEGMENTS A

WHERE TABLESPACE_NAME = [테이블스페이스명]

ORDER BY 1 desc;

[데이터 건수 160만(14.xxG)]

-- 1회차

--15.5938720703125 [스키마명] SYS_LOB0000254467C00004$$

--2.1875 [스키마명] [테이블명]

--0.0107421875 [스키마명] SYS_LOB0000254424C00006$$

--0.00115966796875 [스키마명] SYS_LOB0000254462C00005$$

-- 2회차

--30.6788330078125 [스키마명] SYS_LOB0000254467C00004$$

--2.1875 [스키마명] [테이블명]

--0.0107421875 [스키마명] SYS_LOB0000254424C00006$$

--0.00115966796875 [스키마명] SYS_LOB0000254462C00005$$

-- 3회차

--30.7491455078125 [스키마명] SYS_LOB0000254467C00004$$

--2.1875 [스키마명] [테이블명]

--0.0107421875 [스키마명] SYS_LOB0000254424C00006$$

--0.00115966796875 [스키마명] SYS_LOB0000254462C00005$$

1회차 / 15G만큼 데이터 신규추가

2회차 / 1회차 데이터 삭제 후 15G만큼 데이터 신규추가 HWM은 15G만큼 올라갔음

3회차 / 2회차 데이터 삭제 후 15G 만큼 데이터 신규추가 HWM이 TS MAX BYTES 도달했으므로 앞서 빈 15G를 재사용함으로 보임

[데이터 건수 320만(29.xxG)]

-- 1회차

--25.04510498046875 [스키마명] SYS_LOB0000332450C00004$$

--3.4970703125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

-- 배치 수행중 TS FULL 발생

커밋되지않은 DEL 블록은 예상대로 재사용할 수 없음

CASE B : 한 배치 안에서 DELETE 후 COMMIT → INSERT

DECLARE
  -- 변수 선언
  ...
  
BEGIN
  -- 테이블의 모든 데이터를 삭제
  DELETE FROM [테이블명]
  
  -- 트랜잭션 커밋
  COMMIT; 
  
  -- 약15G 데이터를 삽입
  FOR i IN 1..1600000 LOOP
    
  -- 임의 값 생성
  변수명 := '값';
  ...

  -- 데이터 삽입
  INSERT INTO [테이블명] (
      COL, ...) 
  VALUES (
     변수명, ...
  );
  END LOOP;

  -- 트랜잭션 커밋
  COMMIT;  
END;

-- ==== 테이블 스페이스, 주 용량 세그먼트 확인

SELECT BYTES /1024 /1024 /1024, A.*

FROM DBA_SEGMENTS A

WHERE TABLESPACE_NAME = [테이블스페이스명]

ORDER BY 1 desc;

[데이터 건수 160만(14.xxG)]

-- 1회차

--12.49139404296875 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--25.04510498046875 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 3회차

--25.10760498046875 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

[데이터 건수 320만(29.xxG)]

-- 1회차

--25.06072998046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

-- 배치 수행중 TS FULL 발생

앞 케이스와 거의 동일함 DELETE후 커밋을 하여도 곧바로 재사용은 불가해 보임

CASE C : 배치 분리

-- ============ 배치 1 ==============
DELETE FROM [테이블명];
  commit; 
-- ================================== 


-- (시간여유 혹은 풀스캔을 두자)
-- select * from [테이블명] -- 0건인데도 시간이 꽤걸림(블록클린아웃작업을 하나봄)

-- ============ 배치 2 ==============
DECLARE
  -- 변수 선언
  ...
  
BEGIN
  
  -- 약15G 데이터를 삽입
  FOR i IN 1..1600000 LOOP
    
  -- 임의 값 생성
  변수명 := '값';
  ...

  -- 데이터 삽입
  INSERT INTO [테이블명] (
      COL, ...) 
  VALUES (
     변수명, ...
  );
  END LOOP;

  -- 트랜잭션 커밋
  COMMIT;  
END;

-- ==== 테이블 스페이스, 주 용량 세그먼트 확인

SELECT BYTES /1024 /1024 /1024, A.*

FROM DBA_SEGMENTS A

WHERE TABLESPACE_NAME = [테이블스페이스명]

ORDER BY 1 desc;

[데이터 건수 160만(14.xxG)]

-- [ 텀 X ]

-- 1회차

--12.57244873046875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--24.75115966796875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 3회차

--25.37615966796875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- [ 텀 X FULL SCAN ]

-- 1회차

--12.57244873046875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--25.12615966796875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- [ 텀 하루 ]

-- 1회차

--12.57244873046875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--12.69744873046875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- [ 텀 6시간 ]

-- 1회차

--12.57244873046875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--12.85369873046875 [스키마명] SYS_LOB0000332450C00004$$

--1.7939453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

[데이터 건수 320만(29.xxG)]

-- [ 텀 X ]

-- 1회차

--24.99822998046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

-- 배치 수행중 TS FULL 발생

-- [ 텀 X FULL SCAN ]

-- 1회차

--25.06072998046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

-- 배치 수행중 TS FULL 발생

-- [ 텀 하루 ]

-- 1회차

--24.99822998046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00007$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--25.32635498046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- [ 텀 6시간 ]

-- 1회차

--25.32635498046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--26.13104248046875 [스키마명] SYS_LOB0000332450C00004$$

--3.5439453125 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

삭제 배치 끝나고 일정시간이 있어야 재사용가능함을 보임

CASE D : LOB RETENTION 을 NONE으로 설정하여 CASE B, C를 실행한다

-- =========== 시스템 단위 UNDO_RETENTION 파라미터 확인(확인하지않아도 상관없음) ===========
SELECT name, value
FROM v$parameter
WHERE name = 'undo_retention';
-- undo_retention	900


-- =========== LOB 단위 UNDO_RETENTION 파라미터 확인 ===========
SELECT table_name,
       column_name,
       securefile,
       retention_type, 
       retention_value
FROM   dba_lobs A
WHERE  table_name = [테이블명]
AND    owner = [스키마명];
--[테이블명]	[lob컬럼1]	YES	DEFAULT	
--[테이블명]	[lob컬럼2]	YES	DEFAULT	
--[테이블명]	[lob컬럼3]	YES	DEFAULT	
--[테이블명]	[lob컬럼4]	YES	DEFAULT	

--RETENTION_TYPE	의미
--NONE	            LOB UNDO 버전 유지 안함
--MIN	            최소 시간 기반
--AUTO	            시스템 AUTO 기능 적용
--MAX	            무한대(retain as long as possible)


-- =========== LOB 단위 UNDO_RETENTION 파라미터 변경 ===========
ALTER TABLE [스키마명].[테이블명] MODIFY LOB ([lob컬럼1]) (RETENTION NONE);
ALTER TABLE [스키마명].[테이블명] MODIFY LOB ([lob컬럼2]) (RETENTION NONE);
ALTER TABLE [스키마명].[테이블명] MODIFY LOB ([lob컬럼3]) (RETENTION NONE);
ALTER TABLE [스키마명].[테이블명] MODIFY LOB ([lob컬럼4]) (RETENTION NONE);

--[테이블명]	[lob컬럼1]	YES	NONE	
--[테이블명]	[lob컬럼2]	YES	NONE	
--[테이블명]	[lob컬럼3]	YES	NONE	
--[테이블명]	[lob컬럼4]	YES	NONE		

-- =========== CASE A B C 수행  ===========
...

-- ==== 테이블 스페이스, 주 용량 세그먼트 확인

SELECT BYTES /1024 /1024 /1024, A.*

FROM DBA_SEGMENTS A

WHERE TABLESPACE_NAME = [테이블스페이스명]

ORDER BY 1 desc;

-- =============[CASE A]=============

-- ===== 데이터 건수 160만(14.xxG) ======

--1회차

--12.4913330078125 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

--2회차

--24.5460205078125 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

--3회차

--24.6163330078125 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- ===== 데이터 건수 320만(29.xxG) ======

--24.9796142578125 [스키마명] SYS_LOB0000332450C00004$$

--3.5 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

--2회차

--TS_FULL 발생

-- =============[CASE B]=============

-- ===== 데이터 건수 160만(14.xxG) ======

--1회차

--12.4913330078125 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

--2회차

--12.9522705078125 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

--3회차

--13.3897705078125 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- ===== 데이터 건수 320만(29.xxG) ======

-- 1회차

--24.97967529296875 [스키마명] SYS_LOB0000332450C00004$$

--3.5 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--TS_FULL 발생

-- 혹은

--25.9796142578125 [스키마명] SYS_LOB0000332450C00004$$

--3.5 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- =============[CASE C]=============

-- ===== 데이터 건수 160만(14.xxG) ======

--1회차

--12.49139404296875 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

--2회차

--12.94451904296875 [스키마명] SYS_LOB0000332450C00004$$

--1.75 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- ===== 데이터 건수 320만(29.xxG) ======

-- 1회차

--24.97967529296875 [스키마명] SYS_LOB0000332450C00004$$

--3.5 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

-- 2회차

--25.76873779296875 [스키마명] SYS_LOB0000332450C00004$$

--3.5 [스키마명] [테이블명]

--0.0001220703125 [스키마명] SYS_LOB0000332450C00006$$

--0.0001220703125 [스키마명] SYS_LOB0000332450C00005$$

LOB UNDO_RETENTION 제거는 확실히 재사용시 영향이 있음, 단 100% 모두 재사용은 아니고 소폭의 HWM증가

추가 : 테이블 풀스캔이 블록,청크 클린아웃을 발현하는지?

-- 데이터 적재
- 생략 -

-- 기존 데이터 삭제하자
DELETE  ~~~ 
COMMIT;

-- ================= 풀스캔 전 스냅샷 ================= 
CREATE TABLE STAT_BEF
AS
SELECT a.sid
    , b.statistic#
    , b.name
    , a.value
FROM v$sesstat a JOIN v$statname b
                 ON a.statistic# = b.statistic#
WHERE a.sid = 1169
--               (SELECT sid
--                FROM v$session
--                WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'))
ORDER BY VALUE DESC, b.statistic#;

-- 시간여유 혹은 풀스캔을 두자
select /*+FULL(A) */* from [테이블명] A; -- 0건인데도 시간이 꽤걸림(블록클린아웃작업을 하나봄)

-- ================= 풀스캔 후 스냅샷 =================
CREATE TABLE STAT_AFT
AS
SELECT a.sid
    , b.statistic#
    , b.name
    , a.value
FROM v$sesstat a JOIN v$statname b
                 ON a.statistic# = b.statistic#
WHERE a.sid = 1169
--               (SELECT sid
--                FROM v$session
--                WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'))
ORDER BY VALUE DESC, b.statistic#;


-- =================델타 =================
SELECT a.sid,
       a.statistic#,
       a.name,
       b.value AS bef_value,
       a.value AS aft_value,
       (a.value - b.value) AS delta_value
FROM STAT_AFT a
JOIN STAT_BEF b
  ON a.sid = b.sid
 AND a.statistic# = b.statistic# AND A.NAME LIKE '%commit clean%'
ORDER BY delta_value DESC, a.statistic#;

-- 주요 결과
--1169 654 cleanouts only - consistent read gets 0 457143 457143
CR 블록을 읽었을 때
해당 블록에 commit 클린아웃이 필요하고, 그 자리에서 클린아웃 성공했음을 의미.
--1169 664 immediate (CR) block cleanout applications 0 457143 457143
클린아웃을 즉시 성공적으로 적용한 CR 블록 수
KTUXC(XID) 정보를 업데이트하고
block SCN을 advance 하는 데 성공했음을 의미.
--1169 666 commit txn count during cleanout 0 457143 457143
cleanout 도중 commit TXN을 몇 개 처리했는지
대부분은 1 (삭제 트랜잭션 1개), 여러 트랜잭션이 엉켜있으면 값이 커질 수 있음.
= 처리한 commit 정보 수
--1169 668 cleanout - number of ktugct calls 0 457143 457143
ktugct = commit cleanout 처리를 담당하는 내부 함수.
호출 횟수 증가 = cleanout 작업을 실제로 수행했다는 뜻.
= cleanout API 호출 횟수

-- FAILURES 지표
1169 235 commit cleanout failures: write disabled 0 0 0
1169 236 commit cleanout failures: block lost 0 0 0
1169 237 commit cleanout failures: cannot pin 0 0 0
1169 238 commit cleanout failures: hot backup in progress 0 0 0
1169 239 commit cleanout failures: buffer being written 0 0 0
1169 240 commit cleanout failures: callback failure 0 0 0
1169 241 commit cleanout failures: delayed log 0 0 0
1169 242 commit cleanout failures: flashback 0 0 0

위 지표상 블록클린아웃은 45만7143 블럭건이 일어났지만 청크 클린아웃은 발현되지 않은것으로 예상된다.

Chunk cleanout(대량 강제 클린아웃)이 실행되는 조건
반드시 “cleanout 실패(statistic failure)”가 먼저 발생해야 한다.
왜냐하면 chunk cleanout은 Oracle의 예외 처리 경로이기 때문.

chunk cleanout이 발동되는 전형적 패턴:
블록 cleanout 시도
하지만 실패 → failure statistic 증가
Oracle이 “이건 블록 단위로 못하겠다” 판단
이후 “chunk 단위(다수 블록)”로 별도 cleanout 작업 실행
redo 대량 발생 (수백 MB~수 GB까지 가기도 함)

아래처럼 cleanout failure로 시작해야 chunk cleanout이 발생한다.
commit cleanout failures: write disabled
commit cleanout failures: block lost
commit cleanout failures: cannot pin
commit cleanout failures: hot backup in progress
commit cleanout failures: buffer being written
commit cleanout failures: write errors
이런 failure가 증가하지 않고는
→ chunk cleanout 경로로 진입 불가

'Admin' 카테고리의 다른 글

WITH GRANT OPTION이 필요한 진짜 이유 – VIEW와 PROCEDURE에서의 차이 (0)	2026.01.28
Flashback query, Flashback Versions Query (0)	2026.01.05
분산 트랜잭션, 2PC PENDING (0)	2026.01.05
Diagnostic Pack / Tuning Pack (0)	2025.07.03
롤(role) 과 권한(privilege) (1)	2025.06.04

현재글ORACLE 청크 공간 재사용 테스트, clob

돼지와함께춤을 님의 블로그

dancewithpig 님의 블로그 입니다.

Oracle, _add_col_optim_enabled, WITH GRANT OPTION, ORA-02063, expcnt mismatch, SHARED DB LINK, DB LINKS, Flashback Versions Query, EXP_STAT$, ORA-03150, 대용량 테이블 컬럼 추가, EXP_OBJ$, 2PC PENDING, datapump, qosdExpStatRead, log miner, internal error code, DEDICATE DB LINK, Expression Statistics Usage, FAST ADD,

Today :
Yesterday :

돼지와함께춤을 님의 블로그