ORACLE Architecture

							        Oracle Database Architecture


 Overview of oracle Database Architecture

 Memory Structure

 Process Structure
Overview of Oracle Architecture

PMON     SMON       RECO      D000          S000      P000     * Total SGA Size :
                                                                    1700 Mbyte
                                                               * Fixed Size :
                                                   Redo Log            70 Kbyte
                           SGA                      Buffer
  Shared SQL Area     Database Buffer Cache
                                                               * Variable Size :
                                                                     490 MByte


                                                                     STORAGE
  4,000,000 KByte     1,200,000 KByte          2,100 KByte


                      DBW0            CKPT            LGWR
  Server
                             Data File
                               Raw Device                                ARCH
       USER
                                                              Archive Log Mode(50M)
    Memory Structure : Shared Pool

          Shared Pool                      Shared Pool Contents
                                          - Text of the SQL or PL/SQL statement
Library Cache         Dictionary
                      Cache               - Parsed form of the SQL or PL/SQL statement
                                          - Execution plan for the SQL or PL/SQL
    Shared
                                            statements
   SQL Area
                                          - Data dictionary cache containing rows of data
                                            dictionary information
                     Control Structures
                     for example:            Library Cache
PL/SQL Procedures                         - shared SQL area
                     Character Set
and Package          Conversion           - private SQL area
                     Memory
                     Network Security     - PL/SQL procedures and package
                     Attributes
Control Structures                        - control structures : lock and library cache handles
for examples;        and so on ..
                                             Dictionary Cache
Locks
Library                                   - names of all tables and views in the database
Cache handles         Reusable
and so on ...         Runtime             - names and datatypes of columns in database tables
                      Memory              - privileges of all Oracle users
                                             SHARED_POOL_SIZE
    Memory Structure :Database Buffer Cache

 Database Buffer Cache holds copies of data blocks read from disk
 All users concurrently connected to the system share access to the buffer cache
 Dirty List
 LRU List
 Size = DB_BLOCK_SIZE * DB_BLOCK_BUFFERS




                SGA                  Database Buffer Cache
               Shared Pool

                   Shared SQL Area
    Memory Structure: Database Buffer Cache

 Purpose: minimize physical IO
 Buffer cache uses LRU algorithm, blocks least recently used are removed from
  the cache
 Dirty block must be written to data file before it can be flushed
 Buffer cache is split into three parts:
     – KEEP POOL – should be used for small objects that should be always
       available
     – RECYCLE_POOL – should be used for large objects that are rarely
       accessed
    – DEFAULT_POOL – should be used for remaining objects
 Single block read puts block in the middle of the LRU list
 Multi block read (usually full scan) puts block at the end of the LRU list
    Memory Structure: Redo Log Buffer

   Circular buffer containing information about changes made to the database
   Redo Entries are used during Database Recovery
   LGWR writes contents of Redo Log Buffer to Online Redo Log
   LOG_BUFFER




                                                           change vector #1
         redo record
                                                           change vector #1

                                                           change vector #1
        Oracle Processes

       SNPn       SMON          PMON            RECO              LCK0


       Pnnn
                                          SGA
                    Database Buffer Cache               Redo Log
                                                         Buffer
                                                                                    Offline
                                                                                    Storage
                                                                                    Device
               Dedicated
        Snnn     Server
                Process
                                DBWR              LGWR                   ARCH

Dnnn
                       User
                      Process            CKPT

                                                                                Control
                                                                                 Files
Users                                                  Redo Log
                                 Data                    Files
                                 Files
Background Process

   DBWR (Database Writer)
    - write all dirty buffers to datafiles
    - Use a LRU algorithm to keep most recently used blocks in memory
    - Defers write for I/O optimization
         dirty list reaches a threshold length
         A process scans a specified number of buffers in the LRU without finding free buffer
         A time-out occurs
         DBWR checkpoint occurs

   LGWR (Log Writer)
    - writes redo log entries to disk
         Commit occurs
         The redo log buffers pool becomes one-third full
         DBWR completes cleaning the buffer blocks at a checkpoint
         LGWR time-out
    - A commit confirmation is not issued until the transaction has been recorded in the redo
     log file
