다른 명령
HWM reorg 테스트
해결 방법 2가지
- Shrink
- - Online으로 테이블의 HWM을 낮추어 공간을 축소
- Table Move 작업
Shrink
1) 테이블과 인덱스 생성
CREATE TABLE HWMTEST(A VARCHAR2(20), B NUMBER, C NUMBER, D VARCHAR2(30), E VARCHAR2(30)); Table created.
CREATE INDEX IDX_HWM_B ON HWMTEST(B); Index created.
2) 데이터 삽입 (총 6000000건의 데이터 삽입)
DECLARE TYPE tbl_ins IS TABLE OF HWMTEST%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR i IN 1..1000000 LOOP w_ins(i).A :=dbms_random.string('x',10); w_ins(i).B :=i; w_ins(i).C :=99; w_ins(i).D :='ABC'||dbms_random.string('x',10); w_ins(i).E :='EEEEEEEEEEEEEEEE'; END LOOP; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; END; /
3) 통계정보 수집
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
4) 실제 사용 블록 및 크기 확인
- 테이블 360MB , 인덱스 128MB 확인
- dba_segments에서 블록수와 dbms_rowid로 조회한 실제 사용 블록수의 차이는 적게나타난것 확인
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B'); SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 46080 360 IDX_HWM_B 16384 128
SELECT TABLE_NAME,NUM_ROWS,BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 6000000 45797
- 실제사용한 블록
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 45448
5) 데이터 삭제
delete from hwmtest where rownum <= 4000000; 4000000 rows deleted.
6) 삭제 후 통계정보 재 수집
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
7) 블록 수 및 용량 확인
- 실제 사용블록은 45448 -> 15151로 줄었지만 용량과 HWM가 찍힌 블록수는 차이가 없는 것을 확인
SELECT TABLE_NAME,NUM_ROWS,BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 2000000 45797
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B'); SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 46080 360 IDX_HWM_B 16384 128
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 15151
8) Shrink 수행
- ORA-10636: ROW MOVEMENT is not enabled 에러 발생시 권한 부여
alter table hwmtest enable row movement; Table altered.
alter table hwmtest shrink space cascade; Table altered.
- row movement 다시 disable
alter table hwmtest disable row movement;
9) 용량 및 블록 수 다시 확인
- 용량과 HWM 블록수 모두 줄어든 것 확인
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B') ; SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 15320 119.6875 IDX_HWM_B 4600 35.9375 2 rows selected.
SELECT TABLE_NAME,NUM_ROWS,BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 2000000 15151
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 15151
Table Move
상동 1) ~ 7)
8) Table Move작업 수행
- Table Move작업을 수행하면 Rowid가 변경되어서 기존의 Index가 Unusable상태로 변경되어 인덱스 사용불가
- 반드시 rebuild 해주어야 함
alter table hwmtest move tablespace test; Table altered.
select index_name,status from user_indexes; INDEX_NAME STATUS ------------------------------ IDX_HWM_B UNUSABLE
alter index IDX_HWM_B rebuild tablespace test; Index altered.
select index_name,status from user_indexes; INDEX_NAME STATUS ------------------------------ IDX_HWM_B VALID
8) 통계정보재수집 및 축소 확인
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
SELECT TABLE_NAME,NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 1000000 7764 1 row selected.
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B') ; SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 7808 61 IDX_HWM_B 2304 18
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 7634 1 row selected.
- 블록수와 용량이 줄어든 것을 확인