SGA --- Shared Pool

SGA is dynamic memory area allocated during Instance Startup.
Parameter for SGA sizing : SGA_MAX_SIZE

Note :You will be more familiar with the Parameters when I will discuss Parameter File and type of Parameter File.Till then dont worry if you Stumped by these Parameters.Take them to be variables defining some values used by Oracle DB Server.

Now let us discuss the importance and functionality of each of the memory sub components of SGA:

1) Shared Pool :
Parameter for Shared Pool sizing : SHARED_POOL_SIZE
The Shared Pool basically consists of two sub memory areas :

i)Library Cache: This Cache area contains the most recently used SQL(Structure Query Language) and PL/SQL(Programming Language SQL) statements.As such we have faster retrieval of query results from the cache than finding data from the Database.The Lib Cache basically stores the statements and their corresponding Excution Paths(i.e sequence of actions server process follows to execute a particular query).Hence if the Query is found in the Cache there is no need to again generate the Execution path and this saves time.However if the entry is not found in the Lib Cache, Execution Path needs to be created fresh for that statement.

• As the Cache size is limited, it purges/removes the entries (sql,pl/sql stmts) based on LRU (Least Recently Used) Algorithm.
• The sizing of Lib Cache depends on Shared Pool sizing.Memory is allocated when a statement is parsed.
  
• The Lib Cache internally consists of Shared SQL area(For Parsing and creating Execution path for SQL statements) and Shared PL/SQL area(For Parsing and creating Execution path for PL/SQL statements).
  

ii)Data Dictionary Cache :DD Cache contains cached information about the Database.
As a DBA , you need to frequently access tables and other database objects.For this, the access to these objects should be defined.What files you can access and what you cannot should be defined?What privileges you have on a particular table?What are the Datafiles in the Database?What are the segment names, extent locations?

All this information is stored in Data Dictionary(Meta Data) Tables.While processing a request,this information is required and for this the Data Dictionary Tables are read and the info is stored in the DD Cache.

The sizing of the DD Cache depends on the Shared Pool size.

I will discuss on Database Buffer Cache,Redo Log Buffer Cache,Java Pool,Large Pool in my next post.