Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Automatic Space Management in Oracle Oracle Tips by Burleson

Space management within Oracle is a very complex topic, and an area of Oracle that has a tremendous impact on the performance of INSERT, UPDATE and DELETE statements.  Prior to Oracle, Oracle provided the pctfree and pctused parameters to allow the DBA determine how data blocks should interact with the freelists for the table.  This tuning task was required for every table and index in the database.

As we may know, the pctfree parameter tells Oracle when to unlink a data block from the freelist, and the pctused parameter tells Oracle when to relink a data block back onto the freelist.  The values for pctfree and pctused are heavily dependent upon the row length and the desired reserved space for row expansion.

Failure to set pctfree properly can lead to chained rows because not enough space on the data block is reserved for row expansion during an SQL UPDATE. Conversely, a failure to set pctused properly can lead to poor INSERT performance because not enough row space exists on a relinked data block.

With Automatic Segment Space Management, the settings for pctused, freelists and freelist_groups are obsolete, and Oracle manages the internal free blocks in a whole new way. 

The Evolution of Bitmaps in Oracle

Over the past several years, Oracle has gradually recognized the benefits of bitmap data structures.  As Oracle has evolved, we see the following progressive introduction of bitmaps:

• Bitmap Indexes – Oracle 7.3.3

• Locally Managed Tablespaces – Oracle 8.0

• Bitmap segment management – Oracle

It is important to note that bitmap segment management in Oracle is optional and can only be implemented at the tablespace level.  Existing systems may continue to use the traditional method of freelist management.  Before we discuss the differences between bitmap freelists and traditional space management, let’s examine how bitmap freelists are implemented. We begin by creating a tablespace with the segment space management auto parameter:

Once a table is allocated in this tablespace, the values for pctused 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. Also, the compatible parameter must be set to 9.0.0 or higher in order to use bitmap freelists.

Automatic Segment Management is a radical departure from traditional segment management, and it removes control from the DBA, who used to be able to control individual unlink and relink behavior to manually adjusting the pctfree and pctused parameters.

Like any automatic procedure, we rely on Oracle to manage the unlinking and relinking of data blocks onto the freelists. Oracle claims that their new algorithm considers row length and row expansion and adjusts segments internally automatically. Even more impressive, Oracle claims that it can automatically detect the implementation of Real Application Clusters (RAC), and automatically allocate multiple freelist groups, based on the number of instances that access the database.

Many Oracle neophytes in Oracle will allocate bitmap space management for a tablespace and continue to specify values for pctused, and freelists and freelist_groups. Remember, Oracle ignores all of these object storage parameters when objects are created within a segment space management auto tablespace.

More disconcerting, there are several limitations on bitmap segment management in Oracle:

• Once allocated, the DBA has on control over the storage behavior of individual tables and indexes inside the tablespace.

• Large objects cannot use automatic segment-space management, and separate tablespaces must be created for tables that contain LOB datatypes.

• You cannot create a temporary tablespace with automatic space management. This is because of the transient nature of temporary segments when sorting is performed.

• Only locally managed tablespaces can use bitmap segment management.

To upgrade an existing object to use automatic bitmap management, you create a new tablespace and use the CTAS or ALTER...MOVE syntax to place the object within the new tablespace.

Oracle Freelists Algorithms

If we accept that Oracle has devised a method for automatic space management, we must examine the following areas of space management. Since we have no access to the proprietary internal algorithms, we can use logic to determine the salient issues in automatic space management. These areas include the dynamic re-setting of freelist unlinks (pctfree), and dynamic relinking of data blocks (pctused) and the number of freelists for a table or index.

• Number of Freelists Internally within Oracle, a shortage of freelists is manifested by a buffer busy wait. This is because the segment header is available in the data cache, but the block cannot be accessed because another task has locked the block to INSERT, DELETE, or UPDATE a row. Oracle may have a mechanism to allocate a new segment header block (with another bitmap freelist) whenever buffer busy waits are detected for the segment. As we may know, Oracle introduced dynamic freelist addition in Oracle8i.

• Freelist Unlinks While it is possible for Oracle to detect the average row length for segments in a bitmap managed tablespace, Oracle has no way of predicting how much space to reserve of each data block for row expansion. This is because Oracle has no knowledge of VARCHAR datatypes that may later be expanded with SQL UPDATE statements. Logic dictates that Oracle must examine the updated row length for every UPDATE and relocate the row if it would chain onto another data block if left on its target block. Row relocation can have a high overhead, especially for batch-oriented SQL updates.

• Freelist relinks For Oracle to optimize the threshold for relinking a data block, Oracle needs a prior knowledge of the volume of subsequent INSERT statements. If the threshold is set too high, only a small amount of space is reserved on the relinked data block, and only a few rows can be INSERTED before Oracle is forced to perform an I/O to grab another data block. Of course, Oracle could detect high-volume INSERTS and use the APPEND option to bypass the freelists and use empty table blocks for subsequent inserts.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

From the docs, we see the convoluted rules for extent, storage and freelist management parameters:

If you specified the MINIMUM EXTENT clause, Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.

If you did not specify MINIMUM EXTENT clause, Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL - Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

AUTO - This keyword tells Oracle that you want to use 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. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks, while freelists, pctused and pctincrease are ignored with ASSM because they are used for transaction/block management.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.