ORACLE tablespace fragmentation

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. As a major factor affecting database performance-Database

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. As a major factor affecting database performance-Database

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 produced

---- When a database is generated, it is divided into multiple logical segments, such as system tablespace and temporary tablespace. 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 fields exist. There is no way to ensure that these ranges are stored adjacent to each other, as shown in figure <1> 〉. 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

---- With the widespread use of database-based application systems over time, more fragments will be generated, which will have the following two main impacts on the database:

---- (1) weaken 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) wasting a lot of tablespaces

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

---- Because free space fragmentation 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 )))

---- We can see 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

---- Calculate the fsfi value of the database and use it 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.
---- 4. Fragment within the Free Range

---- (1) the pctincrease value of the tablespace is not 0.

---- Change the default storage parameter pctincrease of the tablespace to a value other than 0. Generally, it is set to 1, for example:
Alter tablespace temp
Default storage (pctincrease 1 );

---- In this way, smon automatically merges the free range. You can also manually merge the free range:
Alter tablespace temp coalesce;

---- 5. segment fragmentation
---- We know that segments are composed of ranges. In some cases, it is necessary to organize the segments. To view information about a segment, you can view the dba_segments data dictionary and dba_extents data dictionary. If the segment contains too many fragments, the easiest way to compress the data into a range is to use the correct storage parameters to recreate the segment and then insert the data in the old table to the new table, delete the old table at the same time. This process can be completed using the import/export (input/output) tool.

---- The export () Command has a (compression) Mark, which will cause export to determine the physical space allocated to the table during reading, it writes a new initial storage parameter to the output dump file -- equal to all allocated space. If this table is disabled, use the import () tool to regenerate it. In this way, its data will be put into a new, large segment. For example:
Exp user/password file = exp. dmp compress = y grants = y indexes = y
Tables = (table1, table2 );

---- If the output is successful, the output table is deleted from the database, and then the input table is in the output dump file:
Imp user/password file = exp. dmp commit = y buffer = 64000 full = y

---- This method can be used for the entire database.