Striving for Optimal Performance

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.