The relationship between temporary tablespace and 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.