Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Deferred Segment Creation

4 September 2009 25 Comments Written by Christian Antognini

As you know Oracle Database 11g Release 2 was just released. As a result, it is time to start a series of post about some new features. I’ll start with one that is not really related to performance… In fact, this post is a kind of follow-up to a comment written by Bernd Eckenfels about one of my previous posts: System Managed Extent Size – 11g Improvements. The following is an excerpt of Bernd’s comment:

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.

I agree with Bernd, this might be a real problem when you have a lot of unused segments. Oracle also recognized this issue and, in Oracle Database 11g Release 2, provides a partial (yes, only partial…) solution: deferred segment creation. The idea of deferred segment creation is very simple. The segments related to a table or an index are not immediately created when the CREATE TABLE or CREATE INDEX statement is executed, but only when the first row is inserted into the table. Let’s have a look to an example:

SQL> CREATE TABLE t (n number);

Table created.

SQL> SELECT segment_created
  2  FROM user_tables
  3  WHERE table_name = 'T';

SEGMENT_CREATED
---------------
NO

SQL> SELECT segment_name
  2  FROM user_segments
  3  WHERE segment_name = 'T';

no rows selected

SQL> INSERT INTO t VALUES (1);

1 row created.

SQL> SELECT segment_created
  2  FROM user_tables
  3  WHERE table_name = 'T';

SEGMENT_CREATED
---------------
YES

SQL> SELECT segment_name
  2  FROM user_segments
  3  WHERE segment_name = 'T';

SEGMENT_NAME
------------------------------
T

It is important to point out that all segments related to a table are created when the first row is inserted into it. And that, even if they are not used to store data. For example, the segments for an index or a lob are created even if they are not used. This is the reason why I wrote that this is only a partial solution to the problem mentioned by Bernd. The following example illustrates this for a lob:

SQL> CREATE TABLE t (n number, c clob, b blob);

Table created.

SQL> SELECT column_name, segment_name
  2  FROM user_lobs JOIN user_segments USING (segment_name)
  3  WHERE table_name = 'T';

no rows selected

SQL> INSERT INTO t (n) VALUES (1);

1 row created.

SQL> SELECT column_name, segment_name
  2  FROM user_lobs JOIN user_segments USING (segment_name)
  3  WHERE table_name = 'T';

COLUMN_NAME  SEGMENT_NAME
------------ ------------------------------
C            SYS_LOB0000073904C00002$$
B            SYS_LOB0000073904C00003$$

In addition, the feature doesn’t work in all situations (yet). In other words, there are some restrictions. The most important, in my opinion, is that only non-partitioned table can take advantage of it. A full list of restrictions is obviously available in the documentation.

Note that deferred segment creation is enabled by default. To enable/disable it at the session or system level, you can change the initialization parameter DEFERRED_SEGMENT_CREATION. In addition, it’s also possible to enable/disable it for a single table by specifying the deferred segment creation clause.

11gR2
Synthetic Commits and Rollbacks
Script to Download 11gR2 Documentation

25 Comments

