Do not let the chain phenomenon affect database performance

When you normally create a new 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 where the record is stored on the hard disk. The same is true when a record is updated. The database system reads the records that need to be updated from the hard disk to the block based on the value of the ROWID, and then saves the records in the block to the corresponding location on the hard disk after the update is complete. During the update process, the value of the ROWID column is normally not changed.

But if the capacity of a block is not able to hold a single record. A clubhouse, when a single block of data does not have enough space to hold a new row of records or update a row of records, a chain phenomenon occurs. When the capacity of a database is insufficient to accommodate a record, the database has to use more blocks of data to hold the record. In an Oracle database, if a record needs to be saved using multiple databases, we tend to call this line of records a chained line. Accessing a row of records, if you need to access more than one block of data, will require more server resources than accessing a single block of data, which can significantly degrade database performance. The phenomenon of database performance degradation caused by chain rows is called a chain phenomenon. According to expert statistics, the chain phenomenon may reduce the performance of database 10%, or even more, if serious. Therefore, if the database administrator in the database deployment, can effectively avoid the chain phenomenon, then can greatly improve the performance of the database

First, how to determine whether there is a chain of phenomena exist?

So how can the database administrator determine if there is a chain phenomenon in the database? If there is no tool, it is difficult to judge by the eyesight or experience of the database administrator. The database administrator must find a handy tool. In fact Oracle database design This has been expected to adversely affect the performance of the database. In this paper, we have provided a tool to trace and analyze the phenomenon of chain in the database. Under the/rdbms/admin of the Oracle Database installation home directory, there is a script file named Utlchain.sql. This is a script file that is brought from the Oracle database. We can use tools such as a text editor to open this script file and see that the script file is used primarily to create a table to hold the content that is needed to parse the script phenomenon.

First step: Create the table you want.

First, the database management element needs to execute the Utlchain.sql script file provided by the Oracle database. This file is located under/rdbms/admin in the Oracle home directory. The main purpose of this script is to create a table. This form is very useful. The database system will save the results of the analysis to this table. By default, this table is not automatically generated when the database is installed. If the database administrator needs to analyze the existence of a chain phenomenon in the database, then the script file needs to be executed manually to create the table. In this table, the main table name, Head_rowid column and so on.

Step two: Analyze the purpose table.

When the above table is created, there is no data by default. Because no relevant analysis has been done. Suppose you now have a table of products in the database that is used primarily to hold product information. Now the database administrator wants to know if there is a chain in the database system when manipulating this tabular data. At this point, the database administrator needs to use the following statement to parse the query.

Analyze Table Product list chained rows;

The function of the above statement is to analyze the form of product. Determine if the records in this table exist in different blocks. If a record is stored in a different block in the product table, the statement saves the related results to the table that you just created. Therefore, if the database administrator queries the table Chained_rows just established, if there are related records in the table, then there is a chain phenomenon in the database. The database administrator needs to take appropriate steps to avoid this situation. If not, the best.

However, when using this statement, you need to pay attention to a few points. First, after each analysis of the treatment, it is best to delete the records in this table. Because the next time the analysis, if the table has records, the system will not automatically delete. So in the analysis of another table, if there is a chain of phenomena. Then there will be a lot of related records, the database administrator will be reading when the failure. The second is that the frequency of this analysis is best frequently. When the records in the database for a long time or the data update more frequently, it is best to be able to execute the analysis every few days to determine whether there is a chain of phenomena. When a lot of records or tables are chained, it can be difficult to deal with them. So for most transactional database systems, database administrators have to develop a habit of periodic analysis. For most database optimization jobs, it's much more important to track ahead than it is to solve the problem. When the problem is solved, it often goes through a lot of trouble, some even take effective measures, also means to avoid the subsequent operation does not occur. To resolve previous logging issues, it is only possible to completely resolve the data after it has been exported and imported. This obviously increases workload and data risk. To this end, the author stressed once again, for this chain of the phenomenon of tracing analysis, it is best to be able to perform once a week. In special cases, you can also use a scheduled task to execute once a day. Identify problems as early as possible and take effective measures to avoid this situation.

Second, how to avoid the chain of phenomenon?

When the database found that there is a chain of phenomena, it is necessary to adjust the relevant settings in time to avoid this situation. The main reason for the chain phenomenon is that the size of the block is not properly set. If the size of a block of data can not accommodate a record, then it is easy to cause the chain phenomenon. So if the size of the data block is properly adjusted, it can avoid the chain phenomenon to a large extent. In Oracle database, in order to avoid the phenomenon of chaining effectively, it can be realized by adjusting parameter pctfree. The main purpose of this parameter is to update the space reserved for a block. Sometimes the default value of the system is often not able to meet the requirements. This requires the database administrator to set the appropriate values for the 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 longer and needs to occupy more space, you can set a larger block for the table.

Although the Pctfree parameter can be effectively avoided by adjusting the parameters of the chain. But sometimes the improper design of the table is one of the main reasons for this problem. If there is a table m_product form, used to store product information. In this table, its product information mainly divides into the finished product and the raw material two kinds. The raw materials such products, in the system needs to record detailed product specification information, but also need to record both in English and Chinese content. So light this product specification, the Chinese and English add up to have 3,000 or so characters. And the product information is relatively simple. There is a polarization of the records in this table at this time. Some records have a large capacity, the need to use a number of blocks to save, there is a chain of phenomena. And some records, the volume is not very large. While this can be solved by setting larger blocks of data to the table, it also wastes data space. Because there are still a lot of records, there is no use of such a large block of space. So in this case, the one-sided adjustment of pctfree parameters, will reduce the utilization of hard disk space. At this point, I think it is best to adjust the database table design. If you can save the product specification field in a different table, and then connect the keyword to the product table. In this case, all records will be about the same length in the product table. At this time, adjust the Pctfree parameter according to the need, not only can avoid the chain phenomenon effectively, but also improve the utilization of the hard disk space. Of course, for the newly established table, it is necessary to improve the Pctfree parameters to avoid the chain phenomenon. However, the adjustment of the base table for the database system has been put into use, the adjustment of the action is a bit large, will affect the normal use of users. To this end in the database design, you need to fully communicate with the user. This can be foreseen when the database initializes the design. Therefore, the author has always stressed that the database optimization must be done in front.

In addition, if the records in the database are rarely updated, such as some decision analysis systems or data warehouses, they require a large amount of imported data at the beginning. The content in the database is virtually never updated after the data is imported. You do not need to set the Pctfree parameter too large at this time. You can set a smaller value to increase the utilization of your hard disk space and allow the table space to store more records. Visible, the size of the PCTFREE parameter does not have a fixed reference standard. It is mainly based on the purpose of the database, the record of the update in the table, record size and so on. How to determine a reasonable pctfree parameter value, in order to reduce the chain phenomenon and improve the table space utilization, this is also the difficulty and challenge of database optimization.