Oracle HWM分析及处理

#1. oracle HWM原因

HWM，就是我们常说的高水位，简单来讲，由于数据的变动（主要是delete），会产生碎片，而oracle insert数据的时候是不会利用之前delete的空间，产生的问题：

• 浪费了存储空间；
• 读取的时候也包含了那些已经删除的内容，增加了I/O；

简单示例，2张一样的表，一张重复insert,delete，虽然最后记录一致，但做计划分析后，consistent gets 差了很多，再次验证了对I/O负担增加这个现象。

#2. HWM处理方案

#2.1 查看表空间总体使用情况

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 tablespace_name='USERS'
GROUP BY TABLESPACE_NAME ORDER BY 2 DESC;

#2.2 表空间可以压缩的容量

查看具体情况（精确）

select
    a.tablespace_name,
    a.file_id,
    ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "smallest(mb) - hwm",
    ceil(blocks * c.value / 1024 / 1024) "currsize(mb)",
    ceil(blocks * c.value / 1024 / 1024) - ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "savings(mb)"
from dba_data_files a,
    (select file_id, max(block_id + blocks - 1) hwm from dba_extents group by file_id) b,
    (select value from v$parameter  where name = 'db_block_size') c
where a.file_id = b.file_id(+)
and a.status <> 'INVALID'
and a.tablespace_name in ('USERS')
order by 2;

#2.3 粗略查看下表空间

SELECT a.tablespace_name,
       a.file_name,a.file_id,
       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 ('USERS')
 ORDER BY a.tablespace_name, a.file_name;

#2.3 对空间回收处理（建议在业务空闲时操作）

alter database datafile 4 resize 24920M;

#2.4 最后查看空间情况

select
    a.tablespace_name,
    a.file_id,
    ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "smallest(mb) - hwm",
    ceil(blocks * c.value / 1024 / 1024) "currsize(mb)",
    ceil(blocks * c.value / 1024 / 1024) - ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "savings(mb)"
from dba_data_files a,
    (select file_id, max(block_id + blocks - 1) hwm from dba_extents group by file_id) b,
    (select value from v$parameter  where name = 'db_block_size') c
where a.file_id = b.file_id(+)
and a.status <> 'INVALID'
and a.tablespace_name in ('USERS')
order by 2;

#3. shrink处理方式

我个人不推荐用shrink处理方式，会锁表，而且同时要更改row movement和index，比较繁琐； 这里就简单介绍下

#3.1 先开行移动功能

alter table tt enable row movement;

#3.2 碎片整理

--收缩表，降低高水无线，相关索引也一起收缩(建议采用)
alter table tt shrink space cascade;

--关闭行移动
alter table tt disable row movment;

--下面是参考命令
--只整理碎片，不回收空间，在闲时操作
alter table tt shrink space compace;

--整理碎片并回收空间，并调整水位线，闲时操作
alter table tt shrink space;

#4. move 和 shrink的区别

示例命令：

ater table tt move tablespace users;

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