Oracle Instance = SGA (System Global Area) + Background process
Oracle’s memory structure consists of two memory areas known as:
• System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an
Oracle Instance
• Program Global Area (PGA): Allocated when the server process is started
SGA
- SGA can be resized dynamically
- 2 parameters are used for SGA sizing:
- SGA_MAX_SIZE
- SGA_TARGET
- allocated and tracked in granules. Granule size is-
- 4 MB if estimated SGA size is < 128 MB
- 16 MB otherwise
- consists of several memory structure
- Shared pool
- Database buffer cache
- Redo log buffer
- Large Pool
- Java Pool
Shared Pool (Library cache+Data Dictionary Cache)
- used to store the most recently executed SQL statements and the most recently used data definitions.
- consists of two key performance-related memory structures:
- Library cache
- Data dictionary cache
- Sized by parameter SHARED_POOL_SIZE (ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;)
- Library Cache
- stores information about the most recently used SQL and PL/SQL statements.
- Data Dictionary Cache
- 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.
Database Buffer Cache
- The database buffer cache stores copies of data blocks that have been retrieved from the data files.
- It enables great performance gains when you obtain and update data.
- DB_BLOCK_SIZE determines the primary block size.
- Consists of independent sub-caches:
- DB_CACHE_SIZE: default buffer cache, it always exists and cannot be zero
- DB_KEEP_CACHE_SIZE: used to retain blocks in memory that are likely to be reused.
- DB_RECYCLE_CACHE_SIZE: used to eliminate blocks from memory that have little change of being reused.
Redo Log Buffer Cache
- The redo log buffer cache records all changes made to the database data blocks.
- Its primary purpose is recovery.
- Size is defined by LOG_BUFFER.
Large Pool
- It relieves the burden placed on the shared pool.
- This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore
operations.
- Sized by LARGE_POOL_SIZE (ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;)
Java Pool
- The Java pool services the parsing requirements for Java commands.
- Required if installing and using Java.
- It is stored much the same way as PL/SQL in database tables.
- It is sized by the JAVA_POOL_SIZE parameter.
PGA (Program Global Area or Process Global Area)
- a memory region that contains data and control information for a single server process or a single background process.
- PGA is allocated when a process is created and deallocated when the process is terminated.
- In contrast to the SGA, which is shared by several processes, the PGA is an area that is used
by only one process.
- In a dedicated server configuration, the PGA includes these components:
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 stage in the processing of SQL statements that are currently used by the session
Stack space: Contains other session variables
User process:
- Started at the time a database user requests connection to the Oracle server
- It does not interact directly with the Oracle server.
Server process:
- Connects to the Oracle Instance and is started when a user establishes a session.
- directly interacts with the Oracle server.
- It fulfills calls generated and returns results.
- Can be dedicated or shared server.
Background process:
- Available when an Oracle instance is started
- The relationship between the physical and memory structures is maintained and enforced by
Oracle’s background processes.
- Mandatory background processes:
DBWn
PMON
CKPT
LGWR
SMON
RECO
- Optional background processes
ARCn, LMON, Snnn, QMNn, LMDn, CJQ0, Pnnn, LCKn, Dnnn
Database Writer Processes (DBWn)
DBWn writes when:
• Checkpoint
• Dirty buffers threshold reached
• No free buffers
• Timeout
• RAC ping request
• Tablespace offline
• Tablespace read only
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP
Log Writer (LGWR)
LGWR writes:
• At commit
• When one-third full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes
System Monitor (SMON)
Responsibilities:
• 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
Process Monitor (PMON)
Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarts dead dispatchers
Checkpoint (CKPT)
Responsible for:
• Signalling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database
No comments:
Post a Comment