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 as of 11.2.0.2

5 October 2010 9 Comments Written by Christian Antognini

One year ago I wrote a post entitled Deferred Segment Creation. If you read the comments related to it you can see that several people were concerned by the fact that it was not possible to easily get rid of segments associated to empty tables. That was with version 11.2.0.1. Now we have version 11.2.0.2 and this specific problem has been fixed.

As of 11.2.0.2 DBMS_SPACE_ADMIN, and not DBMS_SPACE as written in the New Feature Guide, provides the following procedures:

  • MATERIALIZE_DEFERRED_SEGMENTS
  • DROP_EMPTY_SEGMENTS

With them it is possible to materialize/drop the segments of the empty tables and their associated objects. Depending on the specified parameters, they can process all database segments, all segments owned by a specific schema, all segments associated to a specific table, or all segments associated to a specific partition.

Let’s have a look to an example:

  • Create a partitioned table with a primary key and a LOB column
SQL> CREATE TABLE t (
  2    id NUMBER,
  3    c CLOB,
  4    CONSTRAINT t_pk PRIMARY KEY (id) USING INDEX LOCAL
  5  )
  6  SEGMENT CREATION DEFERRED
  7  PARTITION BY HASH(id) PARTITIONS 4;
  • Show that no segment is available
SQL> SELECT segment_name, segment_type, bytes, extents
  2  FROM user_segments
  3  WHERE segment_name IN ('T','T_PK')
  4  OR segment_name IN (SELECT segment_name
  5                      FROM user_lobs
  6                      WHERE table_name = 'T')
  7  ORDER BY 1,2;

no rows selected
  • Materialize the segments
SQL> BEGIN
  2    dbms_space_admin.materialize_deferred_segments(
  3      schema_name => 'CHA',
  4      table_name  => 'T'
  5    );
  6  END;
  7  /
  • Show that the segments are now available
SQL> SELECT segment_name, segment_type, bytes, extents
  2  FROM user_segments
  3  WHERE segment_name IN ('T','T_PK')
  4  OR segment_name IN (SELECT segment_name
  5                      FROM user_lobs
  6                      WHERE table_name = 'T')
  7  ORDER BY 1,2;

SEGMENT_NAME                   SEGMENT_TYPE              BYTES    EXTENTS
------------------------------ -------------------- ---------- ----------
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
T                              TABLE PARTITION         8388608          1
T                              TABLE PARTITION         8388608          1
T                              TABLE PARTITION         8388608          1
T                              TABLE PARTITION         8388608          1
T_PK                           INDEX PARTITION           65536          1
T_PK                           INDEX PARTITION           65536          1
T_PK                           INDEX PARTITION           65536          1
T_PK                           INDEX PARTITION           65536          1
  • Get rid of the segments (this is possible because the table is empty)
SQL> BEGIN
  2    dbms_space_admin.drop_empty_segments(
  3      schema_name => 'CHA',
  4      table_name  => 'T'
  5    );
  6  END;
  7  /
  • Show that no segment is available
SQL> SELECT segment_name, segment_type, bytes, extents
  2  FROM user_segments
  3  WHERE segment_name IN ('T','T_PK')
  4  OR segment_name IN (SELECT segment_name
  5                      FROM user_lobs
  6                      WHERE table_name = 'T')
  7  ORDER BY 1,2;

no rows selected

The attentive reader might have noticed two additional new features available since 11.2.0.2. The first one is that deferred segment creation is also supported for partitioned table. The second one is that the initial extents associated to partitioned tables and partitioned LOBs (but not to partitioned indexes) have a new default size of 8MB. Note that this new default is only used for segments created in an EXTENT MANAGEMENT LOCAL AUTOALLOCATE tablespace.

11gR2, Partitioning
Parallel Full Table Scans Do Not Always Perform Direct Reads
UKOUG Conference in Birmingham

9 Comments

2 Pings/Trackbacks

  1. Nicolas Gasparotto Nicolas Gasparotto
    5 October 2010    

    Yes, that’s rather nice, adding to that the TRUNCATE within DROP ALL STORAGE option to deallocate *ALL* the segments to get a segment free table.

    Nicolas.

    Reply
  2. Christian Antognini Christian Antognini
    5 October 2010    

    Hi Nicolas

    You are right, I forgot to mention the enhanced TRUNCATE syntax. Thank you for pointing it out.

    Cheers,
    Chris

    Reply
  3. bpolarsk bpolarsk
    8 October 2010    

    Does this ‘dbms_space_admin.drop_empty_segment’ works for partitions flagged missing after a restore skip tablespaces ? Up to now, we were forced to exchange these with tables created specially for that.

    Reply
    • Christian Antognini Christian Antognini
      11 October 2010    

      Hi

      I didn’t test it and, therefore, I cannot be sure at all… But, honestly, I would be surprised that it works in such a situation.

      Cheers,
      Chris

      Reply
  4. Smee Smee
    10 December 2010    

    Hi,
    does this (deferred_segment_creation)parameter can define in the response file for silent install?

    Cheers.

    Reply
    • Christian Antognini Christian Antognini
      12 December 2010    

      Hi Smee

      I nener did that… But as for any other parameter you should be able to specify it through the DBCA parameter named initParams.

      Cheers,
      Chris

      Reply
  5. Smee Smee
    13 December 2010    

    Hi Christian,

    tnx for your quick response. I tried to put this parameter into the response file, but this is an advanced parameter and I failed. I try something else.

    Thanks once more.

    Best regards,
    Smee

    Reply
  6. October 2010 Blogroll Report | AskDba.org Weblog October 2010 Blogroll Report | AskDba.org Weblog
    28 December 2011    

    […] highlights changes related to Deferred Segment creation introduced in 11.2.0.2 in following post http://antognini.ch/2010/10/deferred-segment-creation-as-of-11-2-0-2/ Jarneil writes post about SQL Plan Management in 11g and discusses a case when adding index to […]

  7. Deferred Segement Creation PARALLEL | Spot On Oracle Deferred Segement Creation PARALLEL | Spot On Oracle
    7 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 documentation. […]

  1. October 2010 Blogroll Report | AskDba.org Weblog on 28 December 2011 at 07:22
  2. Deferred Segement Creation PARALLEL | Spot On Oracle on 7 August 2014 at 06:53

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.