高水位(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语句:
SELECT D.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_size
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER NOT LIKE '%SYS%' ;
或:
SELECT OWNER,
SEGMENT_NAME TABLE_NAME,
SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
GREATEST(NVL(HWM, 1), 1)),
2),
0) WASTE_PER
FROM (SELECT A.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 - 1 HWM,
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)) + 2 AVG_USED_BLOCKS,
ROUND(100 *
(NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),
2) CHAIN_PER,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
WHERE A.OWNER = B.OWNER
AND SEGMENT_NAME = TABLE_NAME
AND 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操作后,则最好回收一下表的高水位。