Oracle database fragmentation Causes and defragmentation

Database fragmentation is a major factor in database performance and should be discovered and defragmented in a timely manner.

First, how the debris is produced

When a database instance is created, it is divided into multiple logical segments (segment) called Tablespace (tablespace), such as system table space, temporary (temporary) tablespace, and so on. A table space can contain multiple data ranges (extent) and one or more free range blocks, which are free spaces.

The logical relationships between table space, segment, range, and free space are as follows:

When a segment is generated in a tablespace, space is allocated for the initial scope of the segment from the active free space in the table space. When these initial ranges are full of data, the Genhai request adds another scope. Such an extension process continues until the maximum range value is reached, or there is no free space in the table space for the next range.

The ideal state is that a segment of data can be present in a single range. In this way, all the data is stored close to the other data in the paragraph, and a few pointers are used to find the data.     However, the case that a segment contains multiple scopes is present in large quantities, and there is no measure to ensure that the ranges are stored adjacent to each other. When a space requirement is met, the database no longer merges adjacent free ranges (unless there is no alternative), but instead looks for the largest free range in the table space to use. This will gradually form more and more discrete, separated, smaller free space, that is, fragmentation.

Ii. impact of fragmentation on the system

1, resulting in weakened system performance

As mentioned above, when a space requirement is to be satisfied, the database will first find the current maximum free range, and the "maximum" free range gradually becomes smaller, it becomes more and more difficult to find a large enough free scope, which leads to the speed barrier in the table space, and makes the spatial distribution of the database more and more away from the ideal state;

2, waste a lot of table space

Some extent cannot be merged automatically, wasting a lot of table space.

Iii. fragmentation calculations in the free range

Since free space debris is made up of several parts, such as the range, the maximum size, etc., we can visualize the value of the FSFI (free Spaces Fragmentation Index: Space debris indexes):

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

As you can see, the maximum possible value for FSFI is 100 (an ideal single file tablespace). As the range increases, the FSFI values slow down, and as the maximum range size decreases, the FSFI value drops rapidly.

You can use the following SQL to calculate FSFI values:

/* Formatted on 2011/01/27 14:12:43 (QP5 v5.115.810.9015) * *

SELECT Tablespace_name,

SQRT (MAX (blocks)/SUM (blocks))

* (100/sqrt (SQRT (blocks)))

Fsfi

From Dba_free_space

GROUP by Tablespace_name

Order by 1;

For example, in a database running above SQL, get the following FSFI values:

Tablespace_name FSFI

------------------------------ ----------

Qs_oa 54.3794714

RBS 59.4603558

Sysaux 84.0138802

SYSTEM 70.5946502

UNDOTBS1 20.0110492

USERS 100

XEZF 6.52482383--More fragments

Xezf_index 32.1055286

8 rows selected.