본문 바로가기
Oracle/운영

Online Redefinition시 Block 감소량 비교

by 취미툰 2022. 8. 16.
반응형

Reorg 방법 중 하나인 Online Redefinition 을 수행했을 때 Block 감소량이 크지 않다는 이야기를 듣고 직접 테스트를 진행하여 확인해보았습니다.

Reorg의 목적 중 하나인 HWM 을 낮추고 불필요하게 사용하는 Block들을 재정리하는 것에 대한 효과가 크지 않다면, Online 상에서 사용하는 Reorg 방법이라 하더라도 크게 사용성이 있을까라는 생각은 드네요.

테스트시나리오는 이전 글에서 Online Redefinition에서 테스트 했던 테이블 그대로 다시 생성하여 진행하였고, 거기에 block 확인하는 부분을 추가하였습니다.

https://bae9086.tistory.com/399

 

Table Reorg 방법 정리

운영시에 Reorg를 해야하는 경우는 자주 있을 수 있습니다. 용량이슈는 늘 DB운영시에 따라오는 부분이니깐요. 특히 DML이 잦은 테이블의 크기가 실제 데이터블록에 비해 크기가 커질 수 있습니다.

bae9086.tistory.com

환경

Oracle 12.2.0.1

 

 

1.테스트 테이블 생성

테이블 생성
CREATE TABLE REF_ORIGINAL(A VARCHAR2(20), B NUMBER, C NUMBER,D VARCHAR2(30));

인덱스 생성
CREATE INDEX IND_REF_ORIGINAL ON REF_ORIGINAL(A);
CREATE INDEX IND_REF_ORIGINAL2 ON REF_ORIGINAL(B);
CREATE INDEX IND_REF_ORIGINAL3 ON REF_ORIGINAL(C);
CREATE INDEX IND_REF_ORIGINAL4 ON REF_ORIGINAL(D);

 

2.데이터 삽입

DECLARE

TYPE tbl_ins IS TABLE OF REF_ORIGINAL%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 1..10000000 LOOP
   w_ins(i).A :=dbms_random.string('x',10);
   w_ins(i).B :=i;
   w_ins(i).C :=MOD(i,5);
   w_ins(i).D :='ABC'||dbms_random.string('x',10);

END LOOP;
   FORALL i in 1..10000000 INSERT INTO REF_ORIGINAL VALUES w_ins(i);
   COMMIT;
END;

 

3.용량 및 Block 수 확인

최초 데이터 1000만건 삽입 후 크기 확인결과 테이블은 424MB이고 블록수는 53,445(DBMS_SPACE.SPACE_USAGE 마지막 쿼리 결과값의 블록 수 합계)입니다.

데이터 count
select count(*) from REF_ORIGINAL
10,000,000


SQL> select segment_name,blocks, bytes/1024/1024 MB from dba_segments
    where segment_name in ('REF_ORIGINAL','IND_REF_ORIGINAL','IND_REF_ORIGINAL2','IND_REF_ORIGINAL3','IND_REF_ORIGINAL4');

SEGMENT_NAME       BLOCKS       MB        
------------------ ------------ --------- 
REF_ORIGINAL              54272       424
IND_REF_ORIGINAL          39936       312
IND_REF_ORIGINAL2         21504       168
IND_REF_ORIGINAL3         29696       232
IND_REF_ORIGINAL4         46080       360

SQL> SELECT TABLE_NAME,
   NUM_ROWS,
   BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'REF_ORIGINAL';

TABLE_NAME      NUM_ROWS         BLOCKS       
--------------- ---------------- ------------ 
REF_ORIGINAL            10000000        53957


SQL> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM REF_ORIGINAL;

USED      
--------- 
    53336
    
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 1 Bytes = 8192
 FS4 Blocks = 109 Bytes = 892928
Full Blocks = 53335Bytes = 436920320

 

DBMS_SPACE.SPACE_USAGE명령어는 아래 접은글을 확인해주세요.

더보기

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      => 'YSBAE',                   -- 1. Owner 입력
      segment_name       => 'REF_ORIGINAL',             -- 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;
/

4.데이터 삭제 및 삽입하여 블록 단편화현상 만들기

데이터 삭제
DELETE FROM REF_ORIGINAL where C=1;
COMMIT;


다시 데이터 입력
begin
    for i in 1..1000000 LOOP
    INSERT INTO REF_ORIGINAL(A,B,C,D) VALUES('AFTER FRAGMENTATION',i + 10000000,mod(i,5),'a');
  
    END LOOP;
    commit;
END;
/

데이터 삭제
DELETE FROM REF_ORIGINAL where C=2;
COMMIT;

데이터 다시 입력
begin
    for i in 1..1000000 LOOP
    INSERT INTO REF_ORIGINAL(A,B,C,D) VALUES('AFTER FRAGMENTATION',i + 11000000,mod(i,5),'a');
  
    END LOOP;
    commit;
END;
/

데이터 건수 확인
SQL> select count(*) from REF_ORIGINAL;

