What is Sort Area in Oracle?

Sort Areas are the final memory structures that we are going to talk about in this module. A Sort Area is a large block of memory that Oracle uses to sort data when you issue a query with an ORDER BY clause. Under the default, which is a dedicated server configuration of Oracle, users will each have their own Sort Area.

Two key initialization parameters control the size of the Sort Area. These are:

1. SORT_AREA_SIZE: The maximum size of the Sort Area.
   
2. SORT_AREA_RETAINED_SIZE: This is a threshold that specifies the maximum amount of sort memory to retain for future sorts. When a sort is finished, any Sort Area memory in excess of this amount will be released.

The function of the SORT_AREA_RETAINED_SIZE parameter is sometimes a bit difficult to grasp. View the following slide show to see how these parameters interact:

Sort activity can also affect disk I/O. Prior to Oracle Database 10g, youwou ld get the majority of sorts to occur in memory through tuning the SORT_AREA_SIZE parameter in the initialization file. Oracle constantly queries and updates the data dictionary stored in the SYSTEM tablespace, and this information is cached in the shared pool section of the SGA, so sizing your shared pool properly is a key to overall performance. As of Oracle Database 10g, Oracle can automatically and dynamically size the different pools in the SGA.

Each server process uses memory in its PGA for sorting rows before returning them to the user. If the memory allocated for sorting is insufficient to hold all the rows that need to be sorted, the server process sorts the rows in multiple passes called runs. The intermediate runs are written to the temporary tablespace of the user, which reduces sort performance because it involves disk I/O. Sizing the sort area of the PGA was a critical tuning point in Oracle database releases prior to Oracle Database 10g. A sort area that was too small for the typical amount of data requiring sorting would result in temporary tablespace disk I/O and reduced performance. A sort area that was significantly larger than necessary would waste memory.
As of Oracle Database 10g, the database provides automatic sizing for the PGA. By default, this memory management is enabled, and sizing for PGA work areas is based on 20 percent of the SGA memory size. By using automatic sizing for the PGA, you eliminate the need to size individual portions of the PGA, such as SORT_AREA_SIZE.
Oracle Database 11g introduced automatic memory management that spans both the SGA and the PGA. By setting a single MEMORY_TARGET initialization parameter (given that the PGA size can be based on a percentage of the SGA memory size), the PGA and SGA will be automatically set to appropriate initial values. Oracle then tunes memory for optimal SGA and PGA performance on an ongoing basis.

Sorts that can be processed entirely in memory execute faster than those that cannot.If you find performance suffering from a lot of disk-based sorts, one remedy to consider would be to increase the Sort Area size. Remember though, that each user potentially could end up with SORT_AREA_RETAINED_SIZE bytes allocated to them. Be sure to consider the impact of that in terms of how much memory is available on the system.
Space for Sort Areas actually comes from the PGA, and using the Shared Server Sort Areas changes how some of that memory is allocated.
Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility.