<p>author：skate</p>
<p>time：2010-05-31</p>
<hr>
<p>我们在使用windows的时候，都知道要定期整理磁盘碎片，因为磁盘碎片会影响性能，给管理上带来额外的<br>
负担。那oracle更是如此，当随着数据增加，oracle处理海量数据本身就已经很费力了啊，如果再有大量<br>
的碎片，那就是雪上加霜啊。所以碎片要引起dba的高度重视，尽早发现尽早处理。</p>
<p><span style="color: #008000;">碎片是怎么产生的呢？</span>
</p>
<p><br>
简单理解就是由于更新和删除产生一些碎小的不能被再次使用的空间，根据每种不同的碎片他们的产生也是有区别的</p>
<p><span style="color: #008000;">block-level的碎片</span>
，而block又分为data block和index block，在data block中存放的是row数据，在index block<br>
中存放的是索引键值数据，所以按上面所说，block-level碎片有细分为<span style="color: #008000;">row-level碎片</span>
和<span style="color: #008000;">Index Leaf Block-level碎片</span>
。</p>
<p><br>
oracle的每一个对象都是存储在segment中，而oracle的最小分配单位是extents（区），在数据更新删除中也会产生碎片<br>
这一级别的碎片就是<span style="color: #008000;">segment碎片</span>
。segment又存在datafile中，而tablespace又是包含datafile的逻辑概念。所以这一层<br>
是<span style="color: #008000;">tablespace-level碎片</span>
；tablespace是在disk上存储，所以这一层就是<span style="color: #008000;">disk-level碎片</span>
。</p>
<p>简单图示如下</p>
<p><br><span style="color: #008000;">disk-level fragmention<br>
tablespace-level fragmentation<br>
segment-level fragmentation<br>
block-level fragmentation<br>
row-level fragmentation<br>
index leaf block-level fragmentation</span>
</p>
<p><strong>顺便提下oracle extents存在的理由</strong>
</p>
<p>一个extents是由多个相连的block组成的，多个extents做成一个segment；extent是oracle的最小分配单位</p>
<p><span style="color: #008000;">extent的优点：<br></span>
1. 提高空间分配，释放的效率，降低管理block的资源成本<br>
2. 提高扫描的效率，因为extent是由相连blocks做成的特性，可以一次读取更多的内容，较低io读写次数</p>
<p><span style="color: #008000;">extent的缺点</span>
<br>
容易产生碎片</p>
<p><br><span style="color: #008000;"><strong>如何确定产生了碎片的呢？</strong>
</span>
</p>
<p><span style="color: #008000;">一。表空间碎片确定参考</span>
</p>
<p>由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用 FSFI--Free Space Fragmentation Index <br>
(自由空间碎片索引)值来直观体现：</p>
<p> FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents))) </p>
<p>可以看出，FSFI 的最大可能值为 100 (一个理想的单文件表空间)。随着范围的增加， FSFI 值缓慢下降，而随着最大范<br>
围尺寸的减少，FSFI 值会迅速下降。通过如下语句查询FSFI：</p>
<p>select tablespace_name,<br>
sqrt(max(blocks) / sum(blocks)) * (100 / sqrt(sqrt(count(blocks)))) FSFI<br>
from dba_free_space<br>
group by tablespace_name<br>
order by 1;</p>
<p>在一个有着足够有效自由空间，且FSFI 值超过30的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近<br>
可比参数时，就需要做碎片整理了（DMT空间可以整理，如果是LMT就无法整理）。（SMON 会将相邻的自由范围自动合并）</p>
<p>temp表空间（非本地管理表空间） 回收 <br>
可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如： <br>
alter tablespace temp default storage(pctincrease 1);</p>
<p>这样smon便会将自由范围自动合并。也可以手工合并自由范围： <br>
alter tablespace temp coalesce; </p>
<p><span style="color: #008000;">二。segment的碎片整理</span>
</p>
<p>segment的碎片整理一般主要整理table和index</p>
<p><span style="color: #008000;">9i之前碎片整理的方法</span>
</p>
<p><strong>1. exp/truncate/imp</strong>
</p>
<p><strong>2.<br>
alter table table_name move stroage(initial 1m)<br>
alter index index_name rebuild [tablespace tablespace_name] [nologging] [online]</strong>
</p>
<p><strong></strong>
</p>
<p><strong>3.CTAS重组</strong>
</p>
<p>在10g之后，不但可以用以上两种方法，还提供了新的方法</p>
<p>alter table <table_name> shrink space [ <null> | compact | cascade ];</p>
<p><span style="color: #008000;">compact</span>
:这个参数当系统的负载比较大时可以用，不降低HWM。如果系统负载较低时，直接用alter table table_name shrink space就一步到位了<br><span style="color: #008000;">cascade</span>
：这个参数是在shrink table的时候自动级联索引，相当于rebulid index。</p>
<p><span style="color: #008000;">普通表</span>
</p>
<p>shrink必须开启行迁移功能。<br>
alter table table_name enable row movement ;</p>
<p>保持HWM，相当于把块中数据打结实了<br>
alter table table_name shrink space compact;</p>
<p>回缩表与降低HWM<br>
alter table table_name shrink space;</p>
<p>回缩表与相关索引，降低HWM<br>
alter table table_name shrink space cascade;</p>
<p>回缩索引与降低HWM<br>
alter index index_name shrink space</p>
<p>虽然在10g中可以用shrink ，但也有些限制：</p>
<p>1). 对cluster，cluster table，或具有Long，lob类型列的对象 不起作用。<br>
2). 不支持具有function-based indexes 或 bitmap join indexes的表<br>
3). 不支持mapping 表或index-organized表。<br>
4). 不支持compressed 表</p>
<p>参考文档：<br><a href=""></a>
</p>
<p><strong>从9i开始index碎片的整理除了drop and create index，还可以 alter index skate.t1_idx rebuild nologging parallel 4 online ;</strong>
</p>
<p><br><span style="color: #008000;">表的碎片查找</span>
</p>
<p>一种笨的方式就是ctas一个表和原来的做下比较，看看空间大小的变化，还有一种就方法就是看视图里数据（要及时analyze才准确）<br>
查看user_tables和user_segments</p>
<p>eg:</p>
<p>SQL> select num_rows,blocks,empty_blocks,avg_space,avg_row_len,last_analyzed from user_tables where table_name='TEST1';</p>
<p> NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED<br>
---------- ---------- ------------ ---------- ----------- -------------<br>
393216 562 78 23 13 2010-5-27 21:</p>
<p><br><span style="color: #008000;">AVG_SPACE:</span>
表示block内的空闲空间大小，根据
pctfree参数来参考，这里以默认%10为准，在block=8192里，允许空间的空间是8192 ×
%10=819.2，也就是说在block剩余空间是819.2时，oracle就认为它是满块了，如果再有数据就要寻找新的block了，当表的
AVG_SPACE远远大于819.2时，就说明有碎片了，因为块没有被充分利用，产生block-level碎片。可以通过alter table
move来整理。</p>
<p><span style="color: #008000;">那可以节省多少空间呢？</span>
<br><br>
数据的实际大小=AVG_ROW_LEN × NUM_ROWS=13 × 393216=5111808<br>
表的实际大小 =BLOCKS × block_size =562 * 8192 =4603904<br></p>
<p>理论上AVG_ROW_LEN × NUM_ROWS <= BLOCKS × block_size 如果相差比较大，那就需要整理，也可以粗略的算下可以节省多少空间，可现在实际上确<br><strong><span style="color: #ff0000;">AVG_ROW_LEN × NUM_ROWS > BLOCKS × block_size ,不知道的是什么原因？</span>
</strong>
</p>
<p><strong></strong>
</p>
<p>突然想到我这个test1表里最后一个字段是clob类型的，而clob在数据大于4k时（一个lob字段包括lobindex和lobsegment,），是存储在另外的log segment中的。</p>
<p><br>
而clob类型的d字段没有存储任何数据，查询user_lobs视图</p>
<p>SQL> select ul.table_name,ul.segment_name, ul.chunk,se.blocks,se.bytes from user_lobs ul ,user_segments se<br>
2 where ul.segment_name=se.segment_name<br>
3 and ul.table_name='TEST1'<br>
4 ;</p>
<p>TABLE_NAME SEGMENT_NAME CHUNK BLOCKS BYTES<br>
------------------------------ ------------------------------ ---------- ---------- ----------<br>
TEST1 SYS_LOB0000183465C00004$$ 8192 8 65536</p>
<p><span style="color: #ff0000;">可这个65536和那两个差值还差很多，再说这8个block还是empty，搞不懂了，谁知道请指点，先谢谢了啊</span>
</p>
<p><br>
换个角度会什么样的？我又用CTAS穿件表test4，这回就符合理论值，无论用</p>
<p><br>
alter table table_name move,还是alter table table_name shrink space compact，都是符合理论值的</p>
<p>create table test4 as select * from test1</p>
<p><br>
分析后： analyze table test4 compute statistics</p>
<p>SQL> select num_rows,blocks,empty_blocks,avg_space,avg_row_len,last_analyzed from user_tables where table_name='TEST4';</p>
<p> NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED<br>
---------- ---------- ------------ ---------- ----------- -------------<br>
393216 802 6 820 12 2010-5-28 12:</p>
<p><br>
数据的实际大小=AVG_ROW_LEN × NUM_ROWS=13 × 393216=5111808<br>
表的实际大小 =BLOCKS × block_size =562 * 8192 =6569984</p>
<p><span style="color: #008000;"><strong>index碎片查找</strong>
</span>
</p>
<p>识别索引是否有碎片</p>
<p>获得关于索引的信息，用下面的命令</p>
<p><br>
analyze index index_name validate structure 或validate index index_name</p>
<p>analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的<br>
数据（存放在index_stats）來判断索引是否需要重新建立。</p>
<p><br>
运行命令后，然后在视图 index_stats查询，这个视图记录索引的一些信息，这个视图只有一个记录，所以在同一时间只能分析一个索引。</p>
<p><span style="color: #008000;">1.删除的行数如占总的行数的30%，即del_lf_rows / lf_rows > 0.3，那就考虑索引碎片整理<br>
2.如果”hight“大于4，可以考虑碎片整理<br>
3.如果索引的行数（LF_rows）远远小于‘LF_BLKS’ ，那就说明有了一个大的删除动作，需要整理碎片</span>
</p>
<p>索引碎片整理方法</p>
<p><span style="color: #008000;">1. recreate index<br>
2. alter index skate.idx_test rebuild nologging parallel 4 online ;<br>
3. 如果叶块是half-empty or near empty，可以运行“alter index coalesce”来合并</span>
</p>
<p>参考：</p>
<p><a href=""></a>
</p>
<p>-----end-----</p>
<p><span style="color: #008000;"><strong>索引页块碎片（Index Leaf Block Fragmentation）</strong>
</span>
</p>
<p>这篇文章将会回答如下问题：</p>
<p>什么是索引页块碎片？什么时候被重用？<br>
什么是半空索引碎片？什么时候被重用？</p>
<p>oracle的标准索引结构是B×tree结构，一个B×tree结构由三种block组成</p>
<p><span style="color: #008000;">根块(root block):</span>
在B×tree里有且只有一个block，所有访问索引都从这开始，root block下有很多child blocks。</p>
<p><br><span style="color: #008000;">分支块（Branch blocks）:</span>
这是中间层，branch block是没有什么限制的，它是随着leaf block的增加而增加的，branch block一般是4层，如果多于4层，就影响性能了。在我们删除行时，branch block是不被删除的。</p>
<p><br><span style="color: #008000;">叶块（leaf block）：</span>
叶块是最底层，上面存储着索引条目和rowid</p>
<p>索引和表数据是级联关系的，当删除表数据的时候，索引条目也会被自动删除，这样在index leaf<br>
block就会产生碎片，这也就是在OLTP系统上有大量更新的表上不建议创建大量的索引，很影响性能<br>
有的人说删除过的索引条目空间不会被再用，因为在应用中不会再有insert相同的数据。其实这个<br>
说法不完全对的，除了半空叶块外，其他的删除的索引空间是可被再利用的。</p>
<p>eg：</p>
<p>本文的所有实验都是在如下平台测试：</p>
<p>SQL> select * from v$version;</p>
<p>BANNER<br>
----------------------------------------------------------------<br>
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod<br>
PL/SQL Release 10.2.0.4.0 - Production<br>
CORE 10.2.0.4.0 Production<br>
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production<br>
NLSRTL Version 10.2.0.4.0 - Production</p>
<p>SQL></p>
<p>SQL> create table test_idx as select seq_test.nextval id,2000 syear, a.* from d<br>
ba_objects a;</p>
<p>表已创建。</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2001 syear, a.* from dba_o<br>
bjects a;</p>
<p>已创建50780行。</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2002 syear, a.* from dba_o<br>
bjects a;</p>
<p>已创建50780行。</p>
<p>SQL> commit;</p>
<p>提交完成。</p>
<p>SQL> desc test_idx<br>
名称 是否为空? 类型<br>
----------------------------------------- -------- ----------------------------</p>
<p>ID NUMBER<br>
SYEAR NUMBER<br>
OWNER VARCHAR2(30)<br>
OBJECT_NAME VARCHAR2(128)<br>
SUBOBJECT_NAME VARCHAR2(30)<br>
OBJECT_ID NUMBER<br>
DATA_OBJECT_ID NUMBER<br>
OBJECT_TYPE VARCHAR2(19)<br>
CREATED DATE<br>
LAST_DDL_TIME DATE<br>
TIMESTAMP VARCHAR2(19)<br>
STATUS VARCHAR2(7)<br>
TEMPORARY VARCHAR2(1)<br>
GENERATED VARCHAR2(1)<br>
SECONDARY VARCHAR2(1)</p>
<p>SQL> create unique index idx_test on test_idx(syear,id) ;</p>
<p>索引已创建。</p>
<p>SQL> <br>
SQL> select segment_name , bytes/1024/1024 ,<br>
2 blocks, tablespace_name , extents<br>
3 from dba_segments<br>
4 where segment_name = 'IDX_TEST';</p>
<p>SEGMENT_NA BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS<br>
---------- --------------- ---------- ------------------------------ ----------<br>
IDX_TEST 4 512 USERS 19</p>
<p>SQL> </p>
<p>SQL> col object_name for a10<br>
SQL> <br>
SQL> select object_name, object_id, data_object_id<br>
2 from dba_objects<br>
3 where object_NAME='IDX_TEST' ;</p>
<p>OBJECT_NAM OBJECT_ID DATA_OBJECT_ID<br>
---------- ---------- --------------<br>
IDX_TEST 59545 59545 <span style="color: #ff0000;">---------Used to join X$BH table（从x$bh查询缓存blocks，要用DATA_OBJECT_ID）</span>
</p>
<p>SQL> </p>
<p>查看系统现在缓存多少，这个要用sysdba用户执行</p>
<p><br>
SQL> select count(*) from x$bh where obj=59545 ;</p>
<p> COUNT(*)<br>
----------<br>
17</p>
<p><span style="color: #008000;">查看执行计划：<br></span>
SQL> set autot trace exp<br>
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;</p>
<p>执行计划<br>
----------------------------------------------------------<br>
Plan hash value: 285868359</p>
<p>--------------------------------------------------------------------------------</p>
<p>-</p>
<p>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time<br>
|</p>
<p>--------------------------------------------------------------------------------</p>
<p>-</p>
<p>| 0 | SELECT STATEMENT | | 181K| 4613K| 102 (5)| 00:00:02<br>
|</p>
<p>|* 1 | INDEX FAST FULL SCAN| IDX_TEST | 181K| 4613K| 102 (5)| 00:00:02<br>
|</p>
<p>--------------------------------------------------------------------------------</p>
<p>-</p>
<p><br>
Predicate Information (identified by operation id):<br>
---------------------------------------------------</p>
<p> 1 - filter("SYEAR">=2000 AND "SYEAR"<=2002)</p>
<p>Note<br>
-----<br>
- dynamic sampling used for this statement</p>
<p>SQL></p>
<p><br><span style="color: #008000;">执行一次查询，让oracle缓存相应的索引block</span>
</p>
<p>SQL> set autot trace statis<br>
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;</p>
<p>已选择152340行。</p>
<p><br>
统计信息<br>
----------------------------------------------------------<br>
0 recursive calls<br>
0 db block gets<br>
10562 consistent gets<br>
411 physical reads<br>
0 redo size<br>
2964382 bytes sent via SQL*Net to client<br>
112105 bytes received via SQL*Net from client<br>
10157 SQL*Net roundtrips to/from client<br>
0 sorts (memory)<br>
0 sorts (disk)<br>
152340 rows processed</p>
<p>SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;</p>
<p>已选择152340行。</p>
<p><br>
统计信息<br>
----------------------------------------------------------<br>
0 recursive calls<br>
0 db block gets<br>
10562 consistent gets<br>
0 physical reads<br>
0 redo size<br>
2964382 bytes sent via SQL*Net to client<br>
112105 bytes received via SQL*Net from client<br>
10157 SQL*Net roundtrips to/from client<br>
0 sorts (memory)<br>
0 sorts (disk)<br>
152340 rows processed</p>
<p>SQL></p>
<p><br><span style="color: #008000;">这个时候再看看oracle缓存了多少</span>
</p>
<p>SQL> select count(*) from x$bh where obj=59545 ;</p>
<p> COUNT(*)<br>
----------<br>
438</p>
<p><span style="color: #008000;">由原来的17增加到438</span>
</p>
<p>SQL> analyze index idx_test validate structure;</p>
<p>索引已分析</p>
<p>SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows<br>
om index_stats;</p>
<p> HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS<br>
---------- ---------- ---------- ---------- ---------- ---------- -----------<br>
2 512 418 152340 1 417 0</p>
<p>SQL></p>
<p><span style="color: #008000;">这个索引idx_test共有418个叶块都已经被缓存里了，和预期的是一样的，下面删除三分之一的数据</span>
</p>
<p>SQL> delete from test_idx where syear=2001;</p>
<p>已删除50780行。</p>
<p>SQL> commit;</p>
<p>提交完成。</p>
<p>SQL></p>
<p><span style="color: #008000;">清空数据缓存</span>
</p>
<p>SQL> alter system flush buffer_cache;</p>
<p>系统已更改。</p>
<p>SQL> alter system flush buffer_cache;</p>
<p>系统已更改。</p>
<p>SQL> alter system flush buffer_cache;</p>
<p>系统已更改。</p>
<p><span style="color: #008000;">再次查询，发现缓存数有所下降了，从438到396</span>
</p>
<p><br>
SQL> select count(*) from x$bh where obj=59545 ;</p>
<p> COUNT(*)<br>
----------<br>
396</p>
<p><span style="color: #008000;">再次执行查询，让其缓存索引块</span>
</p>
<p><br>
SQL> set autot trace stat<br>
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;</p>
<p>已选择101560行。</p>
<p><br>
统计信息<br>
----------------------------------------------------------<br>
0 recursive calls<br>
0 db block gets<br>
7186 consistent gets<br>
425 physical reads<br>
0 redo size<br>
1976416 bytes sent via SQL*Net to client<br>
74870 bytes received via SQL*Net from client<br>
6772 SQL*Net roundtrips to/from client<br>
0 sorts (memory)<br>
0 sorts (disk)<br>
101560 rows processed</p>
<p>SQL></p>
<p><span style="color: #008000;">这次查询缓存的数量发现突然增加很多，从438增加到774</span>
<br>
SQL> select count(*) from x$bh where obj=59545 ;</p>
<p> COUNT(*)<br>
----------<br>
774</p>
<p>突然增加这么多，推测是因为删除的那些空索引块需要重新从磁盘加载到buffer cache中，所以<br>
缓存的会突然增加，用alter system flush buffer_cache不能完全清楚data cache，下面我reboot<br>
数据库，再来查看下</p>
<p><span style="color: #008000;">重启数据库是为了完全清空缓存的索引</span>
</p>
<p>SQL> shutdown immediate;<br>
数据库已经关闭。<br>
已经卸载数据库。<br>
ORACLE 例程已经关闭。</p>
<p>SQL> startup<br>
ORACLE 例程已经启动。<br>
Total System Global Area 574619648 bytes<br>
Fixed Size 1297944 bytes<br>
Variable Size 192938472 bytes<br>
Database Buffers 373293056 bytes<br>
Redo Buffers 7090176 bytes<br>
数据库装载完毕。<br>
数据库已经打开。</p>
<p><span style="color: #008000;">执行查询，使索引缓存</span>
<br>
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;</p>
<p>已选择101560行。</p>
<p><span style="color: #008000;">再来看缓存的多少<br></span>
SQL> select count(*) from x$bh where obj=59545 ;</p>
<p> COUNT(*)<br>
----------<br>
425</p>
<p><span style="color: #008000;">我可以从查询结果中看到，缓存结果425和删除前的438，没有太大的变化，而我删除了三分之一的<br>
数据，按理论说应该缓存的表很少了啊，我们在查看现在的叶块是多少</span>
</p>
<p>SQL> analyze index idx_test validate structure;</p>
<p>索引已分析</p>
<p>SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr<br>
om index_stats;</p>
<p> HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS<br>
---------- ---------- ---------- ---------- ---------- ---------- -----------<br>
2 512 418 152340 1 417 50780</p>
<p>SQL></p>
<p><span style="color: #008000;">从结果来看，叶块和删除前一样418没有变化，这就进一步证明索引叶block虽然被删除了，但是并没有<br>
释放空间，而查询语句并不会跳过这些删除的索引块，所以这些碎片对性能产生很多的影响。</span>
</p>
<p><span style="color: #008000;"><strong>那如何完全删除索引叶块呢？</strong>
</span>
</p>
<p><br>
SQL> alter index idx_test rebuild nologging online;</p>
<p>索引已更改。</p>
<p>SQL> analyze index idx_test validate structure;</p>
<p>索引已分析</p>
<p>SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr<br>
om index_stats;</p>
<p> HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS<br>
---------- ---------- ---------- ---------- ---------- ---------- -----------<br>
2 384 276 101560 1 275 0</p>
<p>SQL></p>
<p><br>
SQL> select count(*) from x$bh where obj=59545 ;</p>
<p> COUNT(*)<br>
----------<br>
139</p>
<p>SQL></p>
<p><span style="color: #008000;">通过以上结果可以看到删除的索引叶块的空间被释放了</span>
</p>
<p><span style="color: #008000;">在删除了2001年后 在insert2003年的</span>
</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2003 syear, a.* from dba_<br>
objects a;</p>
<p>已创建50781行。</p>
<p>SQL> commit;</p>
<p>提交完成。</p>
<p>SQL> select segment_name , bytes/1024/1024 ,<br>
2 blocks, tablespace_name , extents<br>
3 from dba_segments<br>
4 where segment_name = 'IDX_TEST';</p>
<p>SEGMENT_NAME<br>
--------------------------------------------------------------------------------</p>
<p>BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS<br>
--------------- ---------- ------------------------------ ----------<br>
IDX_TEST<br>
4 512 USERS 19</p>
<p><br>
SQL> analyze index idx_test validate structure;</p>
<p>索引已分析</p>
<p>SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr<br>
om index_stats;</p>
<p> HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS<br>
---------- ---------- ---------- ---------- ---------- ---------- -----------<br>
2 512 403 152341 1 402 0</p>
<p>SQL></p>
<p><span style="color: #008000;"><strong>从查询结果来看，索引的总的块数为512，在delete和insert后没有增长，说明索引删除的空间<br>
被重用了啊</strong>
</span>
</p>
<p><br><strong><span style="color: #008000;">什么是半空叶块（Half Empty Leaf Blocks）</span>
</strong>
</p>
<p>一个叶块（ Leaf Block）是用索引键值初始化的，当某些键值被删除后，这个叶块即包含删除的<br>
索引键值，也包含未删除的索引键值，这时这个块就被称为”Half Empty Leaf Blocks“。</p>
<p>下面还是以test_idx为例</p>
<p><br>
SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_<br>
objects a ;</p>
<p>已创建50781行。</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_<br>
objects a ;</p>
<p>已创建50781行。</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_<br>
objects a ;</p>
<p>已创建50781行。</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_<br>
objects a ;</p>
<p>已创建50781行。</p>
<p>SQL> commit;</p>
<p>提交完成。</p>
<p>SQL> select segment_name , bytes/1024/1024 ,<br>
2 blocks, tablespace_name , extents<br>
3 from dba_segments<br>
4 where segment_name = 'IDX_TEST';</p>
<p>SEGMENT_NAME<br>
--------------------------------------------------------------------------------</p>
<p>BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS<br>
--------------- ---------- ------------------------------ ----------<br>
IDX_TEST<br>
9 1152 USERS 24</p>
<p><br>
SQL> delete from test_idx where syear=2005 and mod(id,2)=0;</p>
<p>已删除101562行。</p>
<p>SQL> commit;</p>
<p>提交完成。</p>
<p><span style="color: #008000;">在重新插入101562行数据</span>
</p>
<p><br>
SQL> insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_<br>
objects a ;</p>
<p>已创建50781行。</p>
<p>SQL> insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_<br>
objects a ;</p>
<p>已创建50781行。</p>
<p>SQL> select segment_name , bytes/1024/1024 ,<br>
2 blocks, tablespace_name , extents<br>
3 from dba_segments<br>
4 where segment_name = 'IDX_TEST';</p>
<p>SEGMENT_NAME<br>
--------------------------------------------------------------------------------</p>
<p>BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS<br>
--------------- ---------- ------------------------------ ----------<br>
IDX_TEST<br>
11 1408 USERS 26</p>
<p><br>
SQL></p>
<p>删除了101562行数据，再重新添加101562行数据，可索引块却增加了1408-1152=256个数据块，所以说半空块<br>
索引并没有被重用。从下面的trace也可以看出</p>
<p>SQL> select object_id from dba_objects where object_name='IDX_TEST';</p>
<p>OBJECT_ID<br>
----------<br>
59545</p>
<p>得到tree的dump的命令如下</p>
<p><br>
SQL> alter session set events 'immediate trace name treedump level 59545';</p>
<p>会话已更改。</p>
<p><span style="color: #008000;">然后查看对应的trace文件，如下所示：</span>
</p>
<p>branch: 0x100972c 16815916 (0: nrow: 3, level: 2)<br>
branch: 0x1007fe5 16809957 (-1: nrow: 511, level: 1)<br>
leaf: 0x100972d 16815917 (-1: nrow: 378 rrow: 378)<br>
leaf: 0x100972e 16815918 (0: nrow: 378 rrow: 378)<br>
.<br>
.<br>
.<br>
leaf: 0x1007ff2 16809970 (14: nrow: 400 rrow: 400)<br>
leaf: 0x1007ff6 16809974 (15: nrow: 400 rrow: 332)<br>
leaf: 0x1007ffa 16809978 (16: nrow: 400 rrow: 200)<br>
leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200<span style="color: #008000;">)----------------- </span>
<span style="color: #008000;">Half empty blocks<br></span>
leaf: 0x1008002 16809986 (18: nrow: 400 rrow: 200)<br>
.<br>
.<br>
.<br>
leaf: 0x1009f86 16818054 (19: nrow: 400 rrow: 200)<br>
leaf: 0x1009f4b 16817995 (20: nrow: 400 rrow: 400)<br>
.<br>
.<br>
.<br>
leaf: 0x1009f4f 16817999 (21: nrow: 400 rrow: 400)<br>
leaf: 0x100a15f 16818527 (274: nrow: 56 rrow: 56)</p>
<p><span style="color: #008000;">leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)<br>
解释： leaf block包含400行，这个块已经删除了200行的键值</span>
</p>
<p><span style="color: #008000;"><strong>识别索引是否有碎片</strong>
</span>
</p>
<p><span style="color: #008000;">获得关于索引的信息，用下面的命令</span>
</p>
<p><br>
analyze index index_name validate structure 或validate index index_name</p>
<p>analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的<br>
数据（存放在index_stats）來判断索引是否需要重新建立。</p>
<p><br>
运行命令后，然后在视图 index_stats查询，这个视图记录索引的一些信息，这个视图只有一个记录，所以在同一时间<br>
只能分析一个索引。</p>
<p><strong>1.删除的行数如占总的行数的30%，即del_lf_rows / lf_rows > 0.3，那就考虑索引碎片整理<br>
2.如果”hight“大于4，可以考虑碎片整理<br>
3.如果索引的行数（LF_rows）远远小于‘LF_BLKS’ ，那就说明有了一个大的删除动作，需要整理碎片</strong>
</p>
<p><span style="color: #008000;">索引碎片整理方法</span>
</p>
<p><strong>1. recreate index<br>
2. alter index skate.idx_test rebuild nologging parallel 4 online ;<br>
3. 如果叶块是half-empty or near empty，可以运行“alter index coalesce”来合并</strong>
</p>
<p><br><span style="color: #008000;">参考文档：</span>
</p>
<p><a href="http://oracleact.com/papers/index_leaf_block_frag.html">http://oracleact.com/papers/index_leaf_block_frag.html</a>
<br><a href="http://www.devx.com/gethelpon/10MinuteSolution/16596/0/page/2">http://www.devx.com/gethelpon/10MinuteSolution/16596/0/page/2</a>
</p>
<p><strong><span style="color: #008000;"><span style="font-size: small;">alter table move跟shrink space的区别</span>
</span>
</strong>
</p>
<p><strong></strong>
</p>
<p><strong></strong>
</p>
<p><span style="color: #008000;">今天主要从两点说他们的区别：</span>
</p>
<p><span style="color: #008000;">1. 碎片的整理</span>
</p>
<p><span style="color: #008000;">2.空间的收缩</span>
</p>
<p>SQL> select * from v$version;</p>
<p>BANNER<br>
----------------------------------------------------------------<br>
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi<br>
PL/SQL Release 10.2.0.1.0 - Production<br>
CORE10.2.0.1.0Production</p>
<p>TNS for Linux: Version 10.2.0.1.0 - Production<br>
NLSRTL Version 10.2.0.1.0 - Production</p>
<p>SQL> </p>
<p><span style="color: #008000;">创建测试表</span>
<br>
SQL> create table test3 as<br>
2 select rownum id,<br>
3 dbms_random.string('a', round(dbms_random.value(0,10))) col1,<br>
4 trunc(sysdate) - dbms_random.value(1, 365*2) col2<br>
5 from dual connect by rownum<=10000;</p>
<p>Table created</p>
<p>SQL> select count(1) from test3;</p>
<p> COUNT(1)<br>
----------<br>
10000</p>
<p><br><span style="color: #008000;">查看表test3的blocks使用情况：</span>
</p>
<p>SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................40<br>
Total Bytes.............................327680<br>
Unused Blocks...........................3<br>
Unused Bytes............................24576<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................481921<br>
Last Used Block.........................5<br>
*************************************************<br>
0% -- 25% free space blocks.............0<br>
0% -- 25% free space bytes..............0<br>
25% -- 50% free space blocks............0<br>
25% -- 50% free space bytes.............0<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................32<br>
Total bytes.............................262144</p>
<p>PL/SQL procedure successfully completed</p>
<p><br><span style="color: #008000;">制造碎片</span>
</p>
<p>SQL> DELETE FROM TEST3 WHERE MOD(ID,3)=1;</p>
<p>3334 rows deleted</p>
<p>SQL> commit;</p>
<p>Commit complete</p>
<p><span style="color: #008000;">发现有碎片了</span>
</p>
<p>SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................40<br>
Total Bytes.............................327680<br>
Unused Blocks...........................3<br>
Unused Bytes............................24576<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................481921<br>
Last Used Block.........................5<br>
*************************************************<br>
0% -- 25% free space blocks.............0<br>
0% -- 25% free space bytes..............0<br>
25% -- 50% free space blocks............31<br>
25% -- 50% free space bytes.............253952<br>
50% -- 75% free space blocks............1<br>
50% -- 75% free space bytes.............8192<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................0<br>
Total bytes.............................0</p>
<p>PL/SQL procedure successfully completed</p>
<p>SQL> </p>
<p><span style="color: #008000;">消除碎片</span>
</p>
<p><br>
SQL> alter table test3 move;</p>
<p>Table altered</p>
<p><span style="color: #008000;">查看碎片消除的效果</span>
</p>
<p><br>
SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................32<br>
Total Bytes.............................262144<br>
Unused Blocks...........................6<br>
Unused Bytes............................49152<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................485065<br>
Last Used Block.........................2<br>
*************************************************<br>
0% -- 25% free space blocks.............0<br>
0% -- 25% free space bytes..............0<br>
25% -- 50% free space blocks............0<br>
25% -- 50% free space bytes.............0<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................22<br>
Total bytes.............................180224</p>
<p>PL/SQL procedure successfully completed</p>
<p>SQL> </p>
<p>从以上看，碎片整理的效果很好！！！</p>
<p><strong><span style="color: #008000;">下面在测试用shrink整理碎片</span>
</strong>
</p>
<p><span style="color: #008000;">重建测试环境</span>
</p>
<p><br>
SQL> drop table test3;</p>
<p>Table dropped</p>
<p>SQL> <br>
SQL> create table test3 as<br>
2 select rownum id,<br>
3 dbms_random.string('a', round(dbms_random.value(0,10))) col1,<br>
4 trunc(sysdate) - dbms_random.value(1, 365*2) col2<br>
5 from dual connect by rownum<=10000;</p>
<p>Table created</p>
<p><span style="color: #008000;">查看test3的blocks的使用</span>
</p>
<p><br>
SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................40<br>
Total Bytes.............................327680<br>
Unused Blocks...........................3<br>
Unused Bytes............................24576<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................481921<br>
Last Used Block.........................5<br>
*************************************************<br>
0% -- 25% free space blocks.............0<br>
0% -- 25% free space bytes..............0<br>
25% -- 50% free space blocks............0<br>
25% -- 50% free space bytes.............0<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................32<br>
Total bytes.............................262144</p>
<p>PL/SQL procedure successfully completed</p>
<p><span style="color: #008000;">制造碎片</span>
</p>
<p><br>
SQL> delete from test3 where mod(id,3)=1;</p>
<p>3334 rows deleted</p>
<p>SQL> commit;</p>
<p>Commit complete</p>
<p><span style="color: #008000;">查看碎片情况</span>
<br>
SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................40<br>
Total Bytes.............................327680<br>
Unused Blocks...........................3<br>
Unused Bytes............................24576<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................481921<br>
Last Used Block.........................5<br>
*************************************************<br>
0% -- 25% free space blocks.............0<br>
0% -- 25% free space bytes..............0<br>
25% -- 50% free space blocks............31<br>
25% -- 50% free space bytes.............253952<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................1<br>
Total bytes.............................8192</p>
<p>PL/SQL procedure successfully completed</p>
<p><span style="color: #008000;">用oracle10g新功能整理碎片</span>
</p>
<p><br>
SQL> alter table test3 shrink space compact cascade;</p>
<p>alter table test3 shrink space compact cascade</p>
<p><span style="color: #ff0000;">ORA-10636: ROW MOVEMENT is not enabled</span>
</p>
<p>SQL> alter table test3 enable row movement;</p>
<p>Table altered</p>
<p>SQL> alter table test3 shrink space compact cascade;</p>
<p>Table altered</p>
<p><span style="color: #008000;">再次查看碎片的情况，发现还有一些碎片，整理碎片效果不好</span>
</p>
<p><br>
SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................40<br>
Total Bytes.............................327680<br>
Unused Blocks...........................3<br>
Unused Bytes............................24576<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................481921<br>
Last Used Block.........................5<br>
*************************************************<br>
0% -- 25% free space blocks.............1<br>
0% -- 25% free space bytes..............8192<br>
25% -- 50% free space blocks............2<br>
25% -- 50% free space bytes.............16384<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........12<br>
75% -- 100% free space bytes............98304<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................17<br>
Total bytes.............................139264</p>
<p>PL/SQL procedure successfully completed</p>
<p><span style="color: #008000;">上面是没降低HWM，如果载降低HWM，看看效果</span>
</p>
<p><br>
SQL> alter table test3 shrink space cascade;</p>
<p>Table altered</p>
<p>SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................24<br>
Total Bytes.............................196608<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................481897<br>
Last Used Block.........................8<br>
*************************************************<br>
0% -- 25% free space blocks.............1<br>
0% -- 25% free space bytes..............8192<br>
25% -- 50% free space blocks............2<br>
25% -- 50% free space bytes.............16384<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................17<br>
Total bytes.............................139264</p>
<p>PL/SQL procedure successfully completed</p>
<p><span style="color: #008000;">看来用shrink space整理碎片不彻底，再来看看move的方式</span>
</p>
<p>SQL> alter table test3 move;</p>
<p>Table altered</p>
<p>SQL> exec show_space_t('TEST3','auto','table','Y');</p>
<p>Total Blocks............................32<br>
Total Bytes.............................262144<br>
Unused Blocks...........................6<br>
Unused Bytes............................49152<br>
Last Used Ext FileId....................31<br>
Last Used Ext BlockId...................485081<br>
Last Used Block.........................2<br>
*************************************************<br>
0% -- 25% free space blocks.............0<br>
0% -- 25% free space bytes..............0<br>
25% -- 50% free space blocks............0<br>
25% -- 50% free space bytes.............0<br>
50% -- 75% free space blocks............0<br>
50% -- 75% free space bytes.............0<br>
75% -- 100% free space blocks...........0<br>
75% -- 100% free space bytes............0<br>
Unused Blocks...........................0<br>
Unused Bytes............................0<br>
Total Blocks............................22<br>
Total bytes.............................180224</p>
<p>PL/SQL procedure successfully completed</p>
<p><span style="color: #008000;">效果很明显，整理的很彻底</span>
</p>
<p><strong>测试结论：</strong>
</p>
<p><strong></strong>
</p>
<p><span style="color: #008000;">虽然alter table move和shrink space，都是通过物理调整rowid来整理碎片的，但shrink space整理的不彻底，他好像不是重组，而是尽可能的合并，随意会残留一些block无法整理</span>
</p>
<p><strong>注意：</strong>
</p>
<p><span style="color: #008000;">1.再用alter table table_name move时，表相关的索引会失效，所以之后还要执行 alter index index_name rebuild online; 最后重新编译数据库所有失效的对象</span>
</p>
<p><span style="color: #008000;">2. 在用alter table table_name shrink
space cascade时，他相当于alter table table_name move和alter index index_name
rebuild online. 所以只要编译数据库失效的对象就可以</span>
</p>
<p><span style="color: #339966;"><strong>alter table move和shrink space除了碎片整理的效果有时不一样外，还有什么其他的不同呢</strong>
</span>
</p>
<p><br>
1. Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。<br>
2. shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。</p>
<p><span style="color: #008000;">下面通过实验来验证</span>
</p>
<p><br>
SQL> drop table test3;</p>
<p>Table dropped</p>
<p>SQL> <br>
SQL> create table test3 as<br>
2 select rownum id,<br>
3 dbms_random.string('a', round(dbms_random.value(0,10))) col1,<br>
4 trunc(sysdate) - dbms_random.value(1, 365*2) col2<br>
5 from dual connect by rownum<=10000;</p>
<p>Table created</p>
<p>SQL> analyze table test3 compute statistics;</p>
<p>Table analyzed</p>
<p>SQL> col segment_name for a10;<br>
SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3');</p>
<p>SEGMENT_NA EXTENTS BLOCKS<br>
---------- ---------- ----------<br>
TEST3 5 40</p>
<p>SQL> col table_name for a10;<br>
SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3');</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST3 37 3</p>
<p>SQL></p>
<p><br><span style="color: #008000;">从以上查询可以看出共分了5个extents，使用了37个blocks，这37也就是test3的HWM</span>
</p>
<p>SQL> delete from test3 where rownum<=5000;</p>
<p>5000 rows deleted</p>
<p>SQL> commit;</p>
<p>Commit complete</p>
<p>SQL> analyze table test3 compute statistics;</p>
<p>Table analyzed</p>
<p>SQL> col segment_name for a10;<br>
SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3');</p>
<p>SEGMENT_NA EXTENTS BLOCKS<br>
---------- ---------- ----------<br>
TEST3 5 40</p>
<p>SQL> col table_name for a10;<br>
SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3');</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST3 37 3</p>
<p>SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test3;</p>
<p>USED_BLOCKS<br>
-----------<br>
17</p>
<p><br><span style="color: #008000;">我们从查询中可以发现test3的HWM没有变换还是37blocks，tests总共空间为40blocks。经过删除后test3实际用的块是17个</span>
</p>
<p><br><span style="color: #008000;">下面我们用move降低下HWM</span>
</p>
<p>SQL> alter table test3 move;</p>
<p>Table altered</p>
<p>SQL> col segment_name for a10;<br>
SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3');</p>
<p>SEGMENT_NA EXTENTS BLOCKS<br>
---------- ---------- ----------<br>
TEST3 3 24</p>
<p>SQL> col table_name for a10;<br>
SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3');</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST3 37 3</p>
<p><span style="color: #008000;"><strong>user_tables里的数据没有变化，哦，原来是忘记analyze了，从这里也可以看出user_segments是oracle自动维护的。</strong>
</span>
</p>
<p>SQL> analyze table test3 compute statistics;</p>
<p>Table analyzed</p>
<p>SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3');</p>
<p>SEGMENT_NA EXTENTS BLOCKS<br>
---------- ---------- ----------<br>
TEST3 3 24</p>
<p>SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3');</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST3 20 4</p>
<p>SQL> </p>
<p><br><span style="color: #008000;">现在再来看hwm变为20了，已经降下来了啊，空间也收缩了，从40blocks降到24blocks（注意收缩到initial指定值）。<br>
但shrink space就收缩到存储数据的最小值，下面测试说明</span>
</p>
<p><span style="color: #008000;">创建测试表：</span>
</p>
<p><br>
SQL> create table test5 (id number) storage (initial 1m next 1m);</p>
<p>Table created</p>
<p><span style="color: #008000;">初始化数据</span>
</p>
<p><br>
SQL> <br>
SQL> begin<br>
2 for i in 1..100000 loop<br>
3 insert into test5 values(i);<br>
4 end loop;<br>
5 end;<br>
6 /</p>
<p>PL/SQL procedure successfully completed</p>
<p>SQL> analyze table test5 compute statistics;</p>
<p>Table analyzed</p>
<p>SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5';</p>
<p>SEGMENT_NA EXTENTS BLOCKS INIT<br>
---------- ---------- ---------- ----------<br>
TEST5 17 256 1</p>
<p>SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5';</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST5 180 76</p>
<p><span style="color: #008000;">可以从查询数据看出，test5初始化1m即128个blocks，但数据比较多，所以又按next参数要求扩展了1m空间，扩展了17个extents。<br>
这里的test5总空间大小为256个blocks，使用空间为180blocks，HWM也是180blocks</span>
</p>
<p><br>
SQL> delete from test5 where rownum<=50000;</p>
<p>50000 rows deleted</p>
<p>SQL> analyze table test5 compute statistics;</p>
<p>Table analyzed</p>
<p>SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5';</p>
<p>SEGMENT_NA EXTENTS BLOCKS INIT<br>
---------- ---------- ---------- ----------<br>
TEST5 17 256 1</p>
<p>SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5';</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST5 180 76</p>
<p><span style="color: #008000;">整理碎片，降低HWM</span>
</p>
<p><br>
SQL> alter table test5 move;</p>
<p>Table altered</p>
<p><br>
SQL> analyze table test5 compute statistics;</p>
<p>Table analyzed</p>
<p>SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5';</p>
<p>SEGMENT_NA EXTENTS BLOCKS INIT<br>
---------- ---------- ---------- ----------<br>
TEST5 16 128 1</p>
<p>SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5';</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST5 85 43</p>
<p><span style="color: #008000;">从上面的查询数据可以看出HWM已经从180降低到85，test5总大小从256blocks收缩到128个blocks（initial指定大小）。</span>
</p>
<p><br><span style="color: #008000;">下面看看用shrink space收缩空间的情况</span>
</p>
<p>SQL> alter table test5 enable row movement;</p>
<p>Table altered</p>
<p><br>
SQL> alter table test5 shrink space;</p>
<p>Table altered</p>
<p>SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5';</p>
<p>SEGMENT_NA EXTENTS BLOCKS INIT<br>
---------- ---------- ---------- ----------<br>
TEST5 11 88 1</p>
<p>SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5';</p>
<p>TABLE_NAME BLOCKS EMPTY_BLOCKS<br>
---------- ---------- ------------<br>
TEST5 85 43</p>
<p>SQL> </p>
<p><br>
从上面的数据可以看到test5进一步从128个blocks降低到88个blocks</p>
<p><span style="color: #008000;"><strong>结论：</strong>
</span>
</p>
<p><br><span style="color: #008000;">shrink space收缩到数据存储的最小值，alter table
move（不带参数）收缩到initial指定值，也可以用alter table test5 move storage(initial
500k)指定收缩的大小，这样可以达到shrink space效果</span>
</p>
<p><strong>经过以上测试，得出的两个结论，到底用哪一个命令来整理碎片，消除行迁移呢？这就要根据实际业务需要，如果你只是收缩空间，数据增长很慢，那用shrink可以但是如果数据增长很快的话，用move就比较合适，避免再重新分配空间啊</strong>
</p>
<p><span style="color: #008000;">备注：</span>
<br>
在10g之后，整理碎片消除行迁移的新增功能shrink space</p>
<p>alter table <table_name> shrink space [ <null> | compact | cascade ];</p>
<p><span style="color: #008000;">compact</span>
:这个参数当系统的负载比较大时可以用，不降低HWM。如果系统负载较低时，直接用alter table table_name shrink space就一步到位了</p>
<p><br><span style="color: #008000;">cascade</span>
：这个参数是在shrink table的时候自动级联索引，相当于rebulid index。</p>
<p><strong>普通表：</strong>
</p>
<p><strong></strong>
</p>
<p>shrink必须开启行迁移功能。<br>
alter table table_name enable row movement ;</p>
<p>保持HWM，相当于把块中数据打结实了<br>
alter table table_name shrink space compact;</p>
<p>回缩表与降低HWM<br>
alter table table_name shrink space;</p>
<p>回缩表与相关索引，降低HWM<br>
alter table table_name shrink space cascade;</p>
<p>回缩索引与降低HWM<br>
alter index index_name shrink space</p>
<p>虽然在10g中可以用shrink ，但也有些限制：</p>
<p>1). 对cluster，cluster table，或具有Long，lob类型列的对象 不起作用。<br>
2). 不支持具有function-based indexes 或 bitmap join indexes的表<br>
3). 不支持mapping 表或index-organized表。<br>
4). 不支持compressed 表</p>
<p>------end------</p>本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
