关闭

Oracle 碎片整理

标签: oracletable数据库存储脚本insert
631人阅读 评论(0) 收藏 举报
分类:

Oracle 碎片问题


1>碎片对系统的影响

当要满足一个空间要求时,数据库将首先查找当前最大的自由范围,而"最大"自由范围逐渐变小,要找到一个足够大的自由范围已变得越来越困难,从而导致表空间中的速度障碍,使数据库的空间分配愈发远离理想状态 

部分的extent无法得以自动合并,浪费了大量的表空间


2>自由范围的碎片计算

FSFIfree space fragmentation index 自由空间碎片索引)

FSFI=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents))) 

FSFI最大100(理想的单文件表空间)

-- --查看所有表空间的碎片程度(值在30以下表示碎片很多)


select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select 
b.file_id file_ID, 
b.tablespace_name tablespace_name, 
b.bytes Bytes, 
(b.bytes-sum(nvl(a.bytes,0))) used, 
sum(nvl(a.bytes,0)) free, 
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.bytes 
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);


--运行后

TABLESPACE_NAME                SUM(BYTES)  SUM(FREE) SUM(FREE)*100/SUM(BYTES)

------------------------------ ---------- ---------- ------------------------

SYSTEM                          503316480    9568256               1.90104167

SYSAUX                          251658240    9240576                 3.671875

UNDOTBS1                        152043520  129368064               85.0862069

USERS                             5242880    4784128                    91.25

补充

--查找数据库某个表空间内最大连续的自由空间大小

SELECT tablespace_name,max(bytes)/1024/1024 "max_free_space(M)" 

FROM dba_free_space 

GROUP BY tablespace_name 

ORDER BY 1;



3>碎片整理

--盘区(extent)的碎片整理:alter tablespace temp coalesce

SELECT   'alter tablespace ' || TABLESPACE_NAME || ' coalesce ;'

  FROM   DBA_FREE_SPACE_COALESCED

 WHERE   PERCENT_EXTENTS_COALESCED < 100 OR PERCENT_BLOCKS_COALESCED < 100;

--段(segment)碎片整理,查看数据字典dba_segments,segment 的信息可以查看dba_extents

--表段,索引段等。每种段都可以碎片的整理


3.1>表段碎片整理

方式一:import/export

最简单的方法是用正确的存储参数将这个段重建,将旧表中的数据插入到新表,同时删除旧表。Import/export来完成。

Export命令有个(压缩标志)这个标志在读表时会引发export确定该表所分配的物理空间量,它会向输出转储文件写入一个新的初始化存储参数--等于全部所分配空间。若这个表关闭, 则使用import命令重新生成

$exp user/password  file=exp.dmp  compress=y  grants=y  indexes=y tables(table1,table2);

$imp user/password  file=exp.emp  commit=y  buffer=64000  tables=(table1,table2);

除了exp/imp,还可以使用shrink table alter table tbname move

方式二:Shrink table


http://blogold.chinaunix.net/u1/46911/showart.php?id=2416861 

介绍:10g开始,提供shrink命令

表空间支持自动段空间管理,使用这个特性,降低HWM

分为两个阶段

1.数据重装(compact一系列insertdelete操作,将数据尽量排列在段的前面,这个过程需要在表上加RX锁,即,只在需要移动的行上加锁。涉及到rowid的改变,需要enable row movement同时disable基于rowidtrigger,对业务影响较小

语法:

$shrink space compact 

SQL>alter table <table_name> enable row movement --会造成引用表的XX对象(存储,包,视图)变为失效,执行utlrp.sql来编译无效的对象



2.HWM的调整调整HWM位置,释放空间数据块,需要在表上加X锁,会造成表上所有DML语句阻塞,业务繁忙期影响较大。


语法:

alter table <table_name> shrink space [ <null> | compact | cascade ];


alter table <table_name> shrink space compcat;

收缩表,相当于把块中数据打结实了,但会保持 high water mark;

 

alter table <tablespace_name> shrink space;

收缩表,降低 high water mark;

 

alter table <tablespace_name> shrink space cascade;

收缩表,降低 high water mark,并且相关索引也要收缩一下下

 

alter index idxname shrink space;

回缩索引



补充

alter table move

移动high water mark,但是并不会释放空间。shrink space移动高水位线,并且释放空间。




测试

普通表

Sql脚本,改脚本会生成相应的语句

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;

 

select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;


分区表的处理

进行shrink space时,发生ORA-10631shrink space有限制

在表上建有索引(包括全文索引)会失败

Sql

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;


select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;


select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';



Shrink的几个问题

1 shrink后index是否需要rebuild


SQL> create table my_test3 tablespace users as select * from all_objects whererownum<=20000;
SQL> create index i_my_test3 on my_test3(object_id);
SQL> delete from my_test3 where object_name like '%C%';
SQL> delete from my_test3 where object_name like '%U%';
SQL> alter table my_test3 enable row movement;
SQL> alter table my_test3 shrink space;
SQL> select index_name ,status from user_indexes where index_name='I_MY_TEST3';
INDEX_NAME STATUS
------------------------------ --------
I_MY_TEST3 VALID
--在shrink在移动数据时,一起维护了index上相应的数据rowid的信息,如果move的时候,--index太多,shrink过程中需要维护index的成本也会比较高


2.shrink时对table的lock
对table进行shrink,对table的锁定,v$locked_objects,被加上了row-x(SX)的lock


3.2>索引段碎片整理

--把索引重建(碎片整理后,很多索引会变成unusable状态,必须让塔变成void状态)

FOR REC IN (SELECT INDEX_NAME FROM USER_INDEXES ) --WHERE STATUS='UNUSABLE'

LOOP

tmp_val:='ALTER INDEX '||REC.INDEX_NAME ||' REBUILD';

BEGIN

EXECUTE IMMEDIATE tmp_val;

DBMS_OUTPUT.put_line (tmp_val);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error: ' || tmp_val || '!');

END;

END LOOP;

END;



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:675370次
    • 积分:7925
    • 等级:
    • 排名:第2743名
    • 原创:74篇
    • 转载:340篇
    • 译文:0篇
    • 评论:14条