Transitioning to Locally-Managed Tablespaces

Synopsis. Oracle 8.1.6 offered a new format for
tablespace storage management – locally-managed tablespaces – that offers
significant improvements in tablespace performance and free space management.
Oracle 9i Release 2 improved upon segment storage in locally-managed
tablespaces with the Automatic Segment Storage Management option. This article
delves into the advantages of using locally-managed tablespaces over
dictionary-managed tablespaces and suggests practical methods to achieve the
transition.

One of my production databases had been created under Oracle
8i just a few weeks before I transitioned into the role of Oracle DBA. Our
predecessor DBA had left our company just before we decided to use to Microsoft
Windows 2000 Advanced Server instead of Linux Red Hat as our operating system,
and my boss had inherited the task of managing the switchover. At the time, I
was admittedly green about best practices for tablespace configuration
techniques.

A few months later, when I migrated the 8i database to the
Oracle 9i environment, once again I was under a severe time crunch — just one
weekend between Christmas and New Year’s! — to accomplish that transition. At
that time, I had little time to investigate one of the best new features of
Oracle 9i Release 2, automatic segment space management, and had simply migrated
the database in place with its dictionary-managed tablespaces.

Enter a new colleague and new experiences. During his
orientation to our database environment, my new partner expressed surprise at
how long our nightly database backups took for this particular database, which
contains approximately 45GB of used blocks. He estimated a time frame of
approximately 45 minutes; however, our current database backups were taking
over 2 = hours. He also noticed that the tablespaces’ datafiles had been
created at the maximum size possible on each spindle.

We therefore set out to experiment if locally-managed
tablespaces would make a significant difference in database performance,
especially for nightly backups and exports.

Dictionary-Managed vs. Locally-Managed Tablespaces

First, let’s review the differences between these two
tablespace types. As its name implies, information about the storage capacity
of dictionary-managed tablespaces (DMTs) is retained in the
database’s data dictionary. This incurs at least some additional overhead for
storage management, since data dictionary tables in the SYSTEM tablespace
record all information about which blocks are free, how much free space each
block has, and when a block can be INSERTed into, based on settings for each
table’s PCTFREE and PCTUSED parameters.

Locally-managed tablespaces (LMTs) overcome
this overhead by storing free list information in special bitmap entries
in each datafile of the tablespace. The bitmap entries track whether or not a
block is free or in use, and Oracle automatically updates the bitmap entries
whenever an extent is freed or allocated. Since this information no longer
needs to be written to a data dictionary table, the rollback entries normally
written are eliminated.

Automatic Segment Space Management

LMTs therefore, essentially manage their own extents
automatically, while in a DMT there is a constant need to coalesce free extents
(i.e. adjacent extents that contain free blocks). In addition, LMTs offer
another improvement in Oracle 9i: Automatic Segment Space Management (ASSM).
Setting the SEGMENT SPACE MANAGEMENT clause of the CREATE TABLESPACE
statement to AUTO notifies Oracle to manage automatically the storage space
within the segments within that tablespace. This feature helps a tablespace to
avoid recursive space-management operations that may occur in a DMT when
the consumption or reduction of space in the DMT requires the data dictionary
table that is managing the extents to expand or contract.

When a tablespace is created under Oracle 9i, note that LMT
is the default setting, and in later versions of Oracle, the concept of the DMT
is being phased out. Note, however, that ASSM must be specified (the default
setting is MANUAL) and that the COMPATIBLE parameter of an Oracle 9i database
may need to be adjusted upward to use it.

Transition Methods

We found two basic methods worked for transitioning from a
DMT to an LMT environment:

Method 1: Migrate to Newly-Created LMTs

If sufficient disk space exists, create new locally-managed
tablespaces and migrate tables and indexes to those tablespaces.

Creating New Tablespaces. We used the CREATE
TABLESPACE command to create the new LMTs. We also made sure that the datafiles
were now autoextensible instead of being sized statically, but reduced
in their initial size to accommodate the total space required for all table and
index segments being migrated, which significantly reduced the size of most of
our datafiles.

Here is an example intended to migrate some of the HR
schema’s objects to a newly-created LMT with automatic segment space
management:


DROP TABLESPACE hr_lmpt INCLUDING CONTENTS;
CREATE TABLESPACE hr_lmpt
DATAFILE ‘g:oracleoradatazdcdbhr_lmpt.dbf’
SIZE 512M
REUSE
AUTOEXTEND ON
MAXSIZE 4096M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Migrating Tables. Moving tables to their new
locations was relatively simple via the ALTER TABLE MOVE command. For example:

ALTER TABLE hr.departments MOVE TABLESPACE hr_lmpt;

Special Considerations for LOB Datatypes. One
important consideration to note: If the table contains LOB datatypes (BLOBs, CLOBs,
or NCLOBs), these LOB segments will need to be moved separately to an
appropriate LMT for LOB storage:


ALTER TABLE hr.employee_pictures
MOVE LOB(picture)
STORE AS hr_employee_picture_lob (TABLESPACE hr_lobs);

Migrating Indexes. To move indexes, you will need to
use the ALTER INDEX REBUILD
command. Note that statistics can be recalculated for the index via the COMPUTE STATISTICS directive:


ALTER INDEX hr.dept_pk_idx
REBUILD
TABLESPACE dept_lmt
COMPUTE STATISTICS;

Dropping Old DMTs. Once all objects have been
migrated from the original DMTs, these tablespaces can be removed from the
database:

DROP TABLESPACE hr_dmpt INCLUDING CONTENTS AND DATAFILES;

Method 2: Migrate DMTs to LMTs

If insufficient disk space exists, consider using the
DBMS_SPACE_ADMIN PL/SQL supplied package to switch all DMTs to LMTs. The
advantage of this method is that no migration of data from source DMT to target
LMT need occur:


BEGIN
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(
tablespace_name => ‘HR_DMPT’);
END;
/

Likewise, an LMT can be migrated back to a DMT with this
package:


BEGIN
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL(
tablespace_name => ‘HR_DMPT’);
END;
/

I must admit that I have only experimented briefly with this
approach, and I have yet to apply it directly to a full database migration. One
major point, whichever method you choose. Beware that once the SYSTEM
tablespace is switched from a DMT to an LMT, it is a one-way ticket:
Oracle
will prohibit the creation of any additional DMTs in the database. Therefore, it
is probably a good idea to leave a SYSTEM tablespace that is already a DMT
alone, especially if you decide to revert to DMTs for one or more converted
tablespaces.

Results

With our new tablespace configuration in place, we performed
comparative testing and measured the outcome. I have to admit that I was
pleasantly surprised with the results:

  • With the new tablespace configuration and freshly-migrated table
    and index segments, it now takes only 41 minutes to complete a nightly RMAN
    backup scheme — a complete Incremental Level 0 backup of the database – as
    compared to 150 minutes the same scheme for DMTs, a 72% reduction.

  • With the new configuration, the nightly export scheme, which
    involves exporting every row in the database in either Direct or Conventional
    mode, only takes 160 minutes vs. 216 minutes with DMTs, a 26% reduction.

  • The migration of tables and indexes to their new LMTs had another
    positive side-effect: The number of migrated rows was significantly reduced,
    and all indexes were rebuilt, thus giving my users a “clean” database to fill
    up once again.

While these performance improvements are more likely due to
the migration of the segments to their new tablespaces, and the fact that the
backups do not have to read through a bunch of empty blocks every time a
tablespace is backed up, it is still an impressive improvement worthy of the
experiment.

Conclusion

Oracle 9i’s locally-managed tablespaces offer distinct
advantages over their Oracle 8i cousins, the dictionary-managed tablespaces,
and converting from DMTs to LMTs, while not trivial, does offer significant
advantages, especially automatic segment space management.

References and Additional Reading

While there is no substitute for direct experience, reading
the manual is not a bad idea, either. I have drawn upon the following Oracle 9i
Release 2 documentation for the deeper technical details of this article:

A96524-01 Oracle 9i
Concepts,
Chapter 3

A96540-01 Oracle 9i SQL
Reference,
Chapter 11

A96612-01 Oracle 9i
Supplied PL/SQL Packages and Types Reference,
Chapter 68

»


See All Articles by Columnist
Jim Czuprynski

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles