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.