Bitmap freelists vs.
traditional space management
Before I discuss
the differences between bitmap freelists and traditional space
management, let?s examine how bitmap freelists are implemented. I'll
begin by creating a tablespace with the segment space management auto
EXTENT MANAGEMENT LOCAL -- Turn on LMT
SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM
Once you've defined the tablespace, tables and indexes can easily be
moved into the new tablespace with a variety of methods. Here I've
select * from customer;
cust_name_idx rebuild tablespace assm_lmt_ts;
Note that after 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 using
bitmap arrays. For tables and indexes created inside an LMT
tablespace, the NEXT extent clause is obsolete because the locally
managed tablespace manages them. However, the INITIAL parameter is
still required because Oracle cannot know in advance the size of the
initial table load. For ASSM, the minimum INITIAL value is three
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 performance and storage.
The issue of PCTFREE
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.
The issue of PCTUSED
Improper settings for PCTUSED (e.g., set too small) can
cause huge degradations in the performance of SQL insert statements.
If a data block is not largely empty, excessive I/O will happen during
SQL inserts because the reused Oracle data blocks will become full
quickly. Taken to the extreme, improper settings for PCTUSED can
create a situation where 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 blocks for the insert.
In Oracle with ASSM, the
PCTUSED parameter no longer governs the relink threshold for a table
data block, and you must rely on the judgment of Oracle to determine
when a block is empty enough to be placed onto the freelist.
While Oracle ignores the PCTUSED, FREELISTS, and FREELIST GROUPS
parameters with locally managed tablespaces and ASSM, Oracle does not
give an error message when they are used in a table definition:
SQL> create table
3 (c1 number)
6 pctfree 20 pctused 30
8 ( freelists 23 next 5m ) ;
This could be a serious issue unless you remember that locally
managed tablespaces with ASSM ignore any specified values for PCTUSED,
NEXT, and FREELISTS.
One huge benefit of ASSM is that bitmap
freelists are guaranteed to reduce buffer busy waits, which were a
serious issue prior to Oracle9i. Let?s take a close look at
buffer busy waits
Without multiple freelists,
every Oracle table and index had a single data block at the head of
the table to manage free blocks for the object and provide data blocks
for new rows created by any SQL insert statements. A buffer busy wait
occurs when a data block is inside the data buffer cache but is
unavailable because it is locked by another DML transaction. When you
want to insert multiple tasks into the same table, the tasks are
forced to wait while Oracle assigned free blocks, one at a time.
With ASSM, Oracle 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 (Figure A).
benchmark on SQL insert speed with bitmap freelists