Space Management   «Prev  Next»

Lesson 12 Reducing Oracle fragmentation
Objective Avoid/correct tablespace fragmentation in Oracle?

Correct Oracle Tablespace Fragmentation

There are two ways to prevent the problems caused by tablespace fragmentation:
  1. you can correct the problem when it occurs or
  2. avoid the problem through planning.

Correcting the problem

There is a single command you can use to eliminate the problem of fragmented tablespaces.
The ALTER TABLESPACE command can be called with the keyword COALESCE. The syntax for this command is:

where tablespace_name = the name of the tablespace

As the keyword implies, this command will COALESCE all the extents within the tablespace. Any lost space due to fragmentation is moved to the end of the tablespace. This command is complete in itself; you cannot have any additional parameters if you use the COALESCE keyword with the ALTER TABLESPACE command.
The following SlideShow demonstrates how this process works.

Coalesce Tablespace

Preventing Problem of Tablespace Fragmentation

You can prevent tablespace fragmentation by specifying all the extents within a tablespace to be the same size. As you learned earlier, this standardization can be easily implemented by declaring storage parameters for the tablespace and not declaring storage parameters for the objects within the tablespace. This solution may seem like overkill to prevent an easily correctable problem. Sometimes it may be, since you may have good reasons, unrelated to extent size, for placing different objects in different tablespaces. Experienced researchers at Oracle have shown that there is not a great need for many different extent sizes. Some experts recommend only three different extent sizes; 160K, 160 MB and 4 GB. With fewer extent sizes, it is much easier to assign objects to a tablespace, as many of them will have the same extent size.

Resolving Fragmentation

As of Oracle Database 10g, resolving fragmentation issues became fairly trivial. You can perform an online segment shrink using the Segment Advisor[1] interface accessible through EM. ADDM recommends segments to shrink, and you simply choose to accept the recommendations. For Oracle9i databases, a common means of reducing fragmentation was through an online reorganization accomplished through a
online operation. That is, the copying of the contents of one table to another while the original table is updated. Changes to the original table were tracked and applied to the new table. Physical and logical attributes of the table could be changed during this online operation, thus allowing an online reorganization. Prior to Oracle9i, reducing fragmentation was more difficult. The general recommendation was to avoid fragmentation through careful planning. But the usual way to solve fragmentation was to reorganize a table by exporting the table, dropping it, and importing it. The data was unavailable while the table was in the process of being reorganized. Many DBAs claimed that they saw improved performance after reorganizing segments into a single extent. Over time, a decrease in performance reoccurred as the number of extents the table occupied increased. Oracle performance increased as a result of these reorganization operations, but this improvement was not due to a decrease in the number of extents. When a table is dropped and re-created, several things happened that increased performance:
  1. Each block was loaded as full of rows as possible.
  2. As a consequence, the high-water mark of the table (the highest block that has ever had data in it) was set to its lowest point.
  3. All indexes on the table were rebuilt, which meant that the index blocks were as full as possible. The depth of the index, which determined the number of I/Os it takes to get to the leaf blocks or the index, was sometimes minimized.
By eliminating fragments and shrinking segments in a much more automated and online fashion, database releases since Oracle Database 10g greatly simplify solving fragmentation problems; the result is that optimal conditions exist for performance.
The next lesson is the wrap-up for this module.
[1] Segment Advisor:The Segment Advisor identifies segments that have space available for reclamation. It performs its analysis by examining usage and growth statistics in the Automatic Workload Repository (AWR), and by sampling the data in the segment.