Complete Oracle fragment analysis (1)

Introduction: Oracle DatabaseAs a large database, it is widely used in financial, post and telecommunications, electric power, civil aviation, and other important departments with huge data throughput and extensive 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. As a major factor affecting database performance --Database fragmentationDBAs should pay enough attention to it. 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 ranges, a large number of such ranges exist. There is no way to ensure that these ranges are stored adjacent to each other. To meet a spatial requirement, the database no longer merges adjacent Free Ranges (unless there is no choice), but searches 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.

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) weakened system performance

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.

3. Free-range shard computing

Free Space fragment is composed of several parts, such as the number of ranges and the maximum size of ranges. We can use the FSFI -- Free Space Fragmentation Index (Free Space Fragmentation Index) value to intuitively reflect:

FSFI = 100 * SQRT (max (extent)/sum (extents) * 1/SQRT (count (extents )))

It can be seen that the maximum possible value of FSFI is 100 (an ideal single-file tablespace ). As the range increases, the FSFI value decreases slowly, while the maximum range size decreases, the FSFI value decreases rapidly.

The following script can be used to calculate the FSFI value:

Rem FSFI Value Compute

Rem fsfi. SQL

Column FSFI format 999,99

Select tablespace_name, sqrt (max (blocks)/sum (blocks ))*

(100/sqrt (count (blocks) FSFI

From dba_free_space

Group by tablespace_name order by 1;

Spool fsfi. rep;

/

Spool off;

For example, run the script fsfi. SQL in a database to obtain the following FSFI value:

TABLESPACE_NAME FSFI

RBS 74.06.

SYSTEM 100.00

Temp22.82

TOOLS 75.79

Users' 100.00

USER_TOOLS 100.00

YDCX_DATA 47.34

YDCX_IDX 57.19

YDJF_DATA 33.80

YDJF_IDX 75.55

The FSFI value of the database can be counted as a comparable parameter. In a tablespace with sufficient free space and with a FSFI value greater than 30, the problem of free space is rare. When a space is close to a comparable parameter, it needs to be fragmented.