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

小鱼儿 2022-12-17 10:59 230阅读 0赞

数据库中表不断的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;

![97f40db3efe8636a6581cab157d9f44e.png][]

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

查看表使用的表空间

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')

![6ad22f6fd35b25acee5d752c21e99266.png][]

查看表空间使用情况：

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;

![f9bb3c6b19bebc27d4af712823f623d9.png][]

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

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

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(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)\*&v\_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;

块大小是16K的。16384

![cfe7f0d23a2cf7b4b71a68fcc5f9b23b.png][]

后三列表示当前占用大小，能够收缩大小,最小的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 disable ROW MOVEMENT;--关闭行移动

2)    Resize 数据文件

select  ' alter database datafile '''||file\_name ||''' resize 43g;' from dba\_data\_files where tablespace\_name

in (

'SMART\_NRRPSTA01'

);

![10fd8bf3f83db0258a401d682a4a277b.png][]

![1a609e0e641323567acd1563c9fbfd7e.png][]

收缩前表空间使用如下：

![b582bea561564c09bca9906391a40170.png][]

收缩后表空间使用如下：

![e6a9c7aa583f9a1273de6c4ac7939e57.png][]

磁盘组使用如下：

![e3610696abcd3d46fcc3685791074d0c.png][]

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

[97f40db3efe8636a6581cab157d9f44e.png]: /images/20221122/900a9a0d3d5e4a869107287aa6c97dfc.png
[6ad22f6fd35b25acee5d752c21e99266.png]: /images/20221122/b2ec648be8a94e228a82877460d5c312.png
[f9bb3c6b19bebc27d4af712823f623d9.png]: /images/20221122/e2327fd34cd4464489c0e9bd166819a1.png
[cfe7f0d23a2cf7b4b71a68fcc5f9b23b.png]: /images/20221122/012813d48ee64542a00c89d90c4c5476.png
[10fd8bf3f83db0258a401d682a4a277b.png]: /images/20221122/2899e100469c45929558d36663586cf4.png
[1a609e0e641323567acd1563c9fbfd7e.png]: /images/20221122/ecd5cc4a25b54951adb12ba2341c87f7.png
[b582bea561564c09bca9906391a40170.png]: /images/20221122/c508fcc6d55f497ca29926c41a001054.png
[e6a9c7aa583f9a1273de6c4ac7939e57.png]: /images/20221122/2ec48363d17e40d6bdadfe140c9f4ab5.png
[e3610696abcd3d46fcc3685791074d0c.png]: /images/20221122/a80ad49a7f494e9ca4b190ef12828517.png