Reorg 방법 - Shrink 와 Move

DB를 사용하다보면 DML 작업이 한 테이블에 많이 일어나게 되고 그렇게 되면 HWM(High Water Mark)는 이전에 데이터가 많았던 지점까지 표시하고 있지만 실제로 사용하는 블록은 그렇지 않게 됩니다. 테이블 크기도 불필요하게 커질 뿐아니라, SCAN시 불필요한 I/O도 발생하게 됩니다. 왜냐하면 Full Scan시 데이터 스캔의 범위기준이 HWM까지이기 때문입니다. 따라서 테이블에 사용하지 않는 공간이 많으면 용량뿐 아니라, 조회 성능도 떨어지는 상황이 발생하게 됩니다.

 

그것을 해결해주는 방법으로 Shrink와 Table Move 작업이 있습니다.

Shrink는 10g에서 지원하는 기능으로 Online으로 테이블의 HWM을 낮추어 공간을 축소시킬 수 있는  작업입니다. 테스트를 통해 Shrink기능을 테스트해보겠습니다.

 

OS :OEL 7.6

DB : 12.2.0.1

 

Shrink

 

1.테이블과 인덱스 생성

TEST@ysbae> CREATE TABLE HWMTEST(A VARCHAR2(20), B NUMBER, C NUMBER,
D VARCHAR2(30), E VARCHAR2(30));

Table created.

 

TEST@ysbae> CREATE INDEX IDX_HWM_B ON HWMTEST(B);

Index created.

 

 

