The shared pool is used for objects that can be shared globally,
e,g, as reusable SQL
execution plans; PL/SQL packages, procedures, and functions; and cursor information,
SGA has two parts:
a) Library cache:The library cache is managed by a least recently used (LRU) algorithm.
Parsed and compiled program units and procedures (functions,
packages, and triggers) are stored in this area.
b)Dictionary Cache:Consists of two structures:
i) Shared SQL area
ii) Shared PL/SQL area
The data dictionary cache is a collection of the most
recently used definitions in the database.
• It includes information about database files,
tables, indexes, columns, users, privileges, and
other database objects.
• During the parse phase, the server process looks
at the data dictionary for information to resolve
object names and validate access.
• Caching the data dictionary information into
memory improves response time on queries.
• Size is determined by the shared pool sizing.
2.Database Buffer Cache
The database buffer cache stores copies of data
blocks that have been retrieved from the data files.
The large pool is an optional area of memory in the
SGA configured only in a shared server environment.
Program Global Area (PGA)
The PGA is memory reserved for each user process
that connects to an Oracle database.
PGA is allocated when a process is created and deallocated when the process is terminated.
In contrast to the SGA
the PGA is an area that is used
by only one process. In a dedicated server configuration, the PGA includes these
• Sort area: Used for any sorts that may be required to process the SQL statement
• Session information: Includes user privileges and performance statistics for the session
• Cursor state: Indicates the stage in the processing of the SQL statements that are
currently used by the session
• Stack space: Contains other session variables
UPI:The user process does not interact
directly with the Oracle server. Rather it generates calls through the user program interface
(UPI), which creates a session and starts a server process.
OPI:The server process communicates with the Oracle server using the Oracle Program Interface
DBWR: Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files.
LOG Writer:LGWR performs sequential writes from the redo log buffer cache to the redo log file.
• Instance recovery:– Rolls forward changes in the redo logs
– Opens the database for user access
– Rolls back uncommitted transactions
• Coalesces free space ever 3 sec
• Deallocates temporary segments
Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarts dead dispatchers