Oracle表碎片查询以及整理（高水位线）

超、凢脫俗 2022-06-08 09:27 430阅读 0赞

1.表碎片的来源  
  
当针对一个表的删除操作很多时，表会产生大量碎片。删除操作释放的空间不会被插入操作立即重用，甚至永远也不会被重用。  
  
2.怎样确定是否有表碎片  
  
收集表统计信息  
SQL> exec dbms\_stats.gather\_table\_stats(ownname=>'SCHEMA\_NAME',tabname=> 'TABLE\_NAME');  
  
\-- 确定碎片程度  
  
/\* Formatted on 2017/9/21 14:14:37 (QP5 v5.240.12305.39476) \*/  
  SELECT table\_name,  
         ROUND ( (blocks \* 8), 2) "高水位空间 k",  
         ROUND ( (num\_rows \* avg\_row\_len / 1024), 2) "真实使用空间 k",  
         ROUND ( (blocks \* 10 / 100) \* 8, 2) "预留空间(pctfree) k",  
         ROUND (  
            (  blocks \* 8  
             - (num\_rows \* avg\_row\_len / 1024)  
             - blocks \* 8 \* 10 / 100),  
            2)  
            "浪费空间 k"  
    FROM user\_tables  
   WHERE temporary = 'N'  
ORDER BY 5 DESC;  
  
查询部分结果如下：  
TABLE\_NAME                 高水位空间 k     真实使用空间    预留空间(pctfree) k  浪费空间 k  
\----------------------- ----------     -------         ------------       ----------  
SYSLOG                           1377160     1161895.17       137716           77548.83  
KSOARETMXLS               992648      831316.03       99264.8          62067.17  
GOODACCTDT               494888      410832.89       49488.8           34566.31  
CONTACTDOC               186856      135784.86       18685.6              32385.54  
INVNTRYEXECDT                32216       570.9              3221.6               28423.5  
INVNTRYAPPDT                21128        958.87               2112.8               18056.33  
RETBILLMT               301120      254871.05       30112                16136.95  
LOCATACCTDT               301096      255276.74       30109.6              15709.66  
FORMREVIEW               129760      101530.8       12976                15253.2  
RETGOODSDT               374560      323306.4       37456                13797.6  
DAYGOODSCOLL               137920      110779.87       13792                13348.13  
  
3.查看表上次收集统计信息时间  
  
select table\_name,last\_analyzed from dba\_tables where owner = 'SCHEMA\_NAME'  
  
4.收集整个 Schema 中对象的统计信息  
  
SQL> exec dbms\_stats.gather\_schema\_stats(ownname=>'SCHEMA\_NAME');  
  
5.为什么要整理表碎片  
  
Oracle 对数据段的管理有一个高水位(HWM, High Water Mark)的概念。高水位是数据段中使用过和未使用过的数据块的分界线。高水位以下的数据块是曾使用过的，以上的是从未被使用或初始化过的。  
  
当 Oracle 进行全表扫描(FTS, Full table scan)的操作时，它会读高水位下的所有数据块。如果高水位下还有很多空闲空间（碎片），读取这些空闲数据块会降低操作的性能。  
  
行链接和行迁移  
    行链接 Row Chaining：当插入数据量大的行的，如果一个Block不能存放一条记录，该记录的一部分会存储到同个Extent中的其他Block，这些block形成一个数据块链。  
    行迁移 Row Migration：当Update的时候导致记录长度增加了，存储的Block已经满了，就会发生行迁移。Oracle会迁移整行数据到一个能够存储下整行数据的Block中，迁移的原始指针指向新的存放行数据的        Block，ROWID不变。  
当数据行发生链接（chain）或迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据，必须访问更多的数据块（data block）。  
  
表碎片导致的问题  
    查询响应时间(尤其是全表扫描)变慢  
    产生大量行迁移  
    浪费空间  
  
6.如何整理表碎片  
  
10g之前  
  
两种方法：  
    导出表，删除表，再导入表  
    alter table move  
一般选择第二种，但是不能在线进行而且需要重建索引。  
  
10g之后：  
从 10g 开始，提供一个 shrink 命令，需要表空间是基于自动段管理的。  
可以分成两步操作：  
  
\-- 整理表，不影响DML操作

SQL> alter table TABLE\_NAME enable ROW MOVEMENT;\-\-启动行移动功能

SQL> alter table TABLE\_NAME shrink space compact;  --只整理碎片 不回收空间  
  
\-- 重置高水位，此时不能有DML操作  
  
SQL> alter table TABLE\_NAME shrink space; --整理碎片并回收空间，并调整水位线。业务少时执行

SQL> alter table TABLE\_NAME disable ROW MOVEMENT;--关闭行移动

也可以一步到位：  
alter table TABLE\_NAME shrink space;  
  
shrink 的优势：  
  
    不需要重建索引。  
    可以在线操作。  
  
不需要空闲空间，alter move需要跟当前表一样大小的空闲空间。

注意：

**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释放空闲数据块。