Database Buffer Cache

Data from the Data file is added to the database buffer cache and then sent over network to the client.

All newly added data are placed in Default Buffer.If there is no space in default buffer then data is placed in Keep buffer...also data which is less frequently used are aged out from the default to recycle buffer..

We can have tablespace of multiple size block and hence we also have buffers of non default size.

DB_CACHE_SIZE determines size of default buffer 

KEEP _CACHE_SIZE determines size of the keep buffer

RECYCLE _CACHE_SIZE determines size of the recycle buffer

and DB_BLOCK_SIZE determines size of the default data block..in above case its 8kb.

DB_CACHE_ADVICE if set to ON,READY collects statistics in v$ DB_CACHE_ADVICE view or else set to OFF

Shared Pool and its Components

Shared Pool is used for storing data which is to be shared among all users of DB.

It consists of the following components: -

a.Dictionary Cache(also called as Row Cache): -It is used to resolve table name,privileges,..etc

b.Library Cache: - Its has execution plans of recently executed SQL and PLSQL stmts in Shared SQL and PLSQL areas.

c.Control Structure (Locks and Latches)

It is sized by SHARED_POOL_SIZE parameter.

Size of Both the Cache depends on the shared pool size and memory is allocated internally.

Oracle Architecture and design

The above diagram may seem complex but once we get through its components one by one and then generalize it completely,it will be very easy to understand.

1. Let me start with user connecting to the database: -

In the left side bottom you will see a user process.User process represent a client machine which tries to connect to the database, as soon as we start SQL*Plus or Oracle tool at client side it automatically starts a user process.

User process requests the Listener.ora file for the connection.Listener.ora checks for the clients validity(i.e Protocol being used by the client and the database that user wants to connect).After validation user process is connected to the server process directly.

Server process acts as an security guard and doesn't allow user process to communicate directly to the server.There is alot more to the server process and we shall see it in the future.

2. Working of PGA(Program Global Area)

Once the user is connected to the database via server process,it allocates memory to create PGA(Program Global Area).

PGA is an non sharable and writable memory area.It consists of Stack space which is used to save information about User's session variables and arrays. and UGA(only in dedicated server mode).

3. Working modes of Database

Database can be opened in one of the two modes.

a. Dedicated Mode: - Single server process(i.e PGA ) per user process.

b. Shared Mode: - Server process is shared among multiple user process.

4. Working of UGA(User Global Area)

UGA consists of three sub components
a.User Session Data: - It is additional memory for user's session.
b.Cursor State: - It contains info like no. of rows retrieved or inserted or deleted.
c.Sort Area: - For Sorting...(it is set using parameter SORT_AREA_SIZE for each sort)

Note: - UGA is allocated in PGA when DB is in Dedicated Mode and UGA  is allocated in SGA->Shared Pool when DB is in  Shared mode and Large Pool is not Configured.

5. Working of SGA(System Global Area)
All the above component except the user and server process are memory areas containing several memory components.SGA consists of following memory component: -
a.Shared Pool
b.Database Buffer Cache
c.Redo Log Buffer
d.Large Pool(Optional)
e.Java Pool(Optional)
f.Sort Extent Pool
g.Fixed Area
h.Others

Size of the SGA depends on the size of its components but we can limit the maximum size of SGA using parameter SGA_MAX_SIZE 
Memory is allocated and tracked in Granules for SGA Components.size of Granule depends on the SGA_MAX_SIZE.
If  SGA_MAX_SIZE is <128mb the Granule size is 4mb or else its 16mb.