Do not allow temporary table space to affect Oracle database performance

When sorting, grouping, and indexing are done in an Oracle database, a lot of temporary data is generated. If you have a table of employee information, the database is the time to set up records to save. If a user queries by using the ORDER by sort statement to specify the sort by employee number, all the records that are created after sorting are temporary data. What do Oracle databases do with these temporary data?

Typically, Oracle databases first store these temporary data in the PGA (Program Global Area) of memory. In this program global area there is a place called the sort area, which is designed to hold these temporary data generated by the sort operation. But the size of the partition is limited. When the size of the partition is not large enough to accommodate the sorted records, the database system stores the temporary data in the temporary tablespace. This is the origin of the temporary table space. It looks as if the temporary table space is a temp and won't have much impact on the database. In fact, we are misunderstanding this temporary table space. When a user makes a database operation, sorting, grouping, indexing, and so on, the jobs are unavoidable, which produces a lot of temporary data. This basically requires a temporary tablespace for each database. If the temporary table space is not set properly, it will have a great negative impact on the performance of the database. For this reason, the administrator can not be complacent when maintaining this temporary table space. Avoid the performance of the database due to improper setting of temporary table spaces. Specifically, the main need to pay attention to the following aspects of the content.

To create a user, remember to create a temporary tablespace for the user.

It is a good idea to specify temporary tablespace space for users when creating users. You can use the statement default temporary table space statement to set the default temporary tablespace for the database. However, this is not mandatory in Oracle databases. But I strongly recommend doing so. Because if you do not specify a default temporary tablespace for the user, the database system will be "smart" to create a temporary segment using system table space Systems when the user needs to use the temporary table space because of the sort operation. As we all know, this is a system table space. Because of the data that is associated with the system running in this tablespace, it is generally recommended that the user's data not be stored in this tablespace. So what would be the downside if the user's temporary tablespaces were prevented from being within the system tablespace?

Because the data in the temporary table space is temporary. This database system requires frequent allocation and release of temporary segments. These frequent operations result in a large amount of storage fragmentation in the system table space. When these storage fragments are relatively long, it affects the efficiency of the system reading the hard disk, thus affecting the performance of the database. Second, the size of the system table space is often limited. The temporary segment is also plugged in, which takes up the size of the system table space.

For this database administrator, it is important to note that when a temporary tablespace is not specified for the user, a temporary segment is still required for the user to sort and so on. The database system then puts the temporary segments into the system tablespace. This can negatively affect the performance of the database. So I suggest that readers and database administrators, when creating users at the same time to specify a default table space for the user to reduce the temporary segment of the system table space occupied.

Second, reasonable set up PGA, reduce the chance of temporary table space use.

When a sort operation produces temporary data, the database is not immediately stored in a temporary tablespace. Typically, these temporary data are stored in the PGA program Global area of memory first. The database system enables temporary segments in the temporary tablespace to hold the data only if the program global area cannot hold all of the data. But it is well known that the operating system reads data from memory thousands of times times more than it reads from the hard disk. The ideal scenario for this is that the program's global area is large enough to hold all of the temporary data. At this point, the database system will never use temporary tablespace. This can improve the performance of the database.

But this is only an ideal. The size of this PGA program area is often limited due to a wide range of memory size constraints. So while doing some large sort operations, this temporary table space is still unavoidable. Now the database administrator can do is to properly set the PGA program global area size, minimize the chance of temporary table space use. In practice, the database administrator can set initialization parameter sort_area_size parameters as needed. This parameter mainly controls the size of the sorting area within this PGA program Global zone. Usually, if this database system is mainly used to query and need a lot of sorting, grouping, indexing and other operations, then you can adjust this parameter to enlarge the size of the PGA partition. Conversely, if the system is primarily for update operations, or if other applications are deployed on the database server, the PGA partition will not be able to consume too much memory to prevent adverse effects on other applications. Therefore, the database officials can not across, need to adjust according to the actual situation. If necessary, the system memory can be increased to increase the size of the PGA partition, thus reducing the use of temporary table space to improve the database sorting, grouping and other operations performance.

In summary, if the temporary segment is used frequently, the performance of the database can be reduced due to the difference in performance between memory and hard disk. Therefore, in peacetime work, the database administrator also need to monitor the use of temporary table space, to determine whether the need to take measures to reduce the use of temporary table space to improve the query performance of the database. In order to achieve this goal, the author suggests that the database administrator can view 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). In addition, through dynamic performance view V$sort_usage can also query user session information using sorted segments. This provides data support for database administrators to optimize database performance. For this sort of paragraph, I would like to explain one point. For a sorted segment, all the SQL statements for 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 needs to be used. This sort segment is not freed after the sort is completed, and the sorted segment is released only after the process closes. For this reason, the above two views should be analyzed to get the information that the database administrator wants.

Keep enough hard disk space for the temporary table space.

The data files for other table spaces are fully allocated and initialized when they are created, i.e. they are allocated storage space when they are created. However, the temporary table space corresponds to a different temporary file. For example, in a Linux operating system, temporary tablespaces are not allocated and initialized when temporary table space is created. In other words, no storage space is allocated for temporary files. Only temporary data occurs when temporary files are needed, and the system allocates a place on the hard disk to hold temporary files. At this point, there may be a problem, that is, when the temporary file system is needed to allocate space, the system partition does not have enough storage space. At this point there will be some unpredictable consequences.

For these temporary files, it is best for the database administrator to reserve enough space for them beforehand. As in a Linux operating system, it can be prevented from being used in a separate partition and not allowed by other applications. In that case, there's no need to worry about temporary files being stored anywhere. In addition, temporary tablespaces are used primarily to store temporary files for sorting. To do this, if you can store this temporary table space in a better performance partition, you can also increase the speed at which the database system reads data in the temp table space. In addition, because the system needs to allocate data frequently in the temporary table space, there are more fragments of the partition where the temporary tablespace resides. At this point, if the temporary table space is stored in a separate partition, the database administrator can defragment the partition separately to improve the performance of the partition. So for whatever reason, it's a good idea to prevent temporary tablespaces from being in a separate partition. Not only can the temporary files have storage space, but also can improve the performance of the database.

The last thing you need to note about temporary table spaces is that this temporary tablespace is shared by default for each user. This means that each user who is connected to the database can use the default temporary tablespace. The database administrator can specify additional temporary tablespace space for it. In general, only a temporary tablespace can be