System Managed Extent Size – 11g Improvements

Oracle Database provides two extent management options: locally managed and dictionary managed. Today, when creating a new database, most DBAs use the former for all tablespaces. This is a good thing. What is less obvious is the choice between uniform extent size and system managed extent size. Both of these options have pros and cons. The tendency is to use uniform extent size only when the size of the segments to be stored in a tablespace is (well) known. Otherwise, it is better to delegate the choice of an “optimal” extent size to the database engine. In fact, the aim of system managed extent size is to adjust the size of the extents to the size of the segments to which they are associated. In other words, a large segment should have large extents, a small segment should have small extents. In this way it should be possible to avoid both fragmentation and very high number of extents. To do so, the size of the extents is normalized to a limited number of sizes: 64KB, 1MB, 8MB or 64MB.

Now, let’s discuss two enhancements that have been (silently?) introduced in 11.1.0.7… Yes, in 11.1.0.6 they are not available!

But, before that, let’s create a tablespace and a view that will be used for the tests I’ll show you…

SQL> CREATE TABLESPACE test
  2  DATAFILE '/u01/oradata/DBM11106/test01.dbf' SIZE 1G
  3  BLOCKSIZE 8K
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  5  SEGMENT SPACE MANAGEMENT AUTO;

SQL> CREATE OR REPLACE VIEW v AS
  2  SELECT bytes, extent_id-lag(extent_id,1,-1) OVER (ORDER BY extent_id) AS extents
  3  FROM (
  4    SELECT bytes, extent_id, decode(bytes,lead(bytes) OVER (ORDER BY extent_id),0,1) AS last
  5    FROM user_extents
  6    WHERE segment_name = 'T'
  7  )
  8  WHERE last = 1
  9  ORDER BY extent_id;

Improvement #1

Up to 11.1.0.6, INITIAL (the attribute that can be specified in the storage clause) is only partially considered when creating a new segment. In fact, as the following example shows, even if a segment of 1MB is created, sixteen extents of 64KB are associated to it.

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
  2  TABLESPACE test
  3  STORAGE (INITIAL 1M);

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
     65536         16

One might think that INITIAL is not considered at all. However, it is easy to show that it’s actually used. To do so it’s sufficient to create a segment of 2MB. As the following example shows, two extents of 1MB are associated to it. In other words, even if the value of INITIAL is considered, the size of the first extent is not directly derived from it.

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
  2  TABLESPACE test
  3  STORAGE (INITIAL 2M);

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
   1048576          2

As of 11.1.0.7, however, the size of the first extent is directly derived from INITIAL. The only and obvious limitation is that the extent size is normalized to either 64KB, 1MB, 8MB or 64MB. To do so the database engine uses the extent size which is equal or smaller to INITIAL. Therefore, when the same SQL statements as before are executed, for a segment of 1MB only one extent of 1MB is associated to it.

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
  2  TABLESPACE test
  3  STORAGE (INITIAL 1M);

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
   1048576          1

A curios side effect of this new behavior is that it’s much common to see segments for which the first extent is larger than the second one. Such situations are not new… but, they are much more common than before. The following example illustrates this. Notice that, for a 10MB segment, the first extent is 8MB and the subsequent two are only 1MB each.

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
  2  TABLESPACE test
  3  STORAGE (INITIAL 10M);

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
   8388608          1
   1048576          2

In summary, as of 11.1.0.7, if you set INITIAL to either 64KB, 1MB, 8MB or 64MB, the first extent will have the specified size. So, if you know what the size of an segment will be, it is not bad to provide a sensible value through INITIAL.

Improvement #2

As you might guess, the second improvement is related to another attribute that can be specified in the storage clause: NEXT. The improvement, however, is a not what you might expect in first place. In fact, NEXT is not simply used to specify the size of any extent allocated after the first one. According to my observations NEXT is only used when a parallel load is performed.

Up to 11.1.0.6, when a parallel load is performed, every parallel slave allocates extents from a temporary segment and loads the data into it. In other words, the parallel slaves do not modify the target table. Then, when the transaction is committed, the temporary segment is merged to the target table. The following example illustrates this behavior. Notice three things. First, NEXT is set to 1MB. Second, the insert loads about 10MB of data. Third, I forced the utilization of two parallel slaves. Since each parallel slave allocated its own extents, every parallel slave allocates sixteen 64KB extents, four 1MB extents and one 832KB extent (for a total of about 6MB per parallel slave). Simply put, the extent allocation for the temporary segment follows the rules we are used to.

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
  2  TABLESPACE test
  3  STORAGE (NEXT 1M);

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
     65536          1

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 2;

SQL> INSERT INTO t
  2  SELECT rownum, rpad('*',1000,'*')
  3  FROM dual
  4  CONNECT BY level <= 10000;

