Oracle Database performance: Analysis of disk space occupied by tables

As we all know,TableInOracle DatabaseIn addition, fragmentation has a great obstacle to Oracle database space management. At the same time, if the disk space in the segment is large, it will also affect the database performance. Although the local hosted tablespace technology is adopted in Oracle10G, it can eliminate the negative impact to the maximum extent. However, when the data volume is large and the disk volume increases, the database query efficiency will be reduced. What should I do when the database administrator encounters a similar situation? In this article, I will give some explanations on this issue for your reference.

1. How do I count the number of Disk Partitions used by a table?

When allocating disk space, the Oracle database system usually uses the allocation method one by one. As the table grows over a long period of time, the number of Disk Partitions used increases. If the growth of tables cannot be reasonably predicted during database deployment, the number of disk partitions will increase unreasonably in the future. This eventually affects the database performance. For this reason, the database administrator must keep track of the operation of the database. For example, you need to count the number of Disk Partitions of tables with a large number of common tables and records.

As shown in, this is the disk area of the basic table of product information in an ERP system. According to my understanding, the company's database was used for less than six months. At this time, the number of disk partitions in the product table has reached 25. In the next six months, the number of Disk Partitions may exceed 100. Obviously, the database administrator did not predict this situation during database design. If it continues to grow, it is likely to become a bottleneck in system performance. Therefore, it is necessary to sort it out.

2. Create a New tablespace and port the relevant data.

When the database administrator finds that the number of Disk Partitions occupied by the table is very large, it becomes an indisputable task to organize the table in real time. It is generally recommended to create a new tablespace and then port the relevant data. Before talking about specific operations, I should emphasize one point. Although the database allows this post-event remedy, I do not recommend this operation. The author's opinion is that during database design, it is best to see the growth of tables in the future. If a table has a large number of records, frequent updates, and usage, it is best to set a relatively independent tablespace or a relatively high growth rate for it. These measures can minimize the number of Disk Partitions occupied by tables in the future and improve the query efficiency of tables.

Step 1: Create a New tablespace and create a M_PRODUCT1 table.

Note that we do not recommend creating a table in an existing tablespace. This is mainly because there may be no sequential storage space in the existing tablespace. Therefore, it cannot be ensured that the new table must have continuous disk space. I believe that it is best to recreate a new tablespace. In addition, this table is updated frequently and frequently used. Therefore, setting a tablespace is conducive to subsequent maintenance, such as data upgrade and backup. In addition, you must set an appropriate size for the tablespace. Generally, it is better to meet the needs of the next two years. This can meet the needs of future data growth. Otherwise, if the number of disk areas is too large every six months or a year, it will obviously be a headache.

After the tablespace is created, create a M_PRODUCT1 table. When creating this table, the database administrator can Create a table in the form of Create as Select * FROM M_PRODUCT. When creating a table, import the data to the new table to ensure that the data is consistent with the data type.

Step 2: delete the indexes in the original table and table.

You must first Delete the indexes in the original table before deleting the table. Otherwise, an error message is displayed. In order to ensure data security, I emphasize that it is best to perform a full backup of the entire database before the operation. Next, because this will affect the normal operation of the system, it is best to disconnect all current users before the operation. And set it to inaccessible to the user. To avoid unnecessary troubles during the operation. We recommend that you perform the following operations after the following shift or on weekends when you do not use the database.

Step 3: rename a new table and create an index.

After deleting the original table, change the name of the newly created table to the original table name. Then create related indexes based on the original table. Here, we can easily copy the index when creating a new table. However, the index name cannot be repeated. Therefore, you must add a suffix after the original index name. After the old table is deleted, the index name is changed back and re-enabled. Note: If the table contains a large amount of data, it may take a long time to re-create the index.

Finally, I would like to remind you that if your table grows too fast, you can port the data to a tablespace with a larger uniform disk size to adapt to subsequent table growth. Of course, this is a relatively large project. Although this requires re-indexing the relevant table, this ensures that the table will not be lost from the physical storage of the database. If the database administrator Thinks this operation is necessary, the author can provide some suggestions to speed up the operation. For example, you can use the COPY command to avoid using rollback segments. Or use the Nologging option to avoid time problems caused by redo. When there are many records, the use of NOLOGGING can speed up data insertion. But it also brings about a defect, that is, the rollback operation cannot be performed when a problem occurs. The fish and the bear's paw cannot have both sides. At last, the database administrator still needs to balance the performance and security.

The above method is used to solve the problem of too many disk partitions. It is only a last resort. The most important thing is to reasonably predict the growth of tables in the database design phase. Then, make reasonable planning for the disk space and tablespace based on the expected results. Reasonable planning can control the number of disk areas within a relatively small range.

I have explained so much about the disk space occupied by tables in Oracle databases. I hope that you will be able to learn more from the above.