oracle数据占用空间过大的分析

      在CSDN使用这个博客以来，一致在写一些经济书籍的读后感，在一个技术网站写这个，感觉颇为遗憾。正好最近工作中发生了一件ORACLE数据库空间占用过大的问题，拿出来和大家分享。为了有趣些，参杂了一些事情和个人感受。

 

      事情来源一个开发的同事突然发现我们的数据库中，索引表空间占用操作系统文件大小是数据表空间的3倍。这个表结构和索引设计都是平台部门的设计，都是平台的事情，本来是不想掺乎到这个问题的研究中来。但是可气的是，平台有一伙人突然弄明白了ORACLE中分区表的空间分配方式是segment---extent---block。一张表至少一个segment，其中包含了一定数量的block后。就声明，这个是产品在创建表空间的时候分配策略不对导致，并且还拿出他自己的一张创建的表空间的“样本”sql来秀。看看这些邮件，就有气，尽管对于oralce调优不多，但是多少还系统的读了不少oracle的资料，决定凭此出山摆平这些跳梁蚱蜢。详细过程见下：

 

      问题：
        现场表现出来数据库空间只有索引空间的0.5.这个比较离奇，索引空间竟然比数据空间还大。

      背景：

        我们的数据库设计是采用按照2小时分区表的设计，其中每个数据分区表都存在两个索引分区表，数据和索引在不同的表空间中。

       插一句：到这里是不是高手都能猜出来原因在哪里呢。呵呵

 

       分析结果：
        数据库创建分区表的时候至少使用1M（为什么，下面分析中可以看到），而每一个数据分区表都有两个索引分区表对应，在创建初始时期每个分区表至少一个extent，所以就是1：2，此时数据是空的。
        如果数据进入后，比例会缩小，但是由于分区表划分过细，例如每两小时一个分区表，而两个小时的数据往往还插不完一个extent，所以导致比例还是创建初期的1：2。

         所以最终导致结果索引空间比数据空间大很多。

       根本原因：

       平台采用细粒度的分区表，是因为平台觉得这个表空间中的某些大表查询效率不够的原因。但是划分的时候把一些小容量的表也这样做了，这样导致在数据表中的初始空间消耗比数据还大，导致初始分配的第一个extent都没有弄完，所以这样数据和索引保持比例为1：2.

       另外还有一个次要原因是两个索引平台竟然需要用两个分区。

        解决办法：

        调整两个索引分区表为一个索引分区表，简单

        调整分区表的划分粒度为不同容量的表采用不同的分区粒度，例如小数据表用天，大数据表用小时.这样即使索引采用和表一样的分区分配策略，也是可行的。也比较简单。

        清楚索引的数据模型是怎样的，推断出空间分配策略。因为为索引是单独设立表空间，自然也可以单独设立空间存储策略满足索引的数据模型。难度很高
        调整extent的大小为合适程度，不影响oracle的数据定位效率，难度最高，这个我不敢做，没有这个水平。        
       

分析过程 

空间创建脚本：     

dbf1:=oraclepath||'AAA_IND1.dbf';
s:='create tablespace AAA_ORI_IND datafile '||''''||dbf1||''''||' size '||to_char(v_datafilesize)||'M reuse autoextend ON NEXT '||to_char(v_tdatafilesize)||'M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO online PERMANENT';
DBMS_OUTPUT.PUT_LINE(s);
EXECUTE IMMEDIATE  s;

亮一下平台的脚本：

create tablespace %$BBB$%
  datafile '%$BBB-DBPATH$%%$BBB-DBNAME$%.dbf' size %$BBB-DATA-SIZE$%M REUSE 
   AUTOEXTEND ON NEXT %$PM-AUTO-EXT$%M
   ONLINE
   PERMANENT;//多粗糙呀 

“EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M”说明我们extent采用了本地 统一大小的管理的分配方式（oracle资料认为这个是比特位数据寻址，比数据字典效率高），分配的初始大小是1M（这个也是oracle缺省的，说明其至少包括了128个block，一个block是8k，oracle规定至少包括5个block）。使用的1m是缺省值。
至于采用多少个block为一个extent，也就是extent多大。需要先看看oracle的数据寻址方式。ORACLE实际上的数据寻址从比特位图（这是因为我们采用了本地方式）找到extent，然后再找block的两层定位的方式，所以这个定位为多少，主要是看一个分区表中extent有多少，extent多，这个 UNIFORM SIZE 就可以分大些，extent数量少，这个 UNIFORM SIZE 就可以分小些

目前而我们这个数据空间中每个应用的表的负载情况不同，平台创建的索引数据负载模型我们也不清楚，所以我们统统用缺省值最保守。 

“SEGMENT SPACE MANAGEMENT AUTO online PERMANENT'”说明segment策略问题，我的段采用SPACE MANAGEMENT AUTO 自动分配策略。这个是一个深水线的策略，不是存储分配策略。

分析中采用的脚本和数据

1.说明数据和索引时1：2

select segment_type,sum(bytes) / 1024 / 1024　　 from user_segments
group by segment_type;
 

INDEX 13.00
LOBINDEX 4.00
TABLE PARTITION 17,851.00
TABLE 13.00
LOBSEGMENT 4.00
INDEX PARTITION 33,403.00

 2. 说明了整个用户中，占用了一个extent的段占用了绝大多数，白白浪费了空间

select extents,count(extents) from user_segments
group by extents;
EXTENTS  COUNT(EXTENTS)
1       42839
2       3692
3       141
4       120
5       25
6       1