행위

Reorg 테스트

DB CAFE

thumb_up 추천메뉴 바로가기

1 HWM reorg 테스트[편집]

1.1 해결 방법 2가지[편집]

  1. Shrink
    - Online으로 테이블의 HWM을 낮추어 공간을 축소
  2. Table Move 작업

1.2 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) 실제 사용 블록 및 크기 확인

  1. 테이블 360MB , 인덱스 128MB 확인
  2. 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) 블록 수 및 용량 확인

  1. 실제 사용블록은 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) 용량 및 블록 수 다시 확인

  1. 용량과 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

1.3 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.
  • 블록수와 용량이 줄어든 것을 확인
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=Reorg_테스트&oldid=10310"