oracle表碎片的整理

DBA小记 2020-10-27

6980

数据库在日常的使用过程中，不断的DML操作，导致表和索引出现碎片。这会导致HWM之前有很多的空闲空间，而oracle在做全表扫描的时候会读取HWM以下的所有block，这样会产生额外的IO，影响性能。

可以使用几种方法来降低高水位线（HWM）

1、shrink

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

SQL> alter table test enable row movement; --打开行移动
SQL> alter table test shrink space cascade; --压缩表及相关数据段并下调HWM
SQL> alter table test shrink space compact; --只压缩不下调HWM
SQL> alter table test shrink space ; --下调HWM
SQL> alter table test disable row movement; --关闭行移动

只能在ASSM、本地管理的表空间进行，完成这些之后不需要进行索引的重建，统计信息最好重新收集下。

2、导出导入

使用用exp/imp，expdp/impdp导出后，重新导入重建。

3、CTAS技术

create table newtable as select * from old_table

drop old_table

rename table newtable to old_table

重建索引，收集统计信息。

4、move tablespace

sql> alter table <表名> move tablespace <表空间名>

重建索引，收集统计信息。

实验操作

只做下shrink验证。

SQL> show user;
User is "test"
SQL> create table test as select * from dba_objects;
Table created
SQL> insert into test select * from test;
74448 rows inserted
SQL> 
148896 rows inserted
SQL> 
297792 rows inserted
SQL> commit;
Commit complete
SQL> select count(*) from test;
  COUNT(*)
----------
595584
SQL> create index idx_test on test(object_id);
Index created

收集统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname =>'TEST'); 
PL/SQL procedure successfully completed

查询统计信息收集日期，确保结果正确

select owner,table_name,last_analyzed from dba_tables Where owner='TEST' AND table_name='TEST';

确定表碎片程度：

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 dba_tables
  WHERE table_name = 'TEST';

收集索引信息：

analyze index idx_test validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats;

模拟DML操作，制造碎片。

SQL> delete from test where object_id > 10000;
516856 rows deleted
SQL> commit;
Commit complete
SQL> insert into test select * from dba_objects;
74449 rows inserted
SQL> insert into test select * from dba_objects;
74449 rows inserted
SQL> commit;
Commit complete

收集统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname =>'TEST');

确定表碎片程度：

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 dba_tables
  WHERE table_name = 'TEST';

确定索引碎片程度

select index_name, c.NMB "应有大小", d.SMB "现大小"
  from (select index_name,
               round((select num_rows numrows
                        from dba_tables
                       where table_name = upper('TEST')
                         AND owner = upper('TEST')) /
                     ((8192 - 819.2 - 4 - 20 - 72 - 32) /
                     ((sum(AVG_COL_LEN)) + 2 + 18)) * 8192 / 1024 / 1024) NMB
          from (SELECT b.index_name  index_name,
                       a.column_name,
                       a.AVG_COL_LEN AVG_COL_LEN
                  FROM dba_tab_columns a,
                       (select b.index_name, b.column_name, b.index_owner
                          from dba_ind_columns b
                         where b.table_name = upper('TEST')
                           and B.INDEX_OWNER = upper('TEST')
                         order by b.index_name) b
                 WHERE a.TABLE_NAME = upper('TEST')
                   AND A.OWNER = upper('TEST')
                   and a.column_name = b.column_name
                 order by b.index_name)
         group by index_name) c,
       (SELECT segment_name, round(sum(bytes) / 1024 / 1024) SMB
          FROM dba_segments
         WHERE OWNER = upper('TEST')
         group by segment_name) d
 where c.index_name = d.segment_name;

select name,height,pct_used,del_lf_rows/lf_rows from index_stats;

收缩表：

SQL> alter table test enable row movement; 
Table altered
SQL> alter table test shrink space cascade;
Table altered
SQL> alter table test disable row movement; 
Table altered
SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname =>'TEST')
PL/SQL procedure successfully completed

查看收回后碎片详情：

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 dba_tables
  WHERE table_name = 'TEST';

其他方法可在线重建索引：

alter index idx_test rebuild online;  --重建自动收集统计信息

oracle

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

oracle表碎片的整理

评论