Processing of temporary data in Oracle database
- 2020-06-19 11:55:49
- OfStack
If the user queries using the Order BY sort statement specifying the sort by employee number, then all the records resulting from the sort are temporary data. How does the Oracle database handle this temporary data?
Typically, the Oracle database will first store this temporary data in the in-memory PGA(program Global area). In this program global area, there is a place called the sort area, which is used to store the temporary data generated by the sort operation. But the capacity of this partition is limited. When the partition is not large enough to hold the records generated after sorting, the database system stores the temporary data in the temporary table space. This is where temporary table Spaces come from. It looks as if the temporary tablespace is a temporary one, with little impact on the database. In fact, this is a misunderstanding of the temporary table space. When users perform database operations, sorting, grouping summary, index these jobs are necessary, which will produce a large number of temporary data. Basically every database needs a temporary table space for this purpose. If this temporary table space is not set up properly, it can have a significant negative impact on database performance. For this reason, administrators should not be complacent when maintaining this temporary table space. Avoid impacting database performance due to improper temporary table space Settings. Specifically, the main need to pay attention to the following aspects of the content.
1. Remember to create temporary table Spaces for users when creating them.
It is best to specify temporary table Spaces for users when they are created. For example, the statement default temporary table space statement can be used to set the default temporary table space for the database. However, this is not mandatory in the Oracle database. But I strongly recommend it. If the default temporary tablespace is not specified for the user, the database system will "think smart" and use the system tablespace SYSTEM to create temporary segments when the user needs to use the temporary tablespace for sorting and other operations. As you know, this is a system table space. Since this table space contains data related to system operation, a 1 point recommendation is that user data cannot be stored in this table space. So what are the negative effects of keeping a user's temporary table space out of the system table space?
Because the data in a temporary table space is temporary. The database system needs to allocate and release temporary segments frequently. These frequent operations create a large amount of fragmentation in the system table space. When there are large amounts of these fragments, the system's ability to read the hard disk is affected, thus affecting the performance of the database. Second, the size of the system table space is often limited. At this point the temporary segment to insert a foot, will occupy the size of the system table space.
For this reason, the database administrator needs to pay attention to 1 point. When temporary table Spaces are not specified for users, temporary segments are still required for operations such as user sorting. The database system then places the temporary segment into the system table space. This can adversely affect database performance. Therefore, readers and database administrators are advised to specify a default table space for the user when creating the user, so as to reduce the temporary segment's occupation of the system table space.
2. Set PGA reasonably to reduce the use of temporary table space.
In summary, if temporary segments are used frequently, the performance of the database will be reduced due to the performance difference between memory and hard disk. To do this, the database administrator also needs to monitor the use of temporary table Spaces to determine whether measures should be taken to reduce the use of temporary table Spaces to improve the query performance of the database. For this purpose, I recommend that database administrators look at the dynamic performance view of v$sort_segment. This dynamic performance view allows you to view the usage of the system sort segment (one of the temporary segments). The dynamic performance view, v$sort_usage, also allows you to query user and session information using sort segments. This provides data support for database administrators to optimize database performance. There is one more point I want to make about this sorting segment. For a sort segment, all SQL statements from the same routine (if a sort operation is required) will share the same sort segment. And the sort segment is created the first time it is needed. The sort segment is not released after the sort is completed, but only after the process is closed. The above two views need to be analyzed together to get the information the database administrator wants.
3. Reserve enough hard disk space for the temporary table space.
Data files corresponding to other table Spaces are fully allocated and initialized when they are created, i.e., storage space is allocated when they are created. But the temporary file corresponding to the temporary table space is different. As in the Linux operating system, temporary files are not allocated and initialized when a temporary table space is created. That is, no storage space is allocated for temporary files. Only when temporary data is present and a temporary file is needed will the system allocate a space on the hard disk to save the temporary file. The problem is that when the temporary file system is needed to allocate space for it, there is not enough storage space in the system partition. There are some unforeseen consequences.
For this reason, it is a good idea for the database administrator to reserve enough space in advance for these temporary files. As in the Linux operating system, it can be prevented from being used by other applications in a separate partition. That way, you don't have to worry about temporary files having nowhere to store them. In addition, the temporary table space is mainly used to store 1 sort of temporary files. Therefore, if the temporary table space can be stored in a partition with good performance, it can also improve the speed of the database system to read the data in the temporary table space. In addition, because the system needs to allocate the data in the temporary table space frequently, there will be more fragmentation in the partition where the temporary table space resides. At this point, if the temporary table space is stored in a separate partition, the database administrator can defragment the partition alone, thus improving the performance of the partition. So for whatever reason, it is a good idea to prevent temporary table Spaces in a separate partition. Not only can you ensure that temporary files have storage space, but you can also improve database performance.
One final note about temporary table Spaces is that by default this temporary table space is Shared by individual users. This means that every user connected to the database can use the default temporary table space. The database administrator can specify additional temporary table Spaces for them. Generally, only one temporary table space is required
When a sort operation produces temporary data, the database does not immediately store it in a temporary table space. Typically, this temporary data is first stored in the in-memory PGA program global area. The database system will only enable temporary segments in the temporary table space to hold the data if the program global cannot hold all of it. But as we all know, operating systems read thousands of times more data from memory than from hard disk. Ideally, for this purpose, the program global is large enough to hold all temporary data. The database system will never use a temporary table space. This can improve the performance of the database.
But this is only an ideal after all. The SIZE of this PGA block is often limited due to limitations such as the size of memory. So this temporary table space is still necessary when doing some large sort operations. Now the database administrator can reasonably set the size of the PGA program global area to minimize the use of temporary table Spaces. In practice, the database administrator can set the initialization parameter SORT_AREA_SIZE as required. This parameter controls the size of the sort area in the global region of the PGA program. In general, if the database system is mainly used for queries and requires a lot of sorting, grouping summary, indexing, and so on, you can adjust this parameter appropriately to increase the size of the PGA partition. Conversely, if the system is used primarily for update operations, or if other applications are deployed on the database server, then the PGA partition cannot consume too much memory to prevent adverse effects on other applications. Therefore, the database officer can not be cut 1 knife, need to be adjusted according to the actual situation. If necessary, you can increase system memory to increase the size of the PGA partition, thereby reducing the chance of temporary table Spaces being used to improve the performance of database sorting, grouping summary, and other operations.
Typically, the Oracle database will first store this temporary data in the in-memory PGA(program Global area). In this program global area, there is a place called the sort area, which is used to store the temporary data generated by the sort operation. But the capacity of this partition is limited. When the partition is not large enough to hold the records generated after sorting, the database system stores the temporary data in the temporary table space. This is where temporary table Spaces come from. It looks as if the temporary tablespace is a temporary one, with little impact on the database. In fact, this is a misunderstanding of the temporary table space. When users perform database operations, sorting, grouping summary, index these jobs are necessary, which will produce a large number of temporary data. Basically every database needs a temporary table space for this purpose. If this temporary table space is not set up properly, it can have a significant negative impact on database performance. For this reason, administrators should not be complacent when maintaining this temporary table space. Avoid impacting database performance due to improper temporary table space Settings. Specifically, the main need to pay attention to the following aspects of the content.
1. Remember to create temporary table Spaces for users when creating them.
It is best to specify temporary table Spaces for users when they are created. For example, the statement default temporary table space statement can be used to set the default temporary table space for the database. However, this is not mandatory in the Oracle database. But I strongly recommend it. If the default temporary tablespace is not specified for the user, the database system will "think smart" and use the system tablespace SYSTEM to create temporary segments when the user needs to use the temporary tablespace for sorting and other operations. As you know, this is a system table space. Since this table space contains data related to system operation, a 1 point recommendation is that user data cannot be stored in this table space. So what are the negative effects of keeping a user's temporary table space out of the system table space?
Because the data in a temporary table space is temporary. The database system needs to allocate and release temporary segments frequently. These frequent operations create a large amount of fragmentation in the system table space. When there are large amounts of these fragments, the system's ability to read the hard disk is affected, thus affecting the performance of the database. Second, the size of the system table space is often limited. At this point the temporary segment to insert a foot, will occupy the size of the system table space.
For this reason, the database administrator needs to pay attention to 1 point. When temporary table Spaces are not specified for users, temporary segments are still required for operations such as user sorting. The database system then places the temporary segment into the system table space. This can adversely affect database performance. Therefore, readers and database administrators are advised to specify a default table space for the user when creating the user, so as to reduce the temporary segment's occupation of the system table space.
2. Set PGA reasonably to reduce the use of temporary table space.
In summary, if temporary segments are used frequently, the performance of the database will be reduced due to the performance difference between memory and hard disk. To do this, the database administrator also needs to monitor the use of temporary table Spaces to determine whether measures should be taken to reduce the use of temporary table Spaces to improve the query performance of the database. For this purpose, I recommend that database administrators look at the dynamic performance view of v$sort_segment. This dynamic performance view allows you to view the usage of the system sort segment (one of the temporary segments). The dynamic performance view, v$sort_usage, also allows you to query user and session information using sort segments. This provides data support for database administrators to optimize database performance. There is one more point I want to make about this sorting segment. For a sort segment, all SQL statements from the same routine (if a sort operation is required) will share the same sort segment. And the sort segment is created the first time it is needed. The sort segment is not released after the sort is completed, but only after the process is closed. The above two views need to be analyzed together to get the information the database administrator wants.
3. Reserve enough hard disk space for the temporary table space.
Data files corresponding to other table Spaces are fully allocated and initialized when they are created, i.e., storage space is allocated when they are created. But the temporary file corresponding to the temporary table space is different. As in the Linux operating system, temporary files are not allocated and initialized when a temporary table space is created. That is, no storage space is allocated for temporary files. Only when temporary data is present and a temporary file is needed will the system allocate a space on the hard disk to save the temporary file. The problem is that when the temporary file system is needed to allocate space for it, there is not enough storage space in the system partition. There are some unforeseen consequences.
For this reason, it is a good idea for the database administrator to reserve enough space in advance for these temporary files. As in the Linux operating system, it can be prevented from being used by other applications in a separate partition. That way, you don't have to worry about temporary files having nowhere to store them. In addition, the temporary table space is mainly used to store 1 sort of temporary files. Therefore, if the temporary table space can be stored in a partition with good performance, it can also improve the speed of the database system to read the data in the temporary table space. In addition, because the system needs to allocate the data in the temporary table space frequently, there will be more fragmentation in the partition where the temporary table space resides. At this point, if the temporary table space is stored in a separate partition, the database administrator can defragment the partition alone, thus improving the performance of the partition. So for whatever reason, it is a good idea to prevent temporary table Spaces in a separate partition. Not only can you ensure that temporary files have storage space, but you can also improve database performance.
One final note about temporary table Spaces is that by default this temporary table space is Shared by individual users. This means that every user connected to the database can use the default temporary table space. The database administrator can specify additional temporary table Spaces for them. Generally, only one temporary table space is required
When a sort operation produces temporary data, the database does not immediately store it in a temporary table space. Typically, this temporary data is first stored in the in-memory PGA program global area. The database system will only enable temporary segments in the temporary table space to hold the data if the program global cannot hold all of it. But as we all know, operating systems read thousands of times more data from memory than from hard disk. Ideally, for this purpose, the program global is large enough to hold all temporary data. The database system will never use a temporary table space. This can improve the performance of the database.
But this is only an ideal after all. The SIZE of this PGA block is often limited due to limitations such as the size of memory. So this temporary table space is still necessary when doing some large sort operations. Now the database administrator can reasonably set the size of the PGA program global area to minimize the use of temporary table Spaces. In practice, the database administrator can set the initialization parameter SORT_AREA_SIZE as required. This parameter controls the size of the sort area in the global region of the PGA program. In general, if the database system is mainly used for queries and requires a lot of sorting, grouping summary, indexing, and so on, you can adjust this parameter appropriately to increase the size of the PGA partition. Conversely, if the system is used primarily for update operations, or if other applications are deployed on the database server, then the PGA partition cannot consume too much memory to prevent adverse effects on other applications. Therefore, the database officer can not be cut 1 knife, need to be adjusted according to the actual situation. If necessary, you can increase system memory to increase the size of the PGA partition, thereby reducing the chance of temporary table Spaces being used to improve the performance of database sorting, grouping summary, and other operations.