Upgrade Oracle tablespace to local hosting Mode

Tablespaces have two management modes: data dictionary and local hosting. If the data dictionary is used for maintenance, operations that occur on the database segment and are related to disk partition allocation (such as extending a table) will lead to operations on the data dictionary. If many tables with disk partitions are operated, the data dictionary becomes the bottleneck resource for these operations. It can be seen that if the data dictionary is used to maintain the tablespace, the cost of the database will be very high.

To solve this problem and improve the table space management performance, Oracle database has introduced a new table space management mode, that is, the local management mode. If the tablespace is set to local hosting, these disk partition management operations are all re-allocated to the bitmap block of the data file. In this case, each tag space of the database only contains its own disk zone information. You can use the fast hashed process access technology to access the relevant Sydney, instead of using slow, table-Based Query access. The most important thing is that if many tables with many disk partitions are operated, the data dictionary will not become the performance bottleneck. It can be seen that the performance of local hosting is higher than that of the data dictionary maintenance mode under the same conditions.

I. Two features of the local hosting mode.

In addition to some differences between the local hosting mode and the data dictionary mode, the local hosting mode also provides two distinctive options, automatic allocation and unified allocation. This connection option is mainly used to control the line of sight in which the disk area is allocated to the segment. If this method is set to automatic allocation, the Oracle database system uses an internal algorithm (this algorithm is not required by the database administrator ), when the segment size is adjusted (for example, when the segment size increases), the disk size is automatically increased. That is to say, when the automatic allocation option is used, when the segments in the tablespace increase, the database system determines the size of the next disk partition according to certain rules. The main principle of this algorithm is to use the number of disk areas and the scale-out ratio as the coefficient and use other parameters for simulation calculation. The advantage of automatic distribution of disk space is obvious. It is difficult to set up a proper disk size due to various restrictions at the beginning of database system deployment. If Automatic allocation is adopted, if the size of the disk area is too small at the beginning, the database will automatically increase the size of the next Disk Area of the table as the table is changed in the future, this reduces the number of all disk partitions available to the table. This can greatly improve the database performance. In addition, the automatic allocation option ensures that the number of segments does not exceed the controllable range, because the database automatically adjusts according to the actual situation.

If a unified disk zone management policy is adopted, all disk zones in the tablespace are allocated with the same size specified during the creation of the tablespace, without considering other factors, for example, the storage clause set in the segment creation statement is not considered. The disk size will not be adjusted as some applications change. Obviously, if a uniform allocation policy is adopted, you need to set a reasonable disk partition size during tablespace planning.

Some people will say that, since unified allocation is so troublesome and will not be automatically adjusted, it is better to use an automatic allocation policy. In fact, it cannot be so absolute. The two management options have their respective advantages. Automatic allocation means that no reasonable disk space size is set when the tablespace is created, so the database will adjust itself according to certain rules in the future. The advantage of unified allocation is that when segments are moved or deleted, the free disk areas in the tablespace can be reused better, resulting in very few fragments, because they all adopt a uniform size. My suggestion is that, based on the database management experience, you can determine the appropriate tablespace disk partition size, then it is best to adopt a unified disk partition management policy. On the contrary, if a small number of segments cannot be deleted at the same time, you can use the automatic allocation option to improve the database performance.

2. Upgrade the tablespace from the dictionary hosting mode to the local hosting mode.

If the original tablespace is in the dictionary hosting mode, you can upgrade it to the local hosting mode without recreating the tablespace. This means that the data in the original tablespace will not be lost. For example, for the SYSTEM tablespace, the database SYSTEM provides an application for table space management mode conversion (TableSpace_Migrate_TO_Local ). this application can upgrade the tablespace management mode from the data dictionary hosting mode to the local hosting mode without formatting the System tablespace.

However, the conversion method of the hosting mode as above has some limitations. For example, when this conversion mode is used, the disk Qu ing parameter will be moved into the data file of the tablespace, and a storage clause must be prepared for each segment in the tablespace. At this time, the two management features of the local management mode (automatic allocation policy and disk size Management Policy) will not be available, so as to effectively reduce disk fragments and improve database performance. Therefore, if this upgrade mode is adopted, enterprises will not obtain policy improvement from the upgrade, and the database performance improvement will be compromised.

For this reason, I recommend a more thorough upgrade method. That is, the segments in the tablespace to be converted are exported for backup; then the original tablespace is deleted and re-created (in this case, the tablespace hosting mode is set to local hosting ); finally, import the original segments. Although it is necessary to delete the original large table space, there is a certain operation risk. However, this conversion method can bring high performance. In addition, in order to make this method safe, the database administrator should be able to check the size of this segment before performing operations. This helps reduce errors in subsequent operations. In addition, the table space management mode can be upgraded from the data dictionary hosting mode to the local hosting mode through various methods. However, it is best to determine the hosting mode when deploying the database system. After all, subsequent adjustments will increase the workload and operational risks. It also increases data fragmentation and affects database performance.

1 2 Next Page