Understanding Oracle’s Locally Managed Tablespaces

Locally Managed Tablespace (LMT) is one of the key
features in Oracle database. These have been made available since Oracle 8i. It
is worth using LMTs considering the benefits in doing so. I have put forward
some scenarios that may be worth noting, for systems that are already using LMTs
or planning to shift to LMTs.

Benefits of LMTs

Below are the key benefits offered by LMTs. Not all
are achievable when migrating to LMTs.

  1. Dictionary contention is reduced.

    Extent management in DMTs is maintained
    and carried out at the data dictionary level. This requires exclusive locks on
    dictionary tables. Heavy data processing that results in extent allocation/deallocation
    may sometimes result in contentions in the dictionary.

    Extents are managed at the datafile level in LMTs.
    Dictionary tables are no longer used for storing extent allocation/deallocation
    information. The only information still maintained in the dictionary for LMTs
    is the tablespace quota for users.

  2. Space wastage removed.

    In DMTs, there is no implied mechanism to enforce
    uniform extent sizes. The extent sizes may vary depending on the storage clause
    provided at the object level or the tablespace level, resulting in space
    wastage and fragmentation.

    Oracle enforces the uniform extents allocation in
    the LMTs (when created with UNIFORM SIZE clause). Space wastage is removed, as
    this would result in all the same sized extents in the tablespace.

  3. No Rollback generated.

    In DMTs, all extent allocations and deallocations
    are recorded in the data dictionary. This generates undo information thus using
    vital resources and may compete with other processes.

    In LMTs, no rollback is generated for space
    allocation and deallocation activities.

  4. ST enqueue contention reduced.

    In DMTs, Space Transaction (ST) enqueue
    is acquired when there is a need for extent allocations in DMTs. It is also exclusively acquired by SMON
    process for coalescing free space in DMTs. Only one such enqueue exists per
    instance, and may sometimes result in contention and performance issues if
    heavy extent processing is being carried out. The following error is common in
    such scenario.


    ORA-01575: timeout warning for space management resource

    As ST enqueue is not used by LMTs it reduces the
    overall ST enqueue contention.

  5. Recursive space management operations removed.

    In DMTs, SMON process wakes up every 5 minutes for
    coalescing free space in DMTs. Optionally, the ALTER TABLESPACE <tablespace
    name> COALESCE command is also used to coalesce DMTs and reduce
    fragmentation.

    On the other hand, LMTs avoid recursive space
    management operations and automatically track adjacent free space, thus
    eliminating the need to coalesce free extents. This further reduces
    fragmentation.

  6. Fragmentation reduced.

    Fragmentation is reduced in LMTs but not completely
    eliminated. Since adjacent free spaces are automatically tracked, there is no
    need to do coalescing, as is required in the case of DMTs.

Management of Extents in LMTs

Oracle maintains a bitmap in each datafile to track
used and free space availability in an LMT. The initial blocks in the datafiles
are allocated as File Space Bitmap blocks to maintain the extent allocation
information present in the datafile. Each bit stored in the bitmap corresponds
to a block or a group of blocks. Whenever the extents are allocated or freed,
oracle changes the bitmap values to reflect the new status. Such updates in the
bitmap header do not generate any rollback information.

The number of blocks that a bit represents in a
bitmap depends on the database block size and the uniform extent size allocated
to the tablespace. For example, if the DB_BLOCK_SIZE parameter is set to 8K,
and the tablespace is created with uniform extent sizing of 64K, then 1 bit
will map to one 64K extent, i.e., 64K (extent size)/8K (block size) = 8
database blocks.

Allocation Types in LMTs

Allocation type plays a very important role in how
the LMT is behaving. It specifies how the extent is being allocated by the system.
There are three
types of allocating extents in LMTs- USER, SYSTEM and UNIFORM.

  • USER– The LMT behaves as DMT, allocating extents as
    per the storage clause provided with the object or defaulted at tablespace
    level. The advantage is that allocation of extents is managed at the datafile
    level and such tablespaces will not compete for ST enqueue. The disadvantage is
    that such tablespaces are not subject to uniform extent allocation policy. DMTs that are converted to LMTs fall under this type.

  • SYSTEM– Oracle manages the space. The extents are
    auto allocated by the system based on an internal algorithm. Allocation of extents is managed
    at the datafile level and such tablespaces will not compete for ST enqueue.
    Such tablespaces would have extents of varying sizes and would result in
    fragmentation and some space being wasted. This is a good alternative if the
    extent sizes of the various objects to be placed in the tablespace cannot be
    determined.

  • UNIFORM– All extents are of fixed size in the
    system. The size is provided when creating the LMT. This type gives all the
    benefits offered by LMT and one should aim at achieving this.

Latest Articles