Server Memory 101

Let's first look at some basics. An Oracle database instance consists of a shared memory area known as the System Global Area(SGA), plus a number of background processes.^[4] Although a large part of the SGA typically consists of buffer pools that cache table data, another part that's of particular importance to PL/SQL performance is the shared pool. The shared pool performs two maincaching functions:

^[4] The background processes do things like manage disk I/O, recover from failed processes and transactions, and monitor database processes.

· It holds metadata from the data dictionary

· It holds parsed representations ofSQL statements and PL/SQL programs

The first time any user session runs a particular PL/SQL program, Oracle puts the executable portion of the program into the part of the shared pool called the library cache. When that session needs to execute the same PL/SQL program, Oracle will re-use the cached copy of it, saving a trip to the relatively slow disks. Oracle can even use this cached version when other sessions need to execute the same program. You may be surprised to learn that Oracle will share bytecode across sessions only under certain conditions. Before discussing the conditions for sharing PL/SQL bytecode, though, I'd like to mention other processes and memory areas important to application developers, including the often-confused PGA, CGA, and UGA.

When a client program such as SQL*Plus initiates a session with the database, Oracle assigns a "shadow" process on the database server machine to service that session.Program Global Area (PGA) is the name for the memory area associated with this process. Programmers have little or no control over the portion of the PGA that holds session information about the process state and the operating system resources that it uses; the variable portion of the PGA contains application data such as the CGA and, in dedicated server mode, the UGA.

Any time a session executes a SQL statement or PL/SQL block, Oracle temporarily allocates memory to execute the statement. This workspace is known as the Call Global Area (CGA), and Oracle always allocates CGA from PGA memory. CGA is used only for the duration of these server calls—that is, at the time the statement or block is actually executing. Oracle frees CGA memory as soon as the call completes.

Another important area in memory is known as the User Global Area (UGA). The UGA holds things like session state data and the private SQL and PL/SQL areas. So, for example, PL/SQL package variables and constants reside in the UGA. One important thing about UGA memory is that unlike the CGA, it persists across calls, and normally won't shrink or free until the session ends.

One potentially confusing aspect of UGA memory is that it can live either in the PGA or in the SGA, depending on whether the session has connected to Oracle in a dedicated server configuration or in a shared server configuration.

Dedicated server

Oracle spawns a dedicated process for the session, and puts the UGA and CGA into the PGA memory that this process allocates from the operating system where the server is running. This configuration is appropriate for heavy loads such as intensive OLTP or batch processing, long-running queries, and database backups.

Shared server (formerly known as "multithreaded server" or MTS)

Oracle assigns multiple sessions to a shared back-end process, putting the UGA into memory allocated from the SGA. In this configuration, the shadow process and fixed portions of the PGA will be shared among multiple client processes rather than be dedicated to each. This introduces some overhead, though, and you probably won't get a real payback from using it unless you have many concurrent sessions (hundreds or more) with a lot of idle or "think" time.

The total size of the PGA can vary quite a bit based on what kind of operations your application requires the server to perform. For example, a SQL DML statement that requires a large sort can consume a lot of CGA memory; a PL/SQL package that populates a large PL/SQL collection in a package-level variable requires large amounts of UGA memory.

Figure 20-8 shows a simplified representation of these two different arrangements.^[5]

^[5] The figure doesn't show the shared server's dispatchers and request/response queues, which are somewhat ancillary to this discussion.