Oracle Memory Structure



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