Oracle高水位标记（HWM）

共计 22718 个字符，预计需要花费 57 分钟才能阅读完成。

（一）高水位标记（High Water Mark，HWM）的概念

所谓高水位标记，是指一个已经分配的段中，已经使用的空间与未使用的空间的分界线。在表的使用过程中，随着数据的不断增多 (insert)，HWM 不断向数据段未使用部分方向移动，而在删除数据（delete）的过程中，HWM 并不会向反方向移动，即使删除全部数据，HWM 依然不会改变。但是如果使用了 truncate 命令，则表的 HWM 会被重置为 0。

Oracle 高水位标记（HWM）

图 1.segment

（二）高水位标记的影响

全表扫描要读出直到 HWM 标记的所有的属于该表的数据块（used space），即使该表中没有任何数据；
即使 HWM 下有空闲的数据块，如果在插入数据时使用了 append 参数，则在插入数据时使用 HWM 以上的数据块，HWM 会自动增大。

（三）如何知道一个表的 HWM

1. 首先对表进行分析

SQL > ANALYZE TABLE table_name ESTIMATE/COMPUTE STATISTICS;

2. 查看水线

SELECT
    blocks,                 -- 该表曾经使用过的数据块的数目，即水线 
    empty_blocks,            -- 代表分配给该表，但是在水位线以上的数据块，即从来没有使用过的数据块 
    num_rows
FROM 
    user_tables
WHERE
    table_name =‘table_name’;

* 注：在 Oracle 11g 中，收集数据库对象信息的最好方法，不再是使用带 ESTIMATE 或 COMPUTE 的 ANALYSE 语句，而是使用最新的 DBMS_STATS 包。但是，如果要收集数据库对象存储格式的有效性以及收集表与簇中的行迁移、行链接情况，还得使用 ANALYSE。

（四）Oracle 表段中的高水位线

每个 Oracle 数据块在 ASSM 段中都属于下面的一种状态：

高水位线以上

这些块未被格式化且从来没有被使用过。

高水位线以下（3 类）

– 已经分配，但是未格式化和未使用；

– 格式化，且含有数据；

– 格式化，不含有数据，因为数据被删除了。

（1）在创建表的时候，HWM 位于 segment 左边的起始处，因为没有数据插入，segment 中全部的 block 未被格式化和从未被使用。

Oracle 高水位标记（HWM）

（2）假设一个事务将行数据插入到 segmnet 中，数据库必须分配一组数据块去保存行信息，被分配的数据块全在 HWM 之下，数据库格式化一个位图块来保存元数据，但是没有指定是哪一个数据块。

Oracle 高水位标记（HWM）

在 HWM 以下的数据块是被分配的，在 HWM 以上的数据块从未被分配和格式化。当 insert 时，数据可以写到有可用空间的任何块中。low HWM 以下的部分，所有的块都被格式化，因为它们要么含有数据，要么以前包含数据。

（3）当 insert 时，数据库选择在 HWM 和 low HWM 之间的任意一个块进行写入，或者是 HWM 以下的一个有空闲空间的块进行写入。下图中，在 Low HWM 与 HWM 之间已写满块的两侧的数据块还未格式化。

Oracle 高水位标记（HWM）

（4）low HWM 对于全表扫描是非常重要的。因为 HWM 以下的数据块只有在要使用时才格式化，有一些块还未格式化。基于这个原因，数据库会去 bitmap 块查询 low HWM 的位置，然后会去读 low HWM 以下的全部数据块，因为数据库已经知道这些块全部被格式化了，对于在 low HWM 和 HWM 之间的数据块，数据库会挑选那些已经格式化了的数据块进行读操作。

（5）假设一个新的事务进行插入操作，但是 bitmap 指示目前在 HWM 以下已经没有足够的空间了，数据库会向右移动该 segment 的 HWM，分配一组新的未格式化的数据块。

Oracle 高水位标记（HWM）

（五）降低 HWM

降低 HWM 可以使用 rebuild、truncate、shrink、move 等操作。

（5.1）SHRINK

