The Automatic Segment Space Management is one such feature which simplifies the management of free space within an object such as tables or indexes, improves space utilization, and provides significantly better out of box performance and scalability. In versions of the database prior to Oracle9i, data structures called the FREELISTS keep track of blocks within an object which have enough free space to allow insertion of a new row. Administrators can define the number of FREELISTS and FREELIST GROUPS when creating an object. The value of the parameter PCTUSED is used to place a block in and out of the FREELIST. The new mechanism makes the space management within an object completely transparent by using bitmaps to track the space utilization of each data block allocated to the object. The state of the bitmap indicates how much free space exists in a given data block (i.e. > 75%, between 50 and 75%, between 25 to 50% or < 25%) as well as whether it its formatted or not. The new implementation eliminates the necessity to tune space management related controls (such as FREELISTS, FREELIST GROUPS and PCTUSED) thereby freeing database administrators from manually managing the space within a database object. At the same time, it improves the space utilization since the database now has a more accurate knowledge of how free a data block is. This enables better reuse of the available free space especially for objects with rows of highly varying size. Additionally, the Automatic  Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups .  

The performance and manageability gains provided by the Automatic Segment Space Management feature are particular noticeable in a Real Application Cluster environment. It removes the need to alter the number of FREELISTS and FREELIST GROUPS when new instances are brought online thereby saving the downtime associated with such table reorganizations. The chart shown below presents the result of an Oracle internal benchmark comparing the performance of automatic and manual segment space management. This benchmark was conducted on a two node (6 x 336 MHz CPU, 4 GB RAM per node)  Real Application Cluster database by inserting about 3 million rows in a table. The Automatic Segment Space Management provided over 35% performance gain over an optimally tuned segment (8 FREELIST GROUPS, 20 FREELISTS) using the manual mode.   

Automatic Segment Space Management Provides Significantly Better Out of Box Performance and Scalability

The Automatic Segment Space Management feature is available only with locally managed tablespaces. A new clause SEGMENT SPACE MANAGEMENT in the CREATE TABLESPACE command allows administrators to choose between automatic and manual modes. A tablespace created with MANUAL segment space management continues to use FREELISTS for managing free space within the objects located in it.  The following example illustrates how to create a tablespace with Automatic Segment Space Management.

All objects created in the above tablespace will use the automatic segment space management feature to manage their free space.  Any specification of PCTUSED, FREELISTS and FREELIST GROUPS parameters for objects created in this tablespace will be ignored. A new column called SEGMENT_SPACE_MANAGEMENT has been added to the DBA_TABLESPACES view to indicate the segment space management mode used by a tablespace.   

The compatible parameter must be set to 9.0.0 or higher in order to use this feature.

More Info

	Server Manageability : A DBA's Mantra for A Good Night's Sleep - Technical White Paper
	Oracle9i Database Administrator's Guide Release 1 (9.0.1) - Chapter 11: Specifying Segment Space Management in Locally Managed Tablespaces
	Oracle9i Supplied PL/SQL Packages and Types Reference - Chapter 60: DBMS_SPACE
	Oracle9i Supplied PL/SQL Packages and Types Reference - Chapter 47: DBMS_REPAIR

Oracle9i Database Daily Features

Archives