Ask Maclean

Oracle 碎片化解决方案

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]

 

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行的尺寸很大，无法收纳在一个块中时，就会将一行分割，收纳在多个块中。重建表治标不治本。

chained_rows

行迁移 migrated rows

在UPDATE等数据急剧增加，现有的块中无法容纳的情况下，就会只留下指针，转而储存到其他的块中。重建表可以暂时解决。

migrated_rows

 

HWM的上升与碎片化

hwm1

 

  • 会被影响的处理是全扫描

hwm2

索引的碎片化

index_split

 

表的碎片化确认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;
/

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *