Oracle 碎片化解决方案

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

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

服务热线 : 13764045638 QQ号:47079569 邮箱:service@parnassusdata.com

 

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;
/




关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪ICP备14014813号

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569