Effect of disk sorting on Oracle database performance (1)

When you create a session with Oracle, a dedicated sorting area is located in the server memory to provide the sorting space for the session. However, this sorting space is limited. If the number of records exceeds this sorting space, disk sorting is required. However, we all know that the execution speed of disk sorting is 1400 times slower than that of memory sorting. In addition, disk sorting consumes Resources in the temporary tablespace and may affect other SQL sorting in progress, because Oracle must allocate a buffer pool for data blocks in the temporary tablespace. In addition, excessive disk sorting will lead to idle buffer wait, and the data blocks that execute other tasks will be paged out from the buffer pool. For database administrators, sorting in memory is always more popular than sorting disks. Therefore, disk sorting is the culprit affecting Oracle database performance. When optimizing the database, we should try to reduce the order of the database disk. For this reason, I have the following suggestions.

1. Set the Sort_area_size parameter properly

Although the database after Oracle10G will automatically manage the memory. However, in some databases with high performance requirements or high sorting frequency, it is still necessary to adjust some parameters that affect memory allocation. The most important parameter is Sort_area_size.

Oracle Database allocates Sort_area_size for all connected Oracle sessions. Therefore, for databases with a large number of users, increasing the value of this parameter will significantly reduce the probability of disk sorting, but the database also has to pay this price, memory overload is easily caused. However, if the value of this parameter is set too low, it will lead to excessive disk sorting. Therefore, this parameter is not as big as possible. If this parameter is set too large, the performance gains will be reduced. To increase the speed of several queries, a large amount of memory may be wasted. This is undoubtedly what our Database Administrator does not want to see.

In practice, we often need to balance the two. Set a reasonable parameter to minimize the probability of disk sorting by the database, and not overload the server memory. For this reason, I have a suggestion. The database administrator should increase the value of this parameter at intervals and use the Statspack tool to regularly monitor data in memory sorting and disk sorting. It is best to query the data every hour during initial adjustment. With this data, we can get a reasonable parameter value and achieve a balance between the two.

After the preliminary adjustment is completed, monitoring is still required later. As enterprise applications change in the future, this parameter still needs to be adjusted based on actual conditions to improve database performance.

Ii. minimize unnecessary disk sorting

In some cases, although the database administrator does not sort database records directly By Order By statements, the Oracle database server still sorts the query results. Because these statements need to work, data must be sorted first. Therefore, they often have implicit sorting functions.

We should minimize unnecessary sorting during database maintenance or front-end application design. For example, the Distinct keyword is used to cancel duplicate records. However, to achieve this goal, the database must first sort the records before removing Repeated Records. Therefore, avoid using the Distinct keyword during design. In fact, I often encounter this situation in my work. Some records do not actually have repeated records. However, to ensure data accuracy, the program developer adds the Distinct keyword to the SQL statement, this causes unnecessary sorting.

In other cases, unnecessary sorting may occur. Such as sorting and merging connections, unnecessary sorting may also occur. Therefore, whenever a sort merge connection is used, the key values connected to the sorting are executed. Therefore, when designing databases and applications, try to avoid sorting and merging connections. In fact, nested loop connections make better choices in many cases. Because of this nested loop connection, it is more effective and does not lead to unnecessary sorting and full table scanning.

Second, sometimes missing indexes may lead to sorting that is not desired. Therefore, database administrators should try to reduce unnecessary sorting during their daily work so that valuable memory resources can be handed over to more important tasks to improve Oracle database performance.