shrink 技术是一种段收缩技术，可将表与索引高水位以下的碎片进行有效压缩，并将高水位进行回退。

使用方法如下：

step1. 启用行移动

SQL > ALTER TABLE table_name ENABLE ROW MOVEMENT;

step2. 压缩数据及下调 HWM

SQL > ALTER TABLE table_name SHRINK SPACE CASCADE; -- 压缩表及相关数据段并下调 HWM

或

SQL > ALTER TABLE table_name SHRINK SPACE COMPACT; -- 只压缩数据不下调 HWM

SQL > ALTER TABLE table_name SHRINK SPACE;         -- 下调 HWM

即该技术可以一次性压缩数据及下调 HWM，也可以分两个阶段进行，第一阶段：在业务高峰，只压缩数据不下调 HWM，第二阶段：在业务空闲时，下调 HWM。

（5.2）MOVE Tablespace

语法为：

SQL > ALTER TABLE table_name MOVE TABLESPACE tablepsace_name;

需要注意：

–MOVE 后不跟参数也行，不跟参数还是原来的表空间；

–MOVE 后需要重建索引；

– 如果以后还要网表里插入数据，没必要 MOVE。MOVE 释放出来的空间，只能这个表使用，其它的表或者 segment 无法使用。

（5.3）CTAS 技术

即重建表技术。

SQL > CREATE TABLE new_table_name 
AS
SELECT * FROM old_table_name;                          -- 将表的数据写入到一张新的表里 

SQL > DROP TABLE old_table_name;                       -- 删除旧表 

SQL > RENAME table new_table_name TO old_table_name    -- 将新表名更改为旧表名

（5.4）EXP/IMP 或 EXPDP/IMPDP 技术

与 CTAS 技术相当。

（5.5）DEALLOCATE 技术

利用 DEALLOCATE 技术可以回收 HWM 以上从未使用过的数据块。语法如下

SQL > ALTER TABLE table_name DEALLOCATE UNUSED [KEEP integer];         -- 回收表段 HWM 以上的空间 

SQL > ALTER INDEX index_name DEALLOCATE UNUSED [KEEP integer];         -- 回收索引段 HWM 以上的空间

（六）MOVE 与 SHRINK 的区别

	MOVE	SHRINK(仅对 ASSM 有效)
本质	move 实际上是 block 级别的数据块拷贝，对表进行 move 后，该表所在 blockid 会发生改变数据的 rowid 自然也会发生改变，但是数据在 table 中的存储顺序并没有发生改变	shrink 是对行数据进行移动。对表进行 shrink 后，部分行数据的 rowid 发生了变化，而 table 所位于的 block 区域的位置却没有发生变化。
重建索引	需要重建	可以通过 cascade 关键字重建
锁	TM(exclusive)	TM(SX)
空间要求	需要有原表大的空闲空间	不需要额外的空间
效果	压缩后会回收空间	压缩后会回收空间

详细例子见下面测试。

(七) 高水位问题测试

（8.1）测试目的：

1. 了解 Oracle 统计信息的概念；

2. 测试使用 delete 与 truncate 删除数据对 HWM 的影响 [主要目的]

（8.2）主要步骤

（1）创建表 test01

create table test01
(id number,
  name varchar(15)
);

这个时候，去查看表与段的参数

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                  0            0
 
SQL> 
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
 
SQL> exec dbms_stats.gather_table_stats('LIJIAMAN','TEST01');
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                  0            0
 
SQL> 
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE     HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- -------------    ----------- ------------ ----------     ---------- ----------

通过以上结构可以看出，我在创建表后，去查看表信息，发现表拥有的 blocks=0，以为是统计信息的问题，使用 dbms_stats 去重新收集表的基础信息，结果依然相同。然后查看该表段的信息，发现这个段并不存在。可以说明，数据库在创建表后，只存储了表的基本结构信息，只有在插入数据的时候，才会去分配区。

此时由于未分配数据块，也就不存在高水位线的问题。

（2）我们往 test01 里面插入 10000 条数据

