Implementing Deferred Segment Creation After an Upgrade

I have written previously about Deferred Segment Creation. Each empty table and its indexes use only 64Kb (assuming an 8Kb block size and locally managed tablespaces), but in a PeopleSoft there can be be tens of thousands of such tables and that adds up to a saving worth making.

If you are upgrading your database to 11gR2 , you might want to make sure that you are using it.  Deferred segment creation was introduced in Oracle 11.2.0.2 and it became the default in 11.2.0.3.  However, any table created in a previous version will have a physical segment.

This problem could affect any system, but it also manifests itself in PeopleSoft in a particular way.

When you run the alter scripts in PeopleTools a table may be recreated.  If it is a regular table (record type 0) then the CREATE TABLE command will not specify a segment creation clause and so the segment creation will be deferred until rows are inserted.

CREATE TABLE PS_TL_IPT1 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
   EMPLID VARCHAR2(11) NOT NULL,
...
   INITIAL_SEQ_NBR DECIMAL(15) NOT NULL) TABLESPACE TLWORK STORAGE
 (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0)
 PCTFREE 10 PCTUSED 80
/

However, from PeopleTools 8.51, Application Designer uses the Oracle delivered DBMS_METADATA package to extract the DDL to recreate the object from the actual object.  However, this behaviour only occurs for Temporary working storage tables (record type 7).  Yes, these are exactly the tables that would benefit most from deferred segment creation because in many systems there are many unused temporary table instances.  If table was created under a version of the database prior to 11.2.0.2 then the segment will exist and DBMS_METADATA will generate the DDL with the SEGMENT CREATION IMMEDIATE clause. 

-- Create temporary table 
CREATE TABLE PSYPERSON (EMPLID VARCHAR2(11) NOT NULL,
...
   LAST_CHILD_UPDDTM tIMESTAMP) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HRLARGE"
/

You can use DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS to remove the segments for any empty tables (and their indexes) for which the segment has been previously created.  There would be no harm in simply running this program for every table in the system.  If there are rows then DBMS_SPACE_ADMIN will take no action.

The following script identifies candidate tables where the statistics suggest that there are no rowa or where there are no statistics.  I am indebted to Tim Hall for the idea for this tip.

set serveroutput on 
BEGIN
 FOR i IN (
  SELECT owner, table_name
  FROM   all_tables
  WHERE  owner = 'SYSADM'
  AND    segment_created = 'YES'
  AND    temporary = 'N'
  AND   (num_rows = 0 OR num_rows IS NULL)
 ) LOOP
  dbms_output.put_line(i.owner||'.'||i.table_name);
  dbms_space_admin.drop_empty_segments (
    schema_name    => i.owner,
    table_name     => i.table_name);
 END LOOP;
END;
/

As this package drops the empty segments, the SEGMENT_CREATED column on USER_TABLES changes to NO and if you were to extract the DDL with DBMS_METADATA the SEGMENT CREATION clause would have changed to DEFERRED.

As soon as any data is inserted, the segment is created, SEGMENT_CREATED changes to YES and the DDL generated by DBMS_METADATA would have SEGMENT CREATION IMMEDIATE.

The result is that 64Kb of space (assuming a block size of 8Kb) will be freed up for each empty table and index segment that is dropped. Your mileage may vary, but in my demo HR database that is over 20000 tables and 25000 indexes. 2.7Gb isn't a vast amount these days, but it is an easy win.

Added 1.2.2014:

To answer Noons' question below.  So long as the table or partition doesn't have any rows, the segment will be dropped it will as if the segment creation had been deferred.  You don't have to do anything special to the table.  There is no problem applying this to any empty tables create with their segments.  Here is a simple test with my results on 11.2.0.3:

I will create a table and I have explicitly created the segment immediately, then I insert a row, commit the insert and delete the row.  I haven't even bothered to commit the delete.

SQL> create table t(a number) segment creation immediate;
SQL> insert into t values(42);
SQL> commit;
SQL> delete from t;
SQL> select segment_type, segment_name, tablespace_name from user_Segments where segment_name = 'T';

SEGMENT_TYPE       SEGMENT_NAME    TABLESPACE_NAME
------------------ --------------- ------------------------------
TABLE              T               PSDEFAULT

SQL> select table_name, segment_created from user_tables where table_name = 'T';

TABLE_NAME                     SEG
------------------------------ ---
T                              YES

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
  CREATE TABLE "SYSADM"."T"
   ( "A" NUMBER
   ) SEGMENT CREATION IMMEDIATE

So, at the moment the segment exists, it has had rows in it, but they have been deleted and the table is empty. If I run DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS the segment is dropped.

SQL> execute dbms_space_admin.drop_empty_segments (user,'T');
SQL> select segment_type, segment_name, tablespace_name from user_Segments where segment_name = 'T';

no rows selected

SQL> select table_name, segment_created from user_tables where table_name = 'T';

TABLE_NAME                     SEG
------------------------------ ---
T                              NO

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
  CREATE TABLE "SYSADM"."T"
   ( "A" NUMBER
   ) SEGMENT CREATION DEFERRED