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 488SQL> 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방법으로는 적절하지 않아 보입니다.
이상입니다.
'Oracle > 운영' 카테고리의 다른 글
| TABLE이 아닌 OBJECT RENAME TO (0) | 2022.09.05 |
|---|---|
| move tablespace parallel 옵션 사용 (0) | 2022.08.22 |
| Table Reorg 방법 정리 (0) | 2022.08.12 |
| DB 접속 체크 쉘 프로그램 간단하게 만듦 (0) | 2022.08.11 |
| 로그인 트리거 생성 스크립트 (0) | 2022.08.10 |
댓글