Oracle Database fragmentation

Oracle, as a large database, is widely used in finance, post and telecommunications, electric power, civil aviation, etc.

An important sector that has a huge data throughput and is widely used in computer networks. For the system administrator, how does one

It is particularly 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 and timely

Discovering and organizing fragments is a basic maintenance of DBA.

---- 1. How fragments are produced

---- When a database is generated, it is divided into multiple logical segments (

Segment), such as system tablespace and temporary tablespace. One tablespace

It 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, the initial range of the segment is defined in the valid free space of the tablespace.

Allocate space. When these initial ranges are full of data, the segment requests an additional range. Such extension

The process continues until the maximum range value is reached, or there is no free space in the tablespace.

Is used 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.

Is stored adjacent to <1> 〉. To meet a spatial requirement, the database does not merge adjacent

The range (unless there is no choice) is used to find the maximum free range in the tablespace. In this way

Gradually form more and more discrete, separated, and smaller free space, that is, 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.

The database has the following two major impacts:

---- (1) weaken system performance

---- As mentioned above, to meet a spatial requirement, the database first looks for the largest free model currently.

While the "largest" range of freedom gradually decreases, it is becoming increasingly difficult to find a sufficiently large range of freedom.

Difficult, leading to speed barriers in the tablespace, making the database space allocation more and more away from the ideal state;

---- (2) wasting a lot of tablespaces

---- Despite some free ranges (such as tables? When the percentage of ctincrease in zookeeper is not 0, it will be SMON (System

Monitoring) background processes are periodically merged, but some of the Free Ranges cannot be automatically merged.

A large amount of table space is required.

---- 3. Free-range shard computing

---- Since free space fragments are composed of several parts, such as the number of ranges and the maximum size of ranges, we can

Use fsfi -- free space fragmentation index (Free Space fragment index) value to intuitively reflect

:

Fsfi = 100 * SQRT (max (extent)/sum (extents) * 1/SQRT (count (extents )))

---- We can see that the maximum possible value of fsfi is 100 (an ideal single-file tablespace ). With fan

With the increase of circumference, The fsfi value decreases slowly, while with the decrease of the maximum range size, 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

Temp22.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

---- Calculate the fsfi value of the database and use it as a comparable parameter. When one has enough

In tablespaces with valid free space and fsfi value greater than 30, the issue of valid free space is rare.

When a space is close to a comparable parameter, it needs to be fragmented.

---- 4. Fragment within the Free Range

---- (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. Yes

View information about a segment. You can view the dba_segments data dictionary and the range information.

Dba_extents. If the segment contains too many fragments, the easiest way to compress the data to a certain range is

Use the Correct storage parameters to recreate the segment, insert the data in the old table to the new table, and delete the old table.

Table. This process can be completed using the import/export (input/output) tool.

---- The Export () Command has a (compression) flag, which triggers the export to determine when reading the table

The amount of physical space allocated by the table. It will write a new initialized storage parameter to the output dump file --

Equal to all allocated space. If this table is disabled, use the import () tool to regenerate it. In this way,

Its data will be placed in 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. Quantity

Database performance optimization is a highly technical task that requires patience and meticulous work.

A Discussion on database fragmentation,

---- If you can inspire others, it is the author's greatest wish.

In addition, the database should be shut down regularly to clear momery fragments.

End
-- Sort scripts
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

Previous: use ORACLE 10046 event tracking SQL statements

Next article: planning career experience