Oracle SGA

SGA

       System Global Area or Shared Global Area

       A system global area is a group of shared memory areas that dedicated to an Oracle database instance.

       It is a large part of memory that all the oracle background process access.

        It is allocated from shared memory(SHMMAX) from Linux OS, which is specified in /etc/sysctl.conf.

SGA Consists of below components.

• DBBC (Database Buffer Cache)
• RLBC (Redo Log Buffer Cache)
• Shared Pool
• Large Pool
• Java Pool
• Streams Pool
• Fixed SGA

Shared Pool :

    Shared pool consists of two components.

• Library Cache
• Dictionary Cache or Row Cache
• Server Result Cache
• Reserverd Pool     

Library cache and Dictionary Cache

    When a SQL statement is executed it first enters library cache of Shared pool. It does syntax checking and semantec checking , then will create a shared SQL area.
    In Shared SQL area it will have the parsed info and the execution plan for that SQL. So it can be reused.
  
    syntax checking : Checks the SQL is syntactically correct. Eg. select * from  emp might have syntax error like selct * from emp;
    semantec checking : Checks that the user have access to all the objects in the SQL. It uses Dictionary Cache/Row Cache to achive this. Dictionary will  read the dictionary information from SYSTEM Tablespace.
  
  
 So as already mentioned , any SQL statement that is executed will have a Shared SQL area. When another user executes a query , first it checks if there  is any Shared SQL Area present , if it finds then it is called soft parsing , if not it will again create Shared SQL Area which is called hard parsing. We can also call as HIT and MISS.

 Result Cache :
   This memory component stores result  of SQL result Cache and PLSQL function result Cache
      Ex: Assume the query 

     SQL> select count(*) from emp;
       count(*)
       -------------------
       1000000000

 takes 1 hr to execute , if result cache is enabled then the result 1000000000 will be stored in result cache. so next time if you execute the same query it will get from the result cache and will take some seconds.
    
 If there is any dml occurs for emp table , then the result cache will invalidate and it will not get used. Again it will take 1 hr to execute the query for first time.

Database buffer cache

       Holds a copy of data blocks read from data files

       The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. 

       Pinned buffers are currently being accessed.

       It contains Multiple buffer pools are Default,keep,recycle,

Redo Log Buffer

       The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries.

 Includes information about transactions that have not yet been written to online redo log files.

Java pool 

 P arsing of Java code and scripts

Installation tasks related to Java applications with Oracle 11g

Java stored procedure code parsing

Streams pool 

Oracle Streams can be used for:

Replication

Message Queuing

Loading data into a Data Warehouse

Event Notification

Data Protection

Large pool

       Parallel Query performance management.

       Recovery Manager (RMAN) backup and recovery operations.

       Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead caused by shrinking the SQL Cache Area

PARAMETERS

            SGA_TARGET

            SGA_MAX_SIZE

            SHARED_POOL_SIZE

            DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

            DB_RECYCLE_CACHE_SIZE

            DB_nk_CACHE_SIZE

            LARGE_POOL_SIZE

            JAVA_POOL_SIZE

            LOG_BUFFERS

Views

            V$SGA

            V$SGAINFO

            V$SGASTAT