Oracle 19C之表的高水位线

什么是高水位线？高水位线（high-water mark， HWM）

引用官方文档中的解释

To manage space, Oracle Database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used

    在数据库中，如果把表想象成从左到右依次排开的一系列块，高水位线就是曾经包含了数据的最右边的块。原则上 HWM 只会增大, 即使将表中的数据全部 DELETE 删除，HWM 也不会降低。

    在 Oracle 数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。

    水库中的水的位置有一条线叫做水位线，在 Oracle 中，这条线被称为高水位线（Highwarter mark, HWM）。在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说 HWM 为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用 delete 语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。

验证高水位线实验

--创建目标表
create table u1.t5 as select * from dba_objects;
1、删除前，查看一下全表扫描T5所需要的块数
SYS@pdb> select count(1) from u1.t5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1231860717
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T5   | 10000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
SYS@pdb>  delete from u1.t5;
10000 rows deleted.

SYS@pdb> commit;
Commit complete.

3、再次查看执行计划，仍然扫描和删除前一样的blocks
SYS@pdb> set autot trace
SYS@pdb> select count(1) from u1.t5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1231860717
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T5   | 10000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
4、查看T5表区的信息
SYS@pdb> select segment_name,file_id,extent_id,blocks,bytes from dba_extents where segment_name='T5';
SEGMENT_NAME                      FILE_ID  EXTENT_ID     BLOCKS      BYTES
------------------------------ ---------- ---------- ---------- ----------
T5                                     12          0          8      65536
T5                                     12          1          8      65536
T5                                     12          2          8      65536

  Select 语句会对表中的数据进行一次扫描，但是究竟扫描多少数据存储块呢，这个并不是说数据库中有多少数据， Oracle 就扫描这么大的数据块，而是 Oracle 会扫描高水位线以下的数据块。现在来想象一下，如果刚才是一张刚刚建立的空表，你进行了一次 Select操作，那么由于高水位线 HWM 在最低的 0 位置上，所以没有数据块需要被扫描，扫描时间会极短。而如果这个时候你首先插入了一千万条数据，然后再用 delete 语句删除这一千万条数据。由于插入了一千万条数据，所以这个时候的高水位线就在一千万条数据这里。

    后来删除这一千万条数据的时候，由于 delete 语句不影响高水位线，所以高水位线依然在一千万条数据这里。这个时候再一次用 select 语句进行扫描，虽然这个时候表中没有数据，但是由于扫描是按照高水位线来的，所以需要把一千万条数据的存储空间都要扫描一次，也就是说这次扫描所需要的时间和扫描一千万条数据所需要的时间是一样多的。所以有时候有人总是经常说，怎么我的表中没有几条数据，但是还是这么慢呢，这个时候其实奥秘就是这里的高水位线了。

    HWM 不是好事，使用全表扫描时通常要读出 HWM 以下的所有数据块（尽管该表中可能仅有少量数据），这将白白耗费大量 IO 资源。

两个解决办法可降低 HWM：

一个是移动表， move 方法, 将表从一个表空间移动到另一个表空间（也可以在本表空间内 move)。

语法： alter table tbsa move [tablespace users];

优点：可以清除数据块中的碎片,降低高水位线。

缺点： move 需要额外（一倍）的空间。

move 过程中会锁表，其他用户不能在该表上做 DML 或 DDL 操作。

move 之后，相关索引都不可用了，表上的索引需要重建。

alter table u1.t5 move tablespace users;

一个是收缩表， shrink 也叫段重组，表收缩的底层实现的是通过匹配的 INSERT 和DELETE 操作。

它分两个不同的阶段：压缩阶段和降低 HWM 阶段。

语法： alter table tbsa shrink space [cascade][compact];

两个前提：表所在的表空间段管理是 ASSM 方式,因为位图方法才记录有关块实际的满度信息； 表上启用了 row movement。

你发出 alter table tbsa shrink space compact; 那么只完成了第一阶段。这是压缩阶段。在业务高峰时可以先完成第一阶段， 高峰过后，再次 alter table tbsa shrink space， 因压缩阶段工作大部分已完成，将很快进入第二阶段， DML 操作会有短暂的锁等待发生。

测试：

conn sys/oracle@pdb2 as sysdba

drop tablespace tbs_c including datafiles and contents;

create tablespace tbs_c datafile '/u01/app/oracle/oradata/PROD1/tbs_c01.dbf'  size 50M;

drop table tbs_c;

create table tbs_c tablespace tbs_c as select * from dba_objects;

select max(rownum) from tbs_c;

--收集表的统计信息
analyze table tbs_c compute statistics for table;

col table_name for a30

select table_name,blocks, num_rows from user_tables where table_name='TBS_C';

delete from tbs_c;

analyze table tbs_c compute statistics for table;

select table_name,blocks, num_rows from user_tables where table_name='TBS_C';

实验看出 num_rows 已经变为 0 条，但 blocks 并没有减少，说明 HWM 没有下降。

开始收缩表
1)首先启用行移动功能
alter table tbs_c enable row movement;
然后开始收缩表第一阶段-压缩：压缩不会是 HWM 降低
 alter table tbs_c shrink space compact;
analyze table tbs_c compute statistics for table;
select table_name,blocks, num_rows from user_tables where table_name='TBS_C';
2)接着进行收缩表第二阶段-降低 HWM 阶段：
alter table tbs_c shrink space;
analyze table tbs_c compute statistics for table;
select table_name,blocks, num_rows from user_tables where table_name='TBS_C';

注意：

收缩分为两个阶段 第一阶段是压缩阶段，第二阶段是降低 HWM 阶段。 SHRINK 不占用额外的空间。

可以单独完成第一阶段，即 SHRINK SPACE COMPACT 此阶段不降低 HWM，DML 操作几乎不受影响。

可以级联相关的段一起收缩，即 SHRINK SPACE CASCADE。

段必须 ASSM 管理方式，且使能行移动，否则不能收缩，如果不满足这两个前提，MOVE 就是重组表的唯一方式。

不能收缩 MSSM 管理，或有 LONG 列表或是有 refresh_on_commit 物化视图的表。