Transitioning to Locally-Managed Tablespaces
July 30, 2004
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.
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:\oracle\oradata\zdcdb\hr_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.
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:
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.
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