Segment Space and the HWM

Oracle은 space 관리를 위해, Segment에 Block의 상태를 추적합니다. 이때 추적해야 할 Block의 기준을 지정하는 것이 HWM입니다.​

HWM(High Water Mark)란? Segment의 Data Block이 format되지 않으면서 사용된 이력이 없는 지점을 표시한 것.​

Segment Space를 관리하는 방법으로는 MSSM/ASSM 두가지가 있지만, Oracle이 권장하는 ASSM 기법을 사용한다는 전제로 설명하겠습니다. <- 따로 설정하지 않는 이상 ASSM이 Default.​

세션이 처음으로 Table에 Data를 Insert할 때, Oracle은 Single Bitmap Block을 format합니다. 이러한 Bitmap을 사용하여 Free Block을 찾은 다음 각 Block을 Data로 채우기 직전에 format합니다. 또한 아래에 나올 Low HWM의 위치를 얻기위해서 Bitmap Block을 Read합니다. ​

ASSM Segment의 모든 Data Block은 다음 상태에 속합니다.

Above the HWM

사용된 적이 없는 unformatted 상태의 Block

Below the HWM

1. 할당되었지만, 현재 unformatted/unused
2. Formatted/Data를 포함
3. Formatted/Empty : Data가 delete되어 Empty

 

아래 그림처럼 Table 생성시에 HWM는 Segment의 제일 왼쪽부분에 있습니다. 아직 Data가 Insert되지 않았기 때문에 Segment내의 모든 Block은 Never Used/Unformatted 상태입니다. 

HWM at Table Creation

그렇다면 Table에 Insert가 일어난다면 어떻게 될까요?
Oracle은 row를 저장할 Block을 할당해야 합니다. 할당된 Block은 HWM 아래에 있습니다.(그룹의 나머지 Block을 미리 할당하거나 Format 하지 않습니다.) 
Low HWM란? 해당 지점 아래에 모든 Block이 Data를 포함하여 Format이 이루어졌음을 표시 

HWM and Low HWM

Oracle은 HWM와 Low HWM사이의 Available space가 있는 Block을 선택하여 Write합니다. 

HWM and Low HWM

이러한 Low HWM 개념은 Full Table Scan(FTS)에서 중요하게 사용됩니다. HWM 아래의 Block들은 사용될 때 format되기 때문에 일부 Block은 위 그림처럼 Unformatted 상태일 수 있습니다. 따라서 FTS시에 Low HWM 아래에 모든 Block을 Read, Low HWM와 HWM 사이에 Formatted Block을 추가로 Read합니다. ​

여기서 새 트랜잭션이 Table에 row를 Insert하는데 Low HWM와 HWM 사이에 충분한 여유 공간이 없다면?
HWM를 오른쪽으로 이동시켜 unformatted Block의 새 그룹을 할당합니다. 이때 Low HWM는 기존의 HWM위치로 진행합니다. 

Advancing HWM and Low HWM

Database에 Insert Data를 함에 따라 HWM/Low HWM는 계속해서 오른쪽으로 진행합니다. 이러한 HWM/Low HWM는 Object를 수동으로 rebuild, truncate, shrink를 통해 retreat 시킬 수 있습니다.

 

* HWM는 Insert시에 늘어나므로 Insert 후 rollback을 하더라도 HWM는 오른쪽으로 진행한 상태로 유지됩니다.

--test를 위한 table 생성
SQL> create table HWMTEST(
  2  id number, 
  3  likenum varchar2(50),
  4  indate date not null
  5  );

Table created.

--생성 직후 segment 조회
SQL> select segment_name, blocks, bytes/1024/1024 MB  
  2  from dba_segments
  3  where segment_name='HWMTEST';

no rows selected

--test를 위한 data insert
SQL> insert into HWMTEST
  2  select level as id,
  3  'I like '||round(dbms_random.value(1,1000000),0) as likenum,
  4  to_date('2023-01-01', 'yyyy-mm-dd')+dbms_random.value(1,1000) as indate
  5  from dual
  6  connect by level <= 1000000;

1000000 rows created.

--insert 직후 segment 조회
SQL> select segment_name, blocks, bytes/1024/1024 MB  
  2  from dba_segments
  3  where segment_name='HWMTEST';
  SEGMENT_NAME             BLOCKS         MB
-------------------- ---------- ----------
HWMTEST                    4480         35

>>현재 commit/rollback을 하지 않았으므로 다른 세션에서는 select count(*) = 0으로 보임.

--rollback
SQL> rollback;

Rollback complete.

--rollback 후 segment 조회
SQL> select segment_name, blocks, bytes/1024/1024 MB  
  2  from dba_segments
  3  where segment_name='HWMTEST';

SEGMENT_NAME             BLOCKS         MB
-------------------- ---------- ----------
HWMTEST                    4480         35

--1차 insert는 rollback 후 2차 insert 시도를 하면?
SQL> insert into HWMTEST
  2  select level as id,
  3  'I like '||round(dbms_random.value(1,1000000),0) as likenum,
  4  to_date('2023-01-01', 'yyyy-mm-dd')+dbms_random.value(1,1000) as indate
  5  from dual
  6  connect by level <= 1000000;

1000000 rows created.

--2차 insert가 1차 insert와 size가 동일하게 들어가니까 LowHWM~HWM사이에 딱 맞아서 HWM가 더 올라가지는 않음.
SQL> select segment_name, blocks, bytes/1024/1024 MB  
  2  from dba_segments
  3  where segment_name='HWMTEST';

SEGMENT_NAME             BLOCKS         MB
-------------------- ---------- ----------
HWMTEST                    4480         35



--2차 insert가 1차 insert보다 더 크다면?
SQL> insert into HWMTEST
  2  select level as id,
  3  'I like '||round(dbms_random.value(1,1000000),0) as likenum,
  4  to_date('2023-01-01', 'yyyy-mm-dd')+dbms_random.value(1,1000) as indate
  5  from dual
  6  connect by level <= 1200000;

1200000 rows created.

--LowHWM~HWM 사이에 쓸 공간이 충분하지 않으므로 HWM가 더 올라갔음.
SQL> select segment_name, blocks, bytes/1024/1024 MB   
  2  from dba_segments
  3  where segment_name='HWMTEST';

SEGMENT_NAME             BLOCKS         MB
-------------------- ---------- ----------
HWMTEST                    5504         43

SQL> rollback;

Rollback complete.

--rollback을 하여도 한번 올라간 HWM는 내려오지 않는다.
SQL> select segment_name, blocks, bytes/1024/1024 MB   
  2  from dba_segments
  3  where segment_name='HWMTEST';

SEGMENT_NAME             BLOCKS         MB
-------------------- ---------- ----------
HWMTEST                    5504         43

 

참조:

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-5050DCC5-DBBD-4B57-AB14-D83A480B9AAE

Database Concepts