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.
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.
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: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
/-- 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"
/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 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:
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
@go-faster.co.uk on BlueSky
Posts
