Oracel高水位线 HWM

DBA随笔记 2024-12-21

1.1 概述

高水位线(high-water mark，HWM
) 在数据库中，如果把表想象成从左到右依次排开的一系列块，高水位线就是曾经包含了数据的最右边的块，高水位就是用来标明我以往使用过的块数的最高位。原则上HWM
只会增大，即使将表中的数据全部删除，HWM
也不会降低。

HWM
不是好事，查询时要扫描表的块，不是扫描表分配了多少块，而是高水位线以下的所有数据块。全表扫描时通常要读出HWM
以下的所有数据块(尽管该表中可能仅有少量数据)，这将占用大量IO
资源.

1.2 降低 HWM 的方法

1.2.1 Rebuild

复制要保留的数据到临时表 t，drop
原表，然后rename
临时表 t 为原表。

1.2.2 Exp Imp

1.2.3 Move

将表从一个 TBS 移动到另一个 TBS (也可以在本 TBS 内move
)，可以清除表里的碎片。

# 语法: 
alter table t1 move [tablespace users];   
# move后不跟TBS说明还是原来的空间 


优点：可以清除数据块中的碎片，降低高水位线。
缺点：move需要额外 (一倍) 的空间 


move过程中会锁表，其他用户不能在该表上做DML或DDL操作。
move之后，相关索引都不可用了，表上的索引需要重建。

1.2.4 Truncate

采用TRUNCATE
语句删除一个表的数据的时候，类似于重新建立了表，不仅把数据都删除了，还把HWM
给清空恢复为 0。所以如果需要把表清空，在有可能利用TRUNCATE
语句来删除数据的时候就利用TRUNCATE
语句来删除表，特别是那种数据量有可能很大的临时存储表。

1.2.4.1 创建表

scott:
SQL> drop table t purge;
SQL> create table t(id number) SEGMENT CREATION IMMEDIATE;
-- 11gR2的新特性，默认延迟段创建，所以immediate，立即分配段
-- 此时表没有分析，是原始的数据，即8个数据块(分配最小存储空间一个区， 初始大小是64K，一个标准块的大小是8K，刚好是8个BLOCK)
-- sys查看dba表
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T';
SEGMENT_NAME	SEGMENT_TYPE	BLOCKS
------------	------------	---------
T				TABLE			8
-- scott查看user表
SQL> col table_name format a10
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='T';
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
-----------------	----------------	----------	------------------
T

1.2.4.2 插入数据

SQL>
declare
i number;
begin
for i in 1..10000 loop
insert into t values(i);
end loop;
commit;
end;
 /


PL/SQL procedure successfully completed.

1.2.4.3 再次查看表的信息

-- sys查看dba表
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T';
SEGMENT_NAME	SEGMENT_TYPE	BLOCKS
------------	------------	------
T		TABLE		24
-- scott查看user表
SQL> col table_name format a10
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='T';
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
-----------------	----------------	----------	------------------
T
-- 此时表T 占用的数据库已经是24个了. 但是user_tables 显示的信息还是为空。因为没有做统计分析。

1.2.4.4 使用包收集统计信息

-- scott
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('scott','T');
PL/SQL procedure successfully completed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='T';
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
---------	--------	------	-------------
T		10000		20		0

此时user_tables
已经有了数据，显示的使用了 20 个数据块。但是empty_blocks
还是为空。这里要注意的地方。这个字段只有使用analyze
收集统计信息之后才会有数据。

1.2.4.5 analyze 收集统计信息

SQL> ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='T';
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
----------	--------	-------	------------
T		10000		20		4

1.2.4.6 delete 数据不会降低 HWM

SQL> delete from t;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T';
SEGMENT_NAME	SEGMENT_TYPE	BLOCKS
------------	------------	-------
T				TABLE			24
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='T';
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
----------	--------	-------	------------
T			10000		20		4

1.2.4.7 truncate 表可以降低 HWM

SQL> truncate table t;
Table truncated.
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T'; -- 已经改变
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
----------	--------	-------	------------
T				TABLE		8
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T';	-- 需要收集统计信息
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
----------	--------	-------	------------
T		10000		20		4
DBMS_STATS包收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('scott','T');
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T';	-- 需要analyze收集统计信息
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
----------	--------	-------	------------
T		10000		0		4
SQL> ANALYZE TABLE T COMPUTE STATISTICS;
SQL> SELECT segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='T';
TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS
----------	--------	-------	------------
T		10000		0		8

1.2.5 Shrink

收缩表，也叫段重组
：表收缩的底层实现的是通过匹配的INSERT
和DELETE
操作。它分两个不同的阶段：压缩阶段和 DDL 命令阶段。

-- 语法:
alter table t2 shrink space [cascade][compact];
alter table <table_name> shrink space [ <null> | compact | cascade ];

两个前提：1.表的 TBS 段管理是 ASSM 方式，因为位图管理方法才记录有关块实际的满度信息. 2.表上启用了行迁移 row movement。

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

测试：

1.2.5.1 建立表和表空间

-- sys:
SQL> create tablespace klaus datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' size 100m;
SQL> create table scott.t2 tablespace klaus as select * from dba_objects;

1.2.5.2 查看表信息

-- scott:
SQL> select max(rownum) from t2;
MAX(ROWNUM)
--------------------
91923
SQL> select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2'; 
TABLE_NAME	BLOCKS	EMPTY_BLOCKS	NUM_ROWS
---------	------	------------	---------
T2		1578		0	91923

1.2.5.3 analyze 收集统计信息

SQL> analyze table t2 compute statistics;

1.2.5.4 删除数据

SQL> delete t2 where rownum<=40000;
SQL> commit;

1.2.5.5 再次 analyze 收集统计信息

SQL> analyze table t2 compute statistics for table;
SQL> select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2'; 
TABLE_NAME	BLOCKS	EMPTY_BLOCKS	NUM_ROWS
---------	------	------------	---------
T2			1578		0			51923
-- 这时候，num_rows已经减掉了40000条， 但 blocks 并没有减少， 说明HWM没有下降.

1.2.5.6 Shrink 前提-使能行移动

SQL> alter table t2 enable row movement;

1.2.5.7 shrink 第一步-压缩阶段 -HWM 不会降低

SQL> alter table t2 shrink space compact; 
SQL> analyze table t2 compute statistics for table;
SQL> select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2'; 
TABLE_NAME	BLOCKS	EMPTY_BLOCKS	NUM_ROWS
---------	------	------------	---------
T2		1578		0	51923

1.2.5.8 shrink 第二步-DDL命令阶段 -HWM 降低

SQL> alter table t2 shrink space; 
SQL> analyze table t2 compute statistics for table;
SQL> select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2';
TABLE_NAME	BLOCKS	EMPTY_BLOCKS	NUM_ROWS
---------	------	------------	---------
T2		888		0	51923
-- HWM已经降低.

1.3 NOTE

1). 表收缩操作生成undo和redo数据，索引可以得到维护。

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

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

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

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

6). 使用位图管理块，不能收缩MSSM管理，或有LONG列表或是有refresh_on_commit物化视图的表。

文章转载自DBA随笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。