Oracle Database defragmentation

oracle| Data | Database defragmentation http://www.computerworld.com.cn (2001-05-14 11:15:01) We know that Oracle, as a large database, is widely used in finance, post and telecommunications, Electricity, civil aviation and other
The data throughput is huge, and the computer network is widely popular in the important department. For system administrators, how to
To ensure the stable operation of the network, how to improve the database performance, make it more safe and efficient, it is particularly important
。 As a major factor affecting database performance--Database fragmentation--should be given sufficient attention by DBAs, in time
The discovery and defragmentation of fragmentation is a basic maintenance content for DBAs.
----1, how the fragments are produced

----When a database is built, it is divided into multiple logical segments called Table spaces (tablespace) (
segment), 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, that is, free space
Space).

The logical relationships----table spaces, segments, ranges, and free spaces are as follows:



----When a segment is generated in a tablespace, the initial range of this segment is created from the active free space in the table space
Allocate space. When these initial ranges are full of data, the Genhai request adds another scope. Such an extension
The 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 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.
But the case of a segment that contains multiple scopes is large and there is no measure to ensure that the range
is adjacent to the store, as shown in Figure 〈1〉. When you want to meet a space requirement, the database no longer merges adjacent self
By the scope (unless there is no choice), but look for the largest free range in the table space to use. This will be
Gradually forming more and more discrete, separated, smaller free space, that is, fragmentation. For example:



----2, the impact of fragmentation on the system

----over time, the widespread use of database based application systems will produce more and more debris
, will have the following two main impacts on the database:

----(1) causes system performance to weaken

----as described above, when a space requirement is to be met, the database first finds the current largest free fan
Circumference, and the "largest" free range gradually becomes smaller, to find a sufficiently large range of freedom has become increasingly sleepy
, which leads to the speed barrier in the table space, which makes the spatial distribution of the database more and more away from the ideal state;


----(2) Wasting a lot of table space

----Although part of the free range (such as the pctincrease of the table space is not 0) will be Smon (System
monitoring) Background processes are periodically merged, but there is always a part of the free range that cannot be automatically merged, waves
Cost a lot of table space.

----3, free range of debris calculations

----Because free space debris is composed of several parts, such as range, maximum range size, etc., we can
Using Fsfi--free space Fragmentation index (free spatial fragment) value to visualize
：

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

----can see that the maximum possible value for FSFI is 100 (an ideal single file tablespace). With fan
As the circumference increases, the FSFI value slows down, and the FSFI value drops rapidly as the maximum range size decreases.

----The following script can be used to compute FSFI values:

REM FSFI value Compute
REM Fsfi.sql
Column FSFI format 999,99
Select TABLESPACE_NAME,SQRT (max (blocks)/sum (blocks)) *
(100/sqrt (sqrt (blocks)) FSFI
From Dba_free_space
Group BY Tablespace_name ORDER by 1;
Spool Fsfi.rep;
/
Spool off;

----For example, to run a script fsfi.sql in a database, get the following FSFI values:
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

----The FSFI value of the database is counted, it can be used as a comparable parameter. In one has enough
Effective free space, and the FSFI value of more than 30 of the table space, rarely meet the problem of effective free space.
When a space is approaching a comparable parameter, it needs to be defragmented.
----4, free-scope defragmentation

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

----can change the default storage parameter Pctincrease for a tablespace to 0. It is generally set to 1, such as:


Alter Tablespace TEMP
Default storage (Pctincrease 1);

----So Smon will automatically merge the free range. You can also manually merge the free range:
Alter tablespace temp COALESCE;

----5, Fragment defragmentation
----We know that paragraphs are made up of ranges. In some cases, it is necessary to organize fragments of segments. To
View information about a segment, view data dictionary dba_segments, range of information to view the data dictionary
Dba_extents. If the fragment is too fragmented, the easiest way to compress its data into a range is
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 using the import/export (input/Output) tool.

----Export () command has a (compressed) flag that causes export to be identified when reading a table
The amount of physical space allocated by the table, which writes a new initialization storage parameter to the output dump file-
equals all allocated space. If this table is closed, rebuild using the import () tool. Such
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);

----If the output succeeds, delete the exported table from the library and enter the table from 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.
----above, the paper simply analyzes the generation, calculation method and collation of Oracle database fragment for reference only. Number
According to the Library performance optimization is a high technical content, but also need to have enough patience, serious and meticulous work.
A little discussion of database fragmentation,

It is the author's greatest wish that----can make a good point and enlighten us.



In addition, the database should be shutdown regularly to clean up momery fragments.