Oracle PL/SQL Programming, 5th Edition

PL/SQL and Database Instance Memory

By economizing on its use of machine resources such as memory and CPU, an Oracle database can support tens of thousands of simultaneous users on a single database. The databases’s memory management techniques have become quite sophisticated over the years, and correspondingly difficult to understand. It’s true that features such as Automatic Shared Memory Management ease the burden a bit for Oracle Database 10g and later, but administrators of busy databases still need a thorough knowledge of memory management, and advanced PL/SQL programmers should also have a good understanding of this topic. It’s also true that the automation features introduced in recent versions ease the burden on DBAs considerably, but PL/SQL developers still risk wasting memory unless they understand how memory is used for cursors and package variables in particular.

PGA, UGA, and CGA

When a client program such as SQL*Plus or SQL Developer interacts with the database, the database assigns a server process to service its calls. Each server process uses a memory area known as the Process Global Area (PGA) for its private data. Data needed only during a single database call is placed in an area of the PGA known as the Call Global Area (CGA). Data that needs to be retained between a session’s database calls, like package variables and private SQL areas, is placed in the User Global Area (UGA).

The location of the UGA in memory depends on whether the session has connected to the ...