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
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.
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.
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
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
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
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.
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.
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.
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, 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.
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
3 (c1 number)
6 pctfree 20 pctused 30
8 ( freelists 23 next 5m ) ;
DBAs would assume that invalid parameters would be treated as they have
been treated since Oracle7, and reported as an error.
SQL> create index
5 PCTUSED 40 ;
ERROR at line 5:
ORA-02158: invalid CREATE INDEX option
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.
Buffer Busy Waits
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
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.
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.
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.
to Oracle benchmarks, using bitmap FREELISTS removes all segment header
contention and allows for super-fast concurrent insert operations (refer
to figure 1).
1: Oracle Corporation benchmark on SQL insert speed with bitmap FREELISTS.
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
Oracle controls the number of bitmap FREELISTS, ups to 23 per segment.
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.
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.
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
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:
<50% free space
> 50% but
<75% free space
Bitmap value meanings.
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.
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
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
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).
Non-contiguous bitmap blocks within a segment.
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
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
look inside the segment header and take a closer look at the bitmap space
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.
control header block contains the following components:
As we see in
figure 3, Oracle maintains several sets of pointers to bitmap blocks in
the segment header.
Segment header pointers to bitmap blocks.
at each block in detail to understand how space is managed in bitmap
Extent Control Header 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
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
the first three blocks of the first extend list (blocks 0 — 2) are used
for metadata and are not available for segment block addresses.
extent in the segment, Oracle keeps an entry pointing to the bitmap for
that segment (refer to figure 4).
Segment header extent map points to all extent bitmaps in segments.
also has pointers to the last bitmap block within each logical bitmap
level (refer to figure 5).
Pointers to last bitmap block on each bitmap level.
pointer structure allows Oracle to quickly access multiple bitmaps to
improve concurrency of high-volume INSERTs.
Potential Performance Issues with ASSM
Oracle community has mixed feelings about using ASSM tablespaces. Among
the top points about ASSM, we find both Pros and Cons:
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.
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
Oracle docs, we see the convoluted rules for extent, storage and freelist
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
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