oracle高水位线

ORACLE在逻辑存储上分4个粒度: 表空间, 段, 区 和 块.         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 表空间: 包含段,区及块.表空间的数据物理上储存在其所在的数据文件中.一个数据库至少要有一个表空间
ORACLE用HWM来界定一个段中使用的块和未使用的块。由于空间是为新插入的行保留的，并且要适应现有行的增长。被占用的最高空间称为最高使用标记 (HWM)          1.当我们创建一个表时, ORACLE就会为这个对象分配一个段, 在这个段中即使我们未插入任何记录, 也至少有一个区被分配, 第一              个区的第一个块就称为段头块(SEGMENT_HEADER), 段头中就储存了一些信息, 其中HWM的信息就存储在此。            此时, 因为第一 个区的第一块用于存储段头的一些信息, 虽然没有存储任何实际的记录, 但也算是被使用, 此时HWM是位于第2个              块, 当我们不断插入数据后, 第一个块已经放不下后面新插入的数据, 此时, ORACLE将高水位之上的块用于存储新增数据, 同时,             HWM本身也向上移, 也就是说, 当我们不断插入数据时, HWM会不断上移, 这样, 在HWM之下的, 就表示使用过的块。 HWM之上             的就表示已分配但从未使用过的块。         2 .HWM在插入数据时, 当现有空间不足而进行空间的扩展时会向上移, 但删除数据时不会往下移。         3 .HWM本身的信息是存储在段头 ：                   在段空间是手工管理方式时, ORACLE是通过FREELIST(一个单向链表)来管理段内的空间分配,                   在段空间是自动管理方式时, ORACLE是通过BITMAP来管理段内的空间分配。         4 .ORACLE的全表扫描是读取高水位标记(HWM)以下的所有块。         5.HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，但            是如果我们在表上使用了truncate命令，则该表的HWM会被重新置为0，这条高水位线在日常的增删操作中只会上涨，不会下跌  所以问题就产生了, 当用户发出一个全表扫描时, ORACLE始终必须从段一直扫描到HWM, 即使它什么也没有发现。 该任务延长了全表  扫描的时间。
低HWM：     在管理段的时候通常有两种方法：手动管理段空间（Manual Segment Space Management ）和自动段空间（Automatic Segment Space Management） 在手动段空间管理（Manual Segment Space Management）中，段中只有一个HWM，但是在Oracle 9i Release1才添加的自动段空间管理（Automatic Segment Space Management）中，又有了一个低HWM的概念出来。为什么有了HWM还又有一个低HWM呢，这个是因为自动段空间管理的特性造成的。 在手段段空间管理中，当数据插入以后，如果是插入到新的数据块中，数据块就会被自动格式化等待数据访问。而在自动段空间管理中，数据插入到新的数据块以后，数据块并没有被格式化，而是在第一次访问这个数据块的时候才格式化这个块。所以我们又需要一条水位线，用来标示已经被格式化的块。这条水位线就叫做低HWM。一般来说，低HWM肯定是低于等于HWM的。
HWM数据库的操作有如下影响： a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。 b) 即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。
使用delete不能使高水位线降低，使用truncate可以使用高水位线降低，所以当删除整表数据或者分区最好是用truncate， 当用delete删除大表数据时后，还是要重建表，这样可以降低高水位线   TRUNCATE命令回收了由delete命令产生的空闲空间为了保留由delete命令产生的空闲空间，可以使用TRUNCATE TABLE 55LINUX REUSE STORAGE.用此命令后，该表还会是原先的1024块   采用TRUNCATE语句删除一个表的数据的时候，类似于重新建立了表，不仅把数据都删除了，还把HWM给清空恢复为0。所以如果需要把表清空，在有可能利用TRUNCATE语句来删除数据的时候就利用TRUNCATE语句来删除表，特别是那种数据量有可能很大的临时存储表。
在9i的时候，一个很成熟的碎片整理技术。 整理表碎片通常的方法是move表 高水位以下合并碎片，不移动高水位 当然move是不能在线进行的， 不跟参数表还是在原来的表空间 而且move后相应的索引也会失效，需要重建 如果以后还要继续向这个表增加数据，没有必要move，只是释放出来的空间，只能这个表用，其他的表或者segment无法使用该空间	table在进行move操作时，我们只能对它进行select的操作，DML会全部阻塞（move生成的undo和redo是非常少的）。反过来说，当我们的一个session对table进行DML操作且没有commit时，  在另一个session中是不能对这个table进行move操作的，否则oracle会返回这样的错误信息：ORA-00054（资源正忙）
oracle在10g时候提供了shrink space碎片整理功能，不仅能整理碎片还可以收缩高水位,索引也不需要重建。 shrink的一个优点是能在线进行，不影响表上的DML操作，当然，并发的DML操作在shrink结束的时刻会出现短暂的block；  shrink的另外一个优点是在碎片整理结束后，表上相关的index仍然enable。shrink在整理表碎片的时候，行源的rowid已经发生改变，那为什么相关的索引还能enable呢？其实oracle在进行shrink的时候会对相应的索引进行维护，以保证index在shrink结束的时候index仍然有效。这个维护不同于索引rebuild，不会对索引的空间进行整理，shrink有cascede选项，如果在shrink的时候加上该选项，就会对表上相应的索引空间进行整理。   SHRINK(收缩) TABLE(表空间收缩) 实质上构造一个新表(在内部表现为一系列的DML操作,即将副本插入新位置，删除原来位置的记录)靠近末尾处(右端)数据块中的记录往开始处(左端)的空闲空间处移动(DML操作)，不会引起DML触发器当所有可能的移动被完成，高水位线将会往左端移动(DDL操作)，新的高水位线右边的空闲空间被释放(DDL操作)         从10g开始, ORACLE开始提供SHRINK的命令, 假如我们的表空间中支持自动段空间管理(ASSM), 就可以使用这个特性缩小段, 即降低HWM。 10g的这个新特性, 必须启用行记录转移(enable row movement)仅仅适用于堆表,且位于自动段空间管理的表空间(堆表包括:标准表,分区表,物化视图容器,物化视图日志表)        如果经常在表上执行DML操作, 会造成数据库块中数据分布稀疏, 浪费大量空间。 同时也会影响权标扫描的性能。 因为全表扫描需要访问更多的数据块。从oracle10g开始, 表可以通过SHRINK来重组数据使数据分布更紧密, 同时降低HWM释放空闲数据块。	shrink必须开启行迁移功能。 segment shrink分为两个阶段: 1 数据重组(compact)： 执行ALTER TABLE test SHRINK SPACE compact: 通过一系列insert、delete操作, 将数据尽量排列在段的前面。 在这个过程中需要在表上加RX锁, 即只在需要移动的行上加锁。由于涉及到rowid的改变, 需要enable row movement, 同时要disable基于rowid的trigger。 这一过程对业务影响比较小。 2. HWM调整： 执行ALTER TABLE test SHRINK SPACE： 调整HWM位置, 释放空闲数据块第一步中的结果已经存储到磁盘，不会重新在整理碎片，只是收缩高水位，释放空间。此过程需要在表上加X锁, 会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。     如果系统业务比较繁忙, 可以先执行shrink space compact重组数据, 然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。
我们先看下shrink的工作原理，shrink的算法是从segment的底部开始，移动row到segment的顶部，移动的过程相当于delete/insert操作的组合，在这个过程中会产生大量的undo和redo信息。在HP Unix上还存在BUG，在10.1.0.3.0中，在shrink的时候可能会触发BUG 3888229，产生巨大数量的redo和undo。move是直接移动数据块的位置，鉴于上面的原因，在使用shrink的时候，耗时可能非常长，通常慢于move。 对于空间的要求，shrink不需要额外的空间，move需要两倍的空间。 通过上面的分析，shrink虽然有online的特性，但是也存在很多问题，所以，在进行表碎片整理的时候，还是建议停机检修，使用move，以下是一些move时候的注意点：  1、move前最好逻辑备份待整理的表；  2、对于大表，建议开启并行和nologging  alter table test move nologging parallel 2;  3、整理完毕后重建相关的索引  alter index ind_test rebuild nologging parallel 2;  4、恢复表和索引的并行度、logging  alter table test logging parallel 1;
1. move过程中需要额外的表空间，需要的大小大约等于当前表中数据量的大小，move结束后立即释放该额外空间。  2. move过程中对表加排它锁，会影响其他session的DML操作。  3. move操作并不会维护索引，因此move完毕后需要对索引rebuild。  4. move操作会降低HWM，但是并不会释放HWM以上的空块，也就是说，move只会对HWM以下的块进行操作。  5. move操作的一些相关测试数据：以2000000数据（233M）为例，删除800000条数据，执行move操作。  大概用时4秒，共产生了319K的redo，56K的undo。表由233M缩小至145M。  6. move操作可以完全消除行迁移。  7. move操作后，为表分配的数据段位置发生了改变，即段头块的位置发生了改变。	shrink过程中并没有用到额外的表空间。 shrink操作其实可以分为两步：  第一步：对数据进行重组，即只会整理碎片，不会降低高水位，也就是说不会释放空间。  通过一系列的delete/insert组合来完成，具体的语法是 alter table t1 shrink space compact。该过程会在表上加共享锁，在移动的行中加排它锁。并且会维护索引。  第二步：降低HWM，回收空间，与move不同的是，shrink可以回收HWM以上的块。该过程会在表上加排它锁，因此业务繁忙时并不适合执行该降低HWM的操作。 shrink操作会维护索引，但是不会对索引进行碎片整理。如果加入cascade选项，那么维护索引的同时会对索引进行碎片整理。 shrink操作的一些相关测试数据：以2000000数据（233M）为例，删除800000条数据，分两步执行shrink操作。  数据重组大概用时1分钟58秒，共产生了895M的redo，353M的undo。回收HWM阶段仅用1秒，产生了4K的redo，1K的undo。表由233M缩小至226M。 shrink操作不能完全消除行迁移。 shrink操作后，为表分配的数据段位置并没有发生变化，即段头块的位置没有改变。
create table HWM as select * from dba_objects; SQL> SELECT segment_name, segment_type, blocks FROM dba_segments WHERE segment_name='HWM'; DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目 SQL> ANALYZE TABLE hwm ESTIMATE STATISTICS; SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='HWM'; USER_TABLES.BLOCKS表示已经使用过的数据库块的数目，即水线。 USER_TABLES.EMPTY_BLOCKS 代表分配给该表， 但是在水线以上的数据库块，即从来没有使用的数据块。 BLOCKS + EMPTY_BLOCKS (700+323=1023)比DBA_SEGMENTS.BLOCKS少1个数据库块， 这是因为有一个数据库块被保留用作segment header。 DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。 USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。 SQL> SELECT COUNT (DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used" FROM hwm; 有多少块容纳数据 SQL> delete from hwm; SQL> commit; SQL> ANALYZE TABLE hwm ESTIMATE STATISTICS; SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='HWM'; SQL> TRUNCATE TABLE hwm; SQL> ANALYZE TABLE hwm ESTIMATE STATISTICS; SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='HWM';
move	alter table xxx move     --压缩快之后所有索引都会失效，需要重建一下索引 ALTER INDEX INDEX_NAME REBUILD;   高水位以下合并碎片，同时压缩表，不移动高水位。 alter table xxx move compress
释放未 使用空 间	DEALLOCATE UNUSED为释放HWM上面的未使用空间,但是并不会释放HWM下面的自由空间,也不会移动HWM的位置. Alter  table table_name deallocate unused
查询失效索引语句	select index_name,table_name,tablespace_name,status From dba_indexes Where owner='ISC' And status<>'VALID';
	select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
	普通表          Sql脚本，改脚本会生成相应的语句          select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;          select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;          分区表          进行shrink space时 发生ORA-10631错误.shrink space有一些限制.          在表上建有函数索引（包括全文索引）会失败。          Sql脚本，改脚本会生成相应的语句
	select 'alter table '||table_name||' enable row movement;'||chr(10)||' alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;           select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;           select 'alter table '||segment_name||' modify subpartition ' ||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';
启动关闭行迁移	alter table table_name enable row movement ; alter table table_name disable row movement;  注意, alter table table_name row movement语句会造成引用表table_name的对象(如存储过程、包、视图等)变为无效。 需要执行utlrp.sql来编译无效的对象。
把碎片率高的表找出,按表的空间大小排序找出来，不支持压缩表	select 'drop table ' || segment_name || ' purge;', sum(bytes)/1024/1024 Mbytese  from user_segments a , user_tables b where segment_type='TABLE'   and a.segment_name=b.TABLE_NAME  and b.COMPRESSION='DISABLED'  group by segment_name,COMPRESSION  order by sum(bytes)/1024/1024 desc;
SHRINK(收缩)	alter table table_name shrink space [<null> | compact | cascade] ; 大表可同时降低表自身和表空间的高水位线，小表则只可以降低表自身的高水位线   收缩表, 相当于把块中数据打结实了, 但会保持high water mark 。 alter table table_name shrink space compact ;   收缩表, 降低high water mark alter table table_name shrink space ;     收缩表, 降低high water mark, 并把相关索引也要收缩一下。 alter table table_name shrink space cascade ;  回缩索引。 alter index idx_name shrink space ;
统计信息	因为所有的信息都是根据dba_tables，表的信息是根据统计信息得到的，所以如果统计信息不准确，那么整个搜索的结果都可能是错误的 exec dbms_stats.gather_table_stats('user','table_name'); exec dbms_stats.gather_table_stats(user,'test1',estimate_percent=>100); exec dbms_stats.gather_table_stats(user,'ISC_USER',CASCADE=>TRUE);   select table_name,last_analyzed from user_tables where table_name = 'ISC_USER' order by last_analyzed desc ;
查找数据库中某个表空间下，可以实际存储和需要的表空间差别最大的表	SELECT NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 NEED, BLOCKS*8/1024 TRUE, (BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) RECOVER_MB, TABLE_NAME FROM dba_tables WHERE tablespace_name='PSAPSR3' AND BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9>100 SELECT table_name, ROUND((blocks * 8/1024), 2) "高水位空间 M", ROUND((num_rows * avg_row_len / 1024/1024), 2) "真实使用空间 M", ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) M", ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -blocks * 8 * 10 / 100), 2) "浪费空间 M", ((blocks * 8-(num_rows * avg_row_len / 1024))/1024)/(blocks * 8/1024) "浪费空间 %" FROM user_tables WHERE table_name = 'ISC_USER';
Oracle 9i:        （1）如果是INEXTENT， 可以使alter table tablename deallocate unused将HWM以上所有没使用的空间释放        （2） 如果MINEXTENT >HWM 则释放MINEXTENTS 以上的空间。如果要释放HWM以上的空间则使用KEEP 0。        SQL>alter table tablesname deallocate unused keep 0;        （3）truncate table drop storage(缺省值)命令可以将MINEXTENT 之上的空间完全释放(交还给操作系统),并且重置HWM。        （4）如果仅是要移动HWM,而不想让表长时间锁住,可以用truncate table reuse storage,仅将HWM重置。        （5）ALTER TABLE MOVE会将HWM移动,但在MOVE时需要双倍的表空间,而且如果表上有索引的话,需要重构索引        （6）DELETE表不会重置HWM,也不会释放自由的空间(也就是说DELETE空出来的空间只能给对象本身将来的INSERT/UPDATE使用,不能给其它的对象使用)   Oracle 10g：        （1）可以使用alter table test_tab shrink space命令来联机移动hwm,        （2）如果要同时压缩表的索引,可以发布:alter table test_tab shrink space cascade