[Transfer] Oracle performance tuning - optimizing sort operations

2011-01-14  来源:本站原创  分类:Database  人气:101 

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)
D, Distinct
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) ';
---- ---- ----
18227333829 0.00066584
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;
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 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

  • [Transfer] Oracle performance tuning - optimizing sort operations 2011-01-14

    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

  • Oracle performance tuning - optimizing sort operations 2010-07-10

    Performance of Oracle 10g, is the most talked-about new automatic workload repository (AWR) Automatic Database Diagnostic characteristics and by monitoring (ADDM) provides proactive performance monitoring. When establishing a session with Oracle, it

  • Oracle performance tuning nine Detailed basic method (transfer) 2011-05-12

    Oracle performance tuning basic methodology included several steps, including: 1) setting up a reasonable goal of Oracle performance optimization. 2) Measure and record the current Oracle performance. 3) determine the current performance bottlenecks

  • Oracle performance tuning - Oracle performance tuning principles (a) 2011-04-01

    Oracle performance tuning principles <br /> everything has its source, to solve the problem, they have to start from the source, affect the performance of the source of much ORACLE, including the following areas: database hardware configuration: CPU

  • Oracle performance tuning of the three swords - ASH, AWR, ADDM 2011-08-29

    ASH (Active Session History) ASH to V $ SESSION, based on one sample per second, recording active sessions waiting for the event. Inactive sessions are not sampled by the introduction of a new background process MMNL to complete. ASH buffers of a min

  • Oracle performance tuning and optimization 2010-10-13

    Oracle database performance analysis and evaluation of the main database throughput, response time of two indicators database users. Database users can be divided into the system response time and service time and waiting time for two users, namely:

  • Transfer: ORACLE SQL TUNING 2011-05-10

    Reprinted ORACLE SQL TUNING One. Optimizer mode ORACLE optimizer There are 3 kinds: a. RULE (rule-based) b. COST (based on cost) c. CHOOSE (optional) In order to use cost-based optimizer (CBO, Cost-Based Optimizer), you must be regularly updated stat

  • [Transfer] Hibernate performance tuning 2010-10-21

    Many people think that Hibernate naturally low efficiency and, indeed, in the general case, the need to perform the conversion for the SQL statements Hibernate efficiency than direct JDBC access, however, after after a good performance optimization,

  • Oracle performance tuning study notes (d) - Library Cache Statistics 2011-09-16

    Library Cache Statistics 1. When you change the objectives of the shared pool of soft parsing SQL statements. reload: reload object is invalid or changes in use need to reload. to reduce the reload value. INVLIDATIONS: in v $ librarycache, the number

  • Oracle performance tuning study notes (b) - ORACLE performance tuning often view the analysis 2011-09-10

    Recommended Books :oracle database 10g performance tuning tips& Techniques you performance tools dba_tables: dba_tab_columns: dba_clusters: dba_indexes: dba_ind_columns: index_stats: index_histogram: dba_tab_histograms: Execution statistics to use :d

  • Oracle performance tuning nine basic methods explain (rpm) 2011-05-12

    Oracle performance optimization basic methodology included several steps, including: 1) setting up a reasonable goal of Oracle performance optimization. 2) Measure and record the current Oracle performance. 3) determine the current performance bottle

  • Oracle performance tuning study notes (b) - ---- Oracle SQL TRACE and TKPROF to use 2011-08-30

    Oracle SQL TRACE And using TKPROF After the start sql trace information collected includes 1. Resolution Executive . Returns the number of data 2.cpu And the time the command 3. Physical read and logical read the number of 4. The number of records pr

  • oracle performance tuning tips 2010-07-12

    Many at the right time to do Oracle DBA of us, when the application of management staff is now applied to our notice a very slow, the database is very slow at the right time to the database when we do a few examples of the Select also found the same

  • Oracle performance tuning study notes --- the role of tuning, the standard level 2011-09-15

    1. The role of application designer application developer database administrator system administrator Application level: Database layer SQL statements: Memory, database structure, SQL statements, the operating system level: IO Swap parameters 2 diffe

  • Oracle performance tuning study notes (b) - diagnostic and tuning tools 2011-09-16

    Background process trace file: alert log file: View questions 1 describes the memory errors and block corruption errors (2) Monitoring data manipulation 3 view non-default initialization parameters to view storage dump show parameter dump; backgroup_

  • Oracle performance tuning study notes (b) - diagnostic and tuning tools SQLTRACE and TKPROF 2011-09-16

    1 view system users dump show parameter dump; background_dump_dest: Store alertlog and the background process trace. user_dump_dest: storing the user's trace file. max_dump_file_size: trace file size. alter system set max_dump_file_size = 500; alter

  • Oracle performance tuning study notes (c) - knowledge of database configuration and IO 2011-09-16

    Oracle IO architecture Oracle 11g supported file systems: 1 file system (NAS, SAN, normal file system) 2 bare equipment 3.ASM Oracle processes and file CKPT, LGWR, ARCn, SERVER both read and write control file. Performance guide 1 to maintain a relat

  • Oracle performance tuning study notes (E) - buffer Cache tuning A 2011-09-16

    buffer Cache tuning buffer cache hit ratio is not required to share pool hit rate so high. BD_BLOCK_SIZE: SGA in two queues: LRU lists: monitoring the buffer cache to use based on access frequency and duration of the order. Head for the most recent d

  • Oracle performance tuning study notes (E) - buffer Cache tuning B 2011-09-16

    Dynamic view of diagnostic tools v $ buffer_pool_statistics v $ buffer_pool v $ db_cache_advice v $ sysstat v $ sesstat v $ system_event v $ session_wait v $ bh v $ cache Statpack OEM's tuning to reduce the use of performance indicator information in

  • Oracle performance tuning study notes (n) - the effective use of Oracle storage structures 2011-09-16

    Effective use of Oracle storage structures table types: heap table, cluster table, IOT table, partition table; Select the appropriate physical structure of the impact factor 1 query or DML statement. 2 table size. 3-row, row group, block size. 4 the