Oracle Tuning Sorts to Reduce I/O

A sort area in memory is used to sort records before they are written out to disk. Increasing the size of this memory by increasing the value of the initialization parameters SORT_AREA_SIZE or PGA_AGGREGATE_TARGERT, lets you sort more efficiently. SORT_AREA_SIZE is a dynamic parameter that can be set at either the session level using an ALTER SESSION command or system wide using an ALTER SYSTEM command. PGA_AGGREGATE_TARGET is an initialization parameter that defines the total size of all working areas, including the sort area, for all sessions. This parameter is available in Oracle 9i and 10g.

Most online sorting queries request sorts of only a handful of records at a time. Unless the size of your sort area is unusually small, the whole operation can usually be done in memory. However, in large batch jobs the size of the sort area becomes an issue. The problem is that most sites tend to have a mixture of OLTP and batch processing, so you are faced with the dilemma of tuning for both. The PGA_AGGREGATE_TARGET method of allocating work areas helps with this dilemma. Some sessions will get small sort areas if they do not need much space, and that leaves more memory for those sessions that need large sort areas.

If the data being sorted does not fit in memory, Oracle must sort it in small runs. As each run is completed, Oracle stores the data in temporary segments on disk. After all of the runs have completed, Oracle merges the data to produce the sorted data. This is less efficient than doing the entire sort in memory.

In general, try to allocate as much space in memory as possible for SORT_AREA_SIZE (assuming that your entire sort can fit into memory). When defined, this parameter will cause memory to be allocated from the Process Global Area (PGA) unless the database is using Oracle’s Shared Server architecture (called Multi Threaded Sever (MTS) prior to Oracle9i). If the Oracle Shared Server architecture is being used, then the sort memory will be allocated from the large pool so long as the large pool is allocated. The sort memory will be allocated alternatively from the User global area (UGA) in the shared_pool if the large pool is not available.

Consider using a two megabyte SORT_AREA_SIZE when your sorted data exceeds 100 megabytes in size. Because SORT_AREA_SIZE is allocated per user, increasing this parameter can exhaust memory very quickly if a large number of users are logged on. You also need to make sure that the temporary segments to which the sort operation will write its output (if it runs out of memory) are large enough, with appropriately sized INITIAL and NEXT extents. Again, the PGA_AGGREGATE_TARGET method of allocating working areas helps with this problem.

To find out whether sorting is affecting performance in your system, monitor the sorting disk activity in your system and then adjust accordingly. One good way to do this is to define a separate tablespace for temporary tables. By watching the I/O rate on the temporary tablespaces, you can detect how frequently the sort process failed to perform the entire sort in memory. You can monitor the V$SYSSTAT table to observe memory and disk activity.

Below is an example of querying the V$SYSSTAT table:

SQL> select name, value from v$sysstat where name like 'sort%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                         1658
sorts (disk)                                                              2
sorts (rows)                                                        2232346

The "sorts(memory)" statistic shows the total number of sort operations that could be performed completely within the sort buffer in memory, without using the temporary tablespace segments. The "sorts(disk)" statistic shows the number of sort operations that could not be performed in memory. (Note that this number does not represent the total number of times a temporary table extent was written by a sort process.) Out of a total of 1293 sorts, only two required disk usage.

Dedicated Temporary Tablespace Considerations

Using dedicated temporary tablespaces (created via the create temporary tablespace command) can provide some performance improvements with regards to sorting. This is because the temporary sort segments, once used, will not be removed but will instead be reused by the next process that needs to do a sort to disk. This eliminates the overhead of having to remove and recreate sort segments. This also means that your temporary tablespace may appear to contain segments in it most of the time. This is perfectly natural.

Also, consider in Oracle 9i and later, assigning a default temporary tablespace to the database. This has the benefit of allowing you to assign users to a temporary tablespace other than SYSTEM automatically when the user is created. Sorts occurring in the SYSTEM tablespace can badly fragment the tablespace and can cause other performance related problems.

Using the PGA Aggregate Target Advisory

If you are using automatic PGA memory management mode, then Oracle will use the PGA_AGGREGATE_TARGET parameter to control the amount of PGA memory allocated to user SQL work areas. Oracle attempts to maximize the performance of memory intensive SQL operations (typically sort intensive operations), and reducing the overall work required by those operations as much as possible.

When you first setup your Oracle database, it’s difficult to know what value to set the parameter PGA_AGGEGATE_TARGET to. Oracle makes the following initial recommendations for setting PGA_AGGREGATE_TARGET:

• For OLTP: PGA_AGGREGATE_TARGET = (<total_mem> * 80%) * 20%
• For DSS: PGA_AGGREGATE_TARGET = (<total_mem> * 80%) * 50%

where <total_mem> is the total amount of physical memory available on the system.

