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
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
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:
'/u01/oracle/oradata/customer.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
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
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
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
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
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
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
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
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.