Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

ARCHITECTURE_ORACLE

VIEWS: 36 PAGES: 54

									Basic Oracle Architecture
Technically an Oracle Database is broken into
   two high-level components

  1. INSTANCE
  2. DATABASE
         So Why Differentiate?
Most Oracle installations consist of only a single
 “Instance” and single “Database”…

                      Instance




                       Database
          Oracle RAC is Different
Oracle “Real Application Clusters” allow multiple
  “Instances” to interact with a single
  “Database” to provide high availability…

     Instance 1      Instance 2      Instance 3




                      Database
                  “Instance”?
An Oracle Instance…
  – Is a means to access an Oracle Database
  – Always opens one and only one Database
  – Divided into 2 category
     • memory
     • background process structures
          Picture of an “Instance”
                        Instance
                         SGA
 Shared Pool

    Library          Database Buffer        Redo Log
    Cache                Cache               Buffer
                                                                 Memory
     Data                                                        Structures
  Dictionary            Java Pool          Large Pool
    Cache


                                                                 Back-
PMON          SMON   DBWR           LGWR   CKPT         Others   Ground
                                                                 Processes
                 “Database”??
An Oracle Database…
  – Is a collection of data that is treated as a unit
  – Consists of three file types
            Picture of a “Database”

                          Oracle Database

             Data Files     Control Files   Redo Log Files

Parameter                                                    Archived
   File                                                      Log Files

Password
   File
              Oracle
           Architecture



Overview
          Picture of an “Instance”
                        Instance
                         SGA
 Shared Pool

    Library          Database Buffer        Redo Log
    Cache                Cache               Buffer
                                                                 Memory
     Data                                                        Structures
  Dictionary            Java Pool          Large Pool
    Cache


                                                                 Back-
PMON          SMON   DBWR           LGWR   CKPT         Others   Ground
                                                                 Processes
           Memory Structure
Oracle’s memory structure consists of two
   memory areas known as:
1. System Global Area (SGA): Allocated at
   instance start up, and is a fundamental
   component of an Oracle Instance
2. The SGA is Oracle's structural memory area
   that facilitates the transfer of data and
   information between clients and the Oracle
   database.
                      SGA
•   DATABASE BUFFER
•   REDOLOG FILE
•   JAVA POOL
•   LARGE POOL
                     DATABASE BUFFER
• Default buffer cache –
  This is the default memory      • This memory area is
  cache that stores data blocks     controlled by the
  when they are read from the       db_block_buffers para
  database.
                                    meter in Oracle8i and
• If the DBA does not
                                    below, and
  specifically place objects in
  another data cache (which         db_cache_size in
  will be covered next), then       Oracle9i and above.
  any data requested by
  clients from the database
  will be placed into this
  cache.
 Keep buffer cache
• DBA can assign objects to a      • The
  special cache that will retain     buffer_pool_keep para
  those object’s requested           meter controls the size
  blocks in RAM for as long          of this cache in Oracle8,
  as the database is up.             while the
• The keep cache's main              db_keep_cache_size pa
  function is to hold                rameter handles the
  frequently referenced
  lookup tables that should
                                     cache in Oracle9i and
  always be kept in memory           above. The keep pool is
  for quick access.                  a sub-pool of the default
                                     buffer cache.
Recycle buffer cache
• - Imagine the opposite     • By placing this data
  of the keep cache, and       into the recycle cache, it
  you have the recycle         will neither occupy
  cache. When large table      valuable memory space
  scans occur, the data        nor prevent blocks that
  filling a memory cache       are needed from being
  is unlikely to be needed     placed in a buffer.
  again, and should be       • However, should it be
  quickly discarded from       requested again, the
                               discarded data is quickly
  RAM. .
                               available.
Recycle buffer cache

• The buffer_pool_recycle parameter controls
  the size of this cache in Oracle8 and below,
  while the db_recycle_cache_size parameter
  handles the cache in Oracle9i and above.
 Specific block size caches
• Specific block size caches -
   Beginning in Oracle9i, a
   DBA can create tablespaces    • Oracle9i and above has
   whose blocksize differs         memory settings for 2K, 4K,
   from the overall database       8K, 16K, and 32K
   blocksize.                      caches. The configuration
                                   parameter names are in the
