We can see that no actual segments have been allocated. The default number of extents when creating an object is now effectively zero. Oracle now defers the creation of the segment and the actual allocation of extents and storage until the point in time when the first row gets inserted.

This means for those packaged applications where a large number of objects get created of which relatively few are actually ever used by the specific deployment of the application (eg. SAP) , a substantial amount of storage could potentially be saved. It also can save a significant amount of time deploying such applications as the overheads associated with actually creating the never to be used segments can be avoided.

There also some subtle performance implications when the application attempts to access some of these “empty” tables. I’m just going to create another table, but this one populated with a bunch of rows and run a query that joins this with the “empty” table:

With a table which has yet to be populated, the CBO knows there can be no rows associated with this table (and hence no returns return from this query). You can’t get any less than 0 consistent gets. Note previously, the CBO would be forced to perform at the very least one read (if not more) as it can’t “know” there are indeed no rows, even with the table statistics set at zero (as they may no longer be accurate) until it actually physically accesses a segment.

The segment is not actually created and storage allocated to the object until the first row is inserted into the table. This means that this first insert will be a relatively expensive operation as the work associated with creating the segment and allocating the initial extent needs to be implicitly performed in the background at this time. Not only for the table itself, but for any dependant object as well (eg. indexes).

Although it’s only a single row insert, note the high number of recursive calls and logical I/Os due in large part to the segment(s) being implicitly created in the background at this time. Subsequent inserts are nowhere near as expensive:

Note all the index segments have been created although only 2 of them actually contain data.

Finally, there are some implications with regard to how quotas are enforced that potentially may cause issues. Prior to Oracle11g Release 2, if a user tried to create a segment in a tablespace for which they don’t have sufficient privileges, the operation will fail during the creation of the object:

However with Oracle11g Release 2, as no segment is actually created and hence storage allocated when an object is created, such creation of objects will now initially succeed. No quotas can be violated if no storage is actually used during the creation of the object:

Share this:

Like this:

Related

The last point has caught me out a few times. It would have been nice if Oracle had added a check for the quota to the CREATE TABLE processing, rather than relying on the extent creation to police it. They we would then have the same behavior with relation to table creation and quotas.

I know it’s a *feature*, but it doesn’t half feel like a bug when it happens. :)

It can rather hide a number of potiental problems “under the carpet” for a while for one to only trip up over it all at a later point in time.

The obvious issue of course is that fixing this means changing code unnecessarily as things do “work” as such. I have alway regarded having no quota as being somewhat different as having exceeded a nominated quota limit but the code doesn’t really make such a delineation.

This is a good one. What about partitions and sub partitions? In one of our systems we are facing a lot of wasted space as the initial extent for the sub partitions is 10MB while the data is lesser. Maybe you can include the analysis for partitions and subpartitions in this post to make it more comprehensive.

> Oracle now defers the creation of the segment and
> the actual allocation of extents and storage
> until the point in time when the first row gets inserted.

For what its worth, Oracle EE with the the parameter deferred_segment_creation set to the default TRUE. The feature is not available in SE.
As a side effect, the feature renders conventional export (finally) useless, since empty tables will not get exported.

> As a side effect, the feature renders conventional export (finally) useless, since empty tables will not get exported.

What do you mean by “conventional export”? exp (11.2.0.2) did merrily export a segmentless table with an index, and imp imported it for me, and it remained segmentless after importing…

The thing is, you don’t get the benefit automatically when upgrading, though. If you upgrade a database in place, the tables and indexes already have segments and the empty ones are of course not dropped. If you do a exp from the old version and imp into a new 11.2 database, you still get all segments created, because the DDLs contain an explicit INITIAL specification. The only way to get rid of the empty segments (that I could think of) was to either pre-create the objects, or explicitly do a TRUNCATE TABLE … DROP ALL STORAGE on the empty ones afterwards.
I’m not sure if there is a difference in behaviour between exp/imp and expdp/impdp in this matter.