Oracle降水位是否可以停止,Oracle 降低高水位线的方法

高水位(HIGH WARTER MARK，HWM)好比水库中储水的水位，用于描述数据库中段的扩展方式。高水位对全表扫描方式有着至关重要的影响。当使用DELETE删除表记录时，高水位并不会下降，随之导致的是全表扫描的实际开销并没有任何减少。

例如，首先新建一张空表，大小占用64K，然后插入数据直到表大小变为50G，此时使用DELETE删除所有的数据并且提交，这个时候查询表的大小的时候依然是50G，这就是因为表的高水位没有释放的缘故，而在这时如果使用“SELECT * FROM TABLE_NAME;”语句来查询数据的话，那么查询过程就会很慢，因为Oracle要执行全表扫描，从高水位下所有的块都得去扫描，直到50G的所有块全部扫描完毕。曾遇到一个同事使用DELETE删除了一个很大的分区表，然后执行SELECT查询很久都没有结果，以为是数据库HANG住了，其实这个问题是由于高水位的缘故。所以，表执行了TRUNCATE操作，再次SELECT的时候就可以很快返回结果了。

释放表的高水位通常有如下几种办法：

(1)对表进行MOVE操作：ALTER TABLE TABLE_NAME MOVE;。若表上存在索引，则记得重建索引。

(2)对表进行SHRINK SPACE操作：ALTER TABLE TABLE_NAME SHRINK SPACE;，注意，在执行该指令之前必须开启行移动：ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;。该方法的优点是：在碎片整理结束后，表上相关的索引仍然有效，缺点是会产生大量的UNDO和REDO。

(3)复制要保留的数据到临时表T，DROP原表，然后RENAME临时表T为原表。

(4)exp/imp或expdp/impdp重构表。

(5)若表中没有数据则直接使用TRUNCATE来释放高水位。

如何找出系统中哪些表拥有高水位呢？这里给出两种办法，①比较表的行数和表的大小关系。如果行数为0，而表的当前占用大小减去初始化时的大小(INITIAL_EXTENT)后依然很大，那么说明该表有高水位。②行数和块数的比率，即查看一个块可以存储多少行数据。如果一个块存储的行数少于5行甚至更少，那么说明有高水位。注意，这两种方法都不是十分准确，需要再对查询结果进行筛选。需要注意的是，在查询表的高水位时，首先需要分析表，以得到最准确的统计信息。

下面给出用于查询高水位的几个SQL语句：

SELECTD.OWNER,ROUND(D.NUM_ROWS / D.BLOCKS, 2),

D.NUM_ROWS,

D.BLOCKS,

D.TABLE_NAME,ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_sizeFROMDBA_TABLES DWHERE D.BLOCKS > 10

AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5

AND d.OWNER NOT LIKE '%SYS%' ;

或：

SELECTOWNER,

SEGMENT_NAME TABLE_NAME,

SEGMENT_TYPE,

GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /GREATEST(NVL(HWM,1), 1)),2),0) WASTE_PERFROM (SELECTA.OWNER OWNER,

A.SEGMENT_NAME,

A.SEGMENT_TYPE,

B.LAST_ANALYZED,

A.BYTES,

B.NUM_ROWS,

A.BLOCKS BLOCKS,

B.EMPTY_BLOCKS EMPTY_BLOCKS,

A.BLOCKS- B.EMPTY_BLOCKS - 1HWM,

DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *(1 + (PCT_FREE / 100))) /C.BLOCKSIZE,0),0,1,ROUND((B.AVG_ROW_LEN * NUM_ROWS *(1 + (PCT_FREE / 100))) /C.BLOCKSIZE,0)) + 2AVG_USED_BLOCKS,ROUND(100 *(NVL(B.CHAIN_CNT,0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),2) CHAIN_PER,

B.TABLESPACE_NAME O_TABLESPACE_NAMEFROMSYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ CWHERE A.OWNER =B.OWNERAND SEGMENT_NAME =TABLE_NAMEAND SEGMENT_TYPE = 'TABLE'

AND B.TABLESPACE_NAME =C.NAME)WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /GREATEST(NVL(HWM,1), 1)),2),0) > 50

AND OWNER NOT LIKE '%SYS%'

AND BLOCKS > 100

ORDER BY WASTE_PER DESC;

最后再次提醒各位读者，若表执行了大量的DELETE操作后，则最好回收一下表的高水位。