[oracle] 하이워터마크(HWM, High Water Mark) - 이해하기 쉽게

하이워터마크란 세그먼트 내에서 사용된 적이 있는 블록과 한 번도 사용된 적이 없는 블록의 경계를 구분하는 지점이다. 테이블에 행을 삽입하려고 할 때, HWM 아래에 충분한 공간이 없는 경우 HWM를 오른쪽으로 이동시키고, 새로운 블록을 할당한다.

 

예제쿼리.txt

0.00MB

 

1. 예제 테이블

	데이터 건수	테이블 용량	전체 블록수	사용중인 블록수	하이워터마크
INSERT 50만건	500,000	72MB	9216	8620	9215
DELETE 30만건	200,000	72MB	9216	3449	9215
INSERT 5만건	250,000	72MB	9216	4310	9215
DIRECT-PATH INSERT 5만건	300,000	80MB	10240	5175	10084

* DELETE로 데이터를 지워도 용량, 전체 블록수, 하이워터마크는 줄어들지 않는다.
* HWM는 별도로 관리하지 않으면 늘어나기만 하고 줄어들지 않는다.
* 데이터를 조회시(풀스캔) HWM까지 읽기때문에 불필요한 DISK I/O가 발생할 수 있다.
* DIRECT-PATH INSERT는 테이블 내의 기존 여유 공간이 있더라도 HWM 이후에 데이터가 추가된다. 행이 자주 삭제되는 경우, 빈 블록이 많이 포함된 큰 테이블이 될 수도 있다.

 

2. 데이터 삽입 / 삭제

-- INSERT 50만건
DECLARE
TYPE tbl_ins IS TABLE OF HWMTEST1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 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..100000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..100000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..100000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..100000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..100000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
END;
/

-- DELETE 30만건
DELETE HWMTEST1 WHERE ROWNUM<=300000;
COMMIT;

-- INSERT 5만건
DECLARE
TYPE tbl_ins IS TABLE OF HWMTEST1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..10000 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..10000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;                     
   FORALL i in 1..10000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;                     
   FORALL i in 1..10000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;                     
   FORALL i in 1..10000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;                     
   FORALL i in 1..10000 INSERT INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
END;
/

-- APPEND HINT INSERT 5만건
DECLARE
TYPE tbl_ins IS TABLE OF HWMTEST1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..10000 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..10000 INSERT /*+ APPEND_VALUES */ INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..10000 INSERT /*+ APPEND_VALUES */ INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..10000 INSERT /*+ APPEND_VALUES */ INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..10000 INSERT /*+ APPEND_VALUES */ INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..10000 INSERT /*+ APPEND_VALUES */ INTO HWMTEST1 VALUES w_ins(i);
   COMMIT;
END;
/

 

3. 하이워터마크 축소 / 초기화

3.1. CTAS (CREATE TABLE ... AS SELECT)

CREATE TABLE HWMTEST2 AS SELECT * FROM HWMTEST1;

	데이터 건수	테이블 용량	전체 블록수	사용중인 블록수	하이워터마크
HWMTEST1	300,000	80MB	10240	5175	10084
HWMTEST2	300,000	42MB	5376	5227	5318

 

3.2. MOVE TO ANOTHER TABLESPACE

DROP TABLESPACE USERS2 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE USERS2
DATAFILE '/oracle/app/oracle/oradata/ORA11g/users02.dbf' SIZE 100M;

ALTER TABLE HWMTEST1 MOVE TABLESPACE USERS2;

	데이터 건수	테이블 용량	전체 블록수	사용중인 블록수	하이워터마크
USER1 TS	300,000	80MB	10240	5175	10084
USER2 TS	300,000	42MB	5376	5227	5318

 

3.3. SHRINK

ALTER TABLE HWMTEST1 SHRINK SPACE;
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

ALTER TABLE HWMTEST1 ENABLE ROW MOVEMENT;
Table altered.

-- 세그먼트 정보 조회 (HWMTEST1)
전체 블록 수 : 5272
사용 블록 수 : 5170
데이터 용량 : 41.1875
데이터 건수 : 300000
하이워터 마크 : 5264

	데이터 건수	테이블 용량	전체 블록수	사용중인 블록수	하이워터마크
-	300,000	80MB	10240	5175	10084
SHRINK SPACE	300,000	41.1875MB	5272	5170	5264

 

3.4. TRUNCATE

DELETE 는 기존에 할당된 영역 및 HWM 의 위치가 그대로인 반면, TRUNCATE 는 HWM 의 위치를 초기화시키고, 할당된 영역을 해제시킨다.

	데이터 건수	테이블 용량	전체 블록수	사용중인 블록수	하이워터마크
-	300,000	80MB	10240	5175	10084
DELETE	0	80MB	10240	0	10084
TRUNCATE	0	0.0625MB	8	0	2

-- DELETE
DELETE HWMTEST1;
COMMIT;

300000 rows deleted.

-- TRUNCATE
TRUNCATE TABLE HWMTEST1;

Table truncated.