ORACLE表空间优化

FSFI

SELECT a.TABLESPACE_NAME,
TRUNC(100*SQRT(MAX(BLOCKS)/SUM(BLOCKS))/SQRT(SQRT(COUNT(BLOCKS))),2) FSFI
FROM DBA_FREE_SPACE a, DBA_TABLESPACES b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME
AND b.CONTENT NOT IN ('TEMPORARY','UNDO','SYSAUX')
GROUP BY a.TABLESPACE_NAME ORDER BY FSFI;

上述sql 可查询FSFI（free space fragmentation index 空闲空间碎片索引）来作为表空间碎片参考，普遍认为100为理想值，低于30则碎片过多。但仅适用于表空间管理方式为DMT（dictionary managed tablespace 字典管理）的情况。

DMT表空间碎片

oracle数据存储概念：tablespace -> segment -> extent -> block

字典管理的表空间，segment 中分配的extent 大小不一，反复创建、回收对象后，存留一些极小的空闲extent 无法再被利用，产生气泡碎片。

表空间碎片的影响：

1. 空间浪费。
2. 性能问题。DMT 每次分配extent 时需要扫描UET$、FET$ 两张数据字典表，寻找可用大小的空间，碎片越多，意味着分配速度越慢。

可用以下sql 合并碎片空间：

ALTER TABLESPACE $tablespace_name COALESCE;

LMT提高FSFI

LMT 即 locally managed tablespace 本地管理，oracle 9i 以后默认为LMT。

LMT 管理机制与DMT 不同，LMT在表空间的数据文件头部加入了一个位图区域，在其中记录每个extent的使用状况，分配的extent 大小统一（uniform 下均为给定值； autollocate 系统自动分配，64K、1M、8M……），不存在表空间碎片，FSFI 对LMT 无参考意义。

但是可以想办法提高FSFI 的值。

分析FSFI 的sql 可知，FSFI 本质上是计算最大空闲extent 占所有空闲extent 的块（block）的比例，想办法让extent 数量变少、分配的block 连续即可。

回收站清除

分析测试库的DBA_FREE_SPACE 发现一个现象：很多空闲block 的id 连续，但不属于同一个extent。原因是回收站中还有该表空间的驻留对象，对其进行清除，可提高FSFI：

PURGE TABLESPACE $tablespace_name;

重建表空间

表空间新建时没有频繁创建、销毁对象，也就不存在大量空间分配行为，此时extent 完整且连续。

可将原表空间数据导出，新建一个表空间后再导入

-- 创建目录映射
CREATE DIRECTORY DIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY DIR TO $user;

-- 基于表空间导出dump文件
expdp $user/$pwd@$instance direactory=DIR dumpfile=mine.dmp logfile=exp.log tablespaces=$tablespace_name

-- 删除原表空间
DROP TABLESPACE $tablespace_name INCLUDING CONTENTS AND DATAFILES;

-- 创建表空间
CREATE TABLESPACE $tablespace_name
DATAFILE '/oradata/test.dbf' SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE 10240M;

-- 将dump文件导入新表空间
impdp $user/$pwd dumpfile=mine.dmp table_exists_action=replace logfile=imp.log directory=DIR
-- remap_tablespace=$param1:$param2,$temp1:$temp2