Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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


Comments

Popular posts from this blog

PostgreSQL pgBadger

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart