| Previous | Table of Contents | Next |
Tablespace fragmentation can occur in a number of circumstances. Most fragmentation has definite performance implications. Some types of fragmentation are actually good, such as striping. For example, data striping is good when many users are selecting/ inserting/updating rows all over a rather large table. Striping allows the associated locking and disk I/O to be distributed across several disk devices. Other types of fragmentation are not good. Extent interleaving, row chaining, and row migration all have performance implications. Tablespace fragmentation doesn’t necessarily affect performance, but a significant amount of disk space can be wasted.
Extent interleaving occurs when two or more data objects in the same tablespace are all dynamically growing in such a way that the objects assigned extents are in between extents from other data objects. Oracle performs best with a minimal number of extents, and also when the extents are next to each other. The fewer extents, the fewer trips Oracle has to make to the data dictionary to find out where the next extent to retrieve is. Having extents sprinkled all over the tablespace also causes excessive disk head movement, a time-consuming task for a computer. Objects that are involved in this interleaving should be exported, dropped, and re-created. The best results would be to reorganize the entire tablespace.
Note:
Extent interleaving is easily fixed, but, if not done correctly, can cause tablespace fragmentation.
Row chaining is caused when a row being inserted is larger than the Oracle block size. Oracle will chain, or link by block number, an additional block (or blocks) to hold the remaining data from the row. This chained block cannot be indexed and cannot be referenced without first referencing the original block. This greatly slows the data access as Oracle has to wait for an additional I/O to retrieve the chained block. If there is significant chaining, performance can be seriously affected. The only way to correct chaining is to export the object and its data and either re-create the database with a larger Oracle block size or import the object and its data into an Oracle database that already has a larger block size assigned.
Row migration is caused by an update where the row will no longer fit in its current block. Oracle will relocate the updated row to a different block where it will completely fit, leaving a ROWID pointer in the original block that references the new data location in another block. Like row chaining, there is no reference to this new location except by accessing the original location first. Once again, Oracle will have to wait while an additional disk I/O is performed to retrieve the new row. It can also be a time-consuming process for Oracle to locate a new block with enough free space to hold the new row. Row migration can be easy to fix, especially if there are not a lot of indexes or references to/from other objects. Exporting the table’s data, dropping the table, and importing the data back into the table will correct row migration.
Tablespace fragmentation involves the nonused disk space between objects. This type of fragmentation is caused by deletes and by extents being returned to the free list. It is not a performance issue as much as a space utilization issue. Oracle7 introduced coalescing, a process that will make two or more adjoining empty Oracle blocks into a single larger block of available space. The only way to fully utilize these empty spaces is to reorganize the entire tablespace. The following is a syntax example:
alter tablespace EXAMPLE coalesce;
Note:
I have noticed the Oracle RDBMS doing a better job of tablespace free space management with each new release of the software.
Most fragmentation is a result of poor database design.
Extent interleaving can be completely eliminated by simply putting tables with static or very little growth together in a tablespace and putting each dynamically growing object in a tablespace by itself. This increases tablespace maintenance but eliminates this type of fragmentation.
Row chaining is a result of the database administrator not being fully aware of the data requirements. The only way to eliminate row chaining is to export the entire database, re-create it with a bigger database block size, and import the entire database.
Tip:
If this is an issue in your shop, try to wait until the next upgrade of the Oracle software or the application being relocated to a different machine. Set up the new environment with a database block size large enough to hold an entire row.
Tip:
If row chaining is caused by long raw data types, stored binary data, and so on, take advantage of externally storing these objects outside the Oracle8 environment.
Row migration can be limited by making adjustments to the PCTFREE parameters in the storage clause. PCTFREE is the amount of space left in each Oracle block after insertions for the purpose of accommodating future updates. Altering the PCTFREE clause will not have any effect on any existing migrated rows. The table will have to be reorganized to get rid of the existing migrated rows.
Tablespace fragmentation is difficult to avoid as it is caused by row deletions. The best advice I can offer is to put objects with this kind of activity in tablespaces by themselves.
A schema, or a collection of logical structures, is owned by a user and has the same name as the user. Basically, anything that can be owned by a user, such as tables, triggers, indexes, sequences, basically any database objects are part of that user’s schema. Objects that are not part of a schema are tablespaces, roles, profiles, and rollback segments.
| Previous | Table of Contents | Next |