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.

Prerequisite for understanding Architechture

We are completely going to focus on the server which stores our oracle database.The server of which i am talking about is not much different from the computers which you are using right now,it also has hardisk and ram but a larger one in size.

Hardisk and Ram are type of  memory where we can save our data.Difference is Hardisk is Cheap in terms of cost(hence always larger in size than ram),Non volatile and Slow in accessing where as Ram is exactly opposite of it.

Consider your hardisk as your money in bank and ram as money at home.
Money in you bank is more safe than money at home,then why dont we keep all of it in bank?
bcoz its not easily accessible as we need to write a check to retrieve money also its time consuming.This is the exact reason why we use ram,"FOR FASTER ACCESS TO DATA".

Since ram is faster in access then why dont we keep all of our money at home rather than in bank.
lets assume that we always keep our all money at home and not in bank and one night thief robbed my home.Now at this point of time i am left with no money.similarly ram is volatile.when we switch off our computers the data stored in ram is lost and hence we require bank(i.e hardisk) to save our money(i.e data).

You must be thinking that why is this stupid fellow tell all the stuff about hardisk and ram,how is oracle related to it?Its because oracle server's architecture has two parts
1. Oracle Instance:-This resides in ram
2.Oracle Databse:- This resides in hardisk

so basically we are going to study what all changes are going to take place in hardisk and ram of an oracle server.

Introduction to Oracle 9i Fundamental 1

The first paper(Oracle iSQL) defines the syntax of the language using which we use to communicate with the database in the form of Query.The second paper(Oracle Funda 1) describes the internal architecture of Oracle server and defines its working on the server side.This is the most important and basic paper in the series of Oracle Certification.Its more theoretical rather than practical and hence it requires more and more imagination power.