SQL> declare
  2    i number :=1;
  3  begin
  4    loop
  5      if i > 10000
  6      then
  7        exit;
  8      end if;
  9      insert into test01 values(i,'euvcg');
 10      i:=i+1;
 11    end loop;
 12    commit;
 13  end;
 14  /
 
PL/SQL procedure successfully completed

再去看一下表与段的统计信息

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME  BLOCKS     EMPTY_BLOCKS
----------- ---------- ------------
TEST01      0            0
 
SQL> 
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------ ------------------ ----------- ------------ ---------- ---------- ----------
TEST01       TABLE                        6          162     262144         32          4

表 test01 的 blocks 依然为 0，我们使用 dbms_stats 重新收集统计信息，

SQL> exec dbms_stats.gather_table_stats(‘LIJIAMAN’,’TEST01′);

PL/SQL procedure successfully completed

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            0

SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------ ------------------ ----------- ------------ ---------- ---------- ----------
TEST01       TABLE                        6          162     262144         32          4

收集统计信息后，我们对段进行分析，插入 10000 条数据，oracle 一共分配了 4 个区，每个区包含 8 个数据块，每个数据块大小为 8KB。此时，表的统计数据已经有了，但是表的 Blocks 与段的 Blocks 数量不同，这是什么引起的呢？通过查看两个 blocks 栏位的定义，可以看到：

dba_tables.blocks: 该表已经使用的数据块的数量（Number of used data blocks in the table）；

dba_segments.blocks：该段中数据块的总数（Size, in Oracle blocks, of the segment）。

也就是说还有 4 个数据块还未使用。

可以使用 ANALYZE 进行统计

SQL> analyze table TEST01 compute statistics;
 
Table analyzed
 
SQL> 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            4

经过分析，dba_tables 统计的数据块与 dba_segments 统计的数据块数量相同了。

此时的高水位线应该如下：

Oracle 高水位标记（HWM）

（3）删除 test01 里面的全部数据，重新统计信息，发现数据块并没有被回收释放。这些数据块（dba_tables.blocks=28）曾经拥有过数据，但是现在数据已被删除。

SQL> delete from test01;
 
10000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> analyze table test01 compute statistics;
 
Table analyzed
 
SQL> 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            4
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST01        TABLE                        6          162     262144         32          4

此时的高水位线应该如下：

Oracle 高水位标记（HWM）

黑色（used space）里面目前并没有数据，它仅仅代表曾经被使用过，白色（unused space）代表这些块已经分配给了 test01 段，但是还未使用过。

更多详情见请继续阅读下一页的精彩内容 ：http://www.linuxidc.com/Linux/2017-10/147359p2.htm

（4）测试完了 delete，接下来测试 truncate

SQL> truncate table test01;
 
Table truncated
 
--truncate 后直接查询，发现表的统计信息依然未变化，而段的数据块已经回收了 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            4
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME   SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
-------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST01         TABLE                        6          162      65536          8          1
 
-- 执行 dbms_stats 重新收集统计信息，发现表的 blocks 已经为 0，但是表的 blocks 与段的 blocks 并不相等 
SQL> exec dbms_stats.gather_table_stats('LIJIAMAN','TEST01');
 
PL/SQL procedure successfully completed
 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                  0            4
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST01        TABLE                        6          162      65536          8          1
 
-- 再使用 ANALYZE 进行分析，表的 empty blocks 为 8，与段的 blocks 相等 
SQL> analyze table test01 compute statistics;
 
Table analyzed
  
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                  0            8
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME   SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
-------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST01         TABLE                        6          162      65536          8          1

可以发现，truncate 后，表的空间已经回收，但是并不等于 0，而是一个 extent 的大小。此时高水位线为：

Oracle 高水位标记（HWM）

至于 8 个数据块是否有一个被使用（segment header），由于个人能力有限，无法进行分析 –_-

（8.3）结论：通过测试，delete 无法降低高水位线，truncate 可以。

（八）shrink 与 move 测试

（9.1）测试目的：

1. 测试 shrink 与 move 的区别，主要是第（七）点列出的区别

（9.2）测试步骤

（1）创建测试表，插入数据，分析表，查看统计信息