This falsely assumes that yours is the only database instance on your system. Rather than just allocating 20% of 80% of your overall memory, we would suggest that for a system with moderate activity and moderate concurrent usage (say concurrent usage of 20 users) that a setting of 20M for the PGA_AGGREGATE_TARGET is a good starting place. Once you have configured the initial setting, either run a load test on the database that represents actual volumes, or monitor the initial production runs of that system. Once you have sufficiently run your loads, check the Oracle PGA advisory statistics for more information on how to alter the PGA_AGGREGATE_TARGET setting. In addition to setting the PGA_AGGREGATE_TARGET parameter, you will also need to set the WORKAREA_SIZE_POLICY parameter to AUTO.

Determining if PGA_AGGREGATE_TARGET is Set Correctly

To determine if PGA_AGGREGATE_TARGET is set correctly, you can use the V$PGASTAT view. An example of the output of a query against V$PGASTAT might look like this:

NAME                                                 VALUE UNIT
---------------------------------------------------------- -------
aggregate PGA target parameter                   524288000 bytes
aggregate PGA auto target                        163435776 bytes
global memory bound                                  25600 bytes
total PGA inuse                                    9353216 bytes
total PGA allocated                               73516032 bytes
maximum PGA allocated                            698371072 bytes
total PGA used for auto workareas                        0 bytes
maximum PGA used for auto workareas              560744448 bytes
total PGA used for manual workareas                      0 bytes
maximum PGA used for manual workareas                    0 bytes
over allocation count                                    0
total bytes processed                           3.0072E+10 bytes
total extra bytes read/written                  2.1517E+10 bytes
cache hit percentage                                 65.97 percent

In particular the following statistics are of interest. The aggregate PGA target parameter displays the current setting of the PGA_AGGREGATE_TARGET parameter. The aggregate PGA auto target value gives the amount of PGA memory that Oracle can use for work areas. This is a derived value and low values indicate that there is little available memory for sort areas. If this number is low, you should consider setting the PGA_AGGREGATE_TARGET parameter higher.

The global memory bound statistics is another work area that is dynamically set by Oracle, and increases or decreases as workload changes. If this value falls below 1MB then you should increase the PGA_AGGREGATE_TARGET parameter.

If the total PGA allocated parameter exceeds PGA_AGGREGATE_TARGET frequently, then this means that Oracle has to actually allocate more memory to the private work areas than was expected. If this is the case then PGA_AGGREGATE_TARGET should be increased. Further if the over allocation count is large, then this indicates that the PGA_AGGREGATE_TARGET is too small.

You can determine the amount of memory allocated and used, as well as the maximum amount of memory allocated to a given process via columns of the V$PROCESS view. You can query the PGA_USED_MEM, PGA_ALLOC_MEM and PGA_MAX_MEM columns of V$PROCESS to determine the PGA memory usages for each process. The PGA_USED_MEM column tells you how much memory the process is actually using. The PGA_ALLOC_MEM column indicates how much memory has been allocated to the process (some of which may not be in use). Finally the PGA_MAX_MEMORY indicates the maximum amount of memory that has been allocated to that session during it’s lifetime.

Automatic Statistics and Recommendations

In order to know whether to adjust the PGA_AGGREGATE_TARGET to a larger or smaller value, the database needs statistics on the performance of the database. Oracle 10g introduced the Automatic Workload Repository (AWR) to captures snapshots of system performance. By default, these snapshots are captured every hour and retained for a period of 7 days. These default settings can be changed with the dbms_workload_repository supplied package. The AWR stores the statistics in the Oracle 10g's new SYSAUX tablespace.

Now that the AWR is automatically capturing statistics, they can be used to determine how to tune the buffer cache size. The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis. The ADDM makes recommendations for correcting performance problems, including buffer cache sizing recommendations. These recommendations can be viewed in the Oracle Enterprise Manager or in the DBA_ADVISOR_RECOMMENDATIONS view. ADDM is turned on when the statistics_level parameter is set to TYPICAL or ALL.

Oracle 10g includes the new Memory Advisor to help tune the size of the Shared Pool, the Buffer Cache, and the PGA working areas. The PGA Advisor is one of three portions of the Memory Advisor. This advisor can be consulted to obtain the optimal size of the PGA working areas. The PGA Advisor needs AWR statistics to make appropriate decisions. Like all of the Memory Advisors, they are accessed through Oracle's Enterprise Manager (OEM).

The screenshot above shows OEM and the current PGA memory settings. The Aggregate PGA Target can be changed here. Pressing the Advice button brings up the PGA Advisor. The picture below is an example of advice obtained in OEM from the PGA Advisor.

