Solution for optimizing Oracle physical storage structure

We all know that database-related data is ultimately stored on the relevant physical disks. In fact, the common way to access data is to read and write these physical disks, therefore, the optimization of these physical storage is an important part of system optimization.

The Optimization of the Oracle physical storage structure is mainly to rationally allocate the physical storage address of the logical structure. Although this cannot reduce the number of reads and writes to the physical storage, it can make these reads and writes as parallel as possible, reduce disk read/write competition to improve efficiency. You can also perform precise computing on physical storage to reduce unnecessary physical storage structure expansion, thus improving system utilization.

1. parallel disk read/write Optimization

For physical reads and writes to databases, the Oracle system will optimize the database in parallel as much as possible. For example, in the simplest table retrieval operation, if the table structure and the index in the retrieval domain are not in the same physical structure, the retrieval of the index and the retrieval of the table are performed in parallel during the retrieval process.

2. Parallel Operation Optimization

The optimization of parallel operations is based on the statistical results of Operation statements. The first is to calculate the Access frequency of each table and the connection frequency between tables. The following principles are used to allocate table space and physical disks based on the data, reduce the disk I/O competition between system processes and user processes, separate the tables to be connected from the tablespace/physical disk, and separate frequently accessed tables from the tablespace/physical disk; separate the table structures and indexes of tables that often need to be searched in the tablespace/physical disk.

3. reduce storage structure Expansion

If the database of the application system is fragile and constantly growing or shrinking, the efficiency of such a system is reasonable within the non-dynamic change period, but within the dynamic change period, the performance is poor because of the dynamic expansion of Oracle.

In the process of dynamic expansion, Oracle must expand and allocate new storage space when creating rows and row changes to obtain the default values according to storage requirements, however, table extensions are often not the end of the process, but may also lead to the growth of data files and tablespaces. These extensions will lead to slow online system response. For such a system, the best way is to allocate enough size and appropriate growth margin in advance at the time of establishment. Unfortunately, these are only after a period of actual operation, in order to obtain a reasonable value from the statistical analysis ).

When an object is created, its size should be fully calculated based on the application, and the values of Initial, Next, and Minextents should be defined based on the data, make the database reach a better balance between physical storage and the number of dynamic increases, so that these objects do not increase frequently, but also occupy more databases.

The above content is an introduction to the optimization of the Oracle physical structure. I hope you will have some gains.