Oracle, as a very large database, is widely used in major organizations with huge data throughput and extensive computer networks such as finance, postal and telecommunications, electric power, and civil aviation. As for system administrators, it is particularly important to ensure the stable operation of the network and to improve database performance to make it more secure and efficient. As a major factor affecting database performance, database fragmentation should arouse DBA's sufficient attention. Timely discovery and cleaning up of fragments is a basic maintenance content of DBA.
1. Why do fragments occur?
When a database is generated, it will be divided into multiple logical segments (Segments) known as tablespaces (Tablespace), such as system (System) tablespace, temporary (Temporary) tablespace, etc. A table space can contain multiple data ranges (Extent) and one or more free range blocks, that is, free space (Free Space).
The logical relationships among table spaces, segments, categories, and free spaces are as follows:
When a segment is created in a table space, space will be allocated for the initial scope of the segment from the effective free space of the table space. When these initial ranges are filled with data, the segment will request the addition of another range. This expansion process will continue until the maximum range value is reached, and there may be no free space in the table space for the next range. The most ideal situation is that the data of a segment can be stored in a single category. In this way, all data is stored close to other data in the segment, and fewer pointers are needed to find data. However, there are many cases where a segment contains multiple fields, and there is no way to prove that these fields are stored nearby. When a space requirement is to be met, the database no longer closes the adjacent free range (unless there is no way), but looks for the largest free range in the table space to use. This will gradually form more and more discrete, separated, smaller free spaces, that is, fragments.
2. Impact of fragmentation on the system
As time goes by, the widespread use of database-based application systems will generate more and more fragments, which will have the following two important impacts on the database:
1) Incurring system performance reduction
In general, when a space requirement is to be met, the database will first search for the largest free range at present, and the "largest" free range gradually becomes smaller, and it becomes increasingly difficult to find a large enough free range. It is difficult, so the speed obstacles in the table space make the space allocation of the database further and further away from the ideal state;
2) Spread a large amount of table space
Although some free areas (such as the pctincrease of the table space is non-zero) will be merged periodically by the SMON (system monitoring) background boss process, there are always some free areas that cannot be automatically closed, wasting a lot of table space. .
3. Fragment calculation of free categories
Because free space fragmentation is composed of several parts, such as the number of categories, the maximum range size, etc., we can use the FSFI--Free Space Fragmentation Index (Free Space Fragmentation Index) value to intuitively reflect:
FSFI=100*SQRT(max(extent)/sum(extents))*one/SQRT(SQRT(count(extents)))
As can be seen, the maximum value of FSFI is 100 (an ideal single table space). As the scope increases, the FSFI value decreases slowly, and as the maximum scope 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(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by one;
spool fsfi.rep;
/
spool off;
For example, when running the script fsfi.sql in a certain database, the following FSFI values are lost:
TABLESPACE_NAME FSFI
------------------------------- -------
RBS 74.06
SYSTEM 100.00
TEMP 22.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
---- Once the FSFI value of the database is calculated, it can be used as a comparable parameter. In a table space with sufficient effective free space and a FSFI value above 30, effective free space problems are rarely encountered. When a space is close to comparable parameters, it needs to be defragmented.
4. Sorting out the fragments of free categories
The pctincrease value of the table space is non-zero
You can change the default storage parameter pctincrease of the table space to non-zero. Normally set it to one, like:
alter tablespace temp
default storage(pctincrease 1);
In this way, SMON will automatically group the free categories. You can also group free categories manually:
alter tablespace temp coalesce;
5. Picking up the fragments of the paragraph
We know that segments are composed of categories. In some cases, it is necessary to clean up the fragments of the segment. To view segment-related information, view the data dictionary dba_segments, and for category information, view the data dictionary dba_extents.
Query data segment
select segment_name,tablespace_name,bytes,blocks from USER_EXTENTS;
Query directory segment
select index_name,table_owner,table_name,tablespace_name from USER_EXTENTS;
View segment information through dba_segments
select tablespacee_name,count(*) nobjects,round(sum(bytes)/1024/1204/1024.2) GB,sum(blocks),sum(extents) from
dba_segments group by rollup(tablespace_name);
If a segment is excessively fragmented, the easiest way to compress its data into a range is to rebuild the segment with the correct storage parameters, then insert the data from the old table into the new table, and at the same time delete the old table. This process can be accomplished using the Import/Export (input/output) tool.
The Export () command has a (compressed) flag, which when reading the table will cause Export to determine the amount of physical space allocated to the table, and it will write a new initialization storage parameter to the output dump data-that is, all Allocate space. If this table is closed, use the Import () tool to regenerate it. This way, its data will be placed into a new, larger initial segment. for example:
exp user/password file=exp.dmp compress=Y grants=Y indexes=Y
tables=(table one, table two);
If the output goes smoothly, delete the exported table from the library, and then import the table from the output dump data:
imp user/password file=exp.dmp commit=Y buffer=64000 full=Y