How to organize Oracle Database fragments

Oracle DatabaseAs a large database, it is widely used in financial, post and telecommunications, electric power, civil aviation, and other important departments 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. As a major factor affecting database performance-DatabaseFragmentDBAs should pay enough attention to it. It is a basic maintenance item for DBAs to promptly discover and organize fragments. The existence of fragments in Oracle databases will inevitably affect the work of Oracle databases, the following describes the Oracle database fragmentation methods.

1. How fragments are generated

When a database is generated, it is divided into multiple logical segments, such as System tablespaces and Temporary tablespaces. A tablespace can contain multiple data ranges (Extent) and one or more Free range blocks, that is, 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 scopes, there are a large number of them. There is no way to ensure that these scopes are stored adjacent to each other. To meet the requirements of a space, the database no longer merges adjacent Free Ranges (unless there is no choice), but looks 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

Over time, database-based application systems are widely used, resulting in more and more fragments, 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) a large amount of tablespace is wasted.

Although some free ranges (for example, the pctincrease of a tablespace is not 0) will be periodically merged by the background process of SMON (system monitoring), some free ranges cannot be automatically merged, A large number of tablespaces are wasted.

3. Free-range shard computing

Free Space fragment is composed of several parts, such as the number of ranges and the maximum size of ranges. We can use the FSFI -- Free Space Fragmentation Index (Free Space Fragmentation Index) value 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 (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:

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 can be counted 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.

You can change the default storage parameter pctincrease of a 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.

The Export command has a (compression) flag, which causes 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. A Discussion on database fragmentation,

The following code describes how to automatically process table space fragments.

Coalesce Tablespace Automatically

This technique comes from Sandeep

Naik, a database administrator

For GSXXI, Inc. in New York City, New York

Here is a handy script which can be

Scheduled to automatically run

And coalesces the tablespaces.

This script is designed to run in NT

But can be run in any operating system

By slight modifications in the path where the file spools

From the SQLPLUS environment.

It assumes that the user who runs the script

Has priviledges to view the data dictionary.

Start of code

--------------------------------------

Sqlplus/

Prompt this script will coalesce

Tablespace automatically

Set verify off;

Set termout off;

Set head off;

Spool c: empcoalesce. log

Select alter tablespace

| TABLESPACE_NAME | coalesce;

From DBA_FREE_SPACE_COALESCED where

PERCENT_EXTENTS_COALESCED <100

Or PERCENT_BLOCKS_COALESCED <100;

Spool off;

@ C: empcoalesce. log

Set head on;

Set termout on;

Set verify on;

Prompt Tablespaces are coalesced successfully

Through step-by-step analysis, the article first analyzes how fragments are generated, then the impact of fragments on the system, and finally summarizes the solution for everyone, I hope everyone will be able to get some gains from the content mentioned above.