HWM

TABLE이 차지하는 실제적인 공간 (TABLE USED SPACE) ( HWM 아래 )
===============================================================

 

PURPOSE
--------

얼마나 많은 블럭을 실제적으로 테이블이 사용하고 있는지를 확인하는 자료이다.
즉 테이블이 얼마나 많은 빈 공간(empty block)을 가지고 있는지를 알수 있다.

Explanation
-----------

< 얼마나 많은 블럭이 데이타를 포함하는가(빈 블럭이 아닌) >

테이블에 있는 각각의 row는 ROWID라는 pseudocolumn을 가진다. 이
이 ROWID는 아래와 같이 row의 물리적인 위치에 대한 정보를 가진다.

                     block_number.row.file

하나의 데이타 파일을 가진 테이블스페이스에 테이블이 저장된다면, 테이블의
ROWID에서 블럭번호의 distinct 한 값을 얻을수 있다.

하지만 하나 이상의 데이타 파일을 가진 테이블스페이스에 테이블이 저장된다면
파일 번호는 다르지만 동일한 블럭번호를 가질수 있기 때문에 ROWID에서
블럭번호+파일번호의 distinct 한 값을 얻을수 있다.

아래의 SELECT 문장은 "실제로 사용된" 블럭의 수를 보여준다. ORACLE7 과 ORACLE 8
이상의 서버에서 ROWID의 구조가 다르기 때문에 SELECT 문장은 다르다.

ORACLE 7 인 경우:

   SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
                         SUBSTR(rowid,1,8)) "Used"
     FROM schema.table;

ORACLE 8 이상인 경우:

  SELECT COUNT (DISTINCT
         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
    FROM schema.table;

또는

    SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used"
    FROM schema.table;

여기서 우리는 위의 정보가 ANALYZE TABLE 명령을 수행했는지에 따라
결정되지 않는다는 것을 주목할수 있다. ANALYZE TABLE 명령은 단지
사용된 적이 있는 블럭의 수를 나타내며 또한 블럭에 대한 HWM(HIGH WATER
MARK)에 대한 내용을 나타낸다.

 

< HWM(High Water Mark)란 무엇인가? >

모든 세그먼트는 세그먼트안에 데이타를 포함하고 있는 상위 경게선을 가진다.
이 상위 경계선을 "high water mark" 또는 HWM 라고 부른다. High water mark 는
세그먼트에 할당된 블럭을 표시한다, High water mark 는  일반적으로 5개의
데이타 블럭씩 한번에 옮겨간다. Truncate 명령으로 인하여 High wator mark 아래에는
빈 블럭이 존재할수 있으며 또한 delete로 인하여 빈 공간이 있을수 있다. Delete할때
오라클은 HWM을 아래로 내리지 않으며 또한 테이블을 shrink하지도 않는다. 이것은
Oracle 8 도 마찬가지이다. Full table scan(전체 테이블 검색)시에 일반적으로
HWM까지 읽는다.

데이타 파일은 high water mark을 가지지 않으며, 세그먼트만이 high water mark
을 가진다.

 

< High water mark 를 어떻게 측정하는가 >

특정 테이블의 high water mark를 보기 위해서는 아래와 같이 ANALYZE TABLE
명령을 수행한다.

  ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

테이블 통계을 만든후에 high water mark을 측정한다.

SELECT blocks, empty_blocks, num_rows
FROM   user_tables
WHERE table_name = <tablename>;

BLOCKS 는 세그먼트에 의해 사용된 적이 있는 블럭의 수를 나타낸다.
EMPTY_BLOCKS 는 단지 'HIGH WATER MARK' 위의 블럭을 나타낸다.

레코드를 삭제하는 것은 high water mark의 위치를 아래로 옮기지 않는다.
그러므로, 레코드를 삭제하는 것은 EMPTY_BLOCKS 의 수를 늘리지 않는다.

Example
---------

아래의 에제는 ORACLE 8.1.7 에서 114688 레코드를 가진 BIG_EMP 테이블을
가지고 test하였다.
 
SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks,extents
     FROM dba_segments
     WHERE segment_name='BIG_EMP';

SEGMENT_NAME              SEGMENT_TYPE                             BLOCKS    EXS
------------------------- ------------------------------------ ---------- ------
BIG_EMP                   TABLE                                       955      2
    
SQL> connect scott/tiger
Connected.

SQL> ANALYZE TABLE big_emp ESTIMATE STATISTICS;
Table analyzed.  

SQL> SELECT table_name,num_rows,blocks,empty_blocks
     FROM user_tables
     WHERE table_name='BIG_EMP';

TABLE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------- ---------- ---------- ------------
BIG_EMP                       114688        954            0    

참고 : BLOCKS + EMPTY_BLOCKS (954+0=954)의 값이 DBA_SEGMENTS의 BLOCKS보다
적다, 그 이유는 한 블럭을 세그먼트 헤더로 잡기 때문이다. DBA_SEGMENTS의 BLOCKS
는 테이블에 할당된 전체수를 의미한다.

SQL> SELECT COUNT (DISTINCT
              DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
              DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
     FROM big_emp;

      Used
----------
       672  

SQL> DELETE from big_emp;
114688 rows deleted. 

SQL> commit;
Commit complete.

SQL> ANALYZE TABLE big_emp ESTIMATE STATISTICS;
Table analyzed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
     FROM user_tables
     WHERE table_name='BIG_EMP';

TABLE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------- ---------- ---------- ------------
BIG_EMP                            0        954            0 

SQL> SELECT COUNT (DISTINCT
              DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
              DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
     FROM big_emp;

      Used
----------
         0

SQL> TRUNCATE TABLE big_emp;

Table truncated.

SQL> ANALYZE TABLE big_emp ESTIMATE STATISTICS;

Table analyzed. 

SQL> SELECT table_name,num_rows,blocks,empty_blocks
     FROM user_tables
     WHERE table_name='BIG_EMP';

TABLE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------- ---------- ---------- ------------
BIG_EMP                            0          0            4

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks,extents
     FROM dba_segments
     WHERE segment_name='BIG_EMP';

SEGMENT_NAME              SEGMENT_TYPE                             BLOCKS    EXS
------------------------- ------------------------------------ ---------- ------
BIG_EMP                   TABLE                                         5      1
   
참고 : TRUNCATE 는 레코드를 삭제함으로써 생긴 공간을 반환한다.
       레코드를 삭제함으로써 생긴 공간을 유지하기 위해서는 다음과
       같이 할수 있다.

       TRUNCATE TABLE big_emp1 REUSE STORAGE

결론
----

실제적으로 데이타가 차지하는 공간은 ROWID의 distinct한 값의 수로 알수 있지만
우리는 보통 두번째에 설명한 ANALYZE 이후의 USER_TABLES의 BLOCKS를 가지고 판단한다.

Reference Documents
-------------------
<Note:77635.1>
Korean Bulletin : 10308
Korean Bulletin : 11604

[출처] Table 이 차지하는 실제 공간(HWM)|작성자 지수러브