[Oracle - Shrink Space]

HWM(High Water Mark)란

마지막까지 등록된 블록위치로 데이터가 대량으로 delete 되면 실제로 사용되는 데이터는 HWM보다 훨씬 작을 것이지만 이전에 표시되었던 HWM은 그대로이기 때문에

데이터를 조회시 HWM 표시부분까지 읽습니다. 따라서 불필요한 DISK I/O가 발생합니다.

HWM는 관리자가 별도로 초기화하거나 축소시키지 않으면 늘어나기만 하고 줄어들지 않기때문에 별도의 관리가 필요합니다.

데이터 풀스캔 시 데이터 스캔의 범위 기준이 바로 HWM 이기 때문이라 중요합니다.

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

 

※ shrink space 주의 사항

1) 오라클 10g 이상(Init.ora parameter 'Compatible' must be >=10.0)  

2) 세그먼트 관리방식이 반드시 ASSM(Auto Segment Space Managed) Tablespace이어야 함  

3) 약 20여건씩 INSERT/DELETE하고 COMMIT하는 방식으로 SHRINK함  

4) 개별 ROW 또는 데이터 BLOCK에 대한 LOCK(ENQUEUE)이 사용  

5) FBI(Function-Based Index)를 SHRINK하는 경우 오류 발생하므로 FBI를 DROP한 다음 작업하고 SHRINK작업이 완료된 후 다시 생성  

6) DML TRIGGER를 발생시키지 않음 (ROWID based TRIGGER는 작업 전에 DISABLED 필요)  

7) DML 작업은 세그먼트 SHRINK 중 수행 가능하나,  parallel DML을 수행될 수 없음   

8) 세그먼트를 SHRINK 시키는 특정 단계(HWM을 조정하는 단계)에서 세그먼트에 exclusive 모드로 짧은 시간동안 LOCK(TM)이 걸림

 

 

■ JHTEST 테이블 생성

SQL> CREATE TABLE JHTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER,
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30),
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_HWM_COLB ON HWMTEST1(COLB);

 

■ 샘플 데이터 500만건 insert 

SQL>
DECLARE
TYPE tbl_ins IS TABLE OF JHTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1000000 INSERT INTO JHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO JHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO JHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO JHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO JHTEST VALUES w_ins(i);
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

 

■ HWM 테스트 할 JHTEST2 테이블 생성 

SQL> CREATE TABLE JHTEST2 AS SELECT * FROM JHTEST;

Table created.

 

■ 통계정보 수집

SQL> exec dbms_stats.gather_table_stats('JH','JHTEST');

SQL> exec dbms_stats.gather_table_stats('SYS','JHTEST2');

PL/SQL procedure successfully completed.

 

■ count 시간 확인

SQL> SET TIMING ON;
SQL> select count(*) from JHTEST;

  COUNT(*)
----------
   5000000

Elapsed: 00:00:00.15

 

 

■ 해당 TABLE BLOCK 확인 & 실제 사용하는 Block 수 확인 (87567개)

SQL> select owner,TABLE_NAME, NUM_ROWS, CHAIN_CNT, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN FROM DBA_TABLES where table_name='JHTEST';

OWNER    TABLE_NAME       NUM_ROWS    CHAIN_CNT       BLOCKS  EMPTY_BLOCKS    AVG_SPACE   AVG_ROW_LEN
------------------------------------------------------------------------------------------------ ----
JH         JHTEST          5000000           0          87569            0           0          121


SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from JHTEST;

    BLOCKS
----------
     87567

 

 

■ 샘플데이터 delete (300만건) & count조회 시간 확인

SQL> delete JHTEST where rownum<=3000000;

3000000 rows deleted.


SQL> select count(*) from JHTEST;

  COUNT(*)
----------
   2000000

Elapsed: 00:00:00.15

 

■ 해당 TABLE BLOCK 확인 & 실제 사용하는 Block 수 확인 (35028개)

=> DELETE 작업 후 테이블 및 인덱스 용량과 DBA_TABLE및 DBA_SEGMETNS의 BLOCK 수는 DELETE 작업 전과 동일하지만 실제 사용 BLOCK은35028로 줄었다. 

이는 HWM 까지 사용하고 있다고 표시하고 있기 때문 이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면HWM를 줄여주는 것이 좋습니다.

SQL> select owner,TABLE_NAME, NUM_ROWS, CHAIN_CNT, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN FROM DBA_TABLES where table_name='JHTEST';

OWNER    TABLE_NAME       NUM_ROWS    CHAIN_CNT       BLOCKS  EMPTY_BLOCKS    AVG_SPACE   AVG_ROW_LEN
------------------------------------------------------------------------------------------------ ----
JH        JHTEST          5000000           0          87569            0           0          121


SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from JHTEST;

    BLOCKS
----------
     35028

 

 

TABLE SHRINK

* ALTER table table_name SHRINK SPACE [COMPACT] [CASCADE];

-COMPACT 옵션이 지정된 경우 segment shrink는 1단계인 데이터 compaction까지만 수행되게 된다 Lock을 잡지 않습니다.

-CASCADE 옵션이 지정된 경우 segment shrink는 dependent한 오브젝트들에 대해서도 자동으로 수행되게 됩니다. 예를 들면 테이블을 shrink하면서, 그 테이블에 대해 정의된 인덱스들 또한 자동으로 동시에 shrink할 수 있습니다.

 

■ row-movement 활성화

SQL> alter table jh.JHTEST enable row movement;

Table altered.

 

■ Shrink 실행

SQL> ALTER TABLE JHTEST SHRINK SPACE;

Table altered.

 

■ 통계정보 수집 

SQL> exec dbms_stats.gather_table_stats('JH','JHTEST');

PL/SQL procedure successfully completed.

 

■ shrink 이후 block확인 & count 조회 시간 확인

15 -> 8로 줄었다..얼마 안줄었지만..줄긴 줄었습니다..

SQL> select owner,TABLE_NAME, NUM_ROWS, CHAIN_CNT, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN FROM DBA_TABLES where table_name='JHTEST';

OWNER    TABLE_NAME       NUM_ROWS    CHAIN_CNT       BLOCKS  EMPTY_BLOCKS    AVG_SPACE   AVG_ROW_LEN
------------------------------------------------------------------------------------------------ ----
JH        JHTEST          2000000           0          35035            0           0          121


SQL> select count(*) from JHTEST;

  COUNT(*)
----------
   2000000

Elapsed: 00:00:00.08

 

 

참고

 https://positivemh.tistory.com/350

오라클 reorg, hwm, shrink, move 설명 및 테스트