|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:
- you can correct the problem when it occurs or
- avoid the problem through planning.
Correcting the problem
There is a single command you can use to eliminate the problem of fragmented tablespaces.
ALTER TABLESPACE command can be called with the keyword
The syntax for this command is:
ALTER TABLESPACE tablespace_name COALESCE;
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.
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.
As of Oracle Database 10g, resolving fragmentation issues became fairly trivial.
You can perform an online segment shrink using the Segment Advisor
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
CREATE TABLE...AS SELECT
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:
- Each block was loaded as full of rows as possible.
- 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.
- 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.
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.