Oracle9i New Feature Series: Automatic Segment Space Management

Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps. It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.

ASSM can be specified only with the locally managed tablespaces (LMT). The CREATE TABLESPACE statement has a new clause SEGMENT SPACE MANAGEMENT. Oracle uses bitmaps to manage the free space. 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. Bitmaps allow Oracle to manage free space more automatically.

Here is an example:

CREATE TABLESPACE myts
DATAFILE '/oradata/mysid/myts01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;

The storage parameters PCTUSED, FREELISTS and FREELIST GROUPS specified while creating a table are ignored by Oracle on a LMT ASSM tablespace. Oracle does not produce an error.

One huge benefit of having ASSM is to reduce the “Buffer Busy Waits” you see on segments.

Peter Sherman’s presentation at RMOUG is a good one.