【11gR2新特性】Large Partition Extents:_partition_large_extents & _index_partition_large_extents

11.2.0.2中引入了_partition_large_extents 的新特性，在extent size auto allocate的表空间上若创建分区表，则分区的initial extent size为8M。
试看下面的例子：

 

 

_partition_large_extents
TRUE
Enables large extent allocation for partitioned tables

_index_partition_large_extents
FALSE
Enables large extent allocation for partitioned indices

SQL> select ALLOCATION_TYPE from dba_tablespaces where tablespace_name='USERS';

ALLOCATION_TYPE
------------------
SYSTEM

@partition.sql        ==》测试用CREATE TABLE脚本，将在users表空间上创建一个有384个子分区的表

 alter session set deferred_segment_creation=false;

SQL> select count(*) from dba_tab_subpartitions where table_name='MACLEAN_PARTITION';

  COUNT(*)
----------
       384

SQL> select blocks,count(*) from dba_Extents where segment_name='MACLEAN_PARTITION' group by blocks;

    BLOCKS   COUNT(*)
---------- ----------
      1024        384

SQL> alter session set "_partition_large_extents"=false;

会话已更改。

@partition.sql

SQL> select count(*) from dba_tab_subpartitions where table_name='MACLEAN_PARTITION';

  COUNT(*)
----------
       384

SQL>  select blocks,count(*) from dba_Extents where segment_name='MACLEAN_PARTITION' group by blocks;

    BLOCKS   COUNT(*)
---------- ----------
         8        384

 

可以看到在11.2.0.2 以后_partition_large_extents参数默认打开(default : TRUE)， 且当表空间allocation_type=SYSTEM时  创建的初始化分区大小为8M。

实际该特性可能在导致以下2个问题：

• 若配合deferred_segment_creation=false使用则创建具有大量分区的表时，耗时大幅增长
• 若配合deferred_segment_creation=false使用则建好后空表的所占空间为8M*分区数目，对比关闭该特性的情况会大非常多

 

具体还可以参考以下文档：
Initial Extent Size of a Partition Changed To 8MB From 64KB

Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
A partition of a partitioned table is created with default initial extent size 8MB vs 64KB in previous versions.

Changes
Upgrade to 11.2.0.2.
Cause
Hidden parameter _partition_large_extents was introduced in 11.2.0.2 to enable large extent allocation for partitioned tables if created in autoallocate locally managed tablespaces with default extent size.
The default value for the parameter is TRUE. This hidden parameter supersedes parameter cell_partition_large_extents which was introduced in 11.2.0.1.
Solution
The hidden parameter _partition_large_extents, introduced in 11.2.0.2, affects exadata and non-exadata databases, it has the two values TRUE, FALSE. The default value is TRUE which means all partitioned objects in the db will start with 8MB extents if created in autoallocate locally managed tablespaces.

Similarly parameter _index_partition_large_extents was introduced for partitioned indexes, the default value for this one is FALSE.

Smaller size extents impacts performance of operations like load and scan, typically full table scan FTS (multi-block IO). For partitioned objects with many partitions, the fact of having many partitions leads to more small extents with autoallocation than for a nonpartitioned table; more smaller extents lead to more expensive space allocation/deallocation operations, and the I/O performance of scanning can be impacted due to more extents with smaller extent size.

The change of the default allocation size for partitioned tables was introduced together with the enhanced  deferred segment creation support for partitioned tables in 11.2.0.2 (deferred_segment_creation parameter, default TRUE).
Using both new defaults ensures that a negative impact from pre-created partitioned tables could be ruled out since empty partitions will no longer allocate any space.

If the user forcefully sets the parameter _partition_large_extents to FALSE (either at session or at instance level), then the pre-11.2.0.2 behaviour (of starting from 64k extent size) will be restored.

The user can also override the default extent allocation on a per statement base by specifying INITIAL extent in the CREATE TABLE … / ALTER TABLE … ADD PARTITION … command as well as by changing the default storage initial for the tablespace involved e.g. ALTER TABLESPACE … DEFAULT STORAGE (INITIAL 65536);