Functions of SGA(System Global Area) Components

  Be familiar with the functions of SGA Components of Oracle Database.I am discussing these components one by one. 

•   Shared Pool

  

  SGA stores data in memory for quick
   access and processing

 This shared tool is the area in SGA holding data and controlling and managing information which can be shared between the different users of the database. It has three major areas: i) Library Cache, ii) Data Dictionary Cache  and iii)buffers for parallel execution messages.

i) Library Cache

          It includes the most recently executed SQL statements, their parse
          code and execution plans. The functions of the library cache are to allow sharing of parsed code and execution plans during SQL statement processing. Sharing this information increases the speed of the query processing of similar statements in the database.

ii) Data Dictionary

          Cache holds data dictionary information in memory. It contains the usernames, segment information, profile data, table space information and the sequence numbers. The dictionary cache also holds descriptive information and meta data about the schema objects. Oracle uses this meta data while parsing SQL cursors.

The dictionary cache has also another name, that’s called row cache. It is because it contains the data in rows instead of buffers. It also holds the entire blocks of data. This helps us to reduce the physical access to the data dictionary tables from the system table space. It also enables fine-grained locking of individual data dictionary rows.  

    iii)Database Buffer Cache

It holds data blocks having been read into memory during the execution of Select and Data Manipulation Language Statements (DML). Data is modified or changed in the database buffer cache. A blog that is modified as a dirty block which must be written back to the data files.

      Redo log buffer

            The redo log buffer holds a record of all the changes made in the database. The changes that  are written to the redo log buffer are redo entries.

• Large Pool

  It is an optional memory structure which can be configured to enhance I/O functions and please memory requirements of an Oracle Shared Server Configuration and for parallel Query Processing. This large pool is not a part of the shared pool. Using the large pool inleu of the shared pool decreases the fragmentation of the shared pool. Oracle doesn’t attempt to age memory out of the large pool.The shared pool is useful to assign large memory allocations.

• Java Tool

  This tool can be configures while installing and using Java code. The tool is used for servicing the parsing requirements of Java based applications.

Now you will have to know what is SGA for your clear and better understanding.  SGA means System Global Area. When an oracle instance is started, Oracle Database assigns an area in the memory. That is called SGA. The SGA serves the various purposes. They are given below:

i)                    They maintain the internal data structures which are accessed by many processes and threads concurrently.

ii)                   Caching data blocks read from disk.

iii)                 Buffering redo data before writing it to the online redo log files.

iv)                 Storing SQL execution plans.