• When data is read into the
                                   pattern of db_nk_cache_size
   SGA from these tablespaces,
                                   .
   their data has to be placed
   into memory regions that
   can accommodate their
   special blocksize.
•
Program Global Area (PGA): Allocated when the server
process is started

• PGA :
• basically the memory area reserved for every
  user process which connects to the underlying
  Oracle DB.
• It is allocated at the time of process creation
• gets de-allocated when the process
  terminates.
              System Global Area
• The SGA consists of several memory structures:
   –   Shared Pool
   –   Database Buffer Cache
   –   Redo Log Buffer
   –   Other structures
• There are two additional memory structures that can
  be configured within the SGA:
   – Large Pool
   – Java Pool
           System Global Area
• The size of the SGA is determined by the
  parameters that set the sizes of the various
  pools; these parameters are dynamic
• The SGA_MAX_SIZE parameter sets the
  maximum size of the SGA (so you can limit it)
  and is not a dynamic parameter



                                        Thanks Ned!
          Picture of an “Instance”
                        Instance
                         SGA
 Shared Pool

    Library          Database Buffer        Redo Log
    Cache                Cache               Buffer
                                                                 Memory
     Data                                                        Structures
  Dictionary            Java Pool          Large Pool
    Cache


                                                                 Back-
PMON          SMON   DBWR           LGWR   CKPT         Others   Ground
                                                                 Processes
                   Shared Pool
• Used to store:
  – Most recently executed SQL statements
  – Most recently used data definitions
• It consists of two key performance-related
  memory structures:
  – Library Cache
  – Data Dictionary Cache
• Sized by the parameter SHARED_POOL_SIZE
Library Cache


• Stores information about the most recently used SQL
  and PL/SQL statements
• Enables the sharing of commonly used statements
• Data Dictionary Cache
• A collection of the most recently used definitions in
  the database
• Includes information about database files, tables,
  indexes, columns, users, privileges, and other
  database objects
          Picture of an “Instance”
                        Instance
                         SGA
 Shared Pool

    Library          Database Buffer        Redo Log
    Cache                Cache               Buffer
                                                                 Memory
     Data                                                        Structures
  Dictionary            Java Pool          Large Pool
    Cache


                                                                 Back-
PMON          SMON   DBWR           LGWR   CKPT         Others   Ground
                                                                 Processes
          Picture of an “Instance”
                        Instance
                         SGA
 Shared Pool

    Library          Database Buffer        Redo Log
    Cache                Cache               Buffer
                                                                 Memory
     Data                                                        Structures
  Dictionary            Java Pool          Large Pool
    Cache


                                                                 Back-
PMON          SMON   DBWR           LGWR   CKPT         Others   Ground
                                                                 Processes
            Redo Log Buffer
• Records all changes made to the database
  data blocks
• Primary purpose is recovery
• Redo entries contain information to
  reconstruct or redo changes
• Size defined by LOG_BUFFER
                    Large Pool
• An optional area of memory in the SGA
• Relieves the burden place on the Shared Pool
• Used for:
   – Session memory for the Shared Server
   – I/O server processes
   – Backup and restore operations for RMAN
• Sized by LARGE_POOL_SIZE
• Can be dynamically resized
                 Java Pool
• Services parsing requirements for Java
  commands
• Required if installing and using Java
• Sized by JAVA_POOL_SIZE parameter
      Oracle “Process” Structure
• Oracle takes advantage of various types of
  Processes:
  – User Process: Started at the time a database user
    requests connection to the Oracle Server
  – Server Process: Connects to the Oracle instance
    and is started when a user establishes a session
  – Background Processes: Started when an Oracle
    instance is started
                  User Process
• A program that requests interaction with the Oracle
  server
• Must first establish a connection
• Does not interact directly with the Oracle server

               User                       Server
              Process                     Process
                          Connection
                          Established
              Server Process
• A program that directly interacts with the
  Oracle server
          Picture of an “Instance”
                        Instance
                         SGA
 Shared Pool

    Library          Database Buffer        Redo Log
    Cache                Cache               Buffer
                                                                 Memory
     Data                                                        Structures
  Dictionary            Java Pool          Large Pool
    Cache


                                                                 Back-
