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 debris is generated
---- When generating a database, it is divided into multiple logic segments called tablespace, such as system (System) tablespace, temporry 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, and free space logic relationship is as follows:
---- When generating a segment in the table space, the initial range of this segment is allocated from the initial range of this segment in the initial range of this segment. When these initial ranges are filled with data, the paragraph will request an additional range. Such an extension 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 combined with adjacent free range (unless no choice), but is looking for the largest free range in the table space. This will gradually form more and more discrete, separated, smaller free space, ie debris. E.g:
---- 2, the impact of debris on the system
---- Over time, the extensive use of the database-based application system, more and more debris will have the following two points of the database:
---- (1) lead to weakening system performance
---- 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 smaller, and it is more and more beautiful enough. Difficult, resulting in speed barriers in the table space, making the spatial allocation of the database away from the ideal state;
---- (2) Waste a lot of table space
---- Although some of the free range (such as PctinCrease in the table space) will be periodically merged by the SMON (system monitoring) background process, there is always a part of the free ranges that cannot be automatically merged, and a lot of tables are wasted. space.
---- 3, fragment calculation of 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) to intuitively reflect:
FSFI = 100 * SQRT (Max (Extent) / SUM (Extens)) * 1 / SQRT (SQRT (count (extents)))
---- It can be seen that the maximum possible value of FSFI is 100 (an ideal single text table space). As the range increases, the FSFI value slowly decreases, and the FSFI value will fall quickly as the maximum range size is reduced. ---- 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 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, Freedom Fragmentation
---- (1) The PctinCrease value of the table space is not 0
---- You 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);
---- This SMON will automatically merge free range. It is also possible to manually consolidate free range:
Alter TableSpace Temp coalesce;
---- 5, fragment finishing
---- We know that 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 segment is too large, 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 a 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 enter a table from the output dump file:
IMP user / password file = exp.dmp commit = y buffer = 64000 full = y
---- This method can be used throughout the database.
---- Simple analysis of Oracle database fragments, calculation methods and finishing, for reference only. The performance optimization of the database is a high technical content, and it is necessary to have enough patience and careful work. A point of discussion on database fragmentation,
---- If it can play a brick, the author is inspired by everyone, it is the biggest wish of the author.
