Sort Oracle Database fragments

Source: Internet
Author: User
Tags comparable

 We know that Oracle, as a large database, is widely used in financial, post and telecommunications, electric power, civil aviation, and other important sectors with huge data throughput and extensive computer networks. For system administrators, it is especially important to ensure stable network operation and improve database performance to make it more secure and efficient. Database fragmentation, a major factor affecting database performance, should be paid enough attention by DBAs. It is a basic maintenance item for DBAs to promptly discover and organize fragments.
1. How fragments are generated
---- When a database is generated, it is divided into multiple logical segments called Tablespace), for example, System) Tablespace, Temporary) Tablespace. A tablespace can contain multiple data ranges (Extent) and one or more Free range blocks (Free Space ).
---- The logical relationships between tablespaces, segments, ranges, and free spaces are as follows:
---- When a segment is generated in the tablespace, space is allocated for the initial range of the segment from the valid free space of the tablespace. When these initial ranges are full of data, the segment requests an additional range. This expansion 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 data in a segment can be stored in a single range. In this way, all data is stored close to other data in the segment, and less pointers can be used to find data. However, when a segment contains multiple ranges, a large number of such ranges exist. There is no way to ensure that these ranges are stored adjacent to each other. <1> 〉. To meet a spatial requirement, the database no longer merges adjacent Free Ranges unless there is no choice), but searches for the largest free range in the tablespace for use. This will gradually form more and more discrete, separated, and smaller free spaces, I .e. fragments. For example:
2. Impact of fragmentation on the System
---- With the widespread use of database-based application systems over time, more fragments will be generated, which will have the following two main impacts on the database:
---- 1) the system performance degrades.
---- As mentioned above, to meet a spatial requirement, the database first looks for the current maximum free range, and the "maximum" Free Range gradually decreases, it has become increasingly difficult to find a free range that is large enough, leading to speed barriers in the tablespace, so that the database space allocation is increasingly far away from the ideal state;
---- 2) wasting a lot of tablespaces
---- Although some of the Free Ranges, such as the pctincrease of the tablespace, are not 0) will be monitored by the SMON system.) background processes are periodically merged, but some free ranges cannot be automatically merged, A large number of tablespaces are wasted.
3. Free-range shard computing
---- Because Free Space Fragmentation is composed of several parts, such as the number of ranges and the maximum size of ranges, we can use FSFI -- Free Space Fragmentation Index) Values to intuitively reflect:
FSFI = 100 * SQRT (max (extent)/sum (extents) * 1/SQRT (count (extents )))
---- It can be seen that the maximum possible value of FSFI is 100. It is an ideal single-file tablespace ). As the range increases, the FSFI value decreases slowly, while the maximum range 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 (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 to obtain the following FSFI value:
RBS 74.06.
SYSTEM 100.00
TOOLS 75.79
Users' 100.00
YDCX_IDX 57.19
YDJF_IDX 75.55
---- Calculate the FSFI value of the database and use it as a comparable parameter. In a tablespace with sufficient free space and with a FSFI value greater than 30, the problem of free space is rare. When a space is close to a comparable parameter, it needs to be fragmented.
4. Free-range Fragment
---- 1) The pctincrease value of the tablespace is not 0.
---- Change the default storage parameter pctincrease of the tablespace to a value other than 0. Generally, it is set to 1, for example:
Alter tablespace temp
Default storage (pctincrease 1 );
---- In this way, SMON automatically merges the free range. You can also manually merge the free range:
Alter tablespace temp coalesce;
5. segment fragmentation
---- We know that segments are composed of ranges. In some cases, it is necessary to organize the segments. To view information about a segment, you can view the dba_segments data dictionary and dba_extents data dictionary. If the segment contains too many fragments, the easiest way to compress the data into a range is to use the correct storage parameters to recreate the segment and then insert the data in the old table to the new table, delete the old table at the same time. This process can be completed using the Import/Export input/output) tool.
---- Export) Command has a compression Mark. This mark will cause Export to determine the amount of physical space allocated to the table during reading, it writes a new initial storage parameter to the output dump file -- equal to all allocated space. If this table is disabled, use the Import) tool to generate it again. In this way, its data will be put into a new, large segment. For example:
Exp user/password file = exp. dmp compress = Y grants = Y indexes = Y
Tables = (table1, table2 );
---- If the output is successful, the output table is deleted from the database, and then the input table is in 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.
---- The preceding section briefly analyzes the generation, calculation, and sorting of Oracle Database fragments for your reference only. Database performance optimization is a highly technical task that requires patience and attention.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.