PMON          SMON   DBWR           LGWR   CKPT         Others   Ground
                                                                 Processes
        Background Processes
• Maintains and enforces relationships between
  physical and memory structures
  – Mandatory background processes:
     DBWn      PMON CKPT
     LGWR SMON
  – Optional background processes:
     ARCn       LMDn        QMNn
     CJQ0       LMON        RECO
     Dnnn       LMS         Snnn
     LCKn       Pnnn
                Database Writer (DBWn)
                           Instance

 Shared Pool                 SGA
                                                                              DBWn writes when:
  Shared Pool
                                                                              •   Checkpoint occurs
    Library            Database Buffer                  Redo Log
    Cache                  Cache                         Buffer               •   Dirty buffers reach
     Data                                                                         threshold
  Dictionary               Java Pool                    Large Pool            •   There are no free buffers
    Cache
                                                                              •   Timeout occurs
PMON          SMON    DBWR             LGWR            CKPT          Others
                                                                              •   Tablespace OFFLINE
                                                                              •   Tablespace READ ONLY
                                                                              •   Table DROP or TRUNCATE
                                                                              •   Tablespace BEGIN
                                      Oracle Database
                                                                                  BACKUP
                     Data Files        Control Files     Redo Log Files
                         Log Writer (LGWR)
                                 Instance

 Shared Pool                       SGA
                                                                             LGWR writes:
                                                                             •   At commit
    Library               Database Buffer              Redo Log
    Cache                     Cache                     Buffer               •   When one-third full
     Data                                                                    •   When there is 1 MB of
  Dictionary                     Java Pool             Large Pool                redo
    Cache
                                                                             •   Every three seconds
PMON          SMON       DBWR                LGWR     CKPT          Others
                                                                             •   Before DBWn writes




                Oracle Database
 Data Files      Control Files       Redo Log Files
                System Monitor (SMON)
                                 Instance

 Shared Pool                       SGA
                                                                             Responsibilities:
  Shared Pool
                                                                             •   Instance recovery
    Library               Database Buffer              Redo Log
    Cache                     Cache                     Buffer                    – Rolls forward changes
                                                                                    in online redo log files
     Data
  Dictionary                     Java Pool             Large Pool                 – Opens database for
    Cache                                                                           user access
                                                                                  – Rolls back
PMON          SMON       DBWR                LGWR     CKPT          Others          uncommitted
                                                                                    transactions
                                                                             •   Coalesces free space
                                                                             •   Deallocates temporary
                Oracle Database                                                  segments
 Data Files      Control Files       Redo Log Files
                Process Monitor (PMON)
                                 Instance

 Shared Pool                       SGA
                                                                             Cleans up after failed
  Shared Pool
                                                                                processes by:
    Library               Database Buffer              Redo Log
    Cache                     Cache                     Buffer               •   Rolling back the
     Data
                                                                                 transaction
  Dictionary                     Java Pool             Large Pool            •   Releasing locks
    Cache
                                                                             •   Releasing other
                                                                                 resources
PMON          SMON       DBWR                LGWR     CKPT          Others
                                                                             •   Restarting dead
                                                                                 dispactchers

                Oracle Database
 Data Files      Control Files       Redo Log Files
                     Checkpoint (CKPT)
                        Instance

 Shared Pool              SGA
                                                                          Responsible for:
  Shared Pool
                                                                          •    Signaling DBWn at
    Library          Database Buffer              Redo Log
    Cache                Cache                     Buffer                      checkpoints
     Data                                                                 •    Updating datafile
  Dictionary            Java Pool                Large Pool                    headers with checkpoint
    Cache                                                                      information
                                                                          •    Updating control files
PMON          SMON   DBWR           LGWR        CKPT          Others           with checkpoint
                                                                               information


                                           Oracle Database
                            Data Files      Control Files     Redo Log Files
                       Archiver (ARCn)
• Optional background process
• Automatically archives online redo log files when
  ARCHIVELOG mode is set
• Preserves the record of all changes made to the
  database
                  Oracle Database
     Data Files    Control Files    Redo Log Files          Archived Logs
                                                     ARCn
           Oracle Data Storage
