2011-04-28

Initial Extent Size of a Partition Changed To 8MB From 64KB

Initial Extent Size of a Partition Changed To 8MB From 64KB [ID 1295484.1]


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);

2 comentarii:

  1. I have a segment called PIFR which has 212 extents. Is there any form to shrink this extents and convert it to 1 extent?

    I know that a solution to prevent that segment grows up is to resize the next extend, but i would like to know if is there any form to shrink extents?

    Could someone tell me how to do it?

    Thanks

    RăspundețiȘtergere
  2. Thank you very much for the detailed explaination about this very essential setting! We have an application that writes partitions based on daily calculations - that is a new partition each day - and this procedure applies to multiple tables. These tables hold many thousands of partitions. We developed this approach on a 11.2.0.1 distribution and recently upgraded to 12.1 when we found out that the underlying tablespaces immensely grew when recalculating all partitions (which was necessary due to upgrade). Data worth of 10 gb suddenly needed a terabyte and we couldn't figure out what was going on here.

    So the initial extent size of partitions is a quite crucial setting in a data warehouse environment and i think it should have been better documented by Oracle. Thanks again for the clarification!

    RăspundețiȘtergere