Explain how to sort Oracle Database fragments step by step

Oracle, as a large database, is widely used in financial, post and telecommunications, power, civil aviation, and other important sectors with a large data throughput and a wide spread of computer networks. For system administrators, it is especially important to ensure stable network operation and improve database performance to make it more secure and efficient. Database fragmentation, a major factor affecting database performance, should be paid enough attention by DBAs. It is a basic maintenance item for DBAs to promptly discover and organize fragments.

1. How fragments are generated

When a database is generated, it is divided into multiple logical segments, such as System tablespaces and Temporary tablespaces. A tablespace can contain multiple data ranges (Extent) and one or more Free range blocks, that is, Free Space ).

The logical relationships between tablespaces, segments, ranges, and free spaces are as follows:

When a segment is generated in the tablespace, space is allocated for the initial range of the segment from the valid free space of the tablespace. When these initial ranges are full of data, the segment requests an additional range. This expansion process continues until the maximum range value is reached, or there is no free space in the tablespace for the next range. The ideal state is that data in a segment can be stored in a single range. In this way, all data is stored close to other data in the segment, and less pointers can be used to find data. However, when a segment contains multiple scopes, there are a large number of them. There is no way to ensure that these scopes are stored adjacent to each other. To meet the requirements of a space, the database no longer merges adjacent Free Ranges (unless there is no choice), but looks for the largest free range in the tablespace for use. This will gradually form more and more discrete, separated, and smaller free spaces, I .e. fragments. For example:

2. Impact of fragmentation on the System

Over time, database-based application systems are widely used, resulting in more and more fragments, which will have the following two main impacts on the database:

1) the system performance degrades.

As mentioned above, to meet a spatial requirement, the database first looks for the current maximum free range, and the "maximum" Free Range gradually decreases, it has become increasingly difficult to find a free range that is large enough, leading to speed barriers in the tablespace, so that the database space allocation is increasingly far away from the ideal state;

2) a large amount of tablespace is wasted.

Although some free ranges (for example, the pctincrease of a tablespace is not 0) will be periodically merged by the background process of SMON (system monitoring), some free ranges cannot be automatically merged, A large number of tablespaces are wasted.

1 2 Next Page

[Content navigation]
Page 1st: explains how to sort Oracle Database fragments step by step	Page 2nd: explains how to sort Oracle Database fragments step by step