Oracle碎片简约处理

2018-01-24 10:33:39来源:oschina作者:ChuanQiong人点击

分享

1概要

碎片简单理解就是在大量使用DML操作数据库时,其产生一些不能被再次使用的碎小空间,根据每种不同的碎片他们的产生也是有区别的,主要包涵一下几个层次。


|--disk-levelfragmention


|----tablespace-levelfragmentation


|------segment-levelfragmentation


|--------block-levelfragmentation


|----------row-levelfragmentation


|----------indexleafblock-levelfragmentation


2具体处理方式

注:所有操作使用sysdba权限操作,操作之前请将正在使用的数据库备份(切记)。


一、方式一(SQL执行的方式结果为推荐式)

SELECT

'SegmentAdvice--------------------------'||chr(10)||

'TABLESPACE_NAME:'||tablespace_name||chr(10)||

'SEGMENT_OWNER:'||segment_owner||chr(10)||

'SEGMENT_NAME:'||segment_name||chr(10)||

'ALLOCATED_SPACE:'||allocated_space||chr(10)||

'RECLAIMABLE_SPACE:'||reclaimable_space||chr(10)||

'RECOMMENDATIONS:'||recommendations||chr(10)||

'SOLUTION1:'||c1||chr(10)||

'SOLUTION2:'||c2||chr(10)||

'SOLUTION3:'||c3Advice

FROM

TABLE(dbms_space.asa_recommendations('FALSE','FALSE','FALSE'))

说明:


1.执行脚本后请查阅详情请查看数据编辑器;


2.遇到有‘movement’sql语句的solution请优先执行。


3.验证方式,执行如下语句:

SELECTD.TABLESPACE_NAME,

SPACE||'M'"SUM_SPACE(M)",

/*/*04*/.*/BLOCKS"SUM_BLOCKS",

/*05.*/SPACE-NVL(FREE_SPACE,0)||'M'"USED_SPACE(M)",

/*06.*/ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)||'%'

/*07.*/"USED_RATE(%)",

/*08.*/FREE_SPACE||'M'"FREE_SPACE(M)"/*09.*/FROM(SELECTTABLESPACE_NAME,/*10.*/ROUND(SUM(BYTES)/(1024*1024),2)SPACE,/*11.*/SUM(BLOCKS)BLOCKS/*12.*/FROMDBA_DATA_FILES/*13.*/GROUPBYTABLESPACE_NAME)D,/*14.*/(SELECTTABLESPACE_NAME,/*15.*/ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACE/*16.*/FROMDBA_FREE_SPACE/*17.*/GROUPBYTABLESPACE_NAME)F/*18.*/WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)/*19.*/UNIONALL--如果有临时表空间/*20.*/SELECTD.TABLESPACE_NAME,/*21.*/SPACE||'M'"SUM_SPACE(M)",/*22.*/BLOCKSSUM_BLOCKS,/*23.*/USED_SPACE||'M'"USED_SPACE(M)",/*24.*/ROUND(NVL(USED_SPACE,0)/SPACE*100,2)||'%'"USED_RATE(%)",/*25.*/NVL(FREE_SPACE,0)||'M'"FREE_SPACE(M)"/*26.*/FROM(SELECTTABLESPACE_NAME,/*27.*/ROUND(SUM(BYTES)/(1024*1024),2)SPACE,/*28.*/SUM(BLOCKS)BLOCKS/*29.*/FROMDBA_TEMP_FILES/*30.*/GROUPBYTABLESPACE_NAME)D,/*31.*/(SELECTTABLESPACE_NAME,/*32.*/ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,/*33.*/ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACE/*34.*/FROMV$TEMP_SPACE_HEADER/*35.*/GROUPBYTABLESPACE_NAME)F/*36.*/WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)/*37.*/ORDERBY1;

二、方式二(全面处理方式)


2.1表空间级碎片

1.查询系统表空间使用情况


执行如下语句:

select'altertablespace'||tablespace_name||'coalesce;'from(

selecttablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks))))FSFI

fromdba_free_space

groupbytablespace_namehavingsqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks))))<30and

tablespace_namenotin('SYS','SYSTEM','DBSNMP','SYSMAN','XDB'))

在一个有着足够有效自由空间,且FSFI值超过30的表空间中,很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时,就需要做碎片整理了(DMT空间可以整理,如果是LMT就无法整理,SMON会将相邻的自由范围自动合并)


2.操作方式


执行上一步查询的SQL语句。