7 Pings/Trackbacks

  1. Bernd Eckenfels Bernd Eckenfels
    6 September 2009    

    Cool :) Good to know. No more emptylob tablespaces and manual keepin track of them :)

    Reply
  2. Dion Cho Dion Cho
    8 September 2009    

    Cool stuff.

    But it seems weird that Oracle has determined to have this feature enabled by default, which would make many people embarassed without knowing this new feature.

    Reply
  3. Blogroll Report 28/08/2009 – 04/09/2009 « Coskan’s Approach to Oracle Blogroll Report 28/08/2009 – 04/09/2009 « Coskan’s Approach to Oracle
    9 September 2009    

    […] Christian Antognini-Deferred Segment Creation […]

  4. Chandra Pabba Chandra Pabba
    9 September 2009    

    Just FYI…though the documentation illustrates the use of ALTER TABLE statement for modifying the SEGMENT CREATION clause, but apparently, it doesn’t work. It errors out stating that it’s an INVALID alter table command.

    Reply
  5. Noons Noons
    9 September 2009    

    I’ve asked Nicolas Gasparotto to test the actual use of the deferred segment allocation and its practical application with an ALTER TABLE MOVE, which is specifically documented as valid in the manual.

    Nicholas was most kind in providing the test bed. This was a follow-up from the discussion we had about it in my blog.

    Unfortunately, it doesn’t work. The software refuses the ALTER TABLE MOVE with a syntax error.

    Ah well: it was a good idea. As usual, the implementation by Oracle let it down…

    Maybe this will work properly in 11gr3?

    Reply
  6. Nicolas Gasparotto Nicolas Gasparotto
    9 September 2009    

    I would join Chandra, I was trying to ALTER TABLE, but so far no luck.
    I opened a thread in OTN Forum to see if someone alread got it work

    Let see.

    An other note about that feature, once you got a segment for a table, it does not look so easy to get
    rid off, I tried many different things by expdp/impdp but everytime the segment is came back, see my test
    http://gasparotto.blogspot.com/2009/09/get-rid-off-segment-of-empty-table.html

    Nicolas.

    Reply
  7. Christian Antognini Christian Antognini
    9 September 2009    

    Thank you all for providing interesting information about this topic. Honestly, I didn’t thought to test the ALTER TABLE. What I did test however, is the TRUNCATE TABLE. In other words, I wanted to know whether a TRUNCATE removes the segments… It is not the case. What a pity!

    Cheers,
    Chris

    Reply
  8. Nicolas Gasparotto Nicolas Gasparotto
    9 September 2009    

    Well, I raise a SR to help on the doc reading about that topic.
    ALTER TABLE MOVE path does not provide SEGMENT CREATION clause :
    move_table_clause -> segment_attributes_clause -> physical_attributes_clause -> storage_clause

    The right path to reach this clause is the following :
    ALTER TABLE > column_clause > add_column_clause > column_properties > nested_table_col_properties > physical_properties > deferred_segment_creation > segment_attributes_clause
    If you read it correctly, that won’t work for standard table and “common”
    ALTER TABLE statement.
    So, no luck on the way we would expected.
    Nicolas.

    Reply
  9. Stefan Stefan
    9 September 2009    

    Also note that tables created in sys or system schema will have a segment associated to it.
    So once again: SYS and SYSTEM users are special!

    Reply
  10. Top 10 Oracle 11gR2 New Features | Structured Data Top 10 Oracle 11gR2 New Features | Structured Data
    9 September 2009    

    […] catching up on blog posts I see that Jonathan Lewis, Christian Antognini and Nuno Souto picked up on the deferred segment creation new feature in Oracle 11gR2. In keeping […]

  11. Chandra Pabba Chandra Pabba
    10 September 2009    

    Nicolas/Christian,

    Looks like the ALTER TABLE command is not expected (or supposed) to change the SEGEMENT CREATION attribute. It might have just found a place in the documentation in error (again, this is just my guess based on the following finding!). Here is what I did:

    1) Created a table with deferred segment creation
    2) Another table without deferring segment creation
    3) Used DBMS_METADATA_DIFF to see the difference between the structures, here is what it says:

    SQL> Select dbms_metadata_diff.compare_alter(‘TABLE’,’APP_DATA’,’APP_DATA_TEMP’, ‘CHANDRA’,’CHANDRA’,null,’BDBE01′) DIFF from dual;

    DIFF
    ——————————————————————————–
    — Cannot ALTER to make segment creation deferred TABLE “CHANDRA”.”APP_DATA”
    ALTER TABLE “CHANDRA”.”APP_DATA” RENAME TO “APP_DATA_TEMP”

    Hope it makes sense.

    Thanks
    Chandra

    Reply
  12. Log Buffer #161: a Carnival of the Vanities for DBAs | Pythian Group Blog Log Buffer #161: a Carnival of the Vanities for DBAs | Pythian Group Blog
    11 September 2009    

    […] has been released, it’s time to start looking into the details. Christian Antognini’s Deferred Segment Creation and his Script to Download 11gR2 Documentation are useful articles to get going. If you need help […]

  13. Entradas de Oracle semanas 35-37 « Gruñidos sobre Oracle y SAP Entradas de Oracle semanas 35-37 « Gruñidos sobre Oracle y SAP
    13 September 2009    

    […] Antognini: Deferred Segment Creation y Script to Download 11gR2 […]

  14. Christo Kutrovsky Christo Kutrovsky
    21 September 2009    

    How useless … Major points:

    – doesn’t work on partitioned tables (although interval partitioning somewhat offsets this but still)
    – truncate doest not remove

    Why bother implementing it properly … I am sure it’s complicated, but still …

    Reply
  15. Anthony Anthony
    22 January 2010    

    Just use:

    alter table allocate extent;

    to allocate segment extents.

    Reply
  16. Christian Antognini Christian Antognini
    25 January 2010    

    Hi Anthony

    What is the purpose of doing so? Sorry, but I don’t understand… I mean, here the point is to *not* allocate extents…

    Cheers,
    Chris

    Reply
  17. Paul Muller Paul Muller
    19 April 2010    

    Users of deprecated tools beware! exp (the conventional data export tool) does not export tables that have not yet had their segment allocated.

    Reply
  18. Giuseppe Lottini Giuseppe Lottini
    24 May 2010    

    I had to downgrade from EE 11.2 to SE 11.2, I was unable to impdp the schemas from EE because of “feature not available” regarding the “deferred segment creation” which is default. Now I deleted EE Database and I can’t import schemas on the new (SE) one. I can’t figure how to complete my porting. Any suggestion??

    Reply
    • Christian Antognini Christian Antognini
      24 May 2010    

      Ciao Giuseppe

      Ufff… really weird situation… I didn’t know that it was an EE edition feature.

      To solve the problem I would try to create the tables before importing the data.
      If you do not have the scripts to create the tables, you should be able to extract the DDL from the DP file with the SQLFILE option.

      HTH
      Chris

      Reply
  19. Deferred Segment Creation « Oracle and other Deferred Segment Creation « Oracle and other
    1 July 2010    

    […] me un grande entusiasmo, comunque disattivabile tramite apposito parametro come ricordato anche da Antognini in un suo post di approfondimento […]

  20. Christian Antognini Christian Antognini
    5 October 2010    

    I just wrote a new post that shows how to get rid of empty segments. Refer to Deferred Segment Creation as of 11.2.0.2.

    Reply
  21. Avi Avi
    23 November 2010    

    in case getting error,
    ORA-00439: feature not enabled: Deferred Segment Creation
    what can you do?

    Reply
  22. Christian Antognini Christian Antognini
    23 November 2010    

    Hi Avi

    You are getting this error because deferred segment creation is only available in Enterprise Edition (see https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87.

    But, to answer your question, it would be necessary to know how you get it. In fact, if you ask the question, I guess that you know this restriction and, therefore, it’s not because you specified the “segment creation deferred” option in a CREATE TABLE statement… So, let me know what you are doing.

    Cheers,
    Chris

    Reply
  23. Deferred Segment Creation in PeopleSoft « Ukrainian Oracle User Group Deferred Segment Creation in PeopleSoft « Ukrainian Oracle User Group
    30 June 2011    

    […] http://antognini.ch/2009/09/deferred-segment-creation/ […]

  24. Deferred Segement Creation PARALLEL | Spot On Oracle Deferred Segement Creation PARALLEL | Spot On Oracle
    6 August 2014    

    […] up on Chris Antognini’s findings regarding deferred segment creation (here and here) I noticed another restriction that still finds no mention even in the latest Oracle […]

  1. Blogroll Report 28/08/2009 – 04/09/2009 « Coskan’s Approach to Oracle on 9 September 2009 at 03:17
  2. Top 10 Oracle 11gR2 New Features | Structured Data on 9 September 2009 at 22:03
  3. Log Buffer #161: a Carnival of the Vanities for DBAs | Pythian Group Blog on 11 September 2009 at 18:42
  4. Entradas de Oracle semanas 35-37 « Gruñidos sobre Oracle y SAP on 13 September 2009 at 12:42
  5. Deferred Segment Creation « Oracle and other on 1 July 2010 at 12:02
  6. Deferred Segment Creation in PeopleSoft « Ukrainian Oracle User Group on 30 June 2011 at 01:37
  7. Deferred Segement Creation PARALLEL | Spot On Oracle on 6 August 2014 at 16:08

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.