oracle碎片整理

Oracle一体机用户组 2019-12-17

2531

概述

随着时间推移，数据库产生的碎片会越来越多，将对数据库有以下两点主要影响：一方面是系统性能减弱；另一方面造成大量空间的浪费。通过对碎片的整理，可以减少数据库服务器的IO读写次数、降低内存空间的使用率、加快表的访问速度。

状态描述

某客户营销系统日常运行中存在大量的delete与update操作，难免产生大量的碎片，对系统性能产生一定的影响。尤其是大表并且执行全表扫描的情况下，对系统的影响更大，此次优化主要针对这些对象进行处理。

实施过程

实施概述

由于该系统无法安排停机时间，无法使用导入导出方式进行整理，通过shrink方式进行碎片整理会分数据重组、HWM调整两个阶段进行，由于期间会产生锁，故该操作需要在系统较为空闲的时候进行，按步骤执行即可。

具体实施步骤

确定碎片情况

确定全表扫描的表和碎片情况：

CLEAR COLUMNS BREAKS COMPUTES  
COLUMN segment_owner         FORMAT   a12                HEADING 'Owner'           ENTMAP off
COLUMN OBJECT_NAME           FORMAT   a30                HEADING 'Name'            ENTMAP off
COLUMN OBJECT_TYPE           FORMAT   a24                HEADING 'Type'            ENTMAP off
COLUMN total_mb              FORMAT   999,999            HEADING 'TotalMB'         ENTMAP off
COLUMN free_mb               FORMAT   999,999            HEADING 'FreeMB'          ENTMAP off
COLUMN fragrate              FORMAT   999.99             HEADING 'Fragrate'        ENTMAP off
BREAK ON report
 
select distinct OBJECT_OWNER,
                OBJECT_NAME,
                OBJECT_TYPE,
                COST,
                IO_COST,
                total_mb,
                free_mb,
                fragrate,
                SQL_ID
  from v$sql_plan s,
       (select SEGMENT_OWNER,
               segment_type,
               segment_name,
               sum(total_bytes  1048576) total_mb,
               sum(FS1_BYTES + FS2_BYTES + FS3_BYTES + FS4_BYTES)  1048576 free_mb,
               sum(FS1_BYTES + FS2_BYTES + FS3_BYTES + FS4_BYTES) 
               sum(total_bytes) * 100 fragrate
          from sys.t_space_usage
         where segment_name not like 'BIN$%'
         group by SEGMENT_OWNER, segment_type, segment_name) t
 where operation = 'TABLE ACCESS'
   and options = 'FULL'
   and OBJECT_OWNER not like '%SYS%'
   and s.object_owner = t.segment_owner
   and s.object_name = t.segment_name
 order by io_cost;

从中找出io开销、碎片率较高且表较大的记录。

进行碎片整理

检查操作系统版本

确定表空间是否支持自动段空间管理

SQL> select tablespace_name,segment_space_management from dba_tablespaces;

开启行迁移

alter table table_name enable row movement;

注：table_name为需要整理的表的名称

disable基于rowid的trigger

SQL>select trigger_owner,trigger_name,table_owner,table_name,column_name from dba_trigger_cols where column_name=’ROWID’;
alter trigger trigger _name disable;

开始数据重组（表和索引重组）

alter table tablename shrink space compact cascade;

HWM调整

alter table tablename shrink space cascade;

重新rebuild索引

alter index index_name rebuild online;

重新启用触发器

alter trigger trigger_name enable;

回退步骤

回退步骤

由于是ddl操作，不具有回退性。

原创文章，版权归本文作者所有，如需转载请注明出处

喜欢本文请长按下方的二维码订阅Oracle一体机用户组

oracle

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

oracle碎片整理

评论