Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






The Best Oracle Resource on the Web

Inside LMT and ASSM Management

by Donald K. Burleson


Over the past few releases Oracle has been automating and improving the internal administration of tables and indexes. It has gradually recognized the benefits of bitmap data structures in all areas of the database. Recently, Oracle has introduced two new tablespace parameters that automate storage management functions:
  • LMT (Locally Managed Tablespaces) The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to the tablespace definition. LMT tablespaces automate extent management and remove the ability to specify the NEXT storage parameter. The only exception is when NEXT is used with MINEXTENTS at table creation time.
  • ASSM (Automatic Segment Space Management) The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASSM tablespaces automate FREELIST management and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters. You cannot use ASSM unless you also use LMTs on a tablespace.  Also, beware of performance issues with ASSM under high DML load.
It is important to note that LMT and ASSM are optional and are used in the same instance with "traditional" tablespaces. Remember, LMT and ASSM are implemented at the tablespace level and each instance can have LMT, LMT and ASSM tablespaces, or traditional tablespaces.

The benefits of LMT tablespaces

The main benefit of an LMT is that the data dictionary control structures for the tablespace extent management move from inside the data dictionary (where it's a single point of contention) into the tablespace header blocks, where there is less contention.

The main benefits of locally managed tablespaces include:

  • Marginally faster performance:  Because there is less data dictionary contention, throughput can be faster.

  • Faster tablespace space management activities:  Since space management activities are done in the tablespace blocks, we see less space management contention (ST locks).  Uniform extent management is enforced and tablespace fragmentation can be reduced.

Before we discuss the differences between bitmap FREELISTS and traditional FREELIST management, let's examine how bitmap FREELISTS are implemented. We begin by creating a tablespace with the segment space management auto parameter. Note that ASSM is only valid for locally-managed tablespaces with extent management local syntax.

create tablespace

Once a table or index is allocated in this tablespace, the values for PCTUSED for individual objects will be ignored, and Oracle will automatically manage the FREELISTS for the tables and indexes inside the tablespace. For objects created in this tablespace, the NEXT extent clause is now obsolete because of the locally-managed tablespace (except when a table is created with MINEXTENTS and NEXT). The INITIAL parameter is still required because Oracle cannot know in advance the size of the initial table load. When using Automatic Space Management, the minimum value for INITIAL is three blocks.

There is some debate about whether a one-size-fits-all approach is best for Oracle. In large databases, individual object settings can make a huge difference in both performance and storage. As we may know, the setting for PCTUSED governs FREELIST re-linking. If we want high disk space usage, we set PCTUSED to a value slightly greater than avg_row_len. Conversely, if we want fast INSERT performance, we set PCTUSED to a low value, ensuring that all free blocks are nearly empty, providing lots of block space for INSERT operations.

The Issue of PCTFREE

As a quick review, the PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

The setting for PCTFREE is especially important where a row is initially stored small and expanded at a later time. In such systems, it is not uncommon to set PCTFREE equal to 95, telling Oracle to reserve 95 percent of the data block space for subsequent row expansion.

Fortunately, Oracle does allow you to specify the value for PCTFREE if you are using Automatic Space Management and locally managed tablespaces. Row chaining is a serious problem for the DBA, and it appears that Automatic Space Management is still appropriate for tables for which you need to reserve space for large row expansions with PCTFREE.

The Issue of PCTUSED

As we know, improper settings for PCTUSED can cause huge degradation in the performance of SQL inserts. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the re-used Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation in which the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the FREELIST chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data block for the insert.

In Oracle with Automatic Segment Management, the PCTUSED parameter no longer governs the re-link threshold for a table data block, and we must rely on the judgment of Oracle to determine when a block is empty enough to be placed onto the FREELIST. When using ASSM Oracle will use 4 freeness levels for table blocks internally (in addition to 2 special statuses), putting block back to "free" status when block usage drops at least one level below PCTFREE.

Unlike PCTFREE, in which Oracle cannot tell in advance how much row expansion will occur, Oracle does have information about the right time to re-link a data block. Because Oracle knows the average row length for the table rows (dba_tables.avg_row_len), Oracle should be able to adjust PCTUSED to ensure that the re-linked data block will have room for new rows.

An Oracle Inconsistency

While Oracle ignores the PCTUSED, FREELISTS, and FREELIST GROUPS parameters with LMT and ASSM tablespaces, Oracle does not give an error message when these "ignored" parameters are used in a table definition.

SQL> create table
  2  test_table
  3  (c1 number)
  4  tablespace
  5  asm_test
  6   pctfree 20 pctused 30
  7  storage
  8  ( freelists 23 next 5m ) ;

Table created.

Most Oracle DBAs would assume that invalid parameters would be treated as they have been treated since Oracle7, and reported as an error.

SQL> create index
  2  	test_type_idx
  3  on
  4  	book(book_type)
  5   PCTUSED 40 ;
ERROR at line 5:
ORA-02158: invalid CREATE INDEX option

This could lead to confusion when a DBA believes that they are changing these values when in reality, tablespaces with LMT or SAM ignore any specified values for PCTUSED, NEXT, and FREELISTS.

No More Buffer Busy Waits

One huge benefit of Automatic Segment Management is the bitmap FREELISTS that are guaranteed to reduce buffer busy waits. Let's take a close look at this feature.

Prior to Oracle, buffer busy waits were a major issue. As a review, a buffer busy wait occurs when a data block is inside the data buffer cache, but it is unavailable because it is locked by another DML transaction. A block was unavailable because another SQL insert statement needed to get a block on which to place its row. Without multiple FREELISTS, every Oracle table and index had a single data block at the head of the table to manage the free block for the object. Whenever any SQL insert ran, it had to go to this block and get a data block on which to place its row.

Obviously, single FREELISTS cause a backup. When multiple tasks wanted to insert into the same table, they were forced to wait while Oracle assigned free blocks, one at a time.

Oracle's Automatic Segment Space Management feature claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used, simultaneously eliminating serialization for free space lookups.

According to Oracle benchmarks, using bitmap FREELISTS removes all segment header contention and allows for super-fast concurrent insert operations (refer to figure 1).

Figure 1: Oracle Corporation benchmark on SQL insert speed with bitmap FREELISTS.

Along with the Automatic Segment Management features, we get some new tools for the DBA. Let's take a look at how the Oracle DBA will use these tools.

Internal Freelist Management

With ASSM, Oracle controls the number of bitmap FREELISTS, ups to 23 per segment.

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, it needs a priori 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.

Characteristics of Bitmap Segment Management

Bitmap space management uses four bits inside each data block header to indicate the amount of available space in the data block. Unlike traditional space management with a fixed relink and unlink threshold, bitmap space managements allow Oracle to compare the actual row space for an INSERT with the actual available space on the data block. This enables better reuse of the available free space especially for objects with rows of highly varying size. Here are the values inside the four-bit space:

Value Meaning
0000 Unformatted Block
0001 Block is logically full
0010 <25% free space
0011 >25% but <50% free space
0100 > 50% but <75% free space
0101 >75% free space

Table 1: Bitmap value meanings.

The value of this bitmap indicates how much free space exists in a given data block. In traditional space management, each data block must be read from the FREELIST to see if it has enough space to accept a new row. In Oracle, the bitmap is constantly kept up-to-date with changes to the block, and reducing wasted space because blocks can be kept fuller since the overhead of FREELIST processing has been reduced.

Another enhancement of Oracle space management is that concurrent DML operations improve significantly. This is because different parts of the bitmap can be used simultaneously, thereby eliminating the need to serialize free space lookups.

Please note that Oracle segment control structures are much larger than traditional FREELIST management. Because each data block entry contains the four-byte data block address and the four-bit free space indicator, each data block entry in the space management bitmap will consume approximately six bytes of storage.

It is also important to note that space management blocks are not required to be the first blocks in the segment. In Oracle8, the segment headers were required to be the first blocks in the segment. In Oracle8i this restriction was lifted, and the DBA could allocate additional FREELISTS with the ALTER TABLE command. In Oracle, Oracle automatically allocates new space management blocks when a new extent is created and maintains internal pointers to the bitmap blocks (refer to figure 2).

Figure 2: Non-contiguous bitmap blocks within a segment.

Oracle Freelist Internals

Just like traditional FREELISTS, the bitmap block (BMB) is stored in a separate data block within the table or index. Because Oracle does not publish the internals of space management, we must infer the structure from block dumps. Hence, this information may not be completely accurate, but it will give us a general idea about the internal mechanisms of Oracle automatic space management.

Unlike a linear-linked list in traditional FREELISTS, bitmap blocks are stored in a B-tree structure, much like a B-tree index structure. This new structure has important ramifications for concurrent DML. In traditional FREELISTS, free blocks must be accessed one at a time, and this causes segment header contention in applications with high-volume INSERT operations. Because Oracle can use the FREELISTS blocks much like a B-tree index, multiple transactions can simultaneously access free blocks without locking or concurrency problems.

Now let's look inside the segment header and take a closer look at the bitmap space management techniques.

Segment Extent Control Header

As we have noted, the purpose of bitmap blocks are to track the free blocks in the segment. Since the free blocks are organized in a B-tree, we see the following nodes inside the segment control block. There are three data blocks that comprise the segment control.

The extent control header block contains the following components:

  • The extent map of the segment
  • The "last" block at each level of the B-tree
  • The low high-water mark
  • The high high-water mark
  • First-level bitmap block, which contains a list of all first-level data block addresses, and the four-bit free space indicator for each block
  • Second-level bitmap block, which contains a list of all second-level data block addresses, and the four-bit free space indicator for each block

New High-Water Mark Pointers

The high-water mark in the segment header has also changed in Oracle bitmap blocks. Instead of having a single pointer to the highest free block in an object, the B-tree index structure allows for a range of high-water mark blocks. Hence, we see two pointers for the high-water mark.

  • The low high-water mark (LHWM) All blocks below this block have been formatted for the table.
  • The high high-water mark (HHWM) All blocks above this block have not been formatted. Internally, the HHWM is required to ensure that Oracle direct load operations can access contiguous unformatted blocks.
As we see in figure 3, Oracle maintains several sets of pointers to bitmap blocks in the segment header.

Figure 3: Segment header pointers to bitmap blocks.

Let's look at each block in detail to understand how space is managed in bitmap segment control.

Extent Control Header Block

This block contains the high high-water mark, the low high-water mark, the extent map, and the data block addresses for each of the three levels of bitmap blocks.

The extent map lists all of the data block address for each block within each extent within the segment and shows the four-bit free space of each block within the extent. Since the extent size is controlled by Oracle locally-managed tablespaces, each extent size within the tablespace is uniform, regardless of the NEXT extent size for each object in the tablespace.

Note that the first three blocks of the first extend list (blocks 0 2) are used for metadata and are not available for segment block addresses.

For each extent in the segment, Oracle keeps an entry pointing to the bitmap for that segment (refer to figure 4).


Figure 4: Segment header extent map points to all extent bitmaps in segments.

Oracle also has pointers to the last bitmap block within each logical bitmap level (refer to figure 5).

Figure 5: Pointers to last bitmap block on each bitmap level.

This new pointer structure allows Oracle to quickly access multiple bitmaps to improve concurrency of high-volume INSERTs.

Potential Performance Issues with ASSM

The Oracle community has mixed feelings about using ASSM tablespaces. Among the top points about ASSM, we find both Pros and Cons:

Pros of ASSM:

  • Varying row sizes ASSM is better than a static PCTUSED. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length.
  • Reducing buffer busy waits ASSM will remove buffer busy waits better than using multiple FREELISTS. As we may know, when a table has multiple FREELISTS, all purges must be parallelized to reload the FREELISTS evenly, and ASSM has no such limitation.
  • Great for RAC The bitmap FREELISTS remove the need to define multiple FREELISTS groups for RAC, and provide overall improved FREELIST management over traditional FREELISTS.
Cons of ASSM:
  • Slow for full-table scans Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM-FTS tablespaces are consistently slower than Freelist-FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications, unless partitioning is used with Oracle Parallel Query.
  • Slower for high-volume concurrent INSERTS Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple FREELISTS.
  • ASSM will influence index clustering For row-ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap FREELISTS are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor, and the cost-based optimizer's propensity to favor an index range scan.


The combination of bitmap FREELISTS (automatic segment space management) and Locally Managed Tablespaces, has greatly simplified and improved the internal management of data blocks within Oracle tablespaces. The use of bitmap FREELISTS removes the need to define multiple FREELISTS for tables and indexes that experience high-volume concurrent DML, and provides a simple solution to the problem of segment header contention.

However, the savvy DBA recognizes the tradeoff between one-size-fits-all convenience and the power of being able to set individual object parameters for tables and indexes. The choice of LMT and ASSM for tablespace management depends heavily on the application, and real-world Oracle tablespaces will implement LMT and ASSM tablespaces only after careful consideration.

Supplemental reading

From the Oracle 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.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational