Memory Architecture   «Prev 

Sort Areas and the multi-threaded Server

Sort Areas: PGA or SGA

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 [1]. 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.
As of 2010, I see very few Oracle shops (well under 6%) who continue to use the MTS [unless they have Oracle Java connections, which require MTS Shared Servers], and it is obsolete for today's 64-bit servers with lots of inexpensive RAM resources.

Quest Software's Guy Harrison has this warning about using the MTS:

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] 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.