Oracle高水位线的深入理解
一、准备知识 ORACLE的逻辑存储管理.
1.1 块: 是粒度最小的存储单位,现在标准的块大小是8K,ORACLE每一次I/O操作也是按块来操作的,也就是说当ORACLE从数据文件读数据时,是读取多少个块,而不是多少行. 每一个Block里可以包含多个row.
1.2 区: 由一系列相邻的块而组成,这也是ORACLE空间分配的基本单位,举个例子来说,当我们创建一个表Dave时,首先ORACLE会分配一区的空间给这个表,随着不断的INSERT数据到Dave,原来的这个区容不下插入的数据时,ORACLE是以区为单位进行扩展的,也就是说再分配多少个区给Dave,而不是多少个块.
1.3 段: 是由一系列的区所组成, 一般来说, 当创建一个对象时(表,索引),就会分配一个段给这个对象. 所以从某种意义上来说,段就是某种特定的数据.如CREATE TABLE Dave,这个段就是数据段,而CREATE INDEX ON Dave(NAME), ORACLE同样会分配一个段给这个索引,但这是一个索引段了.查询段的信息可以通过数据字典: SELECT * FROM USER_SEGMENTS来获得.
1.4 表空间: 包含段,区及块.表空间的数据物理上储存在其所在的数据文件中.一个数据库至少要有一个表空间.
二、什么是水线(High Water Mark)?
所有的oracle段(segments，在此，为了理解方便，建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为"high water mark"或HWM。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令，则该表的HWM会被重新置为0。
三、HWM数据库的操作有如下影响：
a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。
b) 即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。
以上是理论基础，下面是语句实操
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
如何知道一个表的HWM？
估算表在高水位线下还有多少空间可用，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少!
第一步：用analyze命令收集一次表的统计信息
ANALYZE TABLE ENTITYPARAMETERVALUE COMPUTE STATISTICS;
第二步：查看块情况
SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = 'ENTITYPARAMETERVALUE';
说明：
BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。
EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。
第三步：查看高水位线下还有多少空间可用，越少越好
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'ENTITYPARAMETERVALUE';
此时为7MB
------------------------------------------------------------------------------
修正高水位线的好处及验证
修正前 获取全表扫描对应的执行计划，查看消耗CPU为1928
explain plan for select * from ENTITYPARAMETERVALUE;
select * from table(dbms_xplan.display);
删除数据测试（Delete删除数据并不会影响高水位线）
delete from ENTITYPARAMETERVALUE where 1=1；
commit；
删除后 表记录数为0 但消耗CPU为 1913 与删除前相差无几
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'ENTITYPARAMETERVALUE';
此时高水位线的可用空间为55MB，明显变大，说明全表扫描做了很多无用功
关键：修正高水位线
对表进行碎片整理，重新收集统计信息
允许行移动
alter table ENTITYPARAMETERVALUE enable row movement;
收缩表
alter table ENTITYPARAMETERVALUE shrink space cascade;
知识点备注：Move 通过移动数据来来降低HWM，因此需要更多的磁盘空间。 Shrink 通过delete 和 insert， 会产生较多的undo 和redo。
shrink space收缩到数据存储的最小值，alter table move（不带参数）收缩到initial指定值，也可以用alter table test move storage(initial 500k)指定收缩的大小，这样可以达到shrink space效果。
总之，使用Move 效率会高点，但是会导致索引失效。Shrink 会产生undo 和redo，速度相对也慢一点。
而后从新对标进行统计信息收集
ANALYZE TABLE ENTITYPARAMETERVALUE COMPUTE STATISTICS;
查看高水位线下还有多少空间可用，越少越好 此时为0.0078MB，说明已经修正成功
此时重新查看全表扫描执行计划，全表扫描CPU消耗仅为 2
这就是通过整理碎片，修正高水位线，开提高查询效率的手段
以下SQL 基于普通表
shrink必须开启行迁移功能。
alter table table_name enable row movement ;
保持HWM，相当于把块中数据打结实了
alter table table_name shrink space compact;
回缩表与降低HWM
alter table table_name shrink space;
回缩表与相关索引，降低HWM
alter table table_name shrink space cascade;
回缩索引与降低HWM
alter index index_name shrink space
修正ORACLE表的高水位线的所有方法
在ORACLE中，执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块。如果在执行删除操作后不降低高水位线标记，则将导致查询语句的性能低下。下面的方法都可以降低高水位线标记。
1.执行表重建指令 alter table table_name move;
(在线转移表空间ALTER TABLE 。。。 MOVE TABLESPACE 。。。ALTER TABLE 。。。 MOVE 后面不跟参数也行，不跟参数表还是在原来的表空间，move后记住重建索引。如果以后还要继续向这个表增加数据，没有必要move，只是释放出来的空间，只能这个表用，其他的表或者segment无法使用该空间)
2.执行alter table table_name shrink space; 注意，此命令为Oracle 10g新增功能，再执行该指令之前必须允许行移动alter table table_name enable row movement;
3.复制要保留的数据到临时表t，drop原表，然后rename临时表t为原表
4.emp/imp
5.alter table table_name deallocate unused
6.尽量truncate吧