COUNT(*)         
---------------- 
         7800000

 

 

5.단편화 발생 후 Block 수 확인

53,445(1+33117+20327)로 Block수는 단편화발생전과 동일하지만 블록의 free space가 25~50%인 FS2 Block의 개수가 단편화 전 (0) -> 단편화 후 (33117) 로 많이 증가한것을 확인할 수 있습니다. 

SQL>  FS1 Blocks = 1 Bytes = 8192
 FS2 Blocks = 33117 Bytes = 271294464
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 0 Bytes = 0
Full Blocks = 20327Bytes = 166518784

 

6.Reorg 수행

1.Online Redefinition 가능 테이블인지 확인
BEGIN
 DBMS_REDEFINITION.CAN_REDEF_TABLE('YSBAE','REF_ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

2.Online Redefiniton 용 테이블 생성
CREATE TABLE REF_NEW(A VARCHAR2(20), B NUMBER, C NUMBER,D VARCHAR2(30));

3.Online Redefinition 수행
BEGIN
 DBMS_REDEFINITION.START_REDEF_TABLE(
 uname => 'YSBAE',
 orig_table => 'REF_ORIGINAL',
 int_table => 'REF_NEW',
 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

4.수행 후 count 확인
SQL> select count(*) from REF_ORIGINAL
UNION ALL
select count(*) from REF_NEW;

COUNT(*)         
---------------- 
         7800000
         7800000


5.관련 dependent object 복사
DECLARE
 error_count pls_integer := 0;
BEGIN
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('YSBAE', 'REF_ORIGINAL', 'REF_NEW', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
 DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

에러 발생시 DBA_REDEFINITION_ERRORS 딕셔너리 뷰에서 에러 확인 가능


6.Sync
BEGIN
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('YSBAE', 'REF_ORIGINAL', 'REF_NEW');
END;
/

7.종료
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('YSBAE', 'REF_ORIGINAL', 'REF_NEW');

 

7.블록수 및 테이블 크기 확인

Online Redefiniton으로 생성한 REF_NEW의 테이블크기는 ORIGINAL보다 오히려 높아졌고(424MB -> 488MB)

블록 수의 경우도 Reorg 후 줄어들어야 하지만 개수는 동일하고(1+33117+20327= 53,445) FS2 블록의 수도 줄지 않은 것을 볼 수 있습니다. 즉, Reorg가 제대로 수행된것이라고 보기 힘든 결과를 보여주네요.

SQL> select segment_name,blocks, bytes/1024/1024 MB from dba_segments
    where segment_name in( 'REF_NEW' ,'REF_ORIGINAL');

SEGMENT_NAME     BLOCKS       MB        
---------------- ------------ --------- 
REF_ORIGINAL            54272       424
REF_NEW                 62464       488
SQL>  FS1 Blocks = 1 Bytes = 8192
 FS2 Blocks = 33117 Bytes = 271294464
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 0 Bytes = 0
Full Blocks = 20327Bytes = 166518784

SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM REF_NEW;  
53445

 

8.MOVE TABLESPACE 명령어로 ORIGINAL TABLE Reorg 후 확인

전통적 reorg 방법인 MOVE TABLESPACE 후 크기는 줄었습니다(424MB -> 328MB)

블록수도 마찬가지로 줄었습니다(53,445 -> 40,918)

그리고 FS2 블록수가 완전 사라지고 FULL BLOCK으로 대체되었습니다.

단편화가 일어나는 블록이 사라지고 블록은 모두 잘 사용되고 있다고 볼 수 있겠습니다.

1.reorg 수행
alter table REF_ORIGINAL move tablespace USERS;


alter index IND_REF_ORIGINAL REBUILD;
alter index IND_REF_ORIGINAL2 REBUILD;
alter index IND_REF_ORIGINAL3 REBUILD;
alter index IND_REF_ORIGINAL4 REBUILD;
2.크기 확인
SQL> select segment_name,blocks, bytes/1024/1024 MB from dba_segments
    where segment_name in ('REF_ORIGINAL','IND_REF_ORIGINAL','IND_REF_ORIGINAL2','IND_REF_ORIGINAL3','IND_REF_ORIGINAL4');

SEGMENT_NAME       BLOCKS       MB        
------------------ ------------ --------- 
REF_ORIGINAL              41984       328
IND_REF_ORIGINAL          26624       208
IND_REF_ORIGINAL2         19456       152
IND_REF_ORIGINAL3         15360       120
IND_REF_ORIGINAL4         24576       192

3.블록수 확인
SQL> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM REF_ORIGINAL;

USED      
--------- 
    40918
    
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 0 Bytes = 0
Full Blocks = 40918Bytes = 335200256

테스트 결과가 예상외로 충격적이네요..

가용성의 장점이 있는 reorg 방법인 Online Redefinition의 결과가 블록수 감소효과도 미미하고(거의 없음), 테이블 크기는 오히려 증가한다면 reorg방법으로는 적절하지 않아 보입니다.

 

이상입니다.

반응형

댓글