English

FAQ: Is Oracle's automatic segment space management recommended?

Question

Is Oracle's automatic segment space management recommended?

Answer

When creating a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets users specify how free and used space within a segment is to be managed.

When set to automatic, Oracle uses bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap.

Automatic segment space management is advantageous when there is a high degree of insert concurrency for a segment. But, there is a significant disadvantage in the loss of block space usage and the distribution of inserts across noncontigous blocks within the segment.

ESRI does not recommend using automatic segment space management because of the potential impact it has on a spatial query when fetching each Oracle block. Under ideal conditions, the rows being retrieved by a spatial query should be clustered in the same physical blocks to avoid expensive disk reads. When automatic segment space management is enabled, as rows are inserted they can be potentially distributed among all blocks in the segment. Which will result in a significant number of blocks having to be fetched upon the execution of the spatial query.

For additional information on automatic segment space management refer to the Oracle 9i Database Administrator Guide (ArcSDE 8.3 & 9.0) and Oracle 10G Database Administrator Guide (ArcSDE 9.1).