Monday , September 25 2017



USER PROCESS : It is the process which places request from client side and will be created when user starts any application.
SERVER PROCESS : It is the process which will do work on behalf of user process on server side.
  1. It is the memory area allocated to server process to perform execution of the SQL statement & to store session information.
  1. The size of memory allocated will be defined using PGA_AGGREGATE_TARGET
  2. Before 9i, PGA is configured using
    4. HASH_AREA_SIZE etc
  1. Sorting will takes place in PGA if the data is small in size. This is called as in-memory sort.
  1. If the data size is larger than sort are size of PGA, Oracle will use both PGA and TEMP table space which needs no.of I/O’s  and automatically database performance will get degraded.


ORACLE INSTANCE : It is a way through which users will access / modify data in the database. It is a combination of memory structures and background processes.
SHARED GLOBAL AREA (SGA) : It is the memory area which contains several memory caches helpful in reading and writing data.
  1. Shared pool contains following components
    1. Library cache – it contains shared SQL & PL/SQL statements.
    2. Data dictionary cache – it contains dictionary information in the form of rows, hence  also called as row cache.
  2. Size of shared pool is defined using SHARED_POOL_SIZE.
  1. It is the memory area where a copy of the data is placed in LRU list.
  2. The status of block in DBC will be any of the following status
    1. UNUSED – block which is never used
    2. FREE – block which is used already but currently it is free
    3. PINNED – block currently in use
    4. DIRTY – block which got modified
  1. DBC contains LRU list and WRITE list which helps in splitting modified blocks with other blocks.
  2. Size of DBC is defined using DB_CACHE_SIZE or DB_BLOCK_BUFFERS.
LOG BUFFER CACHE : It is the memory area where a copy of redo entries are maintained and size is defined by LOG_BUFFER.
Note: LBC should be allotted with smallest size than any other memory component in SGA.
  1. Large pool will be used efficiently at the time of RMAN backup.
  1. Large pool can dedicate some of its memory to shared pool and gets back whenever shared pool is observing less free space.
  1. Size is defined using LARGE_POOL_SIZE.
JAVA POOL : It is memory area used to run java executables (like JDBC driver) and size is defined using JAVA_POOL_SIZE.
  1. It is the memory area used when replicating a database using oracle streams.
  2. This parameter is introduced in 10g and can be defined using STREAM_POOL_SIZE.
  1. If stream pool is not defined and streams are used, then 10% of memory from shared pool will be used. This may affect the database performance.
SMONn : It is the background process responsible for following actions
  1. Instance recovery – this will be done in following phases
    1. Roll forward – compares the SCN between redo log files and data files header and will make sure committed data is written to data files.
    2. Opens the database for user access.
    3. Rollbacks uncommitted transactions with the help of undo table space.
  1. It will coalesce the table spaces which are defined as automatic segment space management.
  1. It will release the temporary segments occupied by the transactions when they are completed (a more detailed post available @ )
The below diagram’s will explain SMONn instance recovery in detail
  1. We know that LGWR wiill write redo entries into redo log files. But if we have more and more redo entries generated (for huge transactions), redo log file size increases and even terabytes of storage is not sufficient.
  1. To overcome this Oracle designed its architecture so that LGWR will write into 2 or more redo log files in a cyclic order (shown in the below diagram)
  1. When doing this, certain events will trigger out which are listed as below.


LGWR moving from one redo log file to another is called LOG SWITCH. At the time of log switch, following actions will take place
  • Checkpoint event will occur – this tells that committed data should be made permanent to data files. (Eg: Its just like automatic saving of email when composing in gmail)
  • CKPT process will update the latest SCN to data file header and control files by taking the info from redo log files.
  • DBWRn will write the corresponding dirty blocks from write list to data files.
  • ARCHn process will generate archives (copy of online redo log files) only if database is in archive log mode.
Note: Checkpoint event not only occurs at log switch. It can occur at repeated interval and this is decided by a parameter LOG_CHECKPOINT_INTERVAL (till 8i) and FAST_START_MTTR_TARGET (from 9i)
Read More:
Upgrading to Oracle Database 10G to 11G
Creating a Physical Standby Database – Data Gaurd
Partitioned Tables and Indexes



Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *