对于Oracle High Water Mark(HWM)的理解

        在网上看到不少关于Oracle High Water Mark(HWM)的理解，但是都弄的不太清楚，而且有些说法是不对的，所以还是逼迫自己写篇文章来学习一下，如有不恰当之处请指教，呵呵~

先看看官方对HWM的定义：

High Water Mark

The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

Figure B-5 shows a segment consisting of three extents containing 10K, 20K, and 30K of space, respectively. The high water mark is in the middle of the second extent. Thus, the segment contains 20K of used space to the left of the high water mark, and 40K of unused space to the right of the high water mark.

Figure B-5 High Water Mark

解释的比较清楚，就是使用过的和未使用过的数据块的分界。

关于HWM的计算，对于使用freelist segments可以按照以下公式：

HWM = dba_segments.blocks - dba_tables.empty_blocks - 1

HWM = allocated blocks - blocks above the hwn - 1

dba_segments.blocks 段的块数量

dba_tables.empty_blocks 表的从未使用过的空块数(需要analyze分析之后才能得到)

对于ASSM方式的HWM计算会更复杂一些，这其中又引入了High HWM和Low HWM的概念。

-a Low HWM below which all blocks are formatted
-a High HWM above which no blocks are formatted
Blocks between the two HWMs can be formatted or unformatted: this improves the scan algorithm when scanning the segment for free blocks. We exactly know where to start from since the Low HWM contains the L1 BMBs addresses. The High HWM is used by sequential scan algorithms to indicate where to stop scanning.

对于这种方式的HWM可以通过dump segment header来找到其中的low HWM和high HWM的值，还有一种更直观的方式是使用DBMS_SPACE.UNUSED_SPACE的输出来计算：HWM=total blocks-unused blocks. 这种计算得到的结果应该是high HWM的值。

在做一些测试的时候，ASSM确实会存在low hwm和high hwm,两者之间的blocks有些是unformatted，有些是存在数据的。

另外，对其中的一些概念的理解

dba_segments.blocks ：段中块的数量

dba_tables.blocks ：使用过的块数量，无论里面有没有数据

dba_tables.empty_blocks ：从未使用过的块数

DBMS_SPACE.SPACE_USAGE中unformatted blocks ：没有被格式化的块数，一定在Low HWM之上

DBMS_SPACE.UNUSED_SPACE中total_blocks ：段中块的数量

DBMS_SPACE.UNUSED_SPACE中unused_blocks ：从未使用过的块数

测试10.2.0.4 ASSM中DBMS_SPACE.UNUSED_SPACE中unused_blocks为0的表segment中存在unformatted blocks，说明unformatted blocks不一定被划分到unused_blocks中。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/59069/viewspace-442056/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/59069/viewspace-442056/