May 22, 2024
PGA and UGA Wrap-Up- Memory Structures-1
So far, we have looked at two memory structures: the PGA and the UGA. You should understand now that the PGA is private to a process. It is the set of variables that an Oracle dedicated or shared server needs to have independent of a session.
The PGA is a “heap” of memory in which other structures may be allocated. The UGA is also a heap of memory in which various session-specific structures may be defined.
The UGA is allocated from the PGA when you use a dedicated server to connect to Oracle and from the SGA under a shared server connection.
This implies that when using a shared server, you must size your SGA’s large pool to have enough space to cater to every possible user that will ever connect to your database concurrently.
So, the SGA of a database supporting shared server connections is generally much larger than the SGA for a similarly configured dedicated server mode-only database. We’ll cover the SGA in more detail next.
The System Global Area
Every Oracle instance has one big memory structure referred to as the System Global Area (SGA). This is a large, shared memory structure that every Oracle process will access at one point or another. It varies in size from dozens of megabytes on small test systems, to a few gigabytes on medium-to-large systems, up to hundreds of gigabytes for really big systems.
On UNIX/Linux, the SGA is a physical entity you can “see” from the OS command line. It is physically implemented as a shared memory segment—a stand-alone piece of memory to which processes may attach. It is possible to have an SGA on a system without having any Oracle processes; the memory stands alone. It should be noted, however, that if you have an SGA without any Oracle processes, this is an indication that the database crashed in some fashion. It is an unusual situation, but it can happen. This is what an SGA “looks like” on Oracle Linux:
0x00000000 32768 oracle 600 9138176 116
0x00000000 32769 oracle 600 1560281088 58
0x00000000 32770 oracle 600 7639040 58
0x322379e0 32771 oracle 600 12288 58
One SGA is represented here, and the report shows the OS account that owns the SGA (oracle for all of these in this example) and the size of the SGA. On Windows, you really can’t see the SGA as a distinct entity the way you can in UNIX/Linux. Because on the Windows platform Oracle executes as a single process with a single address space, the SGA is allocated as private memory to the oracle.exe process. If you use the Windows Task Manager or some other performance tool, you can see how much memory oracle.exe has allocated, but you can’t see the SGA vs. any other piece of allocated memory.
Note Unless you have my parameter settings and you are running my exact same version of Oracle on my exact same OS, you will almost certainly see different numbers than I do. The SGA sizing is very version/OS/parameter dependent.
Within Oracle itself, you can see the SGA regardless of the platform, using another magic V$ view called V$SGASTAT. It might look as follows:
SQL> compute sum of bytes on pool
SQL> break on pool skip 1
SQL> select pool, name, bytes from v$sgastat order by pool, name;
The SGA is broken up into various pools. Here are the major ones you’ll see:
•\ Shared pool: The shared pool contains shared cursors, stored procedures, state objects, dictionary caches, and many dozens of other bits of data. If a user executes a SQL statement, then Oracle will use the shared pool.
•\ Database buffer cache (block buffers): Data blocks read from disk as users query and modify data. Contains the most recently used data blocks.
•\ Fixed SGA: Contains internal housekeeping information regarding the state of the instance and database.
•\ Redo log buffer: A circular buffer that contains information regarding changes to the database. These changes are written to the online redo logs on disk. This information is used for database recovery.
•\ Java pool: The Java pool is a fixed amount of memory allocated for the JVM running in the database. The Java pool may be resized online while the database is up and running.
•\ Large pool: Optional memory area used by shared server connections for session memory, by parallel execution features for message buffers, and by RMAN backup for disk I/O buffers. This pool is resizable online.
•\ Streams pool: This is a pool of memory used by data sharing tools such as Oracle GoldenGate, Oracle Streams, and Data Pump. This pool is resizable online. If the Streams pool is not configured and you use the Streams functionality, Oracle will use up to ten percent of the shared pool for streams memory.
•\ Flashback buffer: Optional memory area used when Flashback Database is enabled. The recovery write process will copy modified blocks from the buffer cache to the flashback buffer, which are written to Flashback Database logs on disk.
•\ Shared I/O pool: Used for I/O operations on SecureFile Large Objects. This area is used for SecureFile deduplication, encryption, and compression.
•\ In-memory area: Optional memory area that allows tables and partitions to be stored in a columnar format. Useful for analytic operations that operate on a few columns returning many rows (as opposed to an OLTP application that returns a few rows with many columns).
•\ Memoptimize pool: Optional memory component that optimizes key- based queries.
•\ Optional Database Smart Flash Cache: Optional memory extension to the database buffer cache for Linux and Solaris systems. Resides on solid-state storage devices that use flash memory.
A typical SGA might look as shown in Figure 4-1. The optional memory components are indicated by the dashed outlines.
Figure 4-1. Typical SGA
More Details