Do not let temporary tablespace affect Oracle database performance

When sorting, grouping and summarizing, and indexing are performed in the Oracle database, a lot of temporary data is generated. If there is an employee information table, the database stores the installation record creation time. If you use the Order BY sorting statement to specify the Order BY employee number, all records generated after sorting are temporary data. How does the Oracle database process the temporary data?

Generally, the Oracle database first stores the temporary data in the PGA (Program global zone) of the memory. In the global area of this program, there is a place called the sorting area, which is used to store the temporary data generated by the sorting operation. However, the partition capacity is limited. When the partition size is insufficient to accommodate the records generated after sorting, the database system will store the temporary data in the temporary tablespace. This is the source of the temporary tablespace. It seems that this temporary tablespace is a temporary task and has little impact on the database. In fact, this is a misunderstanding of the temporary tablespace. When a user performs database operations, sorting, grouping and summarizing, and indexing are indispensable and generate a large amount of temporary data. Therefore, temporary tablespace is required for each database. If the temporary tablespace is improperly set, the database performance will be greatly affected. Therefore, Administrators cannot take it lightly when maintaining this temporary tablespace. Avoid improper temporary tablespace settings affecting database performance. Specifically, pay attention to the following aspects.

1. When creating a user, remember to create a temporary tablespace for the user.

It is recommended that you specify a temporary tablespace when creating a user. For example, you can use the statement default temporary table space to set the default temporary tablespace for the database. However, this is not mandatory in Oracle databases. However, I strongly recommend that you do this. If the default temporary tablespace is not specified for the user, when the user needs to use the temporary tablespace for sorting and other operations, the database SYSTEM uses the SYSTEM tablespace SYSTEM to create temporary segments. As we all know, this is a system tablespace. Because system operation-related data is stored in this tablespace, it is generally recommended that user data cannot be stored in this tablespace. So what is the negative impact if the user's temporary tablespace is prevented from being within the system tablespace?

The data in the temporary tablespace is temporary. For this reason, the database system needs to allocate and release temporary segments frequently. These frequent operations will generate a large amount of storage fragments in the system tablespace. When these storage fragments are large, the efficiency of the system's hard disk reading will be affected, thus affecting the database performance. The size of the system tablespace is usually limited. In this case, the temporary segment also takes up the size of the system tablespace.

For this reason, the database administrator must note that when no temporary tablespace is specified for the user, user sorting and other operations still need to use temporary segments. In this case, the database system puts the temporary segment into the system tablespace. This will adversely affect the performance of the database. Therefore, the author suggests that readers and database administrators specify a default tablespace when creating users to reduce the use of temporary segments in the system tablespace.

2. properly set the PGA to reduce the chance of using temporary tablespace.

When sorting operations generate temporary data, the database does not immediately store it in the temporary tablespace. Generally, the temporary data is stored in the global area of the PGA program in the memory. Only when the global area of the program cannot accommodate all the data, the database system will enable the temporary segments in the temporary tablespace to save the data. However, as we all know, the operating system reads data from the memory several thousand times more than the data block from the hard disk. Therefore, it is ideal that the global area of the program is large enough to accommodate all temporary data. At this time, the database system will never use the temporary tablespace. This improves the database performance.

But after all, this is just an ideal. Due to memory size and other restrictions, the size of this PGA program area is often limited. Therefore, this temporary tablespace is still indispensable for some large sorting operations. What the database administrator can do now is to reasonably set the global size of the PGA program and minimize the chance of using temporary tablespace. For example, in actual work, the database administrator can set the initialization parameter SORT_AREA_SIZE as needed. This parameter mainly controls the size of the sorting area in the global area of the PGA program. Generally, if the database system is mainly used for queries and requires a large number of sorting, grouping, and aggregation operations, you can adjust this parameter to expand the size of the PGA partition. On the contrary, if the system is mainly used for update operations or other applications are deployed on the database server, the PGA partition cannot occupy too much memory, to prevent adverse effects on other applications. Therefore, database officials cannot achieve a one-size-fits-all purpose and must be adjusted based on actual conditions. If necessary, you can increase the system memory to increase the size of the PGA partition. This reduces the usage of temporary tablespace and improves the performance of database sorting, group summary, and other operations.

In short, if the temporary segment is frequently used, the performance of the database will be reduced due to the performance difference between the memory and the hard disk. In normal work, the database administrator needs to monitor the use of temporary tablespace to determine whether measures are required to reduce the use of temporary tablespace to improve the query performance of the database. For this purpose, the database administrator is recommended to view the dynamic performance view v $ sort_segment. In this dynamic performance view, you can view the usage of the system sorting segment (a temporary segment. In addition, you can use the dynamic performance view v $ sort_usage to query the user and session information using the sorting segment. This provides data support for database administrators to optimize database performance. For this sort segment, I would also like to explain a little. For sorting segments, all SQL statements of the same routine (if sorting operations are required) share the same sorting segment. The sorting segment is created when it is used for the first time. After sorting is completed, this sorting segment will not be released. It will only be released after this process is closed. Therefore, the above two views need to be comprehensively analyzed to obtain the information the database administrator wants.

3. reserve sufficient hard disk space for the temporary tablespace.

Data Files corresponding to other tablespaces are fully allocated and initialized when they are created, that is, they are allocated storage space when they are created. However, the temporary files corresponding to the temporary tablespace are different. For example, in Linux, the system does not allocate or initialize temporary files when creating temporary tablespace. That is to say, no storage space is allocated to temporary files. Only when temporary files are needed for temporary data, the system allocates a block on the hard disk to save the temporary files. In this case, a problem may occur, that is, when the temporary file system needs to allocate space for it, the system partition does not have enough storage space. At this time, some unpredictable consequences will occur.

For this reason, it is best for the database administrator to reserve sufficient space for these temporary files in advance. For example, in a Linux operating system, it can be prevented from being in an independent partition and cannot be used by other applications. In this case, you don't have to worry about the temporary file storage. In addition, temporary tablespace is mainly used to store temporary files for sorting. Therefore, if you can store this temporary tablespace in a better-performing partition, you can also speed up the database system to read data in the temporary tablespace. In addition, because the system needs to frequently allocate data in the temporary tablespace, the partition where the temporary tablespace is located will contain a large number of fragments. In this case, if the temporary tablespace is stored in an independent partition, the database administrator can separately fragment the partition to improve the performance of the partition. Therefore, it is a good idea to prevent temporary tablespace from being in an independent partition for whatever reason. This not only ensures that temporary files have storage space, but also improves database performance.

Note that the temporary tablespace is shared by all users by default. That is to say, each user connected to the database can use the default temporary tablespace. The database administrator can specify other temporary tablespace for it. Generally, you only need a temporary tablespace.

1. Three criteria for creating an Oracle database index
2. Detailed description of Oracle Database full-text index (1)
3. How to select an appropriate index type in Oracle Database
4. Create an index in Oracle and force the optimizer to use it (1)
5. Introduction to Oracle database development experience