Oracle Performance Tuning-Optimizing sorting operations __oracle

The most talked about Oracle 10g performance is the new automated Workload repository (AWR) feature and proactive performance monitoring through automated database diagnostics monitoring (ADDM). When an Oracle session is established, a specially sorted area is divided into server memory to provide a sort space for the session. However, this sort space is limited after all, if the number of records exceeds this sort space, you need to do disk sorting. However, as we all know, disk sorting is performed 1400 times times slower than the memory sort execution speed. Also, disk sorting consumes resources in temporary tablespaces and may affect other SQL sorts that are in progress, because Oracle must allocate buffer pools for data blocks in temporary tablespace spaces. Also, excessive disk sorting causes idle buffering to wait, and pagination of data blocks that perform other tasks from the buffer pool. For database administrators, sorting in memory is always more popular than disk sorting. So, disk sequencing is the culprit that affects Oracle database performance.

At the time of database optimization, we should try to reduce the disk sorting of the database. To this end, the author has the following suggestions.

　　a reasonable set of sort_area_size parameters.

Although it is said that the ORACLE10G database will automatically manage memory. However, in some databases with higher performance requirements or higher sorting frequency, it is still necessary to adjust some parameters that affect memory allocation. One of the most important parameters is sort_area_size.

The Oracle database assigns sort_area_size this parameter to all linked Oracle sessions. Therefore, for a database with a large number of users, if you increase the value of this parameter, the probability of the disk sorting will be significantly reduced, but the database will pay this price, it can easily lead to memory overload. However, if the value of this parameter is set too low, it can cause too much disk sorting.

Therefore, this parameter is not the bigger the better. Because this parameter if set too large, its performance gains will be reduced. Because to improve the speed of a limited number of queries, a large amount of memory may be wasted. This is certainly not what our database administrator wants to see.

In practical work, we often need to have a balance between the two. Set a reasonable parameter to make the database less likely to have disk sorting and not overload the server memory.

To this end, I have a suggestion. The database administrator should increase the value of this parameter every once in a while and use the Statspack tool to monitor the memory sort and disk-sorted data at timed intervals. It is best to inquire once per hour at the beginning of the adjustment. With these data, we can get a reasonable parameter value, and get a balance between the two.

After the previous adjustment is complete, still need to monitor in the later stage. Because of the later changes in the enterprise application, this parameter still needs to adjust according to the actual situation, in order to improve the performance of the database.

　　second, minimize unnecessary sorting.

In some cases, the Oracle database server still sorts the query results, although the database administrator does not sort the database records directly through statements such as order by. Because these statements need to work, you must sort the data first. So, they tend to have an implicit sort function.

We should try to reduce this unnecessary sorting when we design the database maintenance or foreground application. such as the DISTINCT keyword, its role is to cancel duplicate records. However, to do this, the database must first sort the records before it can eliminate duplicate records. Therefore, in the design time, try to avoid using the DISTINCT keyword. In fact, the author in the work, often encounter this situation, some records do not exist duplicate records, but the program developers in order to ensure the accuracy of data, in the SQL statement added distinct keyword, resulting in unnecessary sorting.

In addition, in some other cases, it can lead to unnecessary sorting. Sorting merge joins can also result in unnecessary sorting. So whenever a sort merge connection is used, the sort connected key value is executed. Therefore, in the database and application design, try to avoid sorting merge joins. In fact, in many cases, nested loops join makes a better choice. Because of this nested loop connection, it is more efficient and does not cause unnecessary sorting and no more than a full table scan.

Second, sometimes missing indexes can cause some sorts that don't want to be sorted.

Therefore, the database administrator in peacetime work, to minimize these unnecessary sorting, so that valuable memory resources to more important tasks to apply.

　　third, the use of statspack tools to monitor sorting activities.

The Statspack tool is a good tool for improving Oracle database performance. Because it can help us gather a lot of useful information. So our database administrator can also use the Statspack tool to monitor the sorting activities in the database.

For an experienced database administrator, it is necessary to maintain the necessary scheduling of memory sorting and disk sorting. Because we can't control the behavior of the user, and the user's behavior will be adjusted. As the user adjusts, it is possible to add additional disk sorting. Of course, it is also possible that the probability of disk sorting will decrease. In general, however, the probability of this sort of disk is theoretically still climbing as the user's transaction data increases. It's actually going up, but it's not as fast as it is theoretically. This is mainly to see how the database administrator to manage.

According to the author's understanding, the operation of enterprise users will often have a cyclical change, such as by year or month of periodic changes. Database administrators should develop a good habit of using the Statspack tool to monitor the database on a regular basis every month. In particular, to monitor the sorting of the database.

An additional feature of the Statspack tool is the automatic monitoring and alerting function. That is, you can have statspack this tool automatically sends a warning to the database administrator, such as sending it to the administrator, and so on, when the number of disks is sorted more than a preset threshold. The author through monitoring found that every month and the beginning of the month, the number of disk sorting will be greatly increased. This is mainly because at the end of the month, users will make statistics on the transaction data for the month. So at the beginning of the month, because of the number of transactions, so there will be more disk sorting occurs. In this case, it is necessary for the database administrator to adjust the relevant parameters. However, this adjustment is a temporary adjustment, until the cycle has passed, still need to transfer the parameters back. Only in this way can the overall performance of the database be guaranteed. It does not degrade database performance because of memory overload, nor does it cause additional burdens to the database because of disk sorting.

So, while sorting is a tiny part of the execution of SQL statements, it has a relatively large impact on database performance and is very significant. Unfortunately, sorting is often overlooked in SQL tuning. In an Oracle database, sorting is transparent to the user. That is to say, the ordering is rarely limited to the user, and users can sort the data randomly according to their needs. However, the user does not know what kind of operation will degrade the performance of the database. So how to reduce the user's unreasonable operation and create additional sorting, even disk sorting, this is a database administrator in peacetime work must be considered a problem. Through the above three methods, perhaps to the database administrator to find some way to solve the problem. It is believed that the above methods can minimize the occurrence of disk sorting, and no longer let disk sorting become the culprit of database performance.

Optimizing sorting Operations

1. Concept
The server first sorts in the memory area of the specified size, and if the required space exceeds sort_area_size, the sort is sort_area_size in the temp table space. In dedicated server mode, the sort space is in the PGA, in the shared server mode, the sort space is in UGA. If the large Pool,uga is not established in the shared pool, if the large Pool,uga is established in the large pool, and the PGA is not in the SGA, it is separate from each process.

Pga:program Global area, a memory region that holds data and control information for a single process (a server process or background process). The PGA corresponds to process one by one and can only be read and written by the corresponding process, and the PGA is established when the user logs on to the database to create the session.

Two parameters for automatic management of the sort space:
pga_aggregate_target:10m-4000g equals all memory allocated to Oracle instance minus the size of the SGA.
Workarea_size_policy:auto/manual, can be set to auto only if pga_aggregate_target is defined.
These two parameters replace all the *_area_size parameters.

Measures:

Avoid sorting as much as possible, sort in memory, and allocate appropriate temporary space to reduce space allocation calls.

2, need to sort the operation:
A, create an index;
B, parallel inserts involving index maintenance
C, order BY or group by (sort the indexed fields as much as possible)
D, Distinct
E, Union/intersect/minus
F, Sort-merge Join
G, analyze command (may only use estamate instead of compute)

3. Diagnosis and measures
Select * from V$sysstat where name like '%sort% ';
Sort (disk): Required number of IO to go to temporary table spaces
Sort (memory): Number of sorted complete in memory
Sort (rows): Total number of rows sorted

Sort (disk)/Sort (memory) <5%, if more than 5%, increase the sort_area_size value. SELECT disk. Value Disk,mem. Value mem, (disk. Value/mem. Value) *100 ratio from V$sysstat disk,v$sysstat mem WHERE mem.name= ' sorts (memory) ' and disk.name= ' sorts (disk) ';

DISK MEM RATIO
———- ———- ———-
182 27333829 0.00066584

4, monitoring the use of temporary table space and its configuration
Select Tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks from V$sort_segment;

Column Description
Current_users Number of active USERS
Total_extents Total number of extents
Used_extents extents currently allocated to sorts
Extent_hits number of times a unused EXTENT is found in the pool
Max_used_blocks Maximum number of USED BLOCKS
Max_sort_blocks Maximum number of BLOCKS used by a individual SORT

Configuration of temporary table spaces:
A, Initial/next is set to an integer multiple of sort_area_size, allowing an extra block as the header of the segment
B, pctincrease=0
C, create multiple temporary tablespaces based on different sort needs
D, spread temporary table space files onto multiple disks

Source: http://oracle.chinaitlab.com/optimize/719755.html