CELL_PARTITION_LARGE_EXTENTS now obsolete

During the Exadata course that I am just delivering in Munich, I noticed that the fairly new parameter CELL_PARTITION_LARGE_EXTENTS is already obsolete now:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> alter system set cell_partition_large_extents=true;
alter system set cell_partition_large_extents=true
*
ERROR at line 1:
ORA-25138: CELL_PARTITION_LARGE_EXTENTS initialization parameter has been made
obsolete

This parameter was introduced in 11.2.0.1 especially for Exadata Database Machine because the Allocation Unit Size (AU_SIZE) for Diskgroups built upon Exadata Cells is recommended with 4 MB. Large Segments should therefore use a multiple of 4 MB already for their initial extents. Although the parameter was made obsolete, the effect that was achievable with it is still present:

SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';

 MB
----------
 8

I inserted before checking USER_EXTENTS because of the 11g New Feature deferred segment creation:

SQL> drop table t purge;

Table dropped.

SQL> create table t (n number);

Table created.

SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';

no rows selected

SQL> insert into t values (1);

1 row created.

SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';

 MB
----------
 .0625

Notice that only partitioned tables are affected by the 8 MB initial extent behavior. The new hidden parameter _PARTITION_LARGE_EXTENTS (defaults to true!) is now responsible for that:

SQL> alter session set "_partition_large_extents"=false;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';

 MB
----------
 .0625

Notice that the setting of CELL_PARTITION_LARGE_EXTENTS with alter session is silently overridden by the underscore parameter:

SQL> drop table t purge;

Table dropped.

SQL> alter session set cell_partition_large_extents=true;

Session altered.

SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';

 MB
----------
 .0625

The parameter setting of the underscore parameter was still false.

SQL> drop table t purge;

Table dropped.

SQL> alter session set "_partition_large_extents"=true;

Session altered.

SQL> alter session set cell_partition_large_extents=false;

Session altered.

SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';

 MB
----------
 8

Conclusion: With 11.2.0.2, partitioned tables get initial extents of 8 MB in size, which is particular useful in Exadata Environments where the ASM AU_SIZE will be 4 MB. But also ordinary Databases are affected – which is probably a good thing if we assume that partitioned tables will be large in size anyway and will therefore benefit from a large initial extent size as well.

Addendum: During my present Exadata course (03-JUN-2012), I saw a similar parameter for partitioned indexes also: _INDEX_PARTITION_LARGE_EXTENTS defaults to FALSE, though. Brief test:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> create table parti (n number) partition by range (n) (partition p1 values less than (2));

Table created.

SQL> insert into parti values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> create index parti_idx on parti(n) local;

Index created.

SQL> select bytes from user_extents where segment_name='PARTI_IDX';

     BYTES
----------
     65536

SQL> drop index parti_idx;

Index dropped.

SQL> alter session set "_index_partition_large_extents"=true;

Session altered.

SQL> create index parti_idx on parti(n) local;

Index created.

SQL> select bytes from user_extents where segment_name='PARTI_IDX';

     BYTES
----------
   8388608

So this parameter gives us also 8M sized extents for partitioned indexes, but not by default.

About these ads

, ,

  1. #1 by Ofir Manor on March 29, 2011 - 20:22

    I saw that this feature is actually documented in the 11.2.0.2 new features section of the Oracle documentation… All this chapter is worth a read :)

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17128/chapter1_2.htm#sthref25

  2. #2 by Uwe Hesse on March 29, 2011 - 20:40

    Sure it is. But they don’t mention CELL_PARTITION_LARGE_EXTENTS nor the new hidden parameter, although we have still some whitepapers – and courseware – out there that recommend setting the obsolete parameter. So my hope is that this post is not completely useless :-)

  3. #3 by Ofir Manor on March 29, 2011 - 22:12

    Oh, sorry, didn’t mean to imply that… I wasn’t even aware that _partition_large_extents existed, so it was quite interesting!
    I just don’t think many people are aware that Oracle tried to document the new features of 11.2.0.2. It is a bit unusual to have so many small enhancements in a patchset.

  4. #4 by Uwe Hesse on April 1, 2011 - 09:03

    Ofir,
    thank you for pointing to our very informative New Features Guides :-)
    11.2.0.2 is indeed extraordinary. Can’t remember that we could ever download a patchset from MOS/Metalink that was an autonomous installation, for example.

  1. Brief Introduction into Partitioning in #Oracle « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,683 other followers

%d bloggers like this: