As described in the first article in this Oracle DBA 101 series (which you can read here), an Oracle instance consists of the background processes and the Oracle memory structures used when the database is running.
In this article, we’ll go into more detail about the memory structures.
The Oracle Memory Structures
There are two basic memory structures associated with an instance:
- The System Global Area (or SGA for short)
- The Program Global Area (or PGA for short)
System Global Area
The SGA stores data and control information for the instance and is shared by all server and background processes.
Program Global Area
The PGA stores data and control information for a single server or background process. Each server process and background process has its own PGA.
The System Global Area
The SGA includes the following data structures:
The Shared Pool
The shared pool is essentially a buffer for SQL statements – it stores SQL statements and has the following components:
The Library Cache
The library cache stores the current SQL execution plan, and also holds stored procedure and trigger code.
The Dictionary Cache
The data dictionary is a collection of database tables and views containing metadata about the database and its users. The Oracle database server accesses the data dictionary a lot during the parsing of SQL statements
The Database Buffer Cache
The database buffer cache hold copies of data blocks read from disk. The cache is shared between all users currently connected to the database. The buffer cache has a default buffer pool in which all data blocks are stored unless the KEEP buffer pool and/or the RECYCLE buffer pool is configured. If either or both of these pools are configured, then the default pool is used to data blocks from any objects not explicitly assigned to one of those two pools.
The KEEP Buffer Pool
The KEEP buffer pool is a part of the database buffer cache that can be configured to permanently keep frequently accessed data blocks in the buffer cache.
The RECYCLE Buffer Pool
The RECYCLE buffer pool is a part of the database buffer that can be configured for rarely accessed data blocks, to prevent large objects using unnecessary space in the cache.
The nK Buffer Cache
The default buffer pool holds data blocks of the default database block size. It is possible to have data blocks that aren’t of the default size and the nK buffer caches are used to hold these. n can be 2, 4, 8, 16 or 32 as long as it is not the same as the default block size.
The Redo Log Buffer
The redo log buffer holds information about changes made to the database – it holds enough information to recreate any inserts, updates and delete statements as well as any changes to database objects, e.g. alter, create or drop operations. It is primarily used in database recovery.
The Large Pool
The large pool is an optional component of the SGA. It is used for large memory operations when using a shared server configuration and also by disk I/O server processes, as well as backup and restore operations.
The Java Pool
The java pool is an area of memory in the SGA that is used for all session-specific Java code and data within the Java virtual machine (JVM). How exactly it is used depends on the database configuration and will be covered in a later article.
The Streams Pool
The streams pool is used exclusively by Oracle Streams and stores buffered queue messages. It provides memory for Oracle Streams capture processes and apply processes. If it’s not specifically configured, the size of the Streams pool starts at zero and grows dynamically as needed when Oracle Streams is used.
The Program Global Area
One Program Global Area (or PGA) is allocated by Oracle for each server process. It is used to store logon and other session information, as well as to process SQL statements. The collection of all the individual PGAs is known as the Instance PGA. There are basically two areas in each PGA:
The session memory, as it’s name suggests, is used to hold information about a session, e.g. logon information. In a shared server configuration, the session memory is not private but is shared between sessions.
Private SQL Area
Every session that executes a SQL statement has a private SQL area that holds information such as bind variables for the SQL statements, information about the state of execution of the SQL statements, and SQL statement execution work areas.
There are two basic Oracle memory structures – the SGA and the PGA. Each of these in turn consists of various elements that we’ve covered briefly. Understanding these is a necessary foundation for being a good DBA. In future articles we’ll go into more depth about all of these elements that make up the Oracle memory structures.