Maintain excellent Oracle database performance-2

Iii. segment fragmentation

When a database object is generated, a table or an index is used. The tablespace is specified by the user's default value or specified value. A segment generated in a tablespace is used to store object-related data. Before a segment is closed, shrunk, or truncated, the space allocated by the segment is not released.

A segment is composed of a range, And a range is composed of adjacent Oracle blocks. Once the existing range cannot store new data, this segment will obtain a new range, but these ranges are not required to be adjacent to each other. Such expansion will continue until the data files in the tablespace cannot provide more free space or the number of ranges has reached the limit.
　
Therefore, a data segment with too many fragments will not only affect the operation, but also cause space management problems in the tablespace. Therefore, it is very helpful that each data segment contains only one range. With the help of the monitoring system, you can check the DBA_SEGMENTS data dictionary view to find out which database objects contain 10 or more segments and determine their data segment fragments.

If a segment contains too many fragments, you can use either of the following methods:
1. Create a new table with the correct storage parameters, insert the data of the old table into the new table, and delete the old table;
2. Use the Export/Import tool.

Example: exp system/manager file = exp. dmpcompress = Y grants = Y indexes = Y
Tables = (T1, T2)
If the output is successful, go to Oracle and delete the preceding table.
Note: compress = Y indicates that their storage parameters will be modified during the output process.
Imp system/manager file = exp. dmp commit = Y buffer = 64000 full = Y

Iv. Free-range fragmentation

A free range in a tablespace is a set of free spaces connected to the tablespace. When a segment is closed, its range is released and marked as free range. However, these free ranges cannot be merged with adjacent free ranges, and the boundaries between them always exist. However, when the default pctincrease value of the tablespace is not 0, the SMON background process regularly cooperates these adjacent free ranges. If pctincrease is set to 0, the adjacent free range is not automatically merged by the database. However, you can use the Alter table command "coalesce" option to force the merge of adjacent free ranges.

If range merging is not performed, the space allocation in the tablespace will be affected in future space requests. When a large enough range is required, the database does not merge adjacent Free Ranges unless there are no other options. In this way, when the first small free range in the tablespace has been used, the largest free range in the rest of the tablespace will be used. As a result, there is not enough space to use, which leads to a conflict of table space requirements. Due to this situation, the database space allocation is getting farther and farther away from the ideal. Free Space fragments often appear in database tables and indexes that are frequently disabled and regenerated.

In the ideal Oracle tablespace, each database object is stored in a separate range, and all valid free space is concentrated in a large and continuous range. In this way, when an object needs to be attached with a bucket, it can increase the possibility of obtaining sufficient free space while minimizing the loop calls in the space to improve the free space usage.