Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.
System Global Area
Program Global Area
SYSTEM GLOBAL AREA
It is known as shared memory space.
All sessions connected to the database use the SGA common area.
Server and background processes do not reside within the SGA area but allocate memory space for it.
Each database has its own SGA field. This area is allocated by the Oracle database through physical memory on the server and is not allowed to be used by any other application/process until the database is closed.
Oracle Instance= SGA + Background Processes
The size of the SGA area is seen in the figure below.
1. While the database is opening.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1023413288 bytes Database Buffers 620756992 bytes Redo Buffers 7094272 bytes Database mounted. Database opened. SQL> |
2. By running the following command in the open database;
1 2 3 4 5 6 7 | SQL> show sga; Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 956304424 bytes Database Buffers 687865856 bytes Redo Buffers 7094272 bytes |
3. With the following SQL statement;
1 2 3 4 5 6 7 8 | SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 2253784 Variable Size 956304424 Database Buffers 687865856 Redo Buffers 7094272 |
Here;
Fixed Size: This is the area where information such as general information and database status information needed by background processes are kept.
Variable Size: It is the sum of Shared Pool + Large Pool + Streams Pool + Java Pool + Free SGA Memory Areas.
The SGA area and the components that make up this area can be determined by database administrators and can also be automatically managed by Oracle with 11g.
In this context, there are 3 memory management options.
1. Automatic Memory Management
a. Memory Management is left to Oracle.
b. The maximum size that the instance can take is specified in the parameter file.
c. Parameters: memory_target, memory_max_target
d. Oracle automatically manages SGA and PGA fields according to this size.
2. Automatic Shared Memory Management
a. The total size that SGA and PGA can take is specified in the parameter file.
b. Parameters: sga_target, sga_max_target, pga_aggregate_target
3. Manual Memory Management
a. The size of all SGA components and PGA sizes are specified individually in the parameter file.
• All components of SGA can be learned by querying the v$sgastat view.
1 | SQL> select * from v$sgastat; |
PROGRAM GLOBAL AREA
PGA is non-shared memory space.
When a server process is started, PGA memory space is allocated from the server’s physical memory. This memory area is used until the server process terminates. The memory is released back when the process is finished.
PGA has some important components.
Private SQL Area
It holds the information of the parsed SQL sentence.
When a server process executes a SQL statement or PL/SQL code, it uses a private SQL field to store bind variable values, query execution status information, and query workspaces.
Private SQL Area and Shared SQL Area are different structures. The private SQL field of one or more sessions accesses a single Shared SQL field.
For example, let’s assume that the customers table is queried with the same query in 5 different sessions. The SQL statement executed in all 5 sessions will have a single execution plan.
SQL Working Area
It is an area used for intensive memory operations. Memory-intensive operations such as using the sort field with operations such as ORDER BY and hash joins using the hash field to create the hash table can be given as examples.
Session Memory
This is the area where session-specific variables are kept.
