We know that Oracle is a large database, which is widely used in financial, post-transmitted, electricity, civil aviation and other data throughput, and is an important department that is widely popular in computer networks. For system administrators, how to ensure network stability, how to improve database performance, make it more secure and efficient, it is especially important. As a major factor affecting database performance - Database fragment, it should caused sufficient attention to DBA, and it is found that the fragmentation is a basic maintenance of DBA. ---- 1, how the fragmentation is generated --- When generating a database, it is divided into multiple logic sections called table space (system), such as system (System) table space, temporary ( Temporary) Table space, etc. A table space can contain multiple data ranges and one or more free range blocks, i.e., Free Space. ---- Table space, segment, range, free space logic relationship is as follows: ---- When a segment is generated in the table space, the initial range from the table space is the initial range of this segment is the initial range of this segment. When these initial ranges are filled with data, the paragraph will request an additional range. Such an extended process will continue until the maximum range value is reached, or there is no free space in the table space for the next range. The most ideal state is that the data of a segment can be present in a single range. Thus, all data storage is close to other data in the segment, and finding data can be less using some pointers. However, a segment contains a plurality of ranges that exist in a large number, without any measures ensure that these ranges are adjacent storage, as shown in FIG. 1>. When a space requirement is to be met, the database is no longer merged adjacent scope (unless there is no choice), but is looking for the largest free range in the table space. This will gradually form an increasing discrete, separated, smaller free space, ie fragments. For example: ---- 2, the impact of the debris on the system - "Over time, the extensive use of the database-based application system, the resulting debris will be increasing, will have the following two points of the database: ---- (1) leads to system performance weakening - as mentioned above, when a space requirement, the database will first look for the current maximum free range, and the "maximum" free range gradually becomes small, to find one Sufficient and big enough free range has become more difficult, leading to speed barriers in the table space, making the spatial distribution of the database away from the ideal state; ---- (2) Waste a lot of table space ---- Although A portion of the free range (PctinCrease in the table space) will be periodically merged by the SMON (System Monitoring) daemon, but there is always a part of the free ranges that cannot be automatically merged, and a lot of tablespace is wasted. ---- 3, the fragmentation of the free range ---- Since the free space debris is composed of several parts, such as the range, the maximum range size, etc., we can use FSFI - Free Space Fragmentation INDEX (Free Space Debris Index) value To intuitively reflect: FSFI = 100 * SQRT (MAX (Extent) / SUM (EXTENTS)) * 1 / SQRT (SQRT (count (extents))) ---- It can be seen that the maximum possible value of FSFI is 100 (one Ideally single text table space).
As the range increases, the FSFI value slowly decreases, and the FSFI value will fall quickly as the maximum size is reduced. ---- The following scripts can be used to calculate fsfi values: Rem Fsfi Value Compute Rem Fsfi.SQL Column Fsfi Format 999, 99 SELECT TABLESPACAT 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 1; Spool fsfi.rep; / spool off; ---- For example, run the script fsfi.sql in a database, get the following FSFI value: 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 ---- Statistics the FSFI value of the database, you can use it as a comparable parameter. In a table space with enough effective free space, and the FSFI value exceeds 30, there is little problem of effective free space. When a space will be close to the parameter, you need to make a fragmentation. ---- 4, the fragmentation of the free range ---- (1) The PctincRease value of the table space is non-0 --- can change the default storage parameters of the tablespace to non-0. It is generally set to 1, such as ALTER TABLESPACE TEMP DEFAULT Storage (PctinCrease 1); ---- like SMON automatically merges free range. It is also possible to manually consolidate free range: ALTER TABLESPACE TEMP COALESCE; ---- 5, fragment finishing - - We know, the segment is constructed. In some cases, it is necessary to organize the fragments of the segment. To view information about segments, view data dba_segments, range information to view data dictionary DBA_EXTENTS. If the fragment of the segment is too much, the simplest method that compresses its data to a range is to rebuild this segment with the correct storage parameters, and then insert the data in the old table into the new table, and delete the old table. This process can be done with the Import / Export tool. ---- The export () command has a (compressed) flag, which triggered Export to determine the physical space assigned by the table when reading a table, which writes a new initial storage parameter to the output dump file - It is equal to all allocated space. If this table is turned off, use the import () tool to regenerate. In this way, its data will be placed in a new, large initial segment. For example: exp user / password file = exp .dmp compress = y grants = y indexes = y tables = (Table1, Table2); ---- If the output is successful, remove the output table from the library, then transfer from the output Enter the table in the store: IMP user / password file = exp.dmp commit = y buffer = 64000 full = y ---- This method can be used throughout the database.
