Sep 04 2009
Deferred Segment Creation
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:
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.
September 6th, 2009 at 2:43 am
Cool :) Good to know. No more emptylob tablespaces and manual keepin track of them :)
September 8th, 2009 at 1:32 am
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.
September 9th, 2009 at 3:17 am
[...] Christian Antognini-Deferred Segment Creation [...]
September 9th, 2009 at 3:41 am
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.
September 9th, 2009 at 8:41 am
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
http://forums.oracle.com/forums/thread.jspa?threadID=955170&tstart=0
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.
September 9th, 2009 at 9:40 am
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?
September 9th, 2009 at 10:09 am
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
September 9th, 2009 at 10:31 am
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.
September 9th, 2009 at 4:08 pm
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!
September 9th, 2009 at 10:03 pm
[...] 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 [...]
September 10th, 2009 at 3:32 am
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
September 11th, 2009 at 6:42 pm
[...] 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 [...]
September 13th, 2009 at 12:42 pm
[...] Antognini: Deferred Segment Creation y Script to Download 11gR2 [...]
September 21st, 2009 at 8:14 pm
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 …
January 22nd, 2010 at 5:11 am
Just use:
alter table allocate extent;
to allocate segment extents.
January 25th, 2010 at 4:46 pm
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
April 19th, 2010 at 6:44 pm
Users of deprecated tools beware! exp (the conventional data export tool) does not export tables that have not yet had their segment allocated.
May 24th, 2010 at 9:09 pm
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??
May 24th, 2010 at 9:56 pm
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
July 1st, 2010 at 12:02 pm
[...] me un grande entusiasmo, comunque disattivabile tramite apposito parametro come ricordato anche da Antognini in un suo post di approfondimento [...]
October 5th, 2010 at 9:32 am
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.
November 23rd, 2010 at 8:27 am
in case getting error,
ORA-00439: feature not enabled: Deferred Segment Creation
what can you do?
November 23rd, 2010 at 9:19 am
Hi Avi
You are getting this error because deferred segment creation is only available in Enterprise Edition (see http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB).
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
June 30th, 2011 at 1:37 am
[...] http://antognini.ch/2009/09/deferred-segment-creation/ [...]