Cont’d

   PMON (Process Monitor)
    - Cleans up abnormally terminated connection
    - Rolls back uncommited transactions
    - Releases locks held by a terminated process
    - Frees SGA resources allocated to the failed processes
    - Database maintenance


   SMON (System Monitor)
    - Performs automatic instance recovery
    - Reclaims space used by temporary segments no longer in use
    - Merges contiguous area of free space in the datafile
Continued

   CKPT (Check Point)
    - is enabled by setting the parameter CHECKPOINT_PROCESS=TRUE
    - If enabled, take over LGWR’s task of updating files at a checkpoint
    - Updates header of datafiles and control files at the end of checkpoint
    - More frequent checkpoint reduce recovery time from instance failure
    - CKPT improve the performance of database with many database files
   ARCH (Archiver)
    - Copies redo log files to tape or disk for media failure
    - Operates only when a log switch occurs
    - Is optional and is only needed when in ARCHIVELOG mode
    - May write to a tape drive or to a disk
   LCKn (Lock), Dnnn (Dispatcher), Snnn (Server),
    RECO (Recover), Pnnn(Parallel), SNPn(Job Queue),
    QMNn(Queue Monitor),
Server/User Process

   User Processes
    - A user process is used when a user runs an application program
    - Runs the tool/application and is considered the client
    - Passes SQL to the server process and receives the results


   Server Processes
    - A server process must place the data in the database buffer cache
    - Parse and execute SQL statements
    - Read data blocks from disk into the shred database buffers of the SGA
    - Return the results of SQL statements to the user process
        Parse : check syntax, security access, object resolution, optimization
        Execute : applies the parse tree to the data, perform a physical read and
          change
        Fetch : Passes data to the user (only SELECT)
Memory Initialization Parameters (10g)

 Memory initialization parameters
   – SGA_TARGET – total size of Oracle SGA memory (can be changed at
     runtime), when set Oracle is auto-tuning memory
   – SGA_MAX_SIZE – maximum size of SGA_TARGET
   – DB_CACHE_SIZE –size of database buffer cache
   – SHARED_POOL_SIZE – size of shared pool (library cache, dictionary
     cache, control structures)
   – LARGE_POOL_SIZE – size of the large pool (optional, used for large
     memory allocations)
   – JAVA_POOL_SIZE – size of the java pool (used by server side JVM)
   – DB_KEEP_CACHE_SIZE – size of the keep buffer cache (subset of
     DB_CACHE_SIZE)
   – DB_RECYCLE_CACHE_SIZE – size of the recycle buffer cache
     (subset of DB_CACHE_SIZE)
Memory Initialization Parameters (10g)

 Automatic    memory management
   – Oracle manages memory automatically when SGA_TARGET is set
   – The following components are sized automatically by Oracle:
       – Default buffer cache
       – Shared pool
       – Large pool
       – Java pool
   – The following components are not sized automatically:
       – Log buffer
       – Other caches (keep and recycle)
Memory Initialization Parameters (10g)

 Automatic     memory management – example:
      – SGA_TARGET = 1G
      – SHARED_POOL_SIZE = 256M
   Total SGA size – 1GB
   Minimum size of Shared Pool – 256MB (can be more)
   Remaining components – sized automatically by Oracle
Memory Initialization Parameters (10g)

   Setting SGA_TARGET to 0 disables automatic memory
    management
   Total SGA size is unaffected
   Individual component parameters are set to current values:
      – SGA_TARGET = 1G
      – SHARED_POOL_SIZE = 256M
   Set SGA_TARGET = 0
      – SGA_TARGET = 0
      – SHARED_POOL_SIZE = 384M
      – DB_CACHE_SIZE = 512M
      – LARGE_POOL_SIZE = 64M
      – JAVA_POOL_SIZE = 64M
Memory Initialization Parameters (10g)

 Automatic      memory management:
   – Computes memory statistics in background
   – Uses workload information
   – Memory is moved to where is most needed
   – If server parameter file (spfile) is used:
        – Component sizes are saved across shutdowns
        – Saved values are used to initialize component sizes at startup
        – Avoids having to learn optimal values