The graph can be used to obtain an optimal setting for the PGA_AGGREGATE_TARGET initialization parameter. The basic idea of this graph is to find the smallest amount of memory that would obtain a 100% cache hit percentage. Drilling down further, OEM lets us see how well the PGA is performing. This same information could be obtained the V$PGASTAT view, but it is provided here in graphical format.

This graph shows how many optimal, one pass, and multipass executions were performed for various work area sizes.

SQL Considerations To Reduce Sorts

You may not always realize that your program statements invoke a sort. Sorting is performed by the following statements:

• CREATE INDEX
• GROUP BY
• ORDER BY
• INTERSECT
• MINUS
• UNION
• DISTINCT
• Unindexed table joins
• Some correlated subqueries

Your goal should be to always reduce or eliminate sorts completely. Again, this starts at the application layer. Here is a list of suggested things you can look for in application code, which might be causing unneeded sort operations. While these different operations may well be needed, it’s a good idea to review your code and make sure.

1. Avoid using the DISTINCT clause unless necessary.
2. Use the UNION ALL clause in place of the UNION clause unless duplicates need to be eliminated.
3. Try to use HASH JOINS instead of SORT MERGE JOINS. The use of hints will cause the optimizer to choose this join.
4. Use appropriate index hints to avoid sorts.
5. The cost based optimizer will try to avoid a sort operation when the FIRST_ROWS hint is used.
6. Make sure that your SQL query is taking advantage of the best available indexing options.
7. Review operational SQL code for unneeded sort operations, such as order by clauses.

Follow these suggestions if your monitoring shows that you have a sorting problem:

1. Check if the sort is really necessary. Has an index been inadvertently overlooked? Can a SQL statement be structured more efficiently?
    
2. Increase the value of the SORT_AREA_SIZE parameter. Because this increase applies to all user processes, this is likely to consume a lot of memory. Make sure you don't increase the value of SORT_AREA_SIZE to the point where you have little free memory. The maximum allowable value is system-dependent. If you are using the PGA_AGGREGATE_TARGET method of work area allocation, then check the V$PGASTAT view to ensure that this parameter is set correctly.
    
3. Verify your temporary tablespace extents are large enough. Make sure that you specify large enough table extents (in the INITIAL and NEXT parameters on the CREATE statement) to allow SORT_AREA_SIZE of memory to be written to disk without having to throw multiple extents. Make your temporary segments a minimum of SORT_AREA_SIZE + 1 block. It is a very good idea to use Locally Managed Tablespaces with the UNIFORM extent allocation method for your temporary tablespaces. This way, you ensure that the extent sizes are the same for every sort segment.
    
4. Let users who require larger sorts use a temporary tablespace with larger INITIAL and NEXT default tablespace storage parameters. This is a less likely, but possible, alternative which will help reduce the degree of dynamic extension. For example, during daily online transaction processing hours, set your default settings to:

    

   ALTER TABLESPACE temp_tspace DEFAULT STORAGE
   (INITIAL 260K  NEXT 260K  PCTINCREASE 0);

   For overnight processing, you might set the default storage to:

   ALTER TABLESPACE temp_tspace DEFAULT STORAGE
   (INITIAL 5M  NEXT 5M  PCTINCREASE 0);

   If you are using Locally Managed Tablespaces, then you will not be able to modify the default storage clause as shown above. Instead, create two Locally Managed temporary tablespaces, one with smaller extents and the other with larger extents. Instead of altering the default storage clause information above, you would modify the default temporary tablespace for the entire database (in Oracle 9i and 10g). For overnight processing, you would issue a command Similar to the following:

   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE big_temp;

5. Set your INITIAL and NEXT extent sizes to one block plus a multiple of the sort area size. This will achieve minor improvements in response times. Assuming that you have a DB_BLOCK_SIZE of 4K and a SORT_AREA_SIZE of 64K, you may consider any of the following sizes or a higher size, depending on your requirements:

    

   4K  +  (1 * 64K)  =   68K
   4K  +  (2 * 64K)  =  130K
   4K  +  (3*  64K)  =  196K
   4K  +  (4 * 64K)  =  260K

   If you are using Locally Managed Tablespaces, set the uniform extent size to one of the above values.

6. Make sure to use the SORT_AREA_RETAINED_SIZE. Oracle will restore the sort area available to user processes to the size specified in this parameter if it believes that the sort area data will not be referenced in the near future. This will save memory. If memory is tight, we highly recommend that you take advantage of this feature by setting your SORT_AREA_RETAINED_SIZE to half the SORT_AREA_SIZE. For example, you might set:

    

   SORT_AREA_SIZE           = 131072   (128K)
   SORT_AREA_RETAINED_SIZE  = 65536     (64K)

7. Use the TEMPORARY type when creating tablespaces. This tablespace type is tuned especially for sorting and will boost your performance. We recommend that you use it as your users' TEMPORARY tablespace.