Oracle keeps all system and user data in two
   basic storage containers:
1. Tablespace… a logical entity known only to
   Oracle
2. Data Files… physical files that may (typically)
   be seen from the operating system
                 Tablespaces
• Can belong to only one database at a time
• Consist of one or more data files
• Are further divided into logical units of store
                  Data Files
• Can belong to only one tablespace and one
  database
• Are a repository for schema object data
           Database

              Tablespace

                      Data   Data
                      File   File
         2 Types of Tablespace
1. Tablespaces required by Oracle for normal
   operations
2. Tablespaces that contain data and indexes
   that support your applications
         Required Tablespaces
• System – Contains the Oracle Data Dictionary
• Sysaux - New to 10g; supports historic
  monitoring / tuning
• Temporary – Used for disk-based sorting of
  data (select...from…order by)
• Undo – Used for transaction consistency
  during Insert / Update / Delete statements
         SQL Statements (IUDS)
•   Insert      What happens when
•   Update       someone connects to
•   Delete       the database and issues
                 one of these
•   Select       statements?
  Connect to Oracle – What Happens
              User                         Server
             Process                       Process
                       Connect User/Pwd



• Are “User” definition and privileges in SGA
  “Dictionary Cache”?
  – If not, try to fetch from the “System” tablespace
  – If there, validate Pwd and privileges to login
  – Allow connection if all is right; otherwise decline
    connection
  “Select” – What Happens First?
                           Select *
                           From MyTab
                           Order by 1;



• Is this statement in the “Library Cache”?
  – If statement cached then it has been recently
    been “parsed” and “executed”
  – If not cached then “parse” the statement
  – If cached then skip parsing stage and execute
    statement
     “Select” – Parsing the Statement
                          Select *
                          From MyTab
                          Order by 1;



•   Does “MyTab” exist?
•   Does user have Privs to select from “MyTab”
•   What columns are in “MyTab”
•   What is the first column in “MyTab”
  “Select” – Executing the Statement
                                 Select *
                                 From MyTab
                                 Order by 1;



• Is “MyTab” data in the SGA “Buffer Cache”?
   – If not, fetch data into Buffer Cache
• Sort data in “MyTab” by the first column
   – If can sort records in memory then do so
   – If cannot sort in memory then use “Temporary” tablespace
     as disk-based staging area
• Return records to client process
   “I/U/D” – What Happens First?
                           Insert (a,b,c)
                           Into MyTab;




• Is this statement in the “Library Cache”?
  – If statement cached then it has been recently
    been “parsed” and “executed”
  – If not cached then “parse” the statement
  – If cached then skip parsing stage and execute
    statement
  “I/U/D” – Executing the Statement
                           Insert (a,b,c)
                           Into MyTab;




• Validate values to be inserted
• Execute the statement
  – Keep DB version of the record in “Undo”
    tablespace until Commit or Rollback
  – Record changes in SGA “Redo Log Buffer”
  – Change records in SGA “Buffer Cache”
  – DBWn writes changed records to data file(s) as
    part of buffer cache management
  “I/U/D” – Executing the Statement
                          Insert (a,b,c)
                          Into MyTab;
                          Commit;
                          or
                          Rollback;

• If “Commit” then…
  – LGWn writes “Redo Log Buffer” entries to “Redo
    Logs”
  – Undo entries are invalidated
• If “Rollback” then…
  – Migrate DB version of record in Undo back to
    tablespace/data file
                   Archivelog Mode
• If Instance in “Archivelog” mode Redo Log
  Files are “Archived” to Archive Logs; which
  may be used to recover your database in the
  case of disaster

                  Oracle Database
     Data Files    Control Files    Redo Log Files          Archived Logs
                                                     ARCn
                    Summary
• What we called a “Database” is really an “Instance”
  and a “Database.”
• An Oracle “Instance” is not persistent. It consists of
  the System Global Area (SGA) and a collection of
  processes.
• An Oracle “Database” is persistent. It consists of a
  collection of “Required” and “User” tablespaces
  (with corresponding data files) along with other
  supporting files.
• Nearly every component of the “Instance” and
  “Database” are mobilized to execute SQL statements.

								
To top