High Water Mark 意义

High Water Mark 意義

High Water Mark (HWM)指的是當我們建立一個segment的時候，在segment內的一個指標，界定了segment 內曾經配置過的block水位。

The high water mark is the boundary between used and unused space in a segment ~ (From Oracle Concept)

簡單如圖所示，剛建立segment時尚未insert資料，HWM通常在segment header附近，當你不斷地insert資料使用了更多的block之後，HWM自然會跟著移動。但是日後你delete資料後，雖然實際儲存資料的block已經減少，但是HWM仍然不會改變，這就像水庫的水位曾經滿到某個刻度而留下一道水痕，雖然水位下降了但是仍然清晰可見那道高水位。

HWM之下如果存在太多空的block，最大的缺點就是當發生Full Table Scan (FTS)的時候，會造成過多無謂的IO，因為FTS會讀取segment內的block直到HWM為止，如此也會造成buffer pool的使用效率不佳。

來做個測試

SQL> create table tmt as select * from dba_objects;

Table created.

SQL> insert into tmt select * from tmt;

30495 rows created.

SQL> commit;

Commit complete.

SQL> /

Commit complete.

SQL> /

Commit complete.

SQL> commit;

Commit complete.

SQL> @hwm
Enter value for user_name: sys
Enter value for table_name: tmt
Total Blocks: 1792
HWM = 1701
Blocks above HWM: 91

PL/SQL procedure successfully completed.

(hwm.sql是一支可以算出segment 使用total blocks數量和找出HWM位置的script )

SQL> select sum(blocks) from dba_tables where owner='SYS' and table_name='TMT';

SUM(BLOCKS)
-----------

SQL> ANALYZE TABLE TMT COMPUTE STATISTICS;

Table analyzed.

SQL> select sum(blocks) from dba_tables where owner='SYS' and table_name='TMT';

SUM(BLOCKS)
-----------
1700

可以看到目前使用了1700個block

SQL> DELETE TMT;

60990 rows deleted.

SQL> COMMIT;

SQL> @hwm
Enter value for user_name: sys
Enter value for table_name: tmt
Total Blocks: 1792
HWM = 1701
Blocks above HWM: 91

PL/SQL procedure successfully completed.

SQL> select sum(blocks) from dba_tables where owner='SYS' and table_name='TMT';

SUM(BLOCKS)
-----------
1700

儘管刪除了所有資料，HWM仍然是不會改變，Oracle依舊認為該segment用了1700個block

SQL> alter table tmt move tablespace system;

Table altered.

SQL> @hwm
Enter value for user_name: sys
Enter value for table_name: tmt
Total Blocks: 16
HWM = 1
Blocks above HWM: 15

PL/SQL procedure successfully completed.

SQL> select sum(blocks) from dba_tables where owner='SYS' and table_name='TMT';

SUM(BLOCKS)
-----------

SQL> analyze table tmt compute statistics;

Table analyzed.

SQL> select sum(blocks) from dba_tables where owner='SYS' and table_name='TMT';

SUM(BLOCKS)
-----------
0

使用 move table，可以將HWM調回正確的位置上，當然你要用 EXP/IMP也是可以，不過使用move table似乎比較快，記得要重建index。

另外我們可以dump block出來看看裡頭的資訊

SQL> SELECT FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAME='TMT' AND OWNER='SYS';

FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
1 95505 16

SQL> alter system dump datafile 1 block min 95505 block max 95506;

System altered.

*** 2005-11-10 17:46:01.319
*** SESSION ID:(17.155) 2005-11-10 17:46:01.318
Start dump data blocks tsn: 0 file#: 1 minblk 95505 maxblk 95507
buffer tsn: 0 rdba: 0x00417511 (1/95505)
scn: 0x0000.0394fe50 seq: 0x02 flg: 0x04 tail: 0xfe501002
frmt: 0x02 chkval: 0xb8eb type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 15
last map 0x00000000 #maps: 0 offset: 2080
Highwater:: 0x00417512 ext#: 0 blk#: 0 ext size: 15
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 34052 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00417512 length: 15

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
buffer tsn: 0 rdba: 0x00417512 (1/95506)

  http://www.odba.idv.tw/hwm.htm