SQL> SELECT bytes, extent_id-lag(extent_id,1,-1) OVER (ORDER BY extent_id) AS extents
  2  FROM (
  3    SELECT bytes, extent_id, decode(bytes,lead(bytes) OVER (ORDER BY extent_id),0,1) AS last
  4    FROM user_extents
  5    WHERE segment_type = 'TEMPORARY'
  6  )
  7  WHERE last = 1
  8  ORDER BY extent_id;

     BYTES    EXTENTS
---------- ----------
     65536         16
   1048576          4
    851968          1
     65536         16
   1048576          4
    851968          1

SQL> COMMIT;

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
     65536         17
   1048576          4
    851968          1
     65536         16
   1048576          4
    851968          1

As of 11.1.0.7, NEXT is used for sizing the extents of the temporary segment. As a result, if you know that are loading a lot of data, you can avoid the small extents (that might lead to suboptimal performance when the data will be accessed…). Let’s re-execute the same SQL statement as before to see what the difference is. Notice that the only extent of 64KB is the original one that was initially associated to the table.

SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
  2  TABLESPACE test
  3  STORAGE (NEXT 1M);

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
     65536          1

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 2;

SQL> INSERT INTO t
  2  SELECT rownum, rpad('*',1000,'*')
  3  FROM dual
  4  CONNECT BY level <= 10000;

SQL> SELECT bytes, extent_id-lag(extent_id,1,-1) OVER (ORDER BY extent_id) AS extents
  2  FROM (
  3    SELECT bytes, extent_id, decode(bytes,lead(bytes) OVER (ORDER BY extent_id),0,1) AS last
  4    FROM user_extents
  5    WHERE segment_type = 'TEMPORARY'
  6  )
  7  WHERE last = 1
  8  ORDER BY extent_id;

     BYTES    EXTENTS
---------- ----------
   1048576          5
    786432          1
   1048576          5
    786432          1

SQL> COMMIT;

SQL> SELECT * FROM v;

     BYTES    EXTENTS
---------- ----------
     65536          1
   1048576          5
    786432          1
   1048576          5
    786432          1

In summary, as of 11.1.0.7, with NEXT you can control the sizing of the temporary extents used during parallel loads.

