ORACLE 11g 生产中高水位线(HWM)处理

数据库中表不断的insert，delete，update，导致表和索引出现碎片。这会导致HWM之前有很多的空闲空间，而oracle在做全表扫描的时候会读取HWM一下的所有块，这样会产生更多的IO，影响性能。

oracle提供了shrink space碎片整理的功能，对于索引要采取rebuild online的方式进行碎片整理。

高水位的管理机制

高水位的管理机制在 MSSM 和 ASSM 中不同，在以往的手动段空间管理中（MSSM），高水位标记 HWM，一个段分成三部分，header block，used block(row data)，unusedblock，其中 used block 和 unused block 之间的分界线就是高水位标记 HWM，当进行全表扫描的时候，会扫描到 HWM 下的所有数据块，即使 used block 中很多数据被删除了，全表扫描还是以HWM为准。

在自动段管理（ASSM）中，利用位图来代替空闲列表，当会话向表插入数据时，数据库只格式一个单独的位图块，而不是像 MSSM 中那样，会预先格式化一组块。在ASSM表空间中，除了一个 HWM 外，还有一个低 HWM。在 MSSM 中，HWM 推进时，所有的块都会格式化并立即生效，这样 Oracle 就可以安全的读取这些块。但是对于 ASSM，当 HWM推进时，Oracle 并不会立即格式所有的块，只是在第一次使用的时候才会对这些块进行格式化。也就是说，在第一次使用的的时候，即进行 insert 操作时，数据会插入到块中的任意水位线，位于低水位线（LHMW）和高水位线（HHMW）之间。因此在这个区域的许多块就不会被格式化。

背景

生产中这S_OPERATELOG, S_T_RTNRP_STATUS, S_T_SEND_REPORT三张表实际使用量不大（即truncate分区后），表空间数据文件还是占用很高，几个T，现需要释放不用的空间。

生产中遇到的案例一：

以下是生产中的三张表，且都是按天生成的分区表（不足一个月的数据，一年数据量很大）

select segment_name,round(sum(bytes / 1024 / 1024 / 1024), 2) G

from user_segments

where segment_name in

('S_OPERATELOG', 'S_T_RTNRP_STATUS', 'S_T_SEND_REPORT') group by segment_name;

可以看出使用的并不是很大。

查看表使用的表空间

select owner, table_name, tablespace_name
  from dba_tables
 where owner = 'SMART'
   AND TABLE_NAME in
       ('S_OPERATELOG', 'S_T_RTNRP_STATUS', 'S_T_SEND_REPORT')
UNION
select TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME
  from dba_Tab_Partitions
 where TABLE_OWNER = 'SMART'
   AND TABLE_NAME in
       ('S_OPERATELOG', 'S_T_RTNRP_STATUS', 'S_T_SEND_REPORT');

查看表空间使用情况：

select tablespace_name,

ROUND(sum(bytes / 1024 / 1024 / 1024),2) G，ROUND(sum(maxbytes / 1024 / 1024 / 1024),2) max_G

from dba_data_files

where file_name like '+FDATADG%'

GROUP BY TABLESPACE_NAME ORDER BY 2 DESC;

可以看出几个表空间占用空间很大，尤其是前三个表空间。

查看表空间数据文件情况：

SELECT a.tablespace_name,
       'alter database datafile ''' || a.file_name || ''' resize ' ||
       round(ceil(b.resize_to / 1024 / 1024 / 1024), 2) || 'G;' AS "resize_SQL",
       round(a.bytes / 1024 / 1024 / 1024, 2) AS "current_bytes(GB)",
       round(a.bytes / 1024 / 1024 / 1024 -
             b.resize_to / 1024 / 1024 / 1024,
             2) AS "shrink_by_bytes(GB)",
       round(ceil(b.resize_to / 1024 / 1024 / 1024), 2) AS "resize_to_bytes(GB)"

  FROM dba_data_files a,
       (SELECT file_id,
               MAX((block_id + blocks - 1) *
                   (select value
                      from v$parameter
                     where name = 'db_block_size')) AS resize_to
          FROM dba_extents
         GROUP by file_id) b
 WHERE a.file_id = b.file_id
 ORDER BY a.tablespace_name, a.file_name;

SELECT a.tablespace_name,
       a.file_name,
       round(a.bytes / 1024 / 1024 / 1024, 2) AS "current_bytes(GB)",
       round(a.bytes / 1024 / 1024 / 1024 -
             b.resize_to / 1024 / 1024 / 1024,
             2) AS "shrink_by_bytes(GB)",
       round(ceil(b.resize_to / 1024 / 1024 / 1024), 2) AS "resize_to_bytes(GB)"
  FROM dba_data_files a,
       (SELECT file_id,
               MAX((block_id + blocks - 1) *
                   (select value
                      from v$parameter
                     where name = 'db_block_size')) AS resize_to
          FROM dba_extents
         GROUP by file_id) b
 WHERE a.file_id = b.file_id   
   and a.TABLESPACE_NAME in ('SMART_OPLOG01',
                             'SMART_NRRPSTA01',
                             'SMART_NRRPSTA02',
                             'SMART_NSNRP01',
                             'SMART_NSNRP02')
 ORDER BY a.tablespace_name, a.file_name;

后三列表示当前占用大小，能够收缩大小,最小的resize大小。

处理办法：

1) Shrink对应的表

注意：由于在线上，不能进行有表锁的操作，所以我并没有采用这种办法

alter table TABLE_NAME enable ROW MOVEMENT;--启动行移动功能

alter table TABLE_NAME shrink space compact; --只整理碎片不回收空间，业务繁忙的时候执行，等待时间会很久

-- 重置高水位，此时不能有DML操作

--alter table TABLE_NAME shrink space; --整理碎片并回收空间，并调整水位线，业务少时执行

ALTER TABLE TABLE_NAME SHRINK SPACE CASCADE; --收缩表，降低高水位线，并且相关索引也要收缩一下

alter table TABLE_NAME disable ROW MOVEMENT;--关闭行移动

2) Resize 数据文件

select ' alter database datafile '''||file_name ||''' resize 43g;' from dba_data_files where tablespace_name

in (

'SMART_NRRPSTA01'

);

收缩前表空间使用如下：

收缩后表空间使用如下：

磁盘组使用如下：

原+FDATADG磁盘中使用达到96%,释放了20%的空间。

案例二（move 与shrink space区别和注意事项）

都知道alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但move跟shrink space还是有区别的。
Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
而shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。

下面看看实验：

move 实验：

create table test1( empno number primary key,ename varchar2(200)) storage (initial 1m next 2m pctincrease 0) pctfree 5 pctused 40;

insert into test1 SELECT rownum,dbms_random.string('a',200) from dual connect by rownum <=1000000;

commit;

SELECT
tablespace_name,
segment_name,
segment_type,
sum( bytes/1024/1024) MB ,
count(*) cnt
FROM
dba_extents
WHERE segment_name ='TEST1'
GROUP BY
tablespace_name,
segment_name,
segment_type;

delete test1 where empno>10 and empno<900000;
commit;

SELECT
tablespace_name,
segment_name,
segment_type,
sum( bytes/1024/1024) MB ,
count(*) cnt
FROM
dba_extents
WHERE segment_name ='TEST1'
GROUP BY
tablespace_name,
segment_name,
segment_type;

现在进行move 操作：

select b.TABLE_NAME,b.TABLESPACE_NAME from user_tables b where b.TABLE_NAME='TEST1';

select 'alter index '||A.INDEX_NAME||' rebuild online;',A.status from user_indexes a where a.TABLE_NAME='TEST1';

alter table test1 move tablespace users;

SELECT
tablespace_name,
segment_name,
segment_type,
sum( bytes/1024/1024) MB ,
count(*) cnt
FROM
dba_extents
WHERE segment_name ='TEST1'
GROUP BY
tablespace_name,
segment_name,
segment_type;

select 'alter index '||A.INDEX_NAME||' rebuild online;',A.status from user_indexes a where a.TABLE_NAME='TEST1';

alter index SYS_C008190 rebuild online;

可以看出move test1表操作后，索引失效（需要重建），move操作可以收缩段，消除空间碎片（delete 操作），使数据更紧密，分配的表段和dba_extents减少；

shirnk 实验：

create table test2( empno number primary key,ename varchar2(200)) storage (initial 1m next 2m pctincrease 0) pctfree 5 pctused 40;

insert into test2 SELECT rownum,dbms_random.string('a',200) from dual connect by rownum <=1000000;

commit;

SELECT
tablespace_name,
segment_name,
segment_type,
sum( bytes/1024/1024) MB ,
count(*) cnt
FROM
dba_extents
WHERE segment_name ='TEST2'
GROUP BY
tablespace_name,
segment_name,
segment_type;

delete test2 where empno>10 and empno<900000;

commit;

SELECT
tablespace_name,
segment_name,
segment_type,
sum( bytes/1024/1024) MB ,
count(*) cnt
FROM
dba_extents
WHERE segment_name ='TEST2'
GROUP BY
tablespace_name,
segment_name,
segment_type;

现在进行shrink操作：

alter table test2 enable ROW MOVEMENT;--启动行移动功能

alter table test2 shrink space compact; --只整理碎片不回收空间

ALTER TABLE test2 SHRINK SPACE CASCADE; --收缩表，降低高水位线，并且相关索引也要收缩一下

alter table test2 disable ROW MOVEMENT;--关闭行移动

SELECT
tablespace_name,
segment_name,
segment_type,
sum( bytes/1024/1024) MB ,
count(*) cnt
FROM
dba_extents
WHERE segment_name in ('TEST2','TEST1')
GROUP BY
tablespace_name,
segment_name,
segment_type;

可以看出shrink test2表操作后，也可以收缩段，消除空间碎片（delete 操作），使数据更紧密，分配的表段和dba_extents减少；

再次交叉操作：

TEST1表进行move操作，TEST2表进行shrink操作：

现在把test1表进行shrink,test2表进行move 在看他们的结果。

alter table test1 enable ROW MOVEMENT;--启动行移动功能

alter table test1 shrink space compact; --只整理碎片不回收空间，业务繁忙的时候

ALTER TABLE test1 SHRINK SPACE CASCADE; --收缩表，降低高水位线，并且相关索引也要收缩一下

alter table test1 disable ROW MOVEMENT;--关闭行移动

select 'alter index '||A.INDEX_NAME||' rebuild online;',A.status from user_indexes a where a.TABLE_NAME='TEST2';
alter table test2 move tablespace users;

表的结果发生了神奇的变化，哈哈，对比一下：

select 'test1_'||count(*) as total from test1
union
select 'test2_'||count(*) as total from test2;

test1是进行move后shrink操作；

test2是进行shrink后move操作；

原本move 表test1是23MB，shrink后变成22.125MB;

原本shrink表test2是22.235MB,但是move之后，增加到23MB；

他们的表结构以及记录数一样，插入是随机的值。

原因：

这是由于两张表申请的空间是23M；move,shrink 都会移动高水位，从delete后的200多MB，移动到20多MB;

但是move不会释放申请的空间（shrink会释放申请的空间），所以tmove 表test1是23MB，shrink后变成22.125MB;

而原本shrink表test2是22.235MB,但是move之后，增加到23MB（还是到申请的空间23MB）；

结论：

Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作；
而shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。

其他注意事项说明如下：

使用move的一些注意事项：
a. table上的index需要rebuild(索引失效)：
在前面我们讨论过，move操作后，数据的rowid发生了改变，我们知道，index是通过rowid来fetch数据行的，所以，table上的index是必须要rebuild的。
alter index index_name rebuild online；

b.move时对table的锁定
当我们对table进行move操作时，查询v$locked_objects视图可以发现，table上加了exclusive lock

c.关于move时空间使用的问题：
当我们使用alter table move来降低table的HWM时，有一点是需要注意的，这时，当前的tablespace中需要有1倍于table的空闲空间以供使用

Move解决问题：
a. 我们可以使用move将一个table从当前的tablespace上移动到另一个tablespace上：
alter table t move tablespace tablespace_name;
b. 我们还可以用move来改变table已有的block的存储参数,如：
alter table t move storage (initial 30k next 50k);
c.另外，move操作也可以用来解决table中的行迁移的问题。

Shrink space语法：
alter table
<table_name>shrink space [ | compact | cascade ];
alter table <table_name>shrink space compcat;
收缩表，但会保持 high water mark;
alter table
<table_name>shrink space;
收缩表，降低 high water mark;
alter table
<table_name>shrink space cascade;
收缩表，降低 high water mark，并且相关索引也要收缩

用shrink有两个前提条件:
1、表必须启用row movement，如：
alter table table_name enable row movement;
alter table table_name shrink space;
2、表段所在表空间的段空间管理(segment space management)必须为auto

segment shrink分为两个阶段：
1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
注意：shrink space语句两个阶段都执行。
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

生产中遇到的案例三（表空间碎片）

posted @ 2018-12-17 16:38 翰墨文海阅读(3333) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

翰墨文海

QV1319820057,转载望注明出处……

ORACLE 11g 生产中高水位线(HWM)处理

高水位的管理机制

背景

生产中遇到的案例一：

处理办法：

1) Shrink对应的表

2) Resize 数据文件

案例二（move 与shrink space区别和注意事项）

公告

翰墨文海

QV1319820057,转载望注明出处……

ORACLE 11g 生产中高水位线(HWM)处理

高水位的管理机制

背景

生产中遇到的案例一：

处理办法：

1) Shrink对应的表

2) Resize 数据文件

案例二 （move 与shrink space区别和注意事项）

公告

案例二（move 与shrink space区别和注意事项）