如果自己搞不定可以找诗檀软件专业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行的尺寸很大,无法收纳在一个块中时,就会将一行分割,收纳在多个块中。重建表治标不治本。
行迁移 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; /
Comment