Oracle Tablespaces – Defragmenting

Fragmentation in a tablespace is thought to be a performance killer. This has been proven to be a myth. Fragmentation by itself does not cause performance problems in a multi-user Oracle database. What is a problem is fragmentation of free space within a tablespace. What follows is information pertaining to the fragmentation of free space. If your free space is fragmented enough, you may be wasting space that can never be used by any extents in your tablespace. It should be noted that converting to Locally Managed Tablespaces (available since Oracle 8i) will eliminate the problem where fragmented free space cannot be used. Locally Managed Tablespaces, by their very nature, will fragment extents, but this is not a performance problem.

If you are not using Locally Managed Tablespaces, then the rest of this section is important to you. But you might seriously consider implementing Locally Managed Tablespaces so that you do not have to worry about these details.

Defragmentation Recommendations

• To ensure automatic defragmentation occurs for a tablespace be sure that the PCTINCREASE value for the DEFAULT STORAGE clause is set to a non-zero value. A zero value for the PCTINCREASE parameter causes SMON to ignore the tablespace as far as free space coalesence.
   
• To defragment a tablespace manually, the following command can be used:

   

  ALTER TABLESPACE <name> COALESCE;

  Where: <name> is the tablespace name.

Fragmentation Types

Tablespaces are dynamic assets. Like other dynamic assets such as disks, they can become fragmented. This fragmentation occurs due to the creation and deletion of tables and the dynamic extension of tables. Fragmentation can be of two general types, honey-comb and swiss-cheese.

Honey-Comb Fragmentation

Honey-comb fragmentation occurs when one or more objects are deleted or dynamically shrunk leaving contiguous blocks of data storage space empty, like the holes in a piece of honey-comb. Oracle automatically consolidates honey-comb fragmentation about every 30 minutes or so, in high use situations this is not frequent enough to prevent the honey-combs from becoming encapsulated by new data forming swiss-cheese fragmentation.

Swiss-Cheese Fragmentation

Swiss-cheese fragmentation occurs when an object is dropped that has been surrounded by the data from one or more other objects. This results in "holes" in the data structure similar to the "holes" in swiss-cheese. Oracle cannot automatically handle swiss-cheese fragmentation.

Of the two types of fragmentation, swiss-cheese causes the most difficulty and results in wasted database space.

The SMON process automatically consolidates contiguous free extents into single large extents for tablespaces whose default value for the pctincrease storage parameter is greater than zero. This reduces but doesn't eliminate the problem. In Oracle 8i you can avoid the overhead associated with either automatic coalescence or manual coalescence by using locally managed tablespaces. Since in a locally managed tablespace the tablespace itself tracks its extents through use of a bitmap, any contiguous free areas of space are automatically coalesced.

Elimination of Fragmentation

Using Export and Import

This method will consolidate all free space, and, will consolidate all tables into single extents. However, the database won't be available and for large systems, the time required could be extensive.

1. Perform an export on all objects in the tablespace. Remember that you must export each owner's objects for all users who own objects in the tablespace.
    
2. Drop the tablespace using the INCLUDING CONTENTS clause.
    
3. Re-create the tablespace (if you created a script to create the tablespace, you can just re-run the script, be sure to include all active data files. It might be desirable to delete all of a tablespace's data files and consolidate them into one large datafile at this time).
    
4. Import all of the exports generated in step 1.

Another major problem with this method is it won't work on the SYSTEM tablespace.

Using Commands

Honey-comb fragmentation is handled automatically by Oracle.

NOTE: If the SMON process will not coalesce tablespaces whose pct_increase default storage parameter is set to zero any tablespace that has a pct_increase of zero will have to be manually coalesced.

You can use one of two methods. The first method is just to temporarily set the pctincrease to one and await SMON's automatic cleanup. This can take several minutes. The second method involves issuing an event command against the specific tablespace where the fragmentation is happening:

alter session 
set events 'immediate trace name coalesce level ts#';

where ts# is the tablespace number as specified in the ts$ table.

You can create a view against the DBA_FREE_SPACE view that summarizes the fragmentation state of all the tablespaces. For example:

CREATE VIEW free_space (
   tablespace,
   file_id,
   pieces,
   free_bytes,
   free_blocks,
   largest_bytes,
   largest_blks
)
AS
   SELECT tablespace_name,
          file_id,
          COUNT (*),
          SUM (bytes),
          SUM (blocks),
          MAX (bytes),
          MAX (blocks)
   FROM   sys.dba_free_space
   GROUP BY tablespace_name, file_id;