Oracle Database defragmentation

oracle| Data | Database We know that Oracle as a large database, widely used in finance, telecommunications, electricity, civil aviation and other data throughput is huge, the computer network widely popular important departments. For the system administrator, how 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 in database performance, database fragmentation should be given sufficient attention by DBAs to find and defragment them in a timely manner as a basic maintenance component of the DBA.
----1, how the fragments are produced
----When a database is built, it is divided into multiple logical segments (Segment), called Table spaces (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----table spaces, segments, ranges, and free spaces are as follows:

----When a segment is generated in a tablespace, space is allocated from the active free space in the table space for the initial scope of the segment. 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. But the case of a segment that contains multiple scopes is large and there is no measure to ensure that these ranges are stored adjacent to each other, as shown in Figure 〈1〉. 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. For example:

----2, the impact of fragmentation on the system
----over time, the widespread use of database based applications, resulting in more fragmentation, 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 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) Wasting a lot of table space
----Although a portion of the free range (such as the pctincrease of the table space is not 0) will be periodically merged by the Smon (System monitoring) background process, there is always a part of the free range that cannot be merged automatically, wasting a lot of tablespaces.
----3, free range of debris calculations
----Because free space debris is composed of several parts, such as the range number, the maximum range size, etc., we can use the Fsfi--free Space Fragmentation index (free spatial debris indexing) value to visually reflect:
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). As the range increases, the FSFI values slow down, and as the maximum range size decreases, the FSFI value drops rapidly.
----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/s qRT (sqrt (count (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 US Er_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 a tablespace with enough effective free space and FSFI value exceeding 30, it is rare to encounter 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, you can view the data dictionary dba_segments, and the 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 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 using the import/export (input/Output) 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, rebuild using the import () tool. 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);
----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. Database 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.