Optimize Oracle tablespace design to improve database performance

Oracle Database pioneered the design concept of tablespace, which makes an indelible contribution to the high performance of Oracle Database. In this case, many Oracle optimizations are implemented based on the tablespace design concept.

Typical application 1: control the tablespaces occupied by users

In some large database applications, we need to control the disk space occupied by a user or a group of users. This is like setting a disk quota for each user on the file server to prevent hard disk space depletion. Therefore, in the database, we also need to limit the disk space available to users. To achieve this goal, we can implement it through the tablespace.

We can create different tablespaces in the Oracle database, set the maximum storage capacity for them, and then assign users to the tablespaces. In this case, the user's storage capacity is limited by the size of the tablespace.

Typical application 2: control the disk space occupied by the database

Sometimes, there may be more than one service running on the Oracle Database Server. In addition to database servers, there may also be application system servers such as email servers. Therefore, you must first plan the disk space of the Oracle database. Otherwise, when the disk space occupied by multiple application services increases infinitely, in the end, each service may be stopped due to the exhaustion of hard disk space. Therefore, to use multiple application services on the same server, we often need to plan and allocate disk space for them first. Each service cannot exceed the maximum limit we allocate to it, or remind us immediately after it is exceeded. Only in this way can we avoid the crash of various application services due to the depletion of disk space.

Typical application 3: flexibly place tablespaces to improve database input/output performance

The database administrator can also store different types of data in different tablespaces, which can significantly improve the database input and output performance and facilitate data backup and recovery management. Because our database administrator can back up data by tablespace when backing up or recovering data. For example, when designing a large distribution system background database, we can create tablespaces by province. Data Files related to Zhejiang Province are stored in the tablespace of Zhejiang Province. Business records in Beijing are recorded in the tablespace of Beijing. In this way, when the business data in Zhejiang Province is incorrect, the tablespace in Zhejiang province can be restored directly. Obviously, in this design, when data in a tablespace is wrong and needs to be restored, the impact on other tablespaces can be avoided.

In addition, the table space can be backed up independently. When the database capacity is relatively large, if you back up the entire database at once, it will obviously take a lot of time. Although Oracle Database supports hot backup, it occupies a large amount of system resources during the backup process, resulting in a decline in database performance. Therefore, when the database capacity is relatively large, we need to set multiple tablespaces, and then plan the backup time of each tablespace to improve the backup efficiency of the entire database, reduce the impact of backup on the normal operation of the database.

Typical application 4: Sorting large tables

We all know that when there are many records in the table, the query speed will be slow. After the first query is successful, it still takes so much time to re-Sort it for the second time. For this reason, when we design a database, we usually place such a large table object in an independent tablespace to improve the database performance.

Typical application 5: Opening log files and data files separately improves database security

By default, log files and data files are stored in the same tablespace. However, this is not very good for database security. Therefore, when designing a database, we often like to store log files, especially redo log files, in an independent tablespace and store them on another hard disk. In this case, when the hard disk for storing data files fails, the database can be repaired immediately by redoing log files stored in another tablespace, to reduce the loss caused by data loss.

Of course, the advantages of table space are not just that. The higher the performance requirements of enterprises for databases, or the larger the database capacity, the greater the advantages of table space.

Next, let's take a look at the processing methods of tablespace in Oracle databases to see if they have sufficient advantages in performance and security to compete with SQL Server databases.

During database design, we recommend that the database administrator set the tablespace in the following order.

Step 1: Create a tablespace.

When designing a database, you must first design the tablespace. We need to consider whether to create only one tablespace or multiple tablespaces, as well as the storage location and disk quota of each tablespace.

There is no uniform saying about how many tablespaces are designed reasonably. This is mainly determined based on the actual needs of the enterprise. If an enterprise needs to control the user's disk quota, it needs to set the tablespace based on the number of users. When the data capacity of an enterprise is large and it has high requirements on database performance, you need to set different tablespaces based on different types of data, to improve the input/output performance.

Step 2: create a user and create a default tablespace for the user.

When creating a user, we recommend that the database administrator specify the user's default tablespace. Because we use the CREATE statement to CREATE database objects, such as database tables, which are stored in the current default space of the database by default. If you do not specify the user's default tablespace, you must specify the tablespace each time you create a database object. Obviously, this is not very reasonable.

Note that different tablespaces have different permission control policies. The user has full control permissions on table space A, and may have only query permissions on table Space B, or even connection permissions. Therefore, configuring table space access permissions is also a way to improve database security.

1. How to check Oracle availability and tablespace capacity
2. Step 3: manually expand the Oracle database tablespace
3. ORACLE tablespace recovery plan