2.데이터 삽입 (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;

END;

/

 

3.통계정보 수집

TEST@ysbae> exec dbms_stats.gather_table_stats('TEST','HWMTEST');

PL/SQL procedure successfully completed.

 

 

4. 실제 사용 블록 및 크기 확인

테이블 360MB에 인덱스 128MB사용중인것을 확인할 수 있으며, dba_segments에서 보이는 블록수와 dbms_rowid로 조회한 실제 사용 블록수의 차이는 적게나타난것을 확인할 수 있습니다.

 

TEST@ysbae> 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

 

 

TEST@ysbae> COL TABLE_NAME FOR A16
SELECT TABLE_NAME,
   NUM_ROWS,
   BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'HWMTEST';

TABLE_NAME         NUM_ROWS     BLOCKS
---------------- ---------- ----------
HWMTEST             6000000      45797

 

 

실제사용블록

TEST@ysbae> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST;  

      USED
----------
     45448

 

5.데이터 삭제

TEST@ysbae> delete hwmtest where rownum <= 4000000;

4000000 rows deleted.

 

 

6.삭제 후 통계정보 재 수집

TEST@ysbae> exec dbms_stats.gather_table_stats('TEST','HWMTEST');

PL/SQL procedure successfully completed.

 

7.블록 수 및 용량 확인

실제 사용블록은   45448 ->  15151로 줄었지만 용량과 HWM가 찍힌 블록수는  차이가 없는 것을 확인할 수 있습니다.  

TEST@ysbae> 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')
TEST@ysbae> /

SEGMENT_NAME         BLOCKS         MB
---------------- ---------- ----------
HWMTEST               46080        360
IDX_HWM_B             16384        128

 

TEST@ysbae> 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  

TEST@ysbae> alter table hwmtest enable row movement;

Table altered.

 

TEST@ysbae> alter table hwmtest shrink space cascade;

Table altered.

 

+row movement 다시 disable 하기

TEST@ysbae> alter table hwmtest disable row movement;

 

9. 용량 및 블록 수 다시 확인

용량과 HWM 블록수 모두 줄어든 것을 확인할 수 있습니다.

TEST@ysbae> exec dbms_stats.gather_table_stats('TEST','HWMTEST');

PL/SQL procedure successfully completed.

 

TEST@ysbae>  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.

 

 

TEST@ysbae> SELECT TABLE_NAME,
   NUM_ROWS,
   BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'HWMTEST'; 

TABLE_NAME         NUM_ROWS     BLOCKS
---------------- ---------- ----------
HWMTEST             2000000      15151

 

 

 

TEST@ysbae> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST;

      USED
----------
     15151

 

 

Table Move

 

1.테이블 및 인덱스 생성

TEST@ysbae> CREATE TABLE HWMTEST(A VARCHAR2(20), B NUMBER, C NUMBER,
D VARCHAR2(30), E VARCHAR2(30))  ;

Table created.

 

TEST@ysbae> CREATE INDEX IDX_HWM_B ON HWMTEST(B);

 

Index created.

 

 

2.데이터 삽입 (5000000건) 

TEST@ysbae> 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;

END;

/ 

 

PL/SQL procedure successfully completed.

 

 

3.통계정보 생성

TEST@ysbae> exec dbms_stats.gather_table_stats('TEST','HWMTEST');

 

4.용량 및 블록 확인

TEST@ysbae> select segment_name,blocks, bytes/1024/1024 MB from dba_segments
    where segment_name in ('HWMTEST','IDX_HWM_B');  

SEGMENT_NAME         BLOCKS         MB
---------------- ---------- ----------
HWMTEST               38912        304
IDX_HWM_B             16384        128

 

TEST@ysbae> COL TABLE_NAME FOR A16
SELECT TABLE_NAME,
   NUM_ROWS,
   BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'HWMTEST';

TABLE_NAME         NUM_ROWS     BLOCKS
---------------- ---------- ----------
HWMTEST             5000000      38657

 

TEST@ysbae> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST; 

      USED
----------
     37874

1 row selected.

 

5.데이터 삭제

TEST@ysbae> delete hwmtest where rownum <= 4000000;

4000000 rows deleted.

 

6.통계정보 재생성 및 확인

 

TEST@ysbae> exec dbms_stats.gather_table_stats('TEST','HWMTEST');

PL/SQL procedure successfully completed.

 

TEST@ysbae> SELECT TABLE_NAME,
   NUM_ROWS,
   BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'HWMTEST';  

TABLE_NAME         NUM_ROWS     BLOCKS
---------------- ---------- ----------
HWMTEST             1000000      38657

1 row selected.

 

TEST@ysbae>     select segment_name,blocks, bytes/1024/1024 MB from dba_segments
       where segment_name in ('HWMTEST','IDX_HWM_B')  ;

SEGMENT_NAME         BLOCKS         MB
---------------- ---------- ----------
HWMTEST               38912        304
IDX_HWM_B             16384        128

 

 

TEST@ysbae> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST; 

      USED
----------
      7577

1 row selected.

 

 

7.Table Move작업 수행

Table Move작업을 수행하면 Rowid가 변경되어서 기존의 Index가 Unusable상태로 변합니다. 반드시 rebuild해주어야 합니다.

 

TEST@ysbae> alter table hwmtest move tablespace test;

Table altered.

 

 

TEST@ysbae> select index_name,status from user_indexes;

INDEX_NAME    STATUS
------------------------------
IDX_HWM_B     UNUSABLE

 

TEST@ysbae> alter index IDX_HWM_B rebuild tablespace test;

Index altered.

 

TEST@ysbae> select index_name,status from user_indexes;

INDEX_NAME    STATUS
------------------------------
IDX_HWM_B     VALID

 

8.통계정보재수집 및 확인

TEST@ysbae> exec dbms_stats.gather_table_stats('TEST','HWMTEST');

PL/SQL procedure successfully completed.

 

TEST@ysbae> SELECT TABLE_NAME,
   NUM_ROWS,
   BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'HWMTEST';  

TABLE_NAME         NUM_ROWS     BLOCKS
---------------- ---------- ----------
HWMTEST             1000000       7764

1 row selected.

 

TEST@ysbae>  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

 

TEST@ysbae> SELECT COUNT(DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST; 
      USED
----------
      7634

1 row selected.

 

마찬가지로 블록수와 용량이 줄어든 것을 확인할 수 있습니다.

테스트의 출처는 아래의 블로그를 참고하여 테스트를 진행하였습니다.

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