Preventing chain-based phenomena from affecting database performance

Normally, when you create a record in a table, the database system writes the data to the block and provides a ROWID value like this row of records. This value records the location of the record stored on the hard disk. This is also true when updating a record. The Database System reads the records to be updated from the hard disk to the block based on the ROWID value. After the update, the records in the block are saved to the corresponding location of the hard disk. During the update process, the value of the ROWID column is usually not changed.

However, if the capacity of a block cannot accommodate one record. That is, when a single data block does not have enough space to store a new row of records or a new row of records, the chain will occur. When the capacity of a database is insufficient to accommodate one record, the database will have to use more data blocks to save this record. In Oracle databases, if a record needs to be stored in multiple databases, we often call this record a chained row. When accessing a row of records, if you need to access multiple data blocks, it will consume more server resources than accessing a single data block, which will greatly reduce the database performance. We call this kind of phenomenon of database performance degradation caused by chained rows as a chain phenomenon. According to expert statistics, in severe cases, the chain phenomenon may reduce the database performance by 10% or more. Therefore, if the data database administrator can effectively avoid the chain Phenomenon During database deployment, the database performance can be greatly improved.

1. How to determine whether there is a chain phenomenon?

So how should the database administrator determine whether the database is chained? If there is no tool, it is difficult to judge based on the eyesight or experience of the database administrator. The database administrator must find a handy tool. In fact, the design of the Oracle database is expected to adversely affect the database performance. Therefore, a tool for tracing and analyzing blockchain phenomena has been provided in the database. There is a script file named utlchain. SQL under/rdbms/admin in the main directory of the Oracle database installation. This is a script file that comes with the Oracle database. We can use a text editor and other tools to open this script file. We can see that this script file is mainly used to create a table to save the content required for analyzing script phenomena.

Step 1: create the required table.

First, the database administrator needs to execute the utlchain. SQL script file provided by the Oracle database. This file is located under/rdbms/admin in the Oracle main directory. This script is mainly used to create a table. This table is useful. The database system saves the analysis results to this table. By default, this table is not automatically generated when the database is installed. If the database administrator needs to analyze whether the database is chained, manually execute the script file to create the table. This table contains the table name, HEAD_ROWID column, and so on.

Step 2: analyze the target table.

After the preceding table is created, no data exists by default. Because no relevant analysis has been performed. Assume that there is a Product table in the database, which is mainly used to store the Product information. The database administrator wants to know whether the database system is chained when operating the table data. In this case, the database administrator needs to use the following statements for analysis and query.

Analyze table product list chained rows;

The above statement is used to analyze the product table. Determine whether the records in this table exist in different blocks. If there are records stored in different blocks in this product table, this statement will save the relevant results to the table just created. Therefore, if the database administrator queries the created table chained_rows and the table contains related records, it indicates that the database is chained. The database administrator must take appropriate measures to avoid this situation. If not, it would be best.

However, when using this statement, pay attention to the following points. First, we recommend that you delete the records in this table after each analysis. This is because the system will not automatically delete records in the table during the next analysis. Therefore, when analyzing another table, the chain phenomenon also exists. At this time, there will be a lot of related records, and the database administrator will fail when reading. Second, the frequency of this analysis is better frequent. When there are too many records in the database or data updates are frequent, it is best to execute this analysis statement every few days to determine whether there is a chain phenomenon. When a large number of records or table cells are chained, the processing will be more difficult. Therefore, for most transactional database systems, database administrators need to develop a habit of periodic analysis. For most database optimization jobs, tracking in advance is much more important than solving problems in time. When the problem arises, it will usually take a lot of weeks to solve it. Even if some effective measures are taken, it means that this situation can be avoided in subsequent operations. To solve the previous record problems, the problem can be completely solved only after data is re-exported and imported. Obviously, this will increase workload and data risks. For this reason, I will not bother to emphasize it again. We recommend that you perform this trace analysis every week. In special cases, you can also execute a task plan once a day. Discover problems as early as possible and take effective measures to avoid such problems.

Ii. How to Avoid chain-based phenomena?

When the database discovers a chain phenomenon, you need to adjust the relevant settings in time to avoid this situation. The main cause of blockchain is that the block size setting is inappropriate. If the size of a data block cannot accommodate one record, the blockchain may occur. Therefore, if you adjust the size of data blocks appropriately, you can avoid this chain phenomenon to a large extent. In Oracle databases, you can adjust the PCTFREE parameter to effectively avoid blockchain. This parameter is mainly used to update the space reserved by a block. Sometimes the default value of the system is often unable to meet the requirements. Therefore, the database administrator needs to set appropriate values based on actual needs. It is worth noting that this value can be set according to the table. For this reason, if the database administrator thinks that the record of a table may be long and takes up a large amount of space, you can set a large block for this table.

Although the PCTFREE parameter can be adjusted to effectively avoid the chain phenomenon. However, improper table design is also one of the main causes of this problem. If there is a table M_PRODUCT, it is used to store product information. In this table, the product information is divided into finished products and raw materials. Among the products such as raw materials, detailed product specifications must be recorded in the system, and English content must also be recorded. Therefore, the maximum number of Chinese and English characters is about 3000 characters. The finished product information is relatively simple. In this case, the records in this table have polarization. Some records have a large capacity and need to be stored using multiple data blocks. This results in a chain. In some cases, the capacity is not very large. At this time, although you can set a large data block for this table to solve this chain phenomenon, it also wastes data space. Because there are still a majority of records, such a large block space is not used at all. In this case, one-sided adjustment of the PCTFREE parameter will reduce the disk space utilization. At this time, I think it is best to adjust the design of database tables. For example, you can save the Product type field in another table and then use the keyword to connect to the Product table. In this case, all records in the Product table are of the same length. At this time, adjust the PCTFREE parameter as needed, which not only effectively avoids the chain phenomenon, but also improves the utilization of hard disk space. Of course, the PCTFREE parameter needs to be appropriately increased for newly created tables to avoid the leeching. However, the adjustment of this base table is a little big for the database system that has been put into use, which will affect the normal use of users. Therefore, we need to fully communicate with users during database design. This situation can be foreseen during database initialization design. Therefore, I have always stressed that database optimization must be done first.

In addition, if the records in the database are rarely updated, such as some decision analysis systems or data warehouses, they only need to import a large amount of data at the beginning. After the data is imported, the content in the database is basically not updated. In this case, you do not need to set the PCTFREE parameter too large. You can set a small value to improve the utilization of hard disk space and store more records in the tablespace. It can be seen that there is no fixed reference standard for the size of the PCTFREE parameter. It mainly depends on the database usage, the update degree of the records in the table, the size of the records, and so on. How to determine a reasonable PCTFREE parameter value to reduce the number of links while improving the utilization of tablespaces is also the difficulty and challenge of database optimization.