【oracle调优】优化全表扫---降低高水位

Oracle高水位线的概念

         Oracle里面的对象放到存储级别都称为segment（段），比如表段，索引段，回滚段等，这些知识segment的逻辑描述。下面就拿表段为例解释下。

         在create table的时候可以指定分配给table的初始空间的大小，一般是都少个extent（区间），extent就是多个连续的block。但是我在表中插入数据是个一点点变化的过程。比如我建了一个表，初始分配了8个extents,每个extents由8个block组成，则就相当于给这个表分配了8*8个blocks。再加入我要插入的数据一行的长度为一个块大小，则我每插入一行就需要使用一个block。

         高水位就是用来标明我以往使用过的块数的最高位。

         刚开始我没有插入数据的时候，高水位就在0块的位置，当我插入1行数据（占用一个块），则高水位就上升1，指向1块的位置。就这样，在不断的插入数据的过程中，高水位一直在增加，用来标示这个表曾经一共用过的块数。

         但是，高水位有个这样的问题，当我删除数据的时候，高水位线是不会自动降的。

而对于全表扫描，系统要扫描多少个块，不是为这个表分配了多少块，而是高水位线以下的所有数据块。

         所以，我原来有个表，首先插入了100行的数据，高水位线到了100的位置，后来我又删了99条数据，只剩了一个数据块有用，但是高水位线依然在100的位置。当我要全表扫描的时候，仍然会把高水位下的100个数据库全部读一遍才能返回数据。而实际100个数据块中只有一个是有用的。

 

如何查看表的高水位线

         首先需要搜集表的统计信息

exec dbms_stats.gather_table_stats(ownname => 'CWCS',tabname => 'GL_FREEVALUE',cascade => true,estimate_percent => 100,method_opt => 'for all indexed column size auto')

         然后查看dba_tables即可

select blocks,        empty_blocks   from dba_tables  where table_name = 'TMP_WJF'

         其中blocks+empty_blocks就是分配给表的总块数，blocks就是高水位线。

 

如何查看表实际使用的数据块数

         使用以下sql

SELECTCOUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||DBMS_ROWID.ROWID_RELATIVE_FNO(rowid))  ZS FROM TMP_WJF;

         如果实际用的块数比高水位线低很多，则调整高水位可显著改善全表扫描效率。

 

如何降低高水位。

表收缩

首先激活行迁移，再执行表收缩

altertable TMP_WJF enablemovement; altertable TMP_WJF shrinkspace;

表收缩的过程大致猜想如下：oracle以行为单位，从表的最后一行开始，然后开始扫描表，发现表前面的数据库有空间空间时，就将这最后一行插入到新的位置，然后删除最后一行的数据，以此迭代。

所以，在shrink的过程中，只有部分行的rowid会发生改变，且表所占的块区域不会改变。

而且在shrink的过程中，oracle会自动维护表相关的索引，所以，若表上索引比较大，则在shrink过程中索引的维护费用也比较大。

         其中altertable TMP_WJF enablemovement；可能会造成表相关的视图、存储过程等失效。索引最好在shrink之后执行下utlrp.sql编译下无效对象。

      Shrink过程分两个过程

      数据重组过程，这个过程只会在需要移动的行上加排他锁。因此对业务影响较小。因为涉及到rowid的变更，需要enable movement和禁用基于rowid的trigger。

    降低HWM过程，这个过程会在全表上加排他锁，所以会阻塞所有关于这个表的DML操作，所以，对业务繁忙的系统还是会造成比较大的影响。

    可以使用altertable TMP_WJF shrinkspace compat；只进行数据重组过程，等业务闲了再altertable TMP_WJF shrinkspace降低高水位。

表重建

altertable TMP_WJF move;

此种方式，在表空间内，至少有两倍该表所占空间的空间才能实施重建。而且该方式不会减少预先为表分配的数据块总数。

对于move操作，会在一块新的区域内拷贝原表的数据，所以，move操作后，表所占的区域也发生了改变，所有行的rowid也发生了改变。

Move操作并不会同步维护索引等相关对象，因此，在move完之后要对索引进行rebuild。

在整个move操作中，会对表加上排他锁，所有的DML操作将会被阻塞，只能进行select。