-- 创建表 
SQL> create table test02
  2  (3    id number,
  4    name varchar(15)
  5  );
 
Table created
 
-- 插入 1000 万条数据 
SQL> declare
  2    i number :=1;
  3  begin
  4    loop
  5      if i > 10000000
  6      then
  7        exit;
  8      end if;
  9      insert into test02 values(i,'euvcg');
 10      i:=i+1;
 11    end loop;
 12    commit;
 13  end;
 14  /
 
PL/SQL procedure successfully completed
 
Executed in 185.125 seconds
 
-- 创建索引 
SQL> create index test02_idx on test02 (id,name);
 
Index created
 
Executed in 17.172 seconds
 
-- 分析表 
SQL> exec dbms_stats.gather_table_stats('LIJIAMAN','TEST02');
 
PL/SQL procedure successfully completed
 
Executed in 3.921 seconds
 
SQL> analyze table test02 compute statistics;
 
Table analyzed
 
Executed in 39.11 seconds

-- 查看分析结果 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST02';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST02                              23357          195
 
Executed in 0.078 seconds
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST02';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST02        TABLE                        6          130  192937984      23552         94
 
Executed in 0.078 seconds

可以看到。test02 一共占用了 94 个 extents，23552 个数据块。其中 23357 个数据块有数据，195 个数据块空闲。

接下来，我们先查看表的信息，这里截取了部分结果。在这里，我们需要了解 ROWID 的作用，rowid 是数据在 Oracle 储存中的具体位置，1- 6 位为 object id，7- 9 位为 file_id，10-15 位代表 block id，16-18 位为 row number。在下面结果中，前 5 条数据在 AAAACD 块中，后 6 条数据在 AAAACE 块中。我们取出一个 block，查看其数据：

SQL> select * from test02 where rowid like 'AAASNnAAGAAAACM%';
 
        ID NAME
---------- ---------------
      2912 euvcg
      2913 euvcg
      2914 euvcg
      2915 euvcg
      ...  ...
      3394 euvcg
      3395 euvcg
 
484 rows selected

（2）删除 test02 的一半数据，数据块并没有释放，执行 shrink 操作

-- 删除 500 万条数据 
SQL> declare
  2    i number :=1;
  3  begin
  4    loop
  5      if i > 10000000
  6      then
  7        exit;
  8      end if;
  9      delete test02 where id = i;
 10      i:=i+2;
 11    end loop;
 12    commit;
 13  end;
 14  /
-- 经过查看，数据块未释放 

-- 激活行移动 
SQL> alter table test02 enable row movement;
 
Table altered
 
Executed in 0.078 seconds
 
-- 执行 shrink 操作 
SQL> alter table test02 shrink space cascade;
 
Table altered
 
Executed in 234.593 seconds

需要注意的是，在执行 shrink 的过程中，在表上是有锁存在的，在表上存在 3 级锁（SX）

SQL> select * from v$lock where type in ('TM','TX');
 
SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
--- ---- ---------- ---------- ---------- ---------- ---------- ----------
 30 TM        74597          0          3          0        178          0
 30 TX       327684       1294          6          0          0          0
 30 TX       327699        994          6          0        178          0

(3) 分析数据，查看表信息

SQL> analyze table test02 compute statistics;
Table analyzed

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST02';
 
TABLE_NAME BLOCKS     EMPTY_BLOCKS
---------- ---------- ------------
TEST02          11648          152
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST02';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST02        TABLE                        6          130   96665600      11800         83

在执行 shrink 之后，我们的数据块使用量由原来的 23552 减少为 11800。

（4）再次查看上面数据块中的数据，可以看出在执行了 shrink 之后，该数据块中的数据发生了变化。对于该块，原来的数据保持不变，但是在已经删除数据的空间中，有其它块的数据插入了进来。

SQL> select * from test02 where rowid like 'AAASNnAAGAAAACM%';
 
        ID NAME
---------- ---------------
      2912 euvcg
      4050 euvcg
      2914 euvcg
      4052 euvcg
      2916 euvcg
      4054 euvcg
      3388 euvcg
      3390 euvcg
      3392 euvcg
      3394 euvcg
 
399 rows selected

（9.3）结论

1.shrink 是对行数据进行移动。对表进行 shrink 后，部分行数据的 rowid 发生了变化，而 table 所位于的 block 区域的位置却没有发生变化；

2.shrink 产生 TM（SX）锁及 TX 锁。

（9.4）对 shrink 有了一定的了解，我们再来看一下 move

-- 创建表 
create table test03
(id number,
  name varchar(15)
);

-- 插入数据 
SQL> declare
  2    i number :=1;
  3  begin
  4    loop
  5      if i > 10000
  6      then
  7        exit;
  8      end if;
  9      insert into test03 values(i,'euvcg');
 10      i:=i+1;
 11    end loop;
 12    commit;
 13  end;
 14  /
 
PL/SQL procedure successfully completed
 
-- 统计信息 
SQL> analyze table test03 compute statistics;
 
Table analyzed
 
-- 查看统计信息 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST03';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST03                                 28            4
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST03';
 
SEGMENT_NAME SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------ ------------------ ----------- ------------ ---------- ---------- ----------
TEST03       TABLE                        6          146     262144         32          4

取其中一个数据块，查看存储的信息

SQL> select rowid,id,name from test03 where rowid like 'AAASNxAAGAAAACY%';
 
ROWID                      ID NAME
------------------ ---------- ---------------
AAASNxAAGAAAACYAAA       3880 euvcg
AAASNxAAGAAAACYAAC       3882 euvcg
AAASNxAAGAAAACYAAE       3884 euvcg
AAASNxAAGAAAACYAAG       3886 euvcg
AAASNxAAGAAAACYAAI       3888 euvcg
AAASNxAAGAAAACYAAK       3890 euvcg
AAASNxAAGAAAACYAAM       3892 euvcg
AAASNxAAGAAAACYAAO       3894 euvcg
AAASNxAAGAAAACYAAQ       3896 euvcg

…                           …    …

执行 move 操作

SQL> alter table test03 move;
 
Table altered
 
SQL> analyze table test03 compute statistics;
 
Table analyzed

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST03';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST03                                 14            2
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST03';
 
SEGMENT_NAME   SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
-------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST03         TABLE                        6          186     131072         16          2

在 move 之前我们查看了数据块 AAASNxAAGAAAACY 的信息，一共有 242 行，在 move 之后，我们再去查看该数据块，发现没有数据存在。经过查看，id=4340 的行信息之前存在该数据块，我们可以去看一下目前该行数据存在哪个数据块，找到新的数据块之后查看数据。

— 查看了数据块 AAASNxAAGAAAACY 的信息，发现没有数据，说明数据已经转移到新的数据块中去了
SQL> select rowid,id,name from test03 where rowid like ‘AAASNxAAGAAAACY%‘;

ROWID ID NAME
——————- ———- —————

— 先前的数据块中存在 id=4340 这一行数据，我们看一下该行数据目前的 rowid
SQL> select rowid,id,name from test03 where id=4340;

ROWID ID NAME
——————- ———- —————
AAASNyAAGAAAAC9AHf 4340 euvcg
— 通过 rowid，我们可以确定该行数据的新的数据块 id，查询该数据块信息
SQL> select rowid,id,name from test03 where rowid like ‘AAASNyAAGAAAAC9%‘;
ROWID ID NAME
——————- ———- —————
… … …
AAASNyAAGAAAAC9ADz 480 euvcg
AAASNyAAGAAAAC9AD0 482 euvcg
AAASNyAAGAAAAC9AD1 484 euvcg
AAASNyAAGAAAAC9AD2 486 euvcg
AAASNyAAGAAAAC9AD3 488 euvcg
AAASNyAAGAAAAC9AD4 490 euvcg
AAASNyAAGAAAAC9AD5 3880 euvcg
AAASNyAAGAAAAC9AD6 3882 euvcg
AAASNyAAGAAAAC9AD7 3884 euvcg
AAASNyAAGAAAAC9AD8 3886 euvcg
AAASNyAAGAAAAC9AD9 3888 euvcg
AAASNyAAGAAAAC9AD+ 3890 euvcg
AAASNyAAGAAAAC9AD/ 3892 euvcg
… … …