10 Comments

  1. Bernd Eckenfels's Gravatar Bernd Eckenfels
    August 13, 2009    

    One thing about uniform size and initial segments I hate is, that an empty segment (lob column) always uses up one extend. This wastes a lot of storage if the col is unused. And as a software vendor you never know when a customer will use the segment (and the customer does not want to decide which segments not to use). With system sized extends this is less a problem (unless you specify a large initial).

  2. Stefan's Gravatar Stefan
    August 13, 2009    

    Hello Christian,
    i am wondering about the NEXT clause in your SQL statements and the LMT tablespace with allocation type system.

    I know the following allocation algorithm in that kind of tablespace:
    SEG < 1M = Extent Size 64k
    1M <= SEG < 64M = Extent Size 1M
    64M <= SEG < 1G = Extent Size 8M
    1G CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
    2 TABLESPACE test
    3 STORAGE (INITIAL 10M NEXT 10M);

    Just to clarify, becuase of maybe some guys are wondering about that.

    Regards
    Stefan

  3. August 13, 2009    

    Hi Stefan

    > i am wondering about the NEXT clause in your SQL statements

    I did the wrong copy/paste… My bad… The result does not change with or without the NEXT clause. I did plenty of tests to find out in which cases it might be relevant. And, at the end, as I wrote in the post, I never saw a case in which NEXT is used (except in case of parallel load, of course). I updated the post accordingly.

    > I know the following allocation algorithm in that kind of tablespace:

    Be careful that there are exceptions to it… For example, when MSSM is used. Here an example:

    SQL> CREATE TABLESPACE test
      2  DATAFILE '/u00/oradata/DBA111/test01.dbf' SIZE 1G
      3  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
      4  SEGMENT SPACE MANAGEMENT MANUAL;
    
    SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
      2  TABLESPACE test
      3  STORAGE (FREELIST GROUPS 6);
    
    SQL> SELECT * FROM v;
    
         BYTES    EXTENTS
    ---------- ----------
         65536          1
    
    SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
      2  TABLESPACE test
      3  STORAGE (FREELIST GROUPS 7);
    
    SQL> SELECT * FROM v;
    
         BYTES    EXTENTS
    ---------- ----------
       1048576          1

    This is because all freelist blocks must be in the first extent. The database engine recognize the “problem” and, therefore, increases the size of the first extent.

    Cheers,
    Chris

  4. Kumar's Gravatar Kumar
    August 13, 2009    

    Hi Chris:
    Very good explanation. So starting 11.1.0.7 you can limit the number of extents if you know the volume of data.
    But since really number of extents dont effect performance, how would this be beneficial?

    Thank you
    Kumar

    • August 13, 2009    

      Hi Kumar

      The number of extents itself is not important for performance. What is important for performance is the size of the extents. In fact, the database engine is not able to perform an I/O operation that spans several extents. As a result, if a lot of small extents have to be read, a lot of small I/O operations have to be performed. Hence, it is much better to avoid small extents…

      Cheers,
      Chris

  5. Philipp's Gravatar Philipp
    August 14, 2009    

    Hi Chris,

    Very neat explanation! From 11.1.0.7 on, is there really a reason to continue using
    “uniform extent size”? It looks like “system managed extent size” with accurate
    INITIAL and NEXT values is the way to go. At least I have some difficutlties to see the cons.

    Thanks, and have a nice weekend
    Philipp

  6. August 16, 2009    

    Hi Philipp

    Provided that sensible values for INITIAL and NEXT are specified, I also do not see why uniform extent size tablespaces should be used. So, the main con is that you have to specify them… And, if you look around, I see lot of people that are no longer willing to specify such parameters at the table level.

    Cheers,
    Chris

  7. Merwyn's Gravatar Merwyn
    December 31, 2009    

    Just wanted to point out that “Improvement #1″ is not a new feature of 11.1.0.7.
    The behaviour is the same in 11g.
    Initial size is taken into consideration to determine the number of extents that will be allocated.
    The total number of extents allocated will depend if the tablespace is using SYSTEM or UNIFORM size.
    The size of the extents that are allocated will depend on the size specified for initial clause.

    The following test case show this.

    SQL> create tablespace test1 datafile ‘/u02/oradata/ordb/dbfiles/test02.dbf’ size 5m extent management local uniform size 65536;
    Tablespace created.

    SQL> create table mytable1 (empno number(2), ename varchar2(100)) tablespace test1 storage (initial 2M);
    Table created.

    SQL> select file_id, extent_id, block_id, blocks, bytes from dba_extents where segment_name = ‘MYTABLE1′;
    FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
    ———- ———- ———- ———- ———-
    7 0 9 8 65536
    7 1 17 8 65536
    7 2 25 8 65536
    …….
    …….
    7 30 249 8 65536
    7 31 257 8 65536

    SQL> create tablespace test2 datafile ‘/u02/oradata/ordb/dbfiles/test03.dbf’ size 5m;
    Tablespace created.

    SQL> create table mytable2 (empno number(2), ename varchar2(100)) tablespace test2 storage (initial 2M);
    Table created.

    SQL> select file_id, extent_id, block_id, blocks, bytes from dba_extents where segment_name = ‘MYTABLE2′;
    FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
    ———- ———- ———- ———- ———-
    8 0 9 128 1048576
    8 1 137 128 1048576

    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
    PL/SQL Release 11.1.0.6.0 – Production
    CORE 11.1.0.6.0 Production
    TNS for Linux: Version 11.1.0.6.0 – Production
    NLSRTL Version 11.1.0.6.0 – Production

  8. January 25, 2010    

    Hi Merwyn

    Sorry, but I don’t understand what do you want to show with your test case. Where is the difference with mine? As I pointed out in the post, the value of the INITIAL parameter is only “fully” used as of 11.1.0.7. At least this is what I see on my test databases… In your case, what happens if you create the table with the following SQL statement? (1M instead of 2M)

    create table mytable2 (empno number(2), ename varchar2(100)) tablespace test2 storage (initial 1M);

    Cheers,
    Chris

  9. July 20, 2010    

    Chris,
    Thanks again for another wonderful (albeit a bit old) blog post that I am just running across. I’m used to the 10g way of System managed extents and could find no documentation of 1M being the NEXT extent size. I’m used to seeing NEXT be NULL in *_tables, but in 11.1.0.7, it shows at 1048576 for NEXT and 65536 for Initial (upon creation of a table without specifying any storage parameters).

    The impact I see here is why application that create 1000s of tables and indexes but only use a a handful. Sure, everything starts out at 64K but as soon as an extent is needed, the segment shoots up to 1M. Maybe this is desirable behavior on Oracle’s part? It is just a change from what I know of 10g and System managed extents. I guess I’ll have to wait until we get to 11gR2 and deferred segment creation :(

  1. Blogroll Report 07/08/2009 – 14/08/2009 « Coskan’s Approach to Oracle on August 14, 2009 at 17:47
  2. Log Buffer #158: a Carnival of the Vanities for DBAs | Pythian Group Blog on August 14, 2009 at 18:40
  3. Entradas de Oracle semana 33 « Gruñidos sobre Oracle y SAP on August 16, 2009 at 19:43
  4. Fragmentation 1 « Oracle Scratchpad on July 13, 2010 at 21:33
  5. 碎片(Fragmentation)–介绍 « a db thinker's home on July 27, 2010 at 08:18
  6. 碎片(Fragmentation)–介绍 | web开发 on October 26, 2011 at 04:28
  7. Autoallocate and PX « Oracle Scratchpad on April 5, 2012 at 19:22

Leave a Reply

Your email address will not be published. Required fields are marked *