Do not let temporary tablespace affect database performance

Welcome to the Oracle community forum, and interact with 2 million technical staff. When sorting, grouping, and indexing are performed in the Oracle database, a lot of temporary data will be generated. If there is an employee information table, the database stores the installation record creation time. If you use the OrderBY sort statement to specify the order by employee ID during query

Welcome to the Oracle community forum, and interact with 2 million technical staff> enter the Oracle database for sorting, grouping and summarizing, and indexing, a lot of temporary data will be generated. If there is an employee information table, the database stores the installation record creation time. If you use the Order BY statement to specify the Order BY employee ID during query

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

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.

[1] [2]