Several issues in Oracle 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

2.1 Causes system performance to weaken

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.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.

Iv. Debris-Finishing

Defragmentation of 4.1 panel (extent)

Use command: Alter TABLESPACE temp COALESCE;

/* Formatted on 2011/01/27 14:23:46 (QP5 v5.115.810.9015) * *

SELECT ' alter tablespace ' | | Tablespace_name | | ' Coalesce; '

From dba_free_space_coalesced

WHERE percent_extents_coalesced < OR percent_blocks_coalesced < 100;

4.2-Segment (segment) defragmentation

The section is composed of extent. In some cases, it is necessary to organize fragments of segments. To view information about a segment, you can view the data dictionary dba_segments,segment information to view the data dictionary dba_extents.

Segments can have table segments, index segments, and so on. Each segment can be defragmented.

(1) Fragmentation of the table segment

If the fragment is too fragmented, the easiest way to compress its data into a range is to rebuild the segment with the correct storage parameters, and then insert the data from the old table into the new table, deleting the old table. This process can be done with the Import/export tool.

The Export command has a (compressed) flag that, when read, causes export to determine the amount of physical space allocated by the table, which writes a new initialization storage parameter to the output dump file-equal to all allocated space. If this table is closed, it is regenerated using the import command. In this way, its data is placed in a new, larger initial segment. For example:

Exp User/password file=exp.dmp compress=y

Grants=y indexes=y tables= (table1,table2);

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/