3.收缩空闲表空间

select/*+ordereduse_hash(a,c)*/

'alterdatabasedatafile'''||a.file_name||'''resize'

||round(a.filesize-(a.filesize-c.hwmsize-100)*0.8)||'M;',

a.filesize,

c.hwmsize

from

(

selectfile_id,file_name,round(bytes/1024/1024)filesizefromdba_data_files

)a,

(

selectfile_id,round(max(block_id)*8/1024)HWMsizefromdba_extents

groupbyfile_id)c

wherea.file_id=c.file_id

anda.filesize-c.hwmsize>100;

执行查询出的SQL语句。


2.2段级碎片
2.2.1表级碎片

这里主要是对表碎片的整理与空间的收缩两方面,以达到碎片的整理目的。可通过两种方式进行操作,请根据实际需要选择。


以下两种方式选其一即可。


1.空间收缩优于数据增长,碎片清理不彻底


a)原理

操作项


执行语句

shrink必须开启行迁移功能。


altertabletable_nameenablerowmovement;

保持HWM,相当于把块中数据打结实了


altertabletable_nameshrinkspacecompact;

回缩表与降低HWM


altertabletable_nameshrinkspace;

回缩表与相关索引,降低HWM


altertabletable_nameshrinkspacecascade;

重新编译失效对象


SQL>@?/rdbms/admin/utlrp.sql

b)实施步骤


i.执行如下语句:

select'altertable'||OWNER||'.'||TABLE_NAME||'enablerowmovement;
'||'altertable'||OWNER||'.'||TABLE_NAME||'shrinkspacecompact;
'||'altertable'||OWNER||'.'||TABLE_NAME||'shrinkspace;
'||'altertable'||OWNER||'.'||TABLE_NAME||'shrinkspacecascade;
'||'altertable'||OWNER||'.'||TABLE_NAME||'disablerowmovement;'from(

SELECTOWNER,SEGMENT_NAMETABLE_NAME,SEGMENT_TYPE,

GREATEST(ROUND(100*(NVL(HWM-AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1)),2),0)WASTE_PER,

ROUND(BYTES/1024,2)TABLE_KB,NUM_ROWS,

BLOCKS,EMPTY_BLOCKS,HWMHIGHWATER_MARK,AVG_USED_BLOCKS,

CHAIN_PER,EXTENTS,MAX_EXTENTS,ALLO_EXTENT_PER,

DECODE(GREATEST(MAX_FREE_SPACE-NEXT_EXTENT,0),0,'N','Y')CAN_EXTEND_SPACE,

NEXT_EXTENT,MAX_FREE_SPACE,

O_TABLESPACE_NAMETABLESPACE_NAME

FROM

(SELECTA.OWNEROWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,A.BYTES,

B.NUM_ROWS,A.BLOCKSBLOCKS,B.EMPTY_BLOCKSEMPTY_BLOCKS,

A.BLOCKS-B.EMPTY_BLOCKS-1HWM,

DECODE(ROUND((B.AVG_ROW_LEN*NUM_ROWS*(1+(PCT_FREE/100)))/C.BLOCKSIZE,0),

0,1,

ROUND((B.AVG_ROW_LEN*NUM_ROWS*(1+(PCT_FREE/100)))/C.BLOCKSIZE,0)

)+2AVG_USED_BLOCKS,

ROUND(100*(NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS,1),1)),2)CHAIN_PER,

ROUND(100*(A.EXTENTS/A.MAX_EXTENTS),2)ALLO_EXTENT_PER,A.EXTENTSEXTENTS,

A.MAX_EXTENTSMAX_EXTENTS,B.NEXT_EXTENTNEXT_EXTENT,B.TABLESPACE_NAMEO_TABLESPACE_NAME

FROMSYS.DBA_SEGMENTSA,

SYS.DBA_TABLESB,

SYS.TS$C

WHEREA.OWNER=B.OWNERand

SEGMENT_NAME=TABLE_NAMEand

SEGMENT_TYPE='TABLE'AND

B.TABLESPACE_NAME=C.NAME

UNIONALL

SELECTA.OWNEROWNER,SEGMENT_NAME||'.'||B.PARTITION_NAME,SEGMENT_TYPE,BYTES,

B.NUM_ROWS,A.BLOCKSBLOCKS,B.EMPTY_BLOCKSEMPTY_BLOCKS,

A.BLOCKS-B.EMPTY_BLOCKS-1HWM,

DECODE(ROUND((B.AVG_ROW_LEN*B.NUM_ROWS*(1+(B.PCT_FREE/100)))/C.BLOCKSIZE,0),

0,1,

ROUND((B.AVG_ROW_LEN*B.NUM_ROWS*(1+(B.PCT_FREE/100)))/C.BLOCKSIZE,0)

)+2AVG_USED_BLOCKS,

ROUND(100*(NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS,1),1)),2)CHAIN_PER,

ROUND(100*(A.EXTENTS/A.MAX_EXTENTS),2)ALLO_EXTENT_PER,A.EXTENTSEXTENTS,

A.MAX_EXTENTSMAX_EXTENTS,B.NEXT_EXTENT,

B.TABLESPACE_NAMEO_TABLESPACE_NAME

FROMSYS.DBA_SEGMENTSA,

SYS.DBA_TAB_PARTITIONSB,

SYS.TS$C,

SYS.DBA_TABLESD

WHEREA.OWNER=B.TABLE_OWNERand

SEGMENT_NAME=B.TABLE_NAMEand

SEGMENT_TYPE='TABLEPARTITION'AND

B.TABLESPACE_NAME=C.NAMEAND

D.OWNER=B.TABLE_OWNERAND

D.TABLE_NAME=B.TABLE_NAMEAND

A.PARTITION_NAME=B.PARTITION_NAME),

(SELECTTABLESPACE_NAMEF_TABLESPACE_NAME,MAX(BYTES)

MAX_FREE_SPACE

FROMSYS.DBA_FREE_SPACE

GROUPBYTABLESPACE_NAME)

WHEREF_TABLESPACE_NAME=O_TABLESPACE_NAMEAND

GREATEST(ROUND(100*(NVL(HWM-AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1)),2),0)>25

ANDOWNERnotin('SYS','SYSTEM','DBSNMP','SYSMAN','XDB')ANDBLOCKS>128

ORDERBY10DESC,1ASC,2ASC);

ii.导出为HTML格式,执行查询出的SQL语句。


iii.SQL>@?/rdbms/admin/utlrp.sql#SQLPlus运行


2.数据增长优于空间收缩,碎片清理彻底


a)原理

操作项


执行语句

整理表


altertabletable_namemovestroage(initial初始大小)

整理对应表索引


alterindexindex_namerebuildnologgingonline

重新编译失效对象


SQL>@?/rdbms/admin/utlrp.sql

b)实施步骤


i.整理表

select'altertable'||OWNER||'.'||TABLE_NAME||'move;'from(

SELECTOWNER,SEGMENT_NAMETABLE_NAME,SEGMENT_TYPE,

GREATEST(ROUND(100*(NVL(HWM-AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1)),2),0)WASTE_PER,

ROUND(BYTES/1024,2)TABLE_KB,NUM_ROWS,

BLOCKS,EMPTY_BLOCKS,HWMHIGHWATER_MARK,AVG_USED_BLOCKS,

CHAIN_PER,EXTENTS,MAX_EXTENTS,ALLO_EXTENT_PER,

DECODE(GREATEST(MAX_FREE_SPACE-NEXT_EXTENT,0),0,'N','Y')CAN_EXTEND_SPACE,

NEXT_EXTENT,MAX_FREE_SPACE,

O_TABLESPACE_NAMETABLESPACE_NAME

FROM

(SELECTA.OWNEROWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,A.BYTES,

B.NUM_ROWS,A.BLOCKSBLOCKS,B.EMPTY_BLOCKSEMPTY_BLOCKS,

A.BLOCKS-B.EMPTY_BLOCKS-1HWM,

DECODE(ROUND((B.AVG_ROW_LEN*NUM_ROWS*(1+(PCT_FREE/100)))/C.BLOCKSIZE,0),

0,1,

ROUND((B.AVG_ROW_LEN*NUM_ROWS*(1+(PCT_FREE/100)))/C.BLOCKSIZE,0)

)+2AVG_USED_BLOCKS,

ROUND(100*(NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS,1),1)),2)CHAIN_PER,

ROUND(100*(A.EXTENTS/A.MAX_EXTENTS),2)ALLO_EXTENT_PER,A.EXTENTSEXTENTS,

A.MAX_EXTENTSMAX_EXTENTS,B.NEXT_EXTENTNEXT_EXTENT,B.TABLESPACE_NAMEO_TABLESPACE_NAME

FROMSYS.DBA_SEGMENTSA,

SYS.DBA_TABLESB,

SYS.TS$C

WHEREA.OWNER=B.OWNERand

SEGMENT_NAME=TABLE_NAMEand

SEGMENT_TYPE='TABLE'AND

B.TABLESPACE_NAME=C.NAME

UNIONALL

SELECTA.OWNEROWNER,SEGMENT_NAME||'.'||B.PARTITION_NAME,SEGMENT_TYPE,BYTES,

B.NUM_ROWS,A.BLOCKSBLOCKS,B.EMPTY_BLOCKSEMPTY_BLOCKS,

A.BLOCKS-B.EMPTY_BLOCKS-1HWM,

DECODE(ROUND((B.AVG_ROW_LEN*B.NUM_ROWS*(1+(B.PCT_FREE/100)))/C.BLOCKSIZE,0),

0,1,

ROUND((B.AVG_ROW_LEN*B.NUM_ROWS*(1+(B.PCT_FREE/100)))/C.BLOCKSIZE,0)

)+2AVG_USED_BLOCKS,

ROUND(100*(NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS,1),1)),2)CHAIN_PER,

ROUND(100*(A.EXTENTS/A.MAX_EXTENTS),2)ALLO_EXTENT_PER,A.EXTENTSEXTENTS,

A.MAX_EXTENTSMAX_EXTENTS,B.NEXT_EXTENT,

B.TABLESPACE_NAMEO_TABLESPACE_NAME

FROMSYS.DBA_SEGMENTSA,

SYS.DBA_TAB_PARTITIONSB,

SYS.TS$C,

SYS.DBA_TABLESD

WHEREA.OWNER=B.TABLE_OWNERand

SEGMENT_NAME=B.TABLE_NAMEand

SEGMENT_TYPE='TABLEPARTITION'AND

B.TABLESPACE_NAME=C.NAMEAND

D.OWNER=B.TABLE_OWNERAND

D.TABLE_NAME=B.TABLE_NAMEAND

A.PARTITION_NAME=B.PARTITION_NAME),

(SELECTTABLESPACE_NAMEF_TABLESPACE_NAME,MAX(BYTES)

MAX_FREE_SPACE

FROMSYS.DBA_FREE_SPACE

GROUPBYTABLESPACE_NAME)

WHEREF_TABLESPACE_NAME=O_TABLESPACE_NAMEAND

GREATEST(ROUND(100*(NVL(HWM-AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1)),2),0)>25

ANDOWNERnotin('SYS','SYSTEM','DBSNMP','SYSMAN','XDB')ANDBLOCKS>128

ORDERBY10DESC,1ASC,2ASC);

执行查询的SQL语句。


ii.整理索引

select'alterindex'||OWNER||'.'||segment_name||'rebuildnologginonline;'from(select*fromSYS.DBA_SEGMENTSwheresegment_type='INDEX');

执行查询的SQL语句。


iii.SQL>@?/rdbms/admin/utlrp.sql#SQLPlus运行


2.2.2索引级碎片

1.分析索引是否有碎片


执行语句:analyzeindexindex_namevalidatestructure;


注:index_name可以执行语句查看:

select*fromdba_segmentswheresegment_type='INDEX'andownerin('MW_APP','MW_SYS','STATDBA','DICTDBA')

a)索引分析完成后,在index_stats查看其分析结果,执行如下语句:

selectheight,blocks,lf_blks,lf_rows,br_blks,br_rows,del_lf_rowsfromindex_stats

b)索引整理原则如下:


i.删除的行数如占总的行数的30%,即del_lf_rows/lf_rows>0.3,那就考虑索引碎片整理;


ii.如果”hight“大于4,可以考虑碎片整理;


iii.如果索引的行数(LF_rows)远远小于‘LF_BLKS’,那就说明有了一个大的删除动作,需要整理碎片。


c)索引碎片整理方法


alterindexindex_namerebuildnologgingonline;


2.分析处理系统索引扩展次数大于10次

SELECT'alterindex'||owner||'.'||segment_name||'rebuildnologginonline;'

FROM(SELECTCOUNT(*),

owner,

segment_name,

t.tablespace_name

FROMdba_extentst

WHEREt.segment_type='INDEX'

ANDt.ownerNOTIN('SYS','SYSTEM','DBSNMP','SYSMAN','XDB')

GROUPBYowner,segment_name,t.tablespace_name

HAVINGCOUNT(*)>10

ORDERBYCOUNT(*)DESC);

执行查询的SQL语句。

相关文章

    无相关信息

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台