Comprehensive Analysis of Oracle shard collection

Oracle, as a super large database, is widely used in financial, post and telecommunications, electric power, civil aviation, and other large data throughput. It is the main organization for extensive promotion of computer networks. As for the system administrator, it is especially important to ensure the stable operation of the responsible Book network and increase the database performance to make it more secure and efficient. As a major factor that affects database performance, database fragmentation should arouse DBA's attention. timely discovery and collection of fragmentation is a fundamental maintenance of DBA.

I. Why did fragments occur?

When a database is generated, it is divided into multiple logical segments (segments), such as System tablespaces and Temporary tablespaces. A tablespace can contain multiple data categories (Extent) and one or more Free category blocks, that is, Free Space ).

Logical relationships between tablespaces, segments, categories, and free spaces are as follows:

When a segment is generated in the expected tablespace, space is allocated for the initial category of the segment from the valid free space of the tablespace. When these initial categories are full of data, segment requests increase to another category. This expansion process will continue until it reaches the maximum category value. Maybe there is no free space in the tablespace for the next category. The most ambitious state is that data in a segment can be in a monotonous category. In this way, all the data is stored in the nearest segment, and there are few pointers to the data. However, there are a large number of situations in multiple categories of a pregnancy package, and no measure can be taken to order these categories to be stored nearby. To meet a spatial requirement, the database should not combine the free category adjacent to the Dragon (unless there is no way), but find the largest free category in the tablespace for use. This will gradually form more and more discrete, separated, and smaller free space, that is, fragments.

Ii. Impact of fragmentation on the System

Over time, the widespread use of database-based application systems will cause more and more fragments of attacks, which will have an important impact on the database:

1) lead to system performance reduction

In general, when we need to meet a spatial requirement, the database will find the largest category of freedom at the current stage, and the "largest" category of freedom will gradually become smaller, it has become increasingly difficult to identify a sufficiently large scope of freedom. As a result, the speed barriers in the tablespace make the allocation of space in the database more and more far away from ambition;

2) spread a large number of tablespaces

Although some of the Free categories (such as the pctincrease of the tablespace is not zero) will be periodically merged by the SMON (System Monitoring) Background boss, but there are always some free categories that cannot be automatically merged, stores a large number of tablespaces.

Iii. Free-range fragment computing

Because Free Space fragment is composed of several parts, such as the number of categories and the maximum range size, 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 or value of FSFI is 100 (an ambitious single data table space ). With the increase of category, the FSFI value will land slowly, and the FSFI value will land quickly with the decrease of the maximum range.

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, if the script fsfi. SQL is run in a database, the following FSFI value is lost:

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. In a tablespace with sufficient free space and a value of more than 30 FSFI, the problem of free space is rare. When a space is close to comparable parameters, we need to clean up the space.

Iv. Free-range debris collection

The pctincrease value of the tablespace is not zero.

You can change the default storage parameter pctincrease of a tablespace to non-zero. Set it to 1 normally, for example:

Alter tablespace temp

Default storage (pctincrease 1 );

In this way, SMON automatically aggregates the free category. You can also manually converge to the free category:

Alter tablespace temp coalesce;

V. segment shard collection

We know that segments are formed by categories. In some cases, it is necessary to collect fragments of a segment. To view information about a segment, you can view the dba_segments data dictionary and dba_extents data dictionary.

Query data segments

Select segment_name, tablespace_name, bytes, blocks from USER_EXTENTS;

Query Directory segments

Select index_name, table_owner, table_name, tablespace_name from USER_EXTENTS;

View segment information through dba_segments

Select tablespacee_name, count (*) nobjects, round (sum (bytes)/1024/1204/1024.2) GB, sum (blocks), sum (extents) from

Dba_segments group by rollup (tablespace_name );

If the segment is too fragmented, the simplest step to compress its data into a category is to recreate the segment with accurate storage parameters, and then insert the data in the old table into the new table, remove the old table. This process can be completed using the Import/Export (input/output) tool.

The Export () Command has a (compression) Mark, which indicates that when reading the table, the Export will be selected to determine the amount of physical space allocated to the table, it writes a new initial storage parameter to the output dump-that is, all allocated space. If this table is closed, the Import () tool is applied to generate a new one. 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 = (table 1, table 2 );

If the output goes smoothly, the output table is removed from the database, and then the input table from the output dump data is:

Imp user/password file = exp. dmp commit = Y buffer = 64000 full = Y