Oracle 碎片整理

DBA随笔记 2024-08-30

839

1. 碎片评估方法

1.1 表空间碎片评估方法

FSFI(Free Space Fragmentation Index)值：

SELECT a.Tablespace_name,
       SQRT(MAX(a.blocks)  SUM(a.blocks)) *
       (100  SQRT(SQRT(COUNT(a.Blocks)))) FSFI
  FROM dba_free_space a, dba_tablespaces b
 WHERE a.Tablespace_name = b.Tablespace_name
   AND b.contents NOT IN ('TEMPORARY', 'UNDO')
 GROUP BY a.Tablespace_name
 ORDER BY FSFI;

FSFI 值〈30%，则该表空间的碎片较多。

1.2. 表碎片的评估方法

在表级， Oracle 也提供了多种方法和计算公式来评估表的碎片情况。以下介绍一种按统计信息进行评估和分析的方法：

SELECT *
  FROM (SELECT a.owner,
               a.Table_name,
               a.Num_rows,
               a.Avg_row_len * a.Num_rows,
               SUM(b.Bytes),
               (a.Avg_row_len * a.Num_rows)  SUM(b.Bytes) frag
          FROM dba_tables a, dba_segments b
         WHERE a.Table_name = b.segment_name
           AND a.owner = b.owner
           AND a.owner NOT IN ('SYS',
                               'SYSTEM',
                               'OUTLN',
                               'DMSYS',
                               'TSMSYS',
                               'DBSNMP',
                               'WMSYS',
                               'EXFSYS',
                               'CTXSYS',
                               'XDB',
                               'OLAPSYS',
                               'ORDSYS',
                               'MDSYS',
                               'SYSMAN')
         GROUP BY a.Owner, a.Table_name, a.Avg_row_len, a.Num_rows
        HAVING a.Avg_row_len * a.Num_rows  SUM(b.Bytes) < 0.7
         ORDER BY SUM(b.Bytes) DESC)
 WHERE ROWNUM <= 100;
 --上述语句将以倒排序方式，显示碎片率最高的前 100 个表.

1.3. 索引碎片的评估方法

1.3.1 按 BLEVEL 分析

SELECT id.Tablespace_name,
       id.Owner,
       id.Index_name,
       id.Blevel,
       SUM(sg.Bytes)  1024  1024,
       sg.Blocks,
       sg.Extents
  FROM dba_indexes id, dba_segments sg
 WHERE id.owner = sg.owner
   AND id.Index_name = sg.segment_name
   AND id.Tablespace_name = sg.Tablespace_name
   AND id.owner NOT IN
       ('SYS', 'SYSTEM', 'USER')
   AND sg.Extents > 100
   AND id.Blevel >= 3
 GROUP BY id.Tablespace_name,
          id.owner,
          id.Index_name,
          id.Blevel,
          sg.Blocks,
          sg.Extents
HAVING SUM(sg.Bytes)  1024  1024 > 100;
--上述语将显示索引高度 Blevel>=3，并且索引大小超过 100MB 的索引。

1.4.Automatic Segment Advisor

Oracle 10g 推出的针对空间进行自动分析工具，通过分析 AWR 数据中相关数据段的空间使用情况和增长统计信息，以及取样数据，分析数据段的可回收空间。

Automatic Segment Advisor 可提供如下具体建议。

如果发现数据数据段有大量空闲空间，并且是 ASSM 管理模式，则推荐采用 shrink技术。如果无法使用 shrink 技术，如不是 ASSM 管理模式，则推荐采用在线重定义技术。
如果发现某些表可以通过 OLTP 等压缩算法进行压缩，使得空间大大节省，该工将提供相应的建议。

iii. 如果发现一个表的 chain row 超过一定阈值(Threshold)，将相关建议记录在相关数据字典之中。

-- 查看 Automatic Segment Advisor 的 Finding 结果 
SELECT af.Task_name,
ao.Attr2 segnarne,
ao.Attr3 partition,
ao.TYPE,
af.MESSAGE
FROM dba_advisor_findings af, dba_advisor_objects ao
WHERE ao.Task_id = af.Task_id AND ao.object_id = af.object_id;
----查询可以shrink 的对象
SELECT f.Impact, O.TYPE, O.Attr1, O.Attr2, f.MESSAGE, f.More_info
  FROM dba_advisor_findings f, dba_advisor_objects o
 WHERE f.object_id = o.object_id
   AND f.Task_name = o.Task_name
   AND f.MESSAGE LIKE '%shrink%'
 ORDER BY f.Impact DESC;
 
-- Automate Segment Advisor 的 Recommendations 结果
SELECT tablespace_name,
       segment_name,
       segment_type,
       partition_name,
       recommendations,
       c1
  FROM TABLE(DBMS_SPACE.Asa_recommendations('FALSE', 'FALSE', 'FALSE'));


--上述脚本不仅告诉客户哪些表、索引存在碎片，而且还预估能回收多少空间， 甚至还将产生推荐的空间回收语句，
--例如 alter table” ST” .” ORDER” modify partition "ORDER_P2" shrink space.
--根据 Auatomatic Segment Advisor 工具的分析结果综合评估，确定需要进行碎片整理的表、索引等对象清单和实施策略。

2. 碎片整理方法

2.1 表空间级碎片整理方法

Oracle 可通过如下命令进行表空间相邻空闲空间的压缩，达到碎片整理的目的。

SQL> alter tablespace < 表空间名 > Coalesce;

2.2 表碎片整理方法

2.2.1 数据 Exp/Imp 及 DataPump

2.2.2 CTAS

SQL〉 create table〈新表名〉 as select...From〈旧表名〉 ...
SQL〉 drop table〈旧表名〉；
SQL〉 rename table〈新表名〉 to〈旧表名〉；

2.2.3 Move Tablespace

SQL> alter table < 表名 > move tablespace < 表空间名 > ;
MoveTablespace 技术更快，而且该表的索引并没有删除，但索引状态将变为 INVALID，需要重建

2.2.4 Shrink

上述传统的碎片整理技术，均需要业务停顿。
Shrink 技术可将表和索引的高水位下的碎片进行有效压缩，并将高水位进行回退。

基本语法如下：
SQL> alter table < 表名 > enable row movement;
SQL> alter table〈表名〉 shrink space cascade;--压缩表及相关数据段并下调 HWM
SQL> alter table〈表名〉 shrink space compact;--只压缩数据不下调 HWM
SQL> alter table〈表名〉 shrink space;--下调 HWM
即该技术可一次性压缩数据并下调 HWM，也可分为两个阶段进行：第一阶段，在业
务高峰时，只压缩数据但不下调 HWM。第二阶段，在业务非高峰时，再下调 HWM, 并释
放空闲空间

技术特点
该技术的特点和优势如下：
(1)可以在线进行，不影响正常应用访问。
(2)只能在 ASSM 管理的表空间上进行。该类表空间也必须是基于本地管理模式
(3)shrink 操作适合于如下数据段。
普通堆表和 IOT 表;
索引；
分区和子分区；
物化视图和物化视图日志。

(4)在对表进行 shrink 操作时，对应的索引自动进行维护。
(5)在对表进行 shrink 操作时，不会触发相应的触发器。
(6)不需要额外的空间。

2.2.5 Online Redefinition

该技术在几乎不中断业务的情况下，通过创建一个中间表，并通过内部机制，保证原表与中间表的数据同步，最后通过一个切换操作，完成表结构的重新定义。

联机在线重定义技术还具有广泛的应用空间，例如：
· 修改普通表或 Cluster 表的存储参数。
· 将普通表或 Cluster 表移动到新的表空间。
· 增加、修改和删除字段。
· 将普通表转换为分区表，或反之。
· 修改分区结构。
· 针对单个分区，修改物理属性。例如，将指定分区移动到新的表空间。
· 重建普通表或 Cluster 表，从而减少碎片。
该技术的最大特点是在不中断相关业务情况下，可联机在线进行碎片整理。

2.3 索引碎片整理

2.3.1 索引 Rebuild 和 Coalesce 技术

SQL> alter index < 索引名 > rebuild online paralle14 nologging ;
SQL> alter table < 索引名 > coalesce ;
即 Rebuild Index 可以在线、并行、不产生日志方式进行。

2.3.12 Shrink

3 总结：

针对表的碎片整理，优先考虑 Shrink 技术。
针对索引的碎片整理，优先考虑 RebuildIndex 技术。
如果 Shrink 技术实施效果不理想，或者有一定局限性，则采用 Online Redefinition技术

最佳实践经验
1.实施时间点建议
尽管 shrink 和 RebuildIndex 均可以在不中断应用的情况下在线进行，但为保险起见，如果业务允许，还是尽量避免在业务高峰时进行。
2.Shrink 技术实施建议
可考虑将 shrink 技术分两步进行言首先，压缩数据但不下调 HWM。其次，下调 HWM并释放空闲空间。
3.Rebuild Index 技术实施建议
由于 Rebuild Index 出现问题的情况基本为 ONLINE 方式，因此如果业务允许，建议Rebuild Index 以非 ONLINE 方式进行。

oracle 表空间空间数据 oracle表空间 oracle索引

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

Oracle 碎片整理

评论