| 订阅 | 在线投稿
分享
 
 
 

深入探讨Oracle数据库10g的Shrink机制

2008-07-17 07:49:07 编辑來源:互联网 国际版 评论
 
 
本文为【深入探讨Oracle数据库10g的Shrink机制】的汉字拼音对照版显示拼音
  cong10gkaishioraclekaishitigongShrinkdeminglingjiaruwomendebiaokongjianzhongzhichizidongduankongjianguanli (ASSM),jiukeyi使shiyongzhegetexingsuoxiaoduanjijiangdiHWMzhelixuyaoqiangtiaoyidian10gdezhegexintexingjinduiASSMbiaokongjianyouxiaofouzehuibao ORA-10635: Invalid segment or tablespace type

  zaizheliwomenlaitaolunruheduiyigeASSMdesegmenthuishoulangfeidekongjian

  tongyangwomenyongxitongshituall_objectslaizaitablespace ASSMshangchuangjianceshibiaomy_objectszheyixiaojiedeneirongshiyanhuanjingweioracle10.1.0.2

  SQL> select * from v$version;

  BANNER

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

  Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

  PL/SQL Release 10.1.0.2.0 - Production

  CORE 10.1.0.2.0 Production

  TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

  NLSRTL Version 10.1.0.2.0 Production

  SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,

  2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT

  3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';

  TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT

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

  ASSM 8192 LOCAL UNIFORM AUTO

  SQL> create table my_objects tablespace assm

  2 as select * from all_objects;

  Table created

  

  ranhouwomensuijidicongtable MY_OBJECTSzhongshanchuyibufenshuju

  SQL> select count(*) from my_objects;

  COUNT(*)

  ----------

  47828

  SQL> delete from my_objects where object_name like '%C%';

  16950 rows deleted

  SQL> delete from my_objects where object_name like '%U%';

  4503 rows deleted

  SQL> delete from my_objects where object_name like '%A%';

  6739 rows deleted

  

  xianzaiwomen使shiyongshow_spaceheshow_space_assmlaikankanmy_objectsdeshujucunchuzhuangkuang

  SQL> exec show_space('MY_OBJECTS','DLINGER');

  Total Blocks............................680

  Total Bytes.............................5570560

  Unused Blocks...........................1

  Unused Bytes............................8192

  Last Used Ext FileId....................6

  Last Used Ext BlockId...................793

  Last Used Block.........................4

  PL/SQL guochengyichenggongwancheng

  SQL> exec show_space_assm('MY_OBJECTS','DLINGER');

  free space 0-25% Blocks:................0

  free space 25-50% Blocks:...............205

  free space 50-75% Blocks:...............180

  free space 75-100% Blocks:..............229

  Full Blocks:............................45

  Unformatted blocks:.....................0

  PL/SQL guochengyichenggongwancheng

  

  zhelitable my_objectsdeHWMxiayou679geblockqizhongfree spacewei25-50%deblockyou205gefree spacewei50-75%deblockyou180gefree spacewei75-100%deblockyou229gefull spacedeblockzhiyou45gezhezhongqingkuangxiawomenxuyaoduizhegetabledexianyoushujuxingjinxingchongzu

  yao使shiyongassmshangdeshinkshouxianwomenxuyao使shigaibiaozhichixingyidongkeyiyongzheyangdeminglinglaiwancheng

  alter table my_objects enable row movement;

  xianzaijiukeyilaijiangdimy_objectsdeHWMhuishoukongjianle使shiyongmingling

  alter table bookings shrink space;

  womenjutidekanyixiashiyandejieguo

  SQL> alter table my_objects enable row movement;

  biaoyigenggai

  SQL> alter table my_objects shrink space;

  biaoyigenggai

  SQL> exec show_space('MY_OBJECTS','DLINGER');

  Total Blocks............................265

  Total Bytes.............................2170880

  Unused Blocks...........................2

  Unused Bytes............................16384

  Last Used Ext FileId....................6

  Last Used Ext BlockId...................308

  Last Used Block.........................3

  PL/SQL guochengyichenggongwancheng

  SQL> exec show_space_assm('MY_OBJECTS','DLINGER');

  free space 0-25% Blocks:................0

  free space 25-50% Blocks:...............1

  free space 50-75% Blocks:...............0

  free space 75-100% Blocks:..............0

  Full Blocks:............................249

  Unformatted blocks:.....................0

  PL/SQL guochengyichenggongwancheng

  

  zaizhixingwanshrinkminglinghouwomenkeyikandaotable my_objectsdeHWMxianzaijiangdaole264deweizhierqieHWMxiadeblockdekongjian使shiyongzhuangkuangfull spacedeblockyou249gefree space wei25-50% Blockzhiyou1ge

  womenjiexialaitaolunyixiashrinkdeshixianjizhiwomentongyang使shiyongtaolunmovejizhidenageshiyanlaiguancha

  SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;

  Table created

  

  wangtable test_hwmzhongcharuruxiadeshuju

  insert into TEST_HWM values (1,'aa');

  insert into TEST_HWM values (2,'bb');

  insert into TEST_HWM values (2,'cc');

  insert into TEST_HWM values (3,'dd');

  insert into TEST_HWM values (4,'ds');

  insert into TEST_HWM values (5,'dss');

  insert into TEST_HWM values (6,'dss');

  insert into TEST_HWM values (7,'ess');

  insert into TEST_HWM values (8,'es');

  insert into TEST_HWM values (9,'es');

  insert into TEST_HWM values (10,'es');

  womenlaikankanzhegetablederowidheblockdeIDhexinxi

  SQL> select rowid , id,name from TEST_HWM;

  ROWID ID NAME

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

  AAANhqAAGAAAAFHAAA 1 aa

  AAANhqAAGAAAAFHAAB 2 bb

  AAANhqAAGAAAAFHAAC 2 cc

  AAANhqAAGAAAAFIAAA 3 dd

  AAANhqAAGAAAAFIAAB 4 ds

  AAANhqAAGAAAAFIAAC 5 dss

  AAANhqAAGAAAAFJAAA 6 dss

  AAANhqAAGAAAAFJAAB 7 ess

  AAANhqAAGAAAAFJAAC 8 es

  AAANhqAAGAAAAFKAAA 9 es

  AAANhqAAGAAAAFKAAB 10 es

  11 rows selected

  SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

  2 from dba_extents where segment_name='TEST_HWM' ;

  EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

  0 6 6 324 5

  1 6 6 329 5

  

  ranhoucongtable test_hwmzhongshanchuyixieshuju

  delete from TEST_HWM where id = 2;

  delete from TEST_HWM where id = 4;

  delete from TEST_HWM where id = 3;

  delete from TEST_HWM where id = 7;

  delete from TEST_HWM where id = 8;

  guanchatable test_hwmderowidheblockiddexinxi

  SQL> select rowid , id,name from TEST_HWM;

  ROWID ID NAME

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

  AAANhqAAGAAAAFHAAA 1 aa

  AAANhqAAGAAAAFIAAC 5 dss

  AAANhqAAGAAAAFJAAA 6 dss

  AAANhqAAGAAAAFKAAA 9 es

  AAANhqAAGAAAAFKAAB 10 es

  SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

  2 from dba_extents where segment_name='TEST_HWM' ;

  EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

  0 6 6 324 5

  1 6 6 329 5

  

  congyishangdexinxiwomenkeyikandaozaitable test_hwmzhongshengxiadeshujushifenbuzaiAAAAFHAAAAFIAAAAFJAAAAFKzheyangsigelianxudeblockzhong

  SQL> exec show_space_assm('TEST_HWM','DLINGER');

  free space 0-25% Blocks:................0

  free space 25-50% Blocks:...............1

  free space 50-75% Blocks:...............3

  free space 75-100% Blocks:..............3

  Full Blocks:............................0

  Unformatted blocks:.....................0

  

  tongguoshow_space_assmwomenkeyikandaomuqianzhesigeblockdekongjian使shiyongzhuangkuangAAAAFHAAAAFIAAAAFJshanggeyouyixingshujuwomencaicefree spacewei50-75%de3geblockshizhesangeblocknamefree spacewei25-50%de1geblockjiushiAAAAFKleshengxiafree spacewei 75-100% de3geblockshiHWMxiayigeshihuadeshangwei使shiyongdeblockguanyuassmxiahwmdeyidongwomenqianmianyijingxiangxiditaolunguolezaiextentbudayuyu16geblockshishiyiyigeextentweidanweilaiyidongde

  ranhouwomenduitable my_objectszhixingshtinkdecaozuo

  SQL> alter table test_hwm enable row movement;

  Table altered

  SQL> alter table test_hwm shrink space;

  Table altered

  SQL> select rowid ,id,name from TEST_HWM;

  ROWID ID NAME

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

  AAANhqAAGAAAAFHAAA 1 aa

  AAANhqAAGAAAAFHAAB 10 es

  AAANhqAAGAAAAFHAAD 9 es

  AAANhqAAGAAAAFIAAC 5 dss

  AAANhqAAGAAAAFJAAA 6 dss

  SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

  2 from dba_extents where segment_name='TEST_HWM' ;

  EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

  0 6 6 324 5

  1 6 6 329 5

  

  dangzhixingleshrinkcaozuohouyouyisidexianxiangchuxianlewomenlaikankanoracleshiruheyidongxingshujudezhelideqingkuanghemoveyijingbutaiyiyanglewomenzhidaozaimovecaozuodeshihousuoyouxingderowiddoufashenglebianhuatablesuoweiyudeblockdequyuyefashenglebianhuadanshisuoyouxingwulicunchudeshunxudoumeiyoufashengbianhuasuoyiwomendedaodejielunshioracleyiblockweidanweijinxingleblockjiandeshujucopynameshrinkhouwomenfaxianbufenxingshujuderowidfashenglebianhuatongshibufenxingshujudewulicunchudeshunxuyefashenglebianhuaertablesuoweiyudeblockdequyuquemeiyoubianhuazhejiushuomingshrinkzhiyidongletableqizhongyibufendexingshujulaiwanchengshifangkongjianerqiezhegeguochengshizaitabledangqiansuo使shiyongdeblockzhongwanchengde

  nameOraclejutiyidongxingshujudeguochengshizenyangdenewomengenjuzheyangdeshiyanjieguokeyilaicaiceyixia

  OracleshiyixingweidanweilaiyidongshujudeOraclecongdangqiantablecunchudezuihouyixingshujukaishiyidongcongdangqiantablezuixian使shiyongdeblockkaishisousuokongjiansuoyishrinkzhiqianrownum10denaxingshuju10,esbeiyidongdaoblock AAAAFHshangxiedao1,aazhexingshujudehoumiansuoyi10,esderownumherowidtongshifashenggaibianranhoushi9,eszhexingshujuchongfushangshuguochengzheshioracleconghouxiangqianyidongxingshujudedazhizunxundeguizenamejutiyidongxingshujudedesuanfashibijiaofuduodebaokuoxiangASSMdetablezhonginsertshuju使shiyongblockdeshunxudesuanfayeshibijiaofuduodedajiayouxingqudekeyizijilaiyanjiuzaizheliwomenbuduozuotaolun

  womenhuankeyizaishrink tabledetongshishrinkzhegetableshangdeindex

  alter table my_objects shrink space cascade;

  tongyangdizhegecaozuozhiyoudangtableshangdeindexyeshiASSMshicaineng使shiyong

  guanyurizhidewentiwomenduibiletongyangshujulianghefenbuzhuangkuangdeliangzhangtablezaimoveheshrinkxiashengchengderedo sizetableshangmeiyouindexdeqingkuangxia

  SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces

  2 where tablespace_name in('ASSM','HWM');

  TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT

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

  ASSM AUTO

  HWM MANUAL

  SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;

  Table created

  SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000;

  Table created

  SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS';

  BYTES/1024/1024

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

  2.1875

  SQL> delete from my_objects where object_name like '%C%';

  7278 rows deleted

  SQL> delete from my_objects1 where object_name like '%C%';

  7278 rows deleted

  SQL> delete from my_objects where object_name like '%U%';

  2732 rows deleted

  SQL> delete from my_objects1 where object_name like '%U%';

  2732 rows deleted

  SQL> commit;

  Commit complete

  SQL> alter table my_objects enable row movement;

  Table altered

  SQL> select value from v$mystat, v$statname

  2 where v$mystat.statistic# = v$statname.statistic#

  3 and v$statname.name = 'redo size';

  VALUE

  ----------

  27808792

  SQL> alter table my_objects shrink space;

  Table altered

  SQL> select value from v$mystat, v$statname

  2 where v$mystat.statistic# = v$statname.statistic#

  3 and v$statname.name = 'redo size';

  VALUE

  ----------

  32579712

  SQL> alter table my_objects1 move;

  Table altered

  SQL> select value from v$mystat, v$statname

  2 where v$mystat.statistic# = v$statname.statistic#

  3 and v$statname.name = 'redo size';

  VALUE

  ----------

  32676784

  duiyutable my_objectsjinxingshrinkchanshengle32579712 27808792=4770920yue4.5Mderedo duitable my_objects1jinxingmovechanshengle32676784-32579712= 97072,yue95Kderedo sizenameyumovebijiaoqilaishrinkderizhixieyaodadeduo

  

  Shrinkdejidianwenti

  a. shrinkhouindexshifouxuyaorebuild

  yinweishrinkdecaozuoyehuigaibianxingshujuderowidnameruguotableshangyouindexshishrink tablehouindexhuibuhuibianweiUNUSABLEnewomenlaikanzheyangdeshiyantongyanggoujianmy_objectsdeceshibiao

  create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;

  create index i_my_objects on my_objects (object_id);

  delete from my_objects where object_name like '%C%';

  delete from my_objects where object_name like '%U%';

  xianzaiwomenlaishrink table my_objects

  SQL> alter table my_objects enable row movement;

  Table altered

  SQL> alter table my_objects shrink space;

  Table altered

  SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';

  INDEX_NAME STATUS

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

  I_MY_OBJECTS VALID

  womenfaxiantable my_objectsshangdeindexdezhuangtaiweiVALIDgujishrinkzaiyidongxingshujushiyeyiqiweihuleindexshangxiangyingxingdeshujurowiddexinxiwomenrenweizheshiduiyumovecaozuohouxuyaorebuild indexdegaijindanshiruguoyigetableshangdeindexshuliangjiaoduowomenzhidaoweihuindexdechengbenshibijiaogaodeshrinkguochengzhongyonglaiweihuindexdechengbenyehuibijiaogao

  b. shrinkshiduitabledelock

  zaiduitablejinxingshrinkshihuiduitablejinxingzenyangdesuodingnedangwomenduitable MY_OBJECTSjinxingshrinkcaozuoshichaxunv$locked_objectsshitukeyifaxiantable MY_OBJECTSshangjialerow-X (SX) delock

  SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;

  OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE

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

  55422 153 DLINGER 3

  SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';

  OBJECT_ID

  ----------

  55422

  namedangtablezaijinxingshrinkshiwomenduitableshikeyijinxingDMLcaozuode

  c. shrinkduikongjiandeyaoqiu

  womenzaiqianmiantaolunleshrinkdeshujudeyidongjizhijiranoracleshiconghouxiangqianyidongxingshujunameshrinkdecaozuojiubuhuixiangmoveyiyangshrinkbuxuyao使shiyongewaidekongxiankongjian原文
 
 
从10g开始,oracle开始提供Shrink的命令,假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段,即降低HWM。这里需要强调一点,10g的这个新特性,仅对ASSM表空间有效,否则会报 ORA-10635: Invalid segment or tablespace type。 在这里,我们来讨论如和对一个ASSM的segment回收浪费的空间。 同样,我们用系统视图all_objects来在tablespace ASSM上创建测试表my_objects,这一小节的内容,实验环境为oracle10.1.0.2: SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production CORE 10.1.0.2.0 Production TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 – Production SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT, 2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT 3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM'; TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT ---------------- ---------- ----------------- --------------- ------------------------ ASSM 8192 LOCAL UNIFORM AUTO SQL> create table my_objects tablespace assm 2 as select * from all_objects; Table created 然后我们随机地从table MY_OBJECTS中删除一部分数据: SQL> select count(*) from my_objects; COUNT(*) ---------- 47828 SQL> delete from my_objects where object_name like '%C%'; 16950 rows deleted SQL> delete from my_objects where object_name like '%U%'; 4503 rows deleted SQL> delete from my_objects where object_name like '%A%'; 6739 rows deleted 现在我们使用show_space和show_space_assm来看看my_objects的数据存储状况: SQL> exec show_space('MY_OBJECTS','DLINGER'); Total Blocks............................680 Total Bytes.............................5570560 Unused Blocks...........................1 Unused Bytes............................8192 Last Used Ext FileId....................6 Last Used Ext BlockId...................793 Last Used Block.........................4 PL/SQL 过程已成功完成。 SQL> exec show_space_assm('MY_OBJECTS','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............205 free space 50-75% Blocks:...............180 free space 75-100% Blocks:..............229 Full Blocks:............................45 Unformatted blocks:.....................0 PL/SQL 过程已成功完成。 这里,table my_objects的HWM下有679个block,其中,free space为25-50%的block有205个,free space为50-75%的block有180个,free space为75-100%的block有229个,full space的block只有45个,这种情况下,我们需要对这个table的现有数据行进行重组。 要使用assm上的shink,首先我们需要使该表支持行移动,可以用这样的命令来完成: alter table my_objects enable row movement; 现在,就可以来降低my_objects的HWM,回收空间了,使用命令: alter table bookings shrink space; 我们具体的看一下实验的结果: SQL> alter table my_objects enable row movement; 表已更改。 SQL> alter table my_objects shrink space; 表已更改。 SQL> exec show_space('MY_OBJECTS','DLINGER'); Total Blocks............................265 Total Bytes.............................2170880 Unused Blocks...........................2 Unused Bytes............................16384 Last Used Ext FileId....................6 Last Used Ext BlockId...................308 Last Used Block.........................3 PL/SQL 过程已成功完成。 SQL> exec show_space_assm('MY_OBJECTS','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............0 free space 75-100% Blocks:..............0 Full Blocks:............................249 Unformatted blocks:.....................0 PL/SQL 过程已成功完成。 在执行玩shrink命令后,我们可以看到,table my_objects的HWM现在降到了264的位置,而且HWM下的block的空间使用状况,full space的block有249个,free space 为25-50% Block只有1个。 我们接下来讨论一下shrink的实现机制,我们同样使用讨论move机制的那个实验来观察。 SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM; Table created 往table test_hwm中插入如下的数据: insert into TEST_HWM values (1,'aa'); insert into TEST_HWM values (2,'bb'); insert into TEST_HWM values (2,'cc'); insert into TEST_HWM values (3,'dd'); insert into TEST_HWM values (4,'ds'); insert into TEST_HWM values (5,'dss'); insert into TEST_HWM values (6,'dss'); insert into TEST_HWM values (7,'ess'); insert into TEST_HWM values (8,'es'); insert into TEST_HWM values (9,'es'); insert into TEST_HWM values (10,'es'); 我们来看看这个table的rowid和block的ID和信息: SQL> select rowid , id,name from TEST_HWM; ROWID ID NAME ------------------ ---------- ----- --------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFHAAB 2 bb AAANhqAAGAAAAFHAAC 2 cc AAANhqAAGAAAAFIAAA 3 dd AAANhqAAGAAAAFIAAB 4 ds AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss AAANhqAAGAAAAFJAAB 7 ess AAANhqAAGAAAAFJAAC 8 es AAANhqAAGAAAAFKAAA 9 es AAANhqAAGAAAAFKAAB 10 es 11 rows selected SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ; EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 然后从table test_hwm中删除一些数据: delete from TEST_HWM where id = 2; delete from TEST_HWM where id = 4; delete from TEST_HWM where id = 3; delete from TEST_HWM where id = 7; delete from TEST_HWM where id = 8; 观察table test_hwm的rowid和blockid的信息: SQL> select rowid , id,name from TEST_HWM; ROWID ID NAME ------------------ ---------- ----- -------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss AAANhqAAGAAAAFKAAA 9 es AAANhqAAGAAAAFKAAB 10 es SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ; EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 从以上的信息,我们可以看到,在table test_hwm中,剩下的数据是分布在AAAAFH,AAAAFI,AAAAFJ,AAAAFK这样四个连续的block中。 SQL> exec show_space_assm('TEST_HWM','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............3 free space 75-100% Blocks:..............3 Full Blocks:............................0 Unformatted blocks:.....................0 通过show_space_assm我们可以看到目前这四个block的空间使用状况,AAAAFH,AAAAFI,AAAAFJ上各有一行数据,我们猜测free space为50-75%的3个block是这三个block,那么free space为25-50%的1个block就是AAAAFK了,剩下free space为 75-100% 的3个block,是HWM下已格式化的尚未使用的block。(关于assm下hwm的移动我们前面已经详细地讨论过了,在extent不大于于16个block时,是以一个extent为单位来移动的) 然后,我们对table my_objects执行shtink的操作: SQL> alter table test_hwm enable row movement; Table altered SQL> alter table test_hwm shrink space; Table altered SQL> select rowid ,id,name from TEST_HWM; ROWID ID NAME ------------------ ---------- ------ ----------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFHAAB 10 es AAANhqAAGAAAAFHAAD 9 es AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ; EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 当执行了shrink操作后,有意思的现象出现了。我们来看看oracle是如何移动行数据的,这里的情况和move已经不太一样了。我们知道,在move操作的时候,所有行的rowid都发生了变化,table所位于的block的区域也发生了变化,但是所有行物理存储的顺序都没有发生变化,所以我们得到的结论是,oracle以block为单位,进行了block间的数据copy。那么shrink后,我们发现,部分行数据的rowid发生了变化,同时,部分行数据的物理存储的顺序也发生了变化,而table所位于的block的区域却没有变化,这就说明,shrink只移动了table其中一部分的行数据,来完成释放空间,而且,这个过程是在table当前所使用的block中完成的。 那么Oracle具体移动行数据的过程是怎样的呢?我们根据这样的实验结果,可以来猜测一下: Oracle是以行为单位来移动数据的。Oracle从当前table存储的最后一行数据开始移动,从当前table最先使用的block开始搜索空间,所以,shrink之前,rownum=10的那行数据(10,es),被移动到block AAAAFH上,写到(1,aa)这行数据的后面,所以(10,es)的rownum和rowid同时发生改变。然后是(9,es)这行数据,重复上述过程。这是oracle从后向前移动行数据的大致遵循的规则,那么具体移动行数据的的算法是比较复杂的,包括向ASSM的table中insert数据使用block的顺序的算法也是比较复杂的,大家有兴趣的可以自己来研究,在这里我们不多做讨论。 我们还可以在shrink table的同时shrink这个table上的index: alter table my_objects shrink space cascade; 同样地,这个操作只有当table上的index也是ASSM时,才能使用。 关于日志的问题,我们对比了同样数据量和分布状况的两张table,在move和shrink下生成的redo size(table上没有index的情况下): SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces 2 where tablespace_name in('ASSM','HWM'); TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT ------------------------------ ------------------------ ASSM AUTO HWM MANUAL SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum<20000; Table created SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000; Table created SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS'; BYTES/1024/1024 --------------- 2.1875 SQL> delete from my_objects where object_name like '%C%'; 7278 rows deleted SQL> delete from my_objects1 where object_name like '%C%'; 7278 rows deleted SQL> delete from my_objects where object_name like '%U%'; 2732 rows deleted SQL> delete from my_objects1 where object_name like '%U%'; 2732 rows deleted SQL> commit; Commit complete SQL> alter table my_objects enable row movement; Table altered SQL> select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE ---------- 27808792 SQL> alter table my_objects shrink space; Table altered SQL> select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE ---------- 32579712 SQL> alter table my_objects1 move; Table altered SQL> select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE ---------- 32676784 对于table my_objects,进行shrink,产生了32579712 – 27808792=4770920,约4.5M的redo ;对table my_objects1进行move,产生了32676784-32579712= 97072,约95K的redo size。那么,与move比较起来,shrink的日志写要大得多。 Shrink的几点问题: a. shrink后index是否需要rebuild: 因为shrink的操作也会改变行数据的rowid,那么,如果table上有index时,shrink table后index会不会变为UNUSABLE呢?我们来看这样的实验,同样构建my_objects的测试表: create table my_objects tablespace ASSM as select * from all_objects where rownum<20000; create index i_my_objects on my_objects (object_id); delete from my_objects where object_name like '%C%'; delete from my_objects where object_name like '%U%'; 现在我们来shrink table my_objects: SQL> alter table my_objects enable row movement; Table altered SQL> alter table my_objects shrink space; Table altered SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS'; INDEX_NAME STATUS ------------------------------ -------- I_MY_OBJECTS VALID 我们发现,table my_objects上的index的状态为VALID,估计shrink在移动行数据时,也一起维护了index上相应行的数据rowid的信息。我们认为,这是对于move操作后需要rebuild index的改进。但是如果一个table上的index数量较多,我们知道,维护index的成本是比较高的,shrink过程中用来维护index的成本也会比较高。 b. shrink时对table的lock 在对table进行shrink时,会对table进行怎样的锁定呢?当我们对table MY_OBJECTS进行shrink操作时,查询v$locked_objects视图可以发现,table MY_OBJECTS上加了row-X (SX) 的lock: SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects; OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE ---------- ---------- ------------------ ----------- 55422 153 DLINGER 3 SQL> select object_id from user_objects where object_name = 'MY_OBJECTS'; OBJECT_ID ---------- 55422 那么,当table在进行shrink时,我们对table是可以进行DML操作的。 c. shrink对空间的要求 我们在前面讨论了shrink的数据的移动机制,既然oracle是从后向前移动行数据,那么,shrink的操作就不会像move一样,shrink不需要使用额外的空闲空间。
󰈣󰈤
 
 
>>返回首页<<
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 
 热帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
©2005- 王朝网络 版权所有