Initial Extent Size of a Partition Changed to 8MB from 64KB

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

SYMPTOMS

Table partition created in 11.2.0.2 has default initial extent size 8MB vs 64KB in previous versions. 
Table created in 11.2.0.2 having many partitions with few rows in each, consumes more space than in earlier 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 auto allocate 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.1 and was covering both tables and indexes.

SOLUTION

Explanation of the default behaviour in 11.2.0.2

The hidden parameter _partition_large_extents, introduced in 11.2.0.2, affects exadata and non-exadata databases. It can be set to either TRUE or FALSE. The default value is TRUE which means all partitioned objects in the database will be created with 8MB extents if created in an auto allocate locally managed tablespace. 

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 data 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 auto allocation than for a non-partitioned 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.

Data loads into target partitioned tables prefer _partition_large_extents=true and ASSM (autoallocate locally managed) tablespace so that you have a few large extents instead of many small extents.  This means for partitioned tables the initial and next extents will be 8MB, so during the load each parallel server process will be allocated its own extent. Therefore, it loads 8MB worth of data before it goes and gets another extent. This way parallel processes spend more time with loading data as opposed to doing space management. Partitioned tables are typically large tables, often subject to parallel execution, but obviously it is determine by a particular implementation whether the data load uses parallel execution or not.

Too many small extents can cause a Full Table Scan (FTS) to take longer. FTS can be done at partition level. E.g. If a partition has millions of small extents (small in absolute value or small compared to the value of the db_block_size), then few blocks can fit into an extent, which means we can read only this many few blocks at a time.

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.

How to Achieve the Pre-11.2.0.2 Behaviour

If the user explicitely 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 basis by specifying INITIAL extent in the CREATE TABLE ... / ALTER TABLE ... ADD PARTITION ... command.

Even though this is a hidden parameter. it is supported to set the value to false if the old behavior is preferred. The impact of setting this parameter is strictly that the old behavior will be restored.