Avoid and correct tablespace fragmentation in Oracle?
Correct Oracle Tablespace Fragmentation
Question: What are the different causes for 'tablespace fragmentation' in Oracle?
Tablespace fragmentation in Oracle occurs when the free space within a tablespace becomes scattered, resulting in a non-contiguous allocation of space. This fragmentation can lead to inefficient use of storage and degraded performance. There are several factors that contribute to tablespace fragmentation, including:
Prolonged Data Manipulation Operations: Frequent insert, update, and delete operations can lead to the creation of free space gaps between the data blocks. Over time, these gaps can result in a fragmented tablespace as the space is not used efficiently.
Inadequate Extent Management: Oracle tablespaces can use two types of extent management: dictionary-managed and locally managed. Dictionary-managed tablespaces are more prone to fragmentation due to the dynamic allocation of extents, which can lead to inefficient use of space. Locally managed tablespaces, on the other hand, offer better space management and reduced fragmentation.
Insufficient PCTFREE and PCTUSED Settings: The PCTFREE parameter specifies the minimum percentage of a block to be reserved for future updates, while the PCTUSED parameter defines the minimum percentage of a block that must be used before it can be considered for new inserts. If these parameters are not set appropriately, it can lead to inefficient space utilization and contribute to tablespace fragmentation.
Inappropriate Storage Parameters: Incorrect settings for initial extent size (INITIAL), the next extent size (NEXT), and the maximum number of extents (MAXEXTENTS) can lead to inefficient allocation of extents within a tablespace, resulting in fragmentation.
Dropped Objects: When objects such as tables or indexes are dropped, the space they occupied is released back to the tablespace as free space. If this freed space is not contiguous with other free space, it can contribute to fragmentation.
Multiple Segments with Different Sizes: If a tablespace contains multiple segments (tables, indexes, etc.) with varying sizes and growth patterns, it can cause fragmentation as the space becomes allocated in a non-contiguous manner.
To mitigate tablespace fragmentation in Oracle, consider the following best practices:
Use locally managed tablespaces with Automatic Segment Space Management (ASSM) to improve space management and reduce fragmentation.
Set appropriate values for PCTFREE and PCTUSED parameters to ensure efficient space utilization.
Monitor and manage the growth of objects within the tablespace to ensure balanced and uniform growth.
Periodically reorganize tablespaces by moving objects to new tablespaces, compacting them using the ALTER TABLE SHRINK SPACE command, or exporting/importing data using Oracle Data Pump.
It is essential to monitor and address tablespace fragmentation proactively to maintain optimal database performance and efficient space utilization.
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.
The ALTER TABLESPACE command can be called with the keyword COALESCE.
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.
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.
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.