诗檀软件专业数据库修复团队
Oracle中可能的碎片化汇总
| 何处碎片化了？ |
发生原因 |
性能影响 |
容量影响 |
解決方案 |
| block 数据块 |
1行的大小＞1block时发生（行链接 chained rows） |
全面影响块的访问 |
因为碎片化所以所需容量增加 |
扩大block size |
| block 数据块 |
更新一个更大的值（行迁移 migrated rows） |
全面影响块的访问 |
同上 |
重建表 or 更大的pctfree? |
| Table 表 |
插入值后，发生大量删除（HWM上升后不会因为delete而下降） |
全扫描性能恶化 |
同上 |
重建表、SHRINK |
| index 索引 |
Index key值的更新以及删除 |
Index扫描性能恶化 |
同上 |
重建索引？coalesce? |
| extent 盘区 |
表以及index的追加或者删除 |
使用LOCAL extent Management 后几乎没有 |
同上 |
UNIFORM EXTENT SIZE |
碎片化的结构以及种类
行链接 chained rows
1行的尺寸很大，无法收纳在一个块中时，就会将一行分割，收纳在多个块中。重建表治标不治本。
行迁移 migrated rows
在UPDATE等数据急剧增加，现有的块中无法容纳的情况下，就会只留下指针，转而储存到其他的块中。重建表可以暂时解决。
HWM的上升与碎片化
索引的碎片化
表的碎片化确认SQL
注意 这里的碎片化 仅仅是指高水位与实际数据量不符而已，你可以选择什么都不做。
教条的说当 表的使用率(used_size/allocated size)小于70%以下时就需要维护了，这是教条的 和不合时宜的。
※请在统计信息是最新的状态下执行
col owner for a20
col table_name for a40
col line 1000
col pagesize 1000
#确认表碎片化的SQL
SELECT b.owner "owner",
b.table_name "table_naeme",
ROUND(a.seg_bytes /1024/1024,1) "allocated_size(MB)",
ROUND(b.tab_bytes /1024/1024,1) "used_size(MB)",
ROUND((a.seg_bytes - b.tab_bytes)/1024/1024,1) "free(MB)"
FROM
(SELECT owner,
segment_name,
SUM(bytes) seg_bytes
FROM dba_segments
GROUP BY owner,
segment_name
) a,
(SELECT owner,
table_name,
SUM(num_rows*avg_row_len) tab_bytes
FROM dba_tables
GROUP BY owner,
table_name
) b
WHERE a.segment_name = b.table_name
AND b.owner NOT IN('SYS','SYSTEM')
ORDER BY 5 DESC;
碎片化解决方案
| 维护方法 |
所需时间 |
储存使用量 |
可否运行时进行搜索 |
可否运行时进行更新 |
解除已经上升的HWM |
解除行移行 |
使用实例 |
操作难度 |
对索引的影响 |
| Export/Import(DataPump) |
△ |
× |
×（不可） |
×（不可） |
○（可） |
○（可） |
○ |
○ |
○（×） |
| 抽取以及载入比较耗时间 |
需要在数据库之外有存放空间 |
|
|
|
|
非常多 |
有非常多的资料 |
在direct时重建 |
| SHRINK方式 |
△ |
△ |
○（可） |
○（可） |
○（可） |
×（不可） |
△ |
○（易） |
○（没有） |
| Segment中分散很多时，比较花时间 |
虽然不需要另外追加容量，但UNDO可能增加 |
|
严格来说是不行的，但根据时机，也会有可行的时候。 |
|
基本无法解除行移行 |
有一些实际使用的例子 |
可以用1个SQL来执行 |
|
| 重新定义在线表 |
○ |
× |
○（可） |
○（可） |
○（可） |
○（可） |
△ |
△（中） |
○（没有） |
| DB内部中的copy＋差分同期＋替换的时间 |
偶尔会需要两倍的尺寸 |
|
严格来说是不行的，但根据时机，也会有可行的时候。 |
|
|
关键数据库中有实际使用的例子 |
设定中需要多个index |
|
- FBDA的情况下、执行DISASSOCIATE_FBA，就可以执行碎片化解决方案。
- 在碎片化解决方案之中，执行内部复制的 项目的OBJECT_ID 会产生变换，所以避免执行的历史管理表就会分裂。
EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA(‘[schema名]’,‘[表名]');
TRUNCATE TABLE [表名];
!expdp ・・・ content=data_only
!impdp ・・・ content=data_only
EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA (‘[schema名]’,‘[表名]');
确认索引的碎片化的SQL
※请保证在统计信息的状态是最新的状态下执行
select owner,index_name,blevel “height” from dba_indexes where blevel >3
- 阶层的高度： 4层以上（ HEIGHT => 4 ）
- 被删除的行队列所占的比例：从20到超过30%的情况（ DEL_LF_ROW/LF_ROWS > 0.2 ）
analyze index test_ind validate structure;
select NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS from INDEX_STATS;
另外、INDEX_STATS视图的内容，会在切断session时被清理。
| 维护方法 |
所需时间 |
储存使用率 |
运行中可否使用索引 |
运行中可否使用更新 |
维护后数据的聚集 |
| DROP INDEX→
CREATE INDEX |
× |
○ |
△（可） |
×（不可） |
○ |
| 在建索引时需要进行全表扫描 |
不需要另外追加DB容量 |
可以搜索，但是全扫描 |
直到完成制成索引为止，都不能更新 |
再次编辑数据 |
| REBUILD方法 |
○ |
△ |
△（可） |
×（不可） |
○ |
| 利用现有的索引重建 |
需要与现有的索引的尺寸是相同的 |
虽然可以搜索索引，但执行时间过长的查询有失败的风险 |
直到完成再次构成索引为止，都不能更新 |
再次编辑数据 |
| REBUILD ONLINE方法 |
× |
× |
△（可） |
○（可） |
○ |
| 在建索引时需要进行全表扫描 |
需要大于等于现有的索引的尺寸（种类表） |
虽然可以搜索索引，但执行时间过长的查询有失败的风险 |
|
再次编辑数据 |
取得停止时机的情况下
- 表：Export Import
- 可以活用现有的技术，从实际成绩较多的观点开始考虑。
- 索引：REBUILD方法
- 从维护速度的观点开始考虑
- 在用export Import完成时不需要
没有取得停止时机的情况下
- 表：重新定义在线表
- 可以对付行移行以及HWM两种情况，从作业时间较少的观点开始考虑
- 索引：REBUILD ONLINE
HWM位置的确认方法：
set serveroutput on;
declare
v_total_blocks number; --分配到segment中的所有块数
v_total_bytes number; --表示total_blocks的字节
v_unused_blocks number; --被分配的块中，还没有被使用过一次的块数。
v_unused_bytes number; --表示unused_blocks 的字节
v_last_used_extent_file_id number; --拥有HWM的extent所储存的数据文件的ID
v_last_used_extent_block_id number; --拥有HWM的extent的第一块ID
v_last_used_block number; --拥有HWM的extent中的块编号
begin
dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype',
v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes,
v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);
dbms_output.put_line('有HWM的extent的数据文件的ID：'
|| to_char(v_last_used_extent_file_id, '9,999,990'));
dbms_output.put_line('有HWM的extent的起始块ID：'
|| to_char(v_last_used_extent_block_id, '9,999,990'));
dbms_output.put_line('有HWM的块的位置：'
|| to_char(v_last_used_block, '9,999,990'));
end;
/