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.

• Initialization Parameters:
  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 series of images to see how these parameters interact:

System overhead consists of I/O to the SYSTEM tablespace for the data dictionary, the TEMP tablespace for sorting, and the tablespaces that contain rollback segments for undo information. You should consider the system profile in spreading the system overhead over multiple drives. For example, if the application generates a lot of data changes versus data reads, the I/O to the rollback segments may increase due to higher writes for changes and higher reads for consistent read functionality. 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.

• Sort Activity: 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.
• Memory for sorting within the PGA: 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.

• Understanding Shared Server Architecture
  The Oracle Database shared server architecture increases the scalability of applications and the number of clients that can simultaneously be connected to the database. The shared server architecture also enables existing applications to scale up without making any changes to the application itself.
  When using a shared server^[1] , clients do not communicate directly with a database server process, a database process that handles a client's requests on behalf of a database. Instead, client requests are routed to one or more dispatchers. The dispatchers place the client requests in a common queue. An idle shared server from the shared pool of server processes picks up and processes a request from the queue. This means a small pool of server processes can serve a large number of clients.
  Figure 5-11.1 and 5-11.2 show the basic difference between
  1. the shared server connection model and
  2. the traditional dedicated server connection model.
  In the shared server model, a dispatcher can support multiple client connections concurrently. In the dedicated server model, there is one server process for each client. Each time a connection request is received, a server process is started and dedicated to that connection until completed. This causes a processing delay.

Historical Note: (MTS) Multi-Threaded Server has been deprecated starting with Oracle 9i. The Multi-Threaded Server (MTS) is a legacy server that existed for Oracle 8i and has been replaced by the Oracle Shared Server beginning with Oracle 9i.

• Program Global Area:
  The Program Global Area is an area of memory allocated and private for one process. The configuration of the PGA depends on the connection configuration of the Oracle database: either shared server or dedicated. In a shared server configuration, multiple users share a connection to the database, minimizing memory usage on the server, but potentially affecting response time for user requests. In a shared server environment, the SGA holds the session information for a user instead of the PGA. Shared server environments are ideal for a large number of simultaneous connections to the database with infrequent or short-lived requests. In a dedicated server environment, each user process gets its own connection to the database; the PGA contains the session memory for this configuration. The PGA also includes a sort area. The sort area is used whenever a user request requires a sort, bitmap merge, or hash join operation.
  As of Oracle9i, the PGA_AGGREGATE_TARGET parameter, in conjunction with the WORKAREA_SIZE_POLICY initialization parameter, can ease system administration by allowing the DBA to choose a total size for all work areas and let Oracle manage and allocate the memory between all user processes. As I mentioned earlier in this chapter, the parameter MEMORY_TARGET manages the PGA and SGA memory as a whole to optimize performance.
• Server Process:
  When you run Oracle using the default, dedicated server configuration, a server process is created for each user connection, each server process has a PGA and Sort Areas are allocated from those PGAs. The retained size portion of the Sort Area is allocated from each user's private SQL area^[2]. With the dedicated server option, this exists in the PGA, but using the multi-threaded server option it exists in the SGA. The other portion of the Sort Area, the part above the retained size, is always stored in the PGA of the server process that is performing the sort.
  The reason that the retained size portion of the Sort Area must be in the SGA when the multi-threaded server option is used, is that once allocated on behalf of a user connection, it must remain available for further use by that same user connection. Keeping it in the PGA would tie it to the server process. Since a different server process may eventually process the next SQL statement for that user, the retained portion must be kept in shared memory.

MTS becomes downright dangerous when Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) is in place.
When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request. First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"
Oracle's Tom Kyte notes that the MTS should not be used without a "real reason" and he notes that shared server connections are slower than with dedicated database connections (Oracle's default behavior):

1. "Unless you have a real reason to use MTS, don't."
2. "a shared server connection is by design "slower" than a dedicated server (more stuff goes on, more complex) it is most likely only getting in the way."

[1] shared server: A database configuration that enables multiple client processes to share a small number of server processes.

[2] private SQL area: An area in memory that holds a parsed statement and other information for processing. The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas.