Oracle数据库表碎片整理规范

概述

DBA一般都需要定期去对Oracle表碎片做整理，这里简单整理表碎片整理流程如下：

1、定位存在碎片的对象

使用如下脚本，检查需要进行碎片整理的对象：

--all tables(partition_tables + non_partition_tables )
select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,
       round(b.seg_bytes_mb, 2) seg_bytes_mb,
       decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) || '%' frag_percent
  from dba_tables a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
          from dba_segments
         group by owner, segment_name) 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')
   and decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) > 30
 order by b.seg_bytes_mb desc;

2、统计信息检查

2.1 统计信息检查

-- 查看统计信息收集日期，确保碎片查询结果准确：
select owner,table_name,last_analyzed from dba_tables Where owner='<OWNER>' AND table_name='<TABLE_NAME>';

2.2 统计信息收集

如果统计信息过旧，则重新收集统计信息：

exec dbms_stats.gather_table_stats(ownname=>'<OWNER>', tabname =>'<TABLE_NAME>');

3、表碎片整理

3.1 打开行移动

alter table <TABLE_NAME> enable row movement ;

3.2 进行表收缩

alter table <TABLE_NAME> shrink space cascade ;

3.3 失效对象编译

语句可能会造成引用表<TABLE_NAME>的对象(如存储过程、包、视图等)变为无效。
运行如下脚本，重新编译失效对象。

@?/rdbms/admin/utlrp.sql

4、对象收缩后的结果检查

运行如下脚本，确认对象空间是否已经完成收缩。

--all tables(partition_tables + non_partition_tables )
select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,
       round(b.seg_bytes_mb, 2) seg_bytes_mb,
       decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) || '%' frag_percent
  from dba_tables a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
          from dba_segments
         group by owner, segment_name) 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')
   and decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) > 30
 order by b.seg_bytes_mb desc;

5、性能监控

5.1、监控数据库会话，是否存在异常等待事件：

select inst_id ,sid,serial#,sql_id,event,machine,module,program,seconds_in_wait from gv$session ;
--看会话在做什么操作
select sid, sql_text
  from v$session a, v$sql b
 where sid in(85,160)
   and(b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id);

5.2、应用监控

收集awr报告，观察应用系统是否运行正常。