oracle碎片小结

落日映苍穹つ 2022-07-12 11:00 244阅读 0赞

author：skate

time：2010-05-31

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

我们在使用windows的时候，都知道要定期整理磁盘碎片，因为磁盘碎片会影响性能，给管理上带来额外的  
负担。那oracle更是如此，当随着数据增加，oracle处理海量数据本身就已经很费力了啊，如果再有大量  
的碎片，那就是雪上加霜啊。所以碎片要引起dba的高度重视，尽早发现尽早处理。

碎片是怎么产生的呢？

简单理解就是由于更新和删除产生一些碎小的不能被再次使用的空间，根据每种不同的碎片他们的产生也是有区别的

block-level的碎片，而block又分为data block和index block，在data block中存放的是row数据，在index block  
中存放的是索引键值数据，所以按上面所说，block-level碎片有细分为row-level碎片和Index Leaf Block-level碎片。

oracle的每一个对象都是存储在segment中，而oracle的最小分配单位是extents（区），在数据更新删除中也会产生碎片  
这一级别的碎片就是segment碎片。segment又存在datafile中，而tablespace又是包含datafile的逻辑概念。所以这一层  
是tablespace-level碎片；tablespace是在disk上存储，所以这一层就是disk-level碎片。

简单图示如下

disk-level fragmention  
  tablespace-level fragmentation  
    segment-level fragmentation  
      block-level fragmentation  
        row-level fragmentation  
      index leaf block-level fragmentation

**顺便提下oracle extents存在的理由**

一个extents是由多个相连的block组成的，多个extents做成一个segment；extent是oracle的最小分配单位

extent的优点：  
1. 提高空间分配，释放的效率，降低管理block的资源成本  
2. 提高扫描的效率，因为extent是由相连blocks做成的特性，可以一次读取更多的内容，较低io读写次数

extent的缺点  
容易产生碎片

**如何确定产生了碎片的呢？**

一。表空间碎片确定参考

由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用 FSFI--Free Space Fragmentation Index  
(自由空间碎片索引)值来直观体现：

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

可以看出，FSFI 的最大可能值为 100 (一个理想的单文件表空间)。随着范围的增加， FSFI 值缓慢下降，而随着最大范  
围尺寸的减少，FSFI 值会迅速下降。通过如下语句查询FSFI：

select tablespace\_name,  
       sqrt(max(blocks) / sum(blocks)) \* (100 / sqrt(sqrt(count(blocks)))) FSFI  
  from dba\_free\_space  
 group by tablespace\_name  
 order by 1;

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

temp表空间（非本地管理表空间）   回收      
  可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：      
  alter   tablespace   temp   default   storage(pctincrease   1);

这样smon便会将自由范围自动合并。也可以手工合并自由范围：      
  alter   tablespace   temp   coalesce;

二。segment的碎片整理

segment的碎片整理一般主要整理table和index

9i之前碎片整理的方法

**1. exp/truncate/imp**

**2.  
alter table table\_name move stroage(initial 1m)  
alter index index\_name rebuild \[tablespace tablespace\_name\] \[nologging\] \[online\]**

**3.CTAS重组**

在10g之后，不但可以用以上两种方法，还提供了新的方法

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

compact:这个参数当系统的负载比较大时可以用，不降低HWM。如果系统负载较低时，直接用alter table table\_name shrink space就一步到位了  
cascade：这个参数是在shrink table的时候自动级联索引，相当于rebulid index。

普通表

shrink必须开启行迁移功能。  
alter table table\_name enable row movement ;

保持HWM，相当于把块中数据打结实了  
alter table table\_name shrink space compact;

回缩表与降低HWM  
alter table table\_name shrink space;

回缩表与相关索引，降低HWM  
alter table table\_name shrink space cascade;

回缩索引与降低HWM  
alter index index\_name shrink space

虽然在10g中可以用shrink ，但也有些限制：

1). 对cluster，cluster table，或具有Long，lob类型列的对象 不起作用。  
2). 不支持具有function-based indexes 或 bitmap join indexes的表  
3). 不支持mapping 表或index-organized表。  
4). 不支持compressed 表

参考文档：  
[http://blog.csdn.net/wyzxg/archive/2010/05/28/5631721.aspx][http_blog.csdn.net_wyzxg_archive_2010_05_28_5631721.aspx]

**从9i开始index碎片的整理除了drop and create index，还可以 alter index skate.t1\_idx rebuild nologging parallel 4 online ;**

表的碎片查找

一种笨的方式就是ctas一个表和原来的做下比较，看看空间大小的变化，还有一种就方法就是看视图里数据（要及时analyze才准确）  
查看user\_tables和user\_segments

eg:

SQL> select num\_rows,blocks,empty\_blocks,avg\_space,avg\_row\_len,last\_analyzed from user\_tables where table\_name='TEST1';

NUM\_ROWS     BLOCKS EMPTY\_BLOCKS  AVG\_SPACE AVG\_ROW\_LEN LAST\_ANALYZED  
\---------- ---------- ------------ ---------- ----------- -------------  
    393216        562           78         23          13 2010-5-27 21:

AVG\_SPACE:表示block内的空闲空间大小，根据pctfree参数来参考，这里以默认%10为准，在block=8192里，允许空间的空间是8192 × %10=819.2，也就是说在block剩余空间是819.2时，oracle就认为它是满块了，如果再有数据就要寻找新的block了，当表的AVG\_SPACE远远大于819.2时，就说明有碎片了，因为块没有被充分利用，产生block-level碎片。可以通过alter table move来整理。

那可以节省多少空间呢？  
          
   数据的实际大小=AVG\_ROW\_LEN × NUM\_ROWS=13 × 393216=5111808  
          表的实际大小  =BLOCKS × block\_size   =562 \* 8192 =4603904

理论上AVG\_ROW\_LEN × NUM\_ROWS <= BLOCKS × block\_size 如果相差比较大，那就需要整理，也可以粗略的算下可以节省多少空间，可现在实际上确  
**AVG\_ROW\_LEN × NUM\_ROWS > BLOCKS × block\_size ,不知道的是什么原因？**

突然想到我这个test1表里最后一个字段是clob类型的，而clob在数据大于4k时（一个lob字段包括lobindex和lobsegment,），是存储在另外的log segment中的。

而clob类型的d字段没有存储任何数据，查询user\_lobs视图

SQL> select ul.table\_name,ul.segment\_name, ul.chunk,se.blocks,se.bytes from user\_lobs ul ,user\_segments se  
  2  where ul.segment\_name=se.segment\_name  
  3  and ul.table\_name='TEST1'  
  4  ;

TABLE\_NAME                     SEGMENT\_NAME                        CHUNK     BLOCKS      BYTES  
\------------------------------ ------------------------------ ---------- ---------- ----------  
TEST1                          SYS\_LOB0000183465C00004$$            8192          8      65536

可这个65536和那两个差值还差很多，再说这8个block还是empty，搞不懂了，谁知道请指点，先谢谢了啊

换个角度会什么样的？我又用CTAS穿件表test4，这回就符合理论值，无论用

alter table table\_name move,还是alter table table\_name shrink space compact，都是符合理论值的

create table test4 as select \* from test1

分析后： analyze  table test4 compute statistics

SQL> select num\_rows,blocks,empty\_blocks,avg\_space,avg\_row\_len,last\_analyzed from user\_tables where table\_name='TEST4';

NUM\_ROWS     BLOCKS EMPTY\_BLOCKS  AVG\_SPACE AVG\_ROW\_LEN LAST\_ANALYZED  
\---------- ---------- ------------ ---------- ----------- -------------  
    393216        802            6        820          12 2010-5-28 12:

数据的实际大小=AVG\_ROW\_LEN × NUM\_ROWS=13 × 393216=5111808  
          表的实际大小  =BLOCKS × block\_size   =562 \* 8192 =6569984

**index碎片查找**

识别索引是否有碎片

获得关于索引的信息，用下面的命令

analyze index index\_name validate structure 或validate index index\_name

analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的  
数据（存放在index\_stats）來判断索引是否需要重新建立。

运行命令后，然后在视图 index\_stats查询，这个视图记录索引的一些信息，这个视图只有一个记录，所以在同一时间只能分析一个索引。

1.删除的行数如占总的行数的30%，即del\_lf\_rows / lf\_rows > 0.3，那就考虑索引碎片整理  
2.如果”hight“大于4，可以考虑碎片整理  
3.如果索引的行数（LF\_rows）远远小于‘LF\_BLKS’ ，那就说明有了一个大的删除动作，需要整理碎片

索引碎片整理方法

1. recreate index  
2. alter index skate.idx\_test rebuild nologging parallel 4 online ;  
3. 如果叶块是half-empty or near empty，可以运行“alter index coalesce”来合并

参考：

[http://blog.csdn.net/wyzxg/archive/2010/05/31/5637549.aspx][http_blog.csdn.net_wyzxg_archive_2010_05_31_5637549.aspx]

\-----end-----

[http_blog.csdn.net_wyzxg_archive_2010_05_28_5631721.aspx]: http://blog.csdn.net/wyzxg/archive/2010/05/28/5631721.aspx
[http_blog.csdn.net_wyzxg_archive_2010_05_31_5637549.aspx]: http://blog.csdn.net/wyzxg/archive/2010/05/31/5637549.aspx