Detail Notes on Components of SGA

Detail Notes on Components of SGA

 Mandatory Components :

 

  1.DBBC(Database Buffer cache)

  2.Shared Pool

  3.Redolog buffer

1.Database Buffer cache :

-->An area in memory where Oracle keeps recently used data blocks so that they do not need to be
constantly reread from disk.

-->Database buffer is classified into 3 components

              A.Dirty Buffer
              B.Dinned Buffer
              C.Free Buffer


A.Dirty Buffer : A dirty buffer refers to blocks in the database buffer cache that are changed,
                          but are not yet written to the disk.

       >    It is modified but not written into the disks.

B.Dinned Buffer : It is currently active task view

C.Free Buffer : It contains no data.

==> Parameter to increase the database cache
       "db_cache_size"

2.Shared Pool:

-->The area in the SGA that contains the data dictionary cache and shared parsed SQL statements.

-->It classified into components called
               A.Data Dictionary Cache
               B.Library cache

A.Data Dictionary Cache  : 

 -->It contains the all system related information.
 -->The data dictionary cache is a key area to tune because the dictionary is accessed so frequently,
 especially by Oracle's internals of Oracle. At startup, the data dictionary cache contains no data.

B.Library Cache :

-->Recently executed SQL statements

--> The Library Cache is a piece of memory within the SGA that Oracle uses in order to store
SQL Statements. Whenever a Process issues an SQL Statement, the text of the Statement goes into the Library Cache where the statement is parsed an validated. If for example I do a insert into city
(name, abbr) values ('Geneva', 'GE');. the Library Cache checks if there is a table named city having
the columns name and abbr. As an additional task, the Library Cache also checks if the user's
privileges are sufficient to execute the statement.

-->In a similar way, the Library Cache also caches PL/SQL Statements and Objects

==> Parameter to increase the database cache(only can increase the size of shared pool)
       "shared_pool_size"

3.Redolog Buffer :