结论：1.move 之后，与先前数据块信息进行对比，发现数据块信息发生了改变，数据已经移到了其它数据块中。多个数据块的信息合并到了同一个数据块，但是数据的顺序并没有发生改变，即数据在原块中的顺序是怎么样的，迁移到新数据块中还是这样的；

2.move 之后，数据块的使用量减少了，说明 move 收缩空间，降低高水位；

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-10/147359.htm

（一）高水位标记（High Water Mark，HWM）的概念

Oracle 高水位标记（HWM）

图 1.segment

（二）高水位标记的影响

全表扫描要读出直到 HWM 标记的所有的属于该表的数据块（used space），即使该表中没有任何数据；
即使 HWM 下有空闲的数据块，如果在插入数据时使用了 append 参数，则在插入数据时使用 HWM 以上的数据块，HWM 会自动增大。

（三）如何知道一个表的 HWM

1. 首先对表进行分析

SQL > ANALYZE TABLE table_name ESTIMATE/COMPUTE STATISTICS;

2. 查看水线

SELECT
    blocks,                 -- 该表曾经使用过的数据块的数目，即水线 
    empty_blocks,            -- 代表分配给该表，但是在水位线以上的数据块，即从来没有使用过的数据块 
    num_rows
FROM 
    user_tables
WHERE
    table_name =‘table_name’;

（四）Oracle 表段中的高水位线

每个 Oracle 数据块在 ASSM 段中都属于下面的一种状态：

高水位线以上

这些块未被格式化且从来没有被使用过。

高水位线以下（3 类）

– 已经分配，但是未格式化和未使用；

– 格式化，且含有数据；

– 格式化，不含有数据，因为数据被删除了。

（1）在创建表的时候，HWM 位于 segment 左边的起始处，因为没有数据插入，segment 中全部的 block 未被格式化和从未被使用。

Oracle 高水位标记（HWM）

（五）降低 HWM

降低 HWM 可以使用 rebuild、truncate、shrink、move 等操作。

（5.1）SHRINK

shrink 技术是一种段收缩技术，可将表与索引高水位以下的碎片进行有效压缩，并将高水位进行回退。

使用方法如下：

step1. 启用行移动

SQL > ALTER TABLE table_name ENABLE ROW MOVEMENT;

step2. 压缩数据及下调 HWM

SQL > ALTER TABLE table_name SHRINK SPACE CASCADE; -- 压缩表及相关数据段并下调 HWM

或

SQL > ALTER TABLE table_name SHRINK SPACE COMPACT; -- 只压缩数据不下调 HWM

SQL > ALTER TABLE table_name SHRINK SPACE;         -- 下调 HWM

即该技术可以一次性压缩数据及下调 HWM，也可以分两个阶段进行，第一阶段：在业务高峰，只压缩数据不下调 HWM，第二阶段：在业务空闲时，下调 HWM。

（5.2）MOVE Tablespace

语法为：

SQL > ALTER TABLE table_name MOVE TABLESPACE tablepsace_name;

需要注意：

–MOVE 后不跟参数也行，不跟参数还是原来的表空间；

–MOVE 后需要重建索引；

– 如果以后还要网表里插入数据，没必要 MOVE。MOVE 释放出来的空间，只能这个表使用，其它的表或者 segment 无法使用。

（5.3）CTAS 技术

即重建表技术。

SQL > CREATE TABLE new_table_name 
AS
SELECT * FROM old_table_name;                          -- 将表的数据写入到一张新的表里 

SQL > DROP TABLE old_table_name;                       -- 删除旧表 

SQL > RENAME table new_table_name TO old_table_name    -- 将新表名更改为旧表名

（5.4）EXP/IMP 或 EXPDP/IMPDP 技术

与 CTAS 技术相当。

（5.5）DEALLOCATE 技术

利用 DEALLOCATE 技术可以回收 HWM 以上从未使用过的数据块。语法如下

SQL > ALTER TABLE table_name DEALLOCATE UNUSED [KEEP integer];         -- 回收表段 HWM 以上的空间 

SQL > ALTER INDEX index_name DEALLOCATE UNUSED [KEEP integer];         -- 回收索引段 HWM 以上的空间

（六）MOVE 与 SHRINK 的区别

	MOVE	SHRINK(仅对 ASSM 有效)
本质	move 实际上是 block 级别的数据块拷贝，对表进行 move 后，该表所在 blockid 会发生改变数据的 rowid 自然也会发生改变，但是数据在 table 中的存储顺序并没有发生改变	shrink 是对行数据进行移动。对表进行 shrink 后，部分行数据的 rowid 发生了变化，而 table 所位于的 block 区域的位置却没有发生变化。
重建索引	需要重建	可以通过 cascade 关键字重建
锁	TM(exclusive)	TM(SX)
空间要求	需要有原表大的空闲空间	不需要额外的空间
效果	压缩后会回收空间	压缩后会回收空间

详细例子见下面测试。

(七) 高水位问题测试

（8.1）测试目的：

1. 了解 Oracle 统计信息的概念；

2. 测试使用 delete 与 truncate 删除数据对 HWM 的影响 [主要目的]

（8.2）主要步骤

（1）创建表 test01

create table test01
(id number,
  name varchar(15)
);

这个时候，去查看表与段的参数

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                  0            0
 
SQL> 
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
 
SQL> exec dbms_stats.gather_table_stats('LIJIAMAN','TEST01');
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                  0            0
 
SQL> 
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE     HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- -------------    ----------- ------------ ----------     ---------- ----------

此时由于未分配数据块，也就不存在高水位线的问题。

（2）我们往 test01 里面插入 10000 条数据

SQL> declare
  2    i number :=1;
  3  begin
  4    loop
  5      if i > 10000
  6      then
  7        exit;
  8      end if;
  9      insert into test01 values(i,'euvcg');
 10      i:=i+1;
 11    end loop;
 12    commit;
 13  end;
 14  /
 
PL/SQL procedure successfully completed

再去看一下表与段的统计信息

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME  BLOCKS     EMPTY_BLOCKS
----------- ---------- ------------
TEST01      0            0
 
SQL> 
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------ ------------------ ----------- ------------ ---------- ---------- ----------
TEST01       TABLE                        6          162     262144         32          4

表 test01 的 blocks 依然为 0，我们使用 dbms_stats 重新收集统计信息，

SQL> exec dbms_stats.gather_table_stats(‘LIJIAMAN’,’TEST01′);

PL/SQL procedure successfully completed

SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            0

SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------ ------------------ ----------- ------------ ---------- ---------- ----------
TEST01       TABLE                        6          162     262144         32          4

dba_tables.blocks: 该表已经使用的数据块的数量（Number of used data blocks in the table）；

dba_segments.blocks：该段中数据块的总数（Size, in Oracle blocks, of the segment）。

也就是说还有 4 个数据块还未使用。

可以使用 ANALYZE 进行统计

SQL> analyze table TEST01 compute statistics;
 
Table analyzed
 
SQL> 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            4

经过分析，dba_tables 统计的数据块与 dba_segments 统计的数据块数量相同了。

此时的高水位线应该如下：

Oracle 高水位标记（HWM）

SQL> delete from test01;
 
10000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> analyze table test01 compute statistics;
 
Table analyzed
 
SQL> 
SQL> select
  2    dt.table_name,
  3    dt.blocks,
  4    dt.empty_blocks
  5  from
  6    dba_tables dt
  7  where
  8    dt.table_name = 'TEST01';
 
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST01                                 28            4
SQL> select
  2    ds.segment_name,
  3    ds.segment_type,
  4    ds.header_file,
  5    ds.header_block,
  6    ds.bytes,
  7    ds.blocks,
  8    ds.extents
  9  from
 10    dba_segments ds
 11  where
 12    ds.segment_name = 'TEST01';
 
SEGMENT_NAME  SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEST01        TABLE                        6          162     262144         32          4