_partition_large_extents和_index_partition_large_extents参数导致表空间增长快

    帮助客户使用expdp导入测试数据时，发现数据量快速增长，瞬间就撑满了表空间，测试库的数据明显多于生产数据库的数据，这是什么原因导致的呢。在网上差了一下才知道是_partition_large_extents参数导致的。从11.2.0.2开始，创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而设置的一个参数,默认为true，即分区表的每个extent为8M。测试一下：

确保deferred_segment_creation  参数为TURE的情况下测试_partition_large_extents参数。

SQL> show parameter defer

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation         boolean     TRUE

col name for a30
col value for a10
col describ for a60 
set lines 300

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm='_partition_large_extents';


NAME                           VALUE      DESCRIB
------------------------------ ---------- --------------------------------------------------
_partition_large_extents       TRUE      Enables large extent allocation for partitioned tables

创建分区表，注意要使用非system和sys表空间

SQL> conn  scott/tiger

SQL> create table test_com_partition_1
(
id int not null,
name varchar2(4000)
)
partition by range(id)
(
partition p10 values less than(10), 
partition p20 values less than(20), 
partition p30 values less than(30), 
partition p40 values less than(40), 
partition pmax values less than(maxvalue)
);

SQL> select PARTITION_NAME,TABLESPACE_NAME from user_segments where SEGMENT_NAME='TEST_COM_PARTITION_1';

no rows selected

 

由于参数deferred_segment_creation为true，所以创建表以后并没有分配segments。下面插入数据

SQL> insert into test_com_partition_1 values (1,lpad('shengruxiahua',3900,'shengruxiahua'));

SQL> select TABLESPACE_NAME,PARTITION_NAME,bytes/1024/1024 from user_segments  where SEGMENT_NAME='TEST_COM_PARTITION_1';

TABLESPACE_NAME            PARTITION_NAME              BYTES/1024/1024
------------------------------ ------------------------------ ---------------
USERS                   P10                        8

只插入一行数据后segment大小是8M，可是不小啊，下面修改_partition_large_extents参数后再次进行测试

SQL> alter system set "_partition_large_extents"=

SQL> create table test_com_partition_2
(
id int not null,
name varchar2(4000)
)
partition by range(id)
(
partition p10 values less than(10), 
partition p20 values less than(20), 
partition p30 values less than(30), 
partition p40 values less than(40), 
partition pmax values less than(maxvalue)
);

SQL> insert into test_com_partition_2 values (1,lpad('shengruxiahua',3900,'shengruxiahua'));

SQL> select TABLESPACE_NAME,PARTITION_NAME,bytes/1024/1024 from user_segments  where SEGMENT_NAME='TEST_COM_PARTITION_2';

TABLESPACE_NAME            PARTITION_NAME              BYTES/1024/1024
------------------------------ ------------------------------ ---------------
USERS                   P10                    .0625 

    结论：当参数_partition_large_extents等于true时（此时可能不可见），创建分区表默认占用空间大小为每个分区8m，而普通表默认占据空间大小仅0.0625m（64k）。另外一个有关系的参数是_index_partition_large_extents，用于控制创建分区索引的大小，默认也是true，即默认分区索引大小为8m，而创建普通索引默认大小为64k。这里就不做实验了。

 

    还有一种情况对子分区的影响。,如果带有子分区,deferred_segment_creation虽然为true，但是在一个分区表中如果有一个子分区插入了记录,那么其他子分区会同时创建segment.对于数据量不多，而且数据大量集中在某几个分区,那强烈建议设置_partition_large_extents为false,节约空间.如果数据量较大,而且数据分布较为均匀,建议设置_partition_large_extents为true. 具体请参考惜分飞 老师的文章。

 

参考文章：

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

https://www.cnblogs.com/wcwen1990/p/6656545.html

https://www.xifenfei.com/2013/08/%e5%88%86%e5%8c%ba%e9%bb%98%e8%ae%a4segment%e5%a4%a7%e5%b0%8f%e5%8f%98%e5%8c%9664k-8m.html