概述

很多时候数据库总有大表删除数据的情况，特别是有些是写在存储过程晚上执行的，而大表删除数据后高水位并不会回收，针对一两张表可能还要时间精力去维护，但是数据库几千张表，我们怎么去找那些在高水位没有回收的大表呢？下面介绍下对高水位表优化的思路。

思路

1、快速收集全库统计信息（开并行度）

dbms_stats.gather_database_stats(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>8);

2、一键检查数据库高水位表

按浪费空间排序，选择前十几张大表做回收。

3、回收高水位表（shrink方式）

由于move需要额外的空间空间，而且move后需要重建索引；shrink后不需要重建索引,如果一个表的索引比较多，shrink过程中用来维护index的成本也会比较高，而且shrink不影响dml操作，能在线处理，这里选择shrink的方式来回收。

4、针对表级别重新收集统计信息

针对已经回收的表重新做统计分析。

5、验证高水位是否回收，是否减少浪费空间。

1、快速收集全库统计信息（dba用户）

1.1、开启计时

set timing on

1.2、设置并行收集

exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');

1.3、开始收集全库统计信息

begin
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>8);
end;
/

这是开并行度8时候资源情况：

1.4、关闭并行收集

exec dbms_stats.set_global_prefs('CONCURRENT','FALSE');

2、一键检查数据库高水位表

SELECT 'alter table ' || table_name || ' enable row movement;',
 'alter table ' || table_name || ' shrink space;',
 'Analyze table '|| table_name ||' COMPUTE STATISTICS;',
 table_name,
 tablespace_name,
 status,
 last_analyzed,
 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 11 DESC;

3、回收高水位表

第一种方法：MOVE回收高水位(move需要额外的空间空间，move后需要重建索引)

alter table my_objects move;

第二种方法：shrink回收高水位(shrink后不需要重建索引,如果一个表的索引比较多，shrink过程中用来维护index的成本也会比较高，，shrink不影响dml操作，能在线)

alter table my_objects enable row movement;
alter table my_objects shrink space;

4、重新收集统计信息

针对表级别手动收集

ANALYZE TABLE MV_OTM_ORDER_RELEASE_LINE COMPUTE STATISTICS;

--耗时2511秒

5、测试是否回收

SELECT table_name,
 tablespace_name,
 status,
 last_analyzed,
 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 table_name='MV_OTM_ORDER_RELEASE_LINE';

篇幅有限，关于高水位表优化的就介绍到这了，这里提2个注意事项，1个是并行度要根据CPU数量来定（要不磁盘I/0会有问题），1个是如果用move回收要注意会消耗多一些额外空间和需要重建索引。

后面会分享更多DBA方面内容，感兴趣的朋友可以关注下！