Performance on Oracle 10g, the most talked about is the new automatic workload repository (AWR) Automatic Database Diagnostic features and monitoring by (ADDM) provides a forward-looking performance monitoring.
When creating a session with Oracle, it will divide the memory in the server used to sort out a specific area, thus providing for the session sort space. However, this sort of space is limited, if the number of records than this sort of space, then the disk needs to be sorted. But we all know, sort of the implementation of the disk faster than the implementation of slow memory sorting 1400 times. Moreover, the disk sort consumes resources in the temporary table space, and may affect other ongoing SQL sort, because Oracle must be a temporary table space allocation of buffer pool data blocks. Also, excessive disk sorting will lead to free buffer waits, and to perform other tasks of the data blocks out from the buffer pool page. For database administrators, in memory sort to sort is always more popular than the disk. Therefore, the disk sort is the culprit of Oracle database performance.
Optimization in the database, we should find ways to reduce database disk sorting. To this end, I have the following suggestions.
A reasonable set Sort_area_size parameters.
Although Oracle10G database automatically after the memory management. However, performance requirements in some sort of high or relatively high frequency in the database, there are still a number of memory allocation is necessary to adjust the parameters. The most important parameter is the Sort_area_size.
Oracle database links for all Oracle sessions allocated Sort_area_size this parameter. Therefore, the database has a large number of users, if the increase in the value of this parameter, will significantly reduce the chance of disk sorting, but the database will have to pay this price, it is easy to cause memory overload. However, if the value of this parameter is set too low, then, would lead to excessive disk sorting.
Therefore, this parameter is not bigger is better. Because this parameter if you set too large, then it brings the performance benefits but will be reduced. Because a limited number of queries to improve the speed, it may waste a lot of memory. This is undoubtedly our database administrators do not want to see.
In practice, we often need to be a balance between the two. Set a reasonable argument, as far as possible to sort the database to reduce disk chance, but also can not make the server memory overload.
I have a suggestion for this. Database administrators should periodically increase the value of this parameter, and use the Statspack tool for regular monitoring of memory and disk sort sort data. Adjustment in the first when the best time for each hour inquiry. With these data, we can get a reasonable parameter values, in an equilibrium between the two this.
Prior period adjustment is completed, at a later stage still needs to be monitored. As late as the change of enterprise applications, this parameter still needs to be adjusted according to actual situation, to improve database performance.
Second, try to reduce unnecessary sorting.
In some cases, though not directly by a database administrator statement, Order By and other sort of database records, but the Oracle database server will still sort the query results. Because these statements need to work, you must first sort the data. Therefore, they often come with hidden sorting.
We are in the database maintenance or when the foreground application designed to try to reduce this unnecessary sort. Distinct keywords such as its role is to remove the duplicate records. However, to achieve this aim, then the database must first sort the records, and then be able to remove the duplicate entries. Therefore, in the design, try to avoid the use of Distinct keywords. In fact, the author at work, often in that case, some records do not exist in fact, duplicate records, but the program developers to protect the accuracy of the data to the SQL statement joined the Distinct keyword, resulting in the non- the necessary order.
In addition, in other cases, can lead to unnecessary sort. Such as the sort merge join, can lead to unnecessary sort. Therefore, whenever using the sort merge join, you will perform the sort key value is connected. Therefore, in database and application design time, to try to avoid the sort merge join. In fact, in many cases, the nested loop join but to make better choices. Because the nested loop join, it is more effective and not cause unnecessary sorting, and better than the full table scan to.
Secondly, sometimes missing the index will lead to some sort of do not want to.
Therefore, database administrators work in peacetime, to try to reduce these unnecessary sort, so that precious memory resources to more important tasks to apply.
Third, the use of Statspack tool to monitor the ordering activity.
Statspack is a tool to improve Oracle database performance of a good supporting tool. Because it can help us to collect a lot of useful information. Therefore, database administrators can also use our Statspack tool sort of database activity monitoring.
For an experienced database administrator, the sort of memory, and disk scheduling needs to maintain the necessary sorting is necessary. Because we can not control the behavior of users; and user behavior will be adjusted. Users in the adjustment process, there may well be additional disk sorting. Of course, there may be less likely to disk sort. However, under normal circumstances, with the increase in the user transaction data, the disk sort is still likely to climb in theory, the. But actually to increase, but the speed of this rise is not as fast as it theoretically. This is mainly to see how to manage a database administrator.
According to the author's understanding of the operation of business users, there is often a cyclical change, such as annual or monthly changes periodically. Database administrators should develop a good habit of regular monthly use of Statspack tool to monitor the database. In particular, sort the database to monitor the situation.
Statspack tool also has an extra feature is the automatic monitoring and alerting capabilities. In other words, this tool allows disk sorting Statspack more than a pre-set threshold, automatically sends a warning to the database administrator, such as through e-mail sent to the administrator and so on. I found that by monitoring end of each month and the beginning of time, the number of disk sorting will greatly increase. This is mainly because in the end, the user will have the month of transaction data for statistical purposes. So when the end of the beginning of time, as more transactions, therefore, will have more disk sorting occurs. In this case, the database administrator it is necessary to adjust the relevant parameters. But this adjustment is a temporary adjustment, until the cycle is over, still must be transferred back parameters. Only in this way, the overall performance of the database will be safeguarded. That will not overload the memory performance and reduce database; also sort the disk will not cause additional burden to the database.
So, although the SQL statement execution order is a very small part, but its impact on database performance than the larger, but also very significant. Unfortunately, the sort is often overlooked in SQL to adjust the place. In the Oracle database, sorting is transparent to the user. That is, sort of users rarely be limited, the user can according to their needs to sort the data freely. However, users do not know what kind of operation will reduce the performance of the database. So how to reduce the unreasonable operation of the user to generate additional sorting, and even disk sorting, which is usually the database administrator must take into account the work of a problem. Through the above three methods, the database administrator may be able to find some ideas to solve the problem. I believe that through the above method, you can sort the greatest degree of reduction of the incidence of the disk, not to order an impact on database performance disk culprit.
Optimization of sorting operations
1, the concept of server sort_area_size first memory area specified in the order size, if the required space for more than sort_area_size, sorting will be carried out in the temporary table space. In dedicated server mode, the sort of space in the PGA, in shared server mode, the sort of space in the UGA. If you do not create large pool, UGA in the shared pool, if you create a large pool, UGA to in the large pool, while the PGA is not sga, it is corresponding with the separate existence of each process.
PGA: program global area, for a single process (server process or background process) to preserve data and control information in the memory. PGA-one correspondence with the process, and can only be read from the corresponding process, PGA created the database in the user login when the session establishment.
Automatic management of the relevant sort of space of two parameters:
Pga_aggregate_target: 10M-4000G, assigned to the oracle instance is equal to all the memory, after subtracting the size of SGA.
Workarea_size_policy: auto / manual, only Pga_aggregate_target has been defined to be set to auto.
These two parameters will replace all the * _area_size parameters.
As much as possible to avoid the sort; sort in memory as much as possible; distribution of suitable temporary space to reduce the space allocation calls.
2, the operation need to be sorted:
A, create the index;
B, parallel index maintenance involves the insertion
C, order by or group by (as much as possible sort of index field)
E, union / intersect / minus
F, sort-merge join
G, analyze command (only possible use of estamate not compute)
3, diagnosis, and measures
Select * from v $ sysstat where name like '% sort%';
Sort (disk): Io required to sort the number of temporary table space
Sort (memory): entirely in memory in order to complete the number of
Sort (rows): Total number of rows to be sorted
Sort (disk) / Sort (memory) <5%, if more than 5%, increasing the value of sort_area_size.
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
---- ---- ----
4, monitoring the use of temporary table space and configuration
Select tablespace_name, current_users, total_extents, used_extents, extent_hits, max_used_blocks, max_sort_blocks FROM v $ sort_segment;
CURRENT_USERS Number of active users
TOTAL_EXTENTS Total number of extents
USED_EXTENTS Extents currently allocated to sorts
EXTENT_HITS Number of times an unused extent was found in the pool
MAX_USED_BLOCKS Maximum number of used blocks
MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort
Temporary table space configuration:
A, initial / next set to an integer multiple of sort_area_size, allowing an extra segment of the header block as
B, pctincrease = 0
C, based on a different sort need to create multiple temporary tablespaces
D, the temporary table space for files across multiple disks
Transfer from: http://boyun.sh.cn/bio/?p=851
Another web site:
1. Using the parallel argument, 8 CPU, you can use the parallel 6, takes up to 6 CPU, no problem under normal circumstances (provided that no other applications accounted for more than 2 CPU resources)
2. nologging, definitely should be used, will substantially increase the speed. (Reduce the amount of redo log)
3. PGA, the maximum ordinary auto pga only 100M, apparently did not achieve the best performance, you should use manual pga
alter session set workarea_size_policy = manual;
alter session set hash_area_size = 100000; - hash_area_size automatically default sort_area_size * 2 to transfer, leading to sort_area_size no more than 1G, manually set up no problem.
alter session set sort_area_size = 2000000000; - enough available memory in the system, the maximum can go to the 2G
4. Set a little higher db_file_multiblock_read_count also help improve the I / O performance. Large enough to ensure the emergence of db_cache_size avoid free_buffer_wait
5. By v $ session_longops to monitor the progress of the