테이블의 Reorg 대상확인 프로시저(DBMS_SPACE.SPACE_USAGE)

테이블의 리오그 대상을 추출할 때 사용할 수 있는 오라클 내부 프로시저

설명 :

세그먼트의 High Water Mark(이하 HWM) 아래의 공간(즉 현재 사용하는 공간)에 대한 사용 정보를 제공합니다. 단, 비트맵 블록(bitmap block), 세그먼트 헤더(segment header), 익스텐트 맵 블록(extent map block)의 정보는 포함되지 않습니다.

 

일반테이블 예제

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      => 'TEST',                   -- 1. Owner 입력
      segment_name       => 'BULKINS',             -- 2. 테이블 이름 입력
      segment_type       => 'TABLE',                -- 3. 세그먼트 종류 입력 TABLE,TABLE PARTITION,TABLE SUBPATITION,INDEX,INDEX PARTITION,INDEX SUBPATITION,CLUSTER,LOB,LOB PARTITION,LOB SUBPATITION
      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;
/

값

SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 16 Bytes = 131072
Full Blocks = 8805
   Bytes = 72130560

 

파티션테이블 예제

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      => 'TEST',                   -- 1. Owner 입력
      segment_name       => 'TB_ATB8202',             -- 2. 테이블 이름 입력
      segment_type       => 'TABLE SUBPARTITION',                -- 3. 세그먼트 종류 입력  TABLE,TABLE PARTITION,TABLE SUBPATITION,INDEX,INDEX PARTITION,INDEX SUBPATITION,CLUSTER,LOB,LOB PARTITION,LOB SUBPATITION
      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,
      partition_name => 'PT_ATB8202_202009_S4'
   );
   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;
/

결과

SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 0 Bytes = 0
Full Blocks = 0
   Bytes = 0

 

 

FS1 Blocks : 0~25% 사이에 free space를 갖는 블록의 개수

FS2 Blocks : 25~50% 사이에 free space를 갖는 블록의 개수

FS3 Blocks : 50~75% 사이에 free space를 갖는 블록의 개수

FS4 Blocks : 75~100% 사이에 free space를 갖는 블록의 개수

full_blocks : full로 marking된 블록의 개수

 

출처 : https://dataonair.or.kr/db-tech-reference/d-guide/dbms-1/?mod=document&uid=101879

 

위의 테이블의 예를 봤을 때 16개의 블록이 FS4 영역에 있는 블록입니다. Reorg 시 반환되어 단편화 해소가 가능할 것으로 예상됩니다.

 

직접 Reorg 테스트 후  확인해보기

 

테스트 시나리오는 아래 블로그에서 참조하여 확인하였습니다

출처 : https://positivemh.tistory.com/350

--1.생성 전 삭제
DROP TABLE HWMTEST1 PURGE;

--2.테이블 생성
CREATE TABLE HWMTEST1(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_HWM_COLB ON HWMTEST1(COLB);

--3.데이터 삽입 140만건
DECLARE
TYPE tbl_ins IS TABLE OF HWMTEST1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
END;
/


--4.블록 수 및 용량 확인

SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
USED
-----
87567

프로시저 수행값
SQL>  FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 1 Bytes = 8192
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 179 Bytes = 1466368
Full Blocks = 87566
Bytes = 717340672
 

--5.데이터 삭제
DELETE HWMTEST1 WHERE ROWNUM<=4000000;
COMMIT;



--6.통계정보 수집 및 블록 수 및 용량 확인
exec dbms_stats.gather_table_stats('TEST','HWMTEST1');

SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
USED
-----
17517

프로시저 수행 값
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 1 Bytes = 8192
 FS3 Blocks = 1 Bytes = 8192
 FS4 Blocks = 70229 Bytes = 575315968
Full Blocks = 17515
   Bytes = 143482880


SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST1','IDX_HWM_COLB');

SEGMENT_NAME BLOCKS MB
-----------------------
HWMTEST1	88704	693
IDX_HWM_COLB	15360	120


--7.REORG( Shrink 사용)
alter table hwmtest1 enable row movement;
alter table hwmtest1 shrink space;



--8.통계정보 재수집 및 블록 수 및 용량 확인
exec dbms_stats.gather_table_stats('TEST','HWMTEST1');


SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('HWMTEST1','IDX_HWM_COLB');

SEGMENT_NAME BLOCKS MB
-----------------------
HWMTEST1	17696	138.25
IDX_HWM_COLB	15360	120

SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST1;
USED
-----
17517

--프로시저 수행 값
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 1 Bytes = 8192
 FS4 Blocks = 0 Bytes = 0
Full Blocks = 17516
   Bytes = 143491072

 

리오그 수행 전, 후 블록수를 프로시저 결과값으로 비교해보면 아래와 같습니다.

FS2,FS3,FS4에 각각 블록들이 보이고 있고 리오그 후 해소된 것을 확인할 수 있습니다.

Full Blocks 수의 차이는 크게 없지만 143491072 - 143482880 = 8,192 block의 차이가 납니다.

FS4 blocks(Free space가 75  ~ 100%)인 블록이 70229 -> 0으로 줄었으므로 block의 낭비가 준것을 확인할 수 있으며 상대적으로 full blocks 의 수가 8,192개 늘어났다고 해석할 수 있겠습니다.

 

--리오그 전
/*
-------------------------[Start Time: 2022/01/14 16:26:07]-------------------------
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 1 Bytes = 8192
 FS3 Blocks = 1 Bytes = 8192
 FS4 Blocks = 70229 Bytes = 575315968
Full Blocks = 17515 Bytes = 143482880
*/
--리오그 후
/*
-------------------------[Start Time: 2022/01/14 16:30:10]-------------------------
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 1 Bytes = 8192
 FS4 Blocks = 0 Bytes = 0
Full Blocks = 17516 Bytes = 143491072
*/

 

 

출처 : https://dataonair.or.kr/db-tech-reference/d-guide/dbms-1/?mod=document&uid=101879