Oracle 10g HWM原理及性能优化

Oracle 10g HWM performance tunings

HWM(HighWaterMark)是表中已经使用过的存储空间与未使用过的存储空间之间的分界线,HWM对全表扫描的性能有非常大的影响。当全表扫描时,Oracle会读取HWM下所有的块,即使这些块中有很多是空块,空块的存在,也即是表中碎片的存在,必将增加全表扫描额外的物理I/O开销及CPU开销,严重降低访问Oracle数据表的性能。通过对Oracle中关于表中HWM的原理及性能优化问题的讨论,针对HWM下的碎片问题提出相关的优化策略,并对其空间重组前后进行性能对比测试。

The high water mark （HWM） is a line separate the used blocks and free blocks in a database segment. The HWM is important because of the effect it has when a full-table scan is performed. When performing a full-table scan, Oracle will read all the blocks below the HWM, even if they are empty blocks. Having many unused blocks below the HWM （thus fragmentations） degrades performance for full-table scans with additional, yet unnecessary I/O and CPU. It also fills the buffer cache with empty blocks. This paper discussed the concept of Oracle HWM and HWM performance tunings. It focused on the data fragmentation is- sues when using HWM and provided perforumance optimization solutions. It also compared the performance differences before and after the fragmentation has been resolved.