Oracle consists of ?

Oracle constitutes of components for its existence.

1) Memory Structures.
2) Back ground running processes.
3) Disk resources to store the data.
4) Special resources designed to handle problems ranging from incorrect entry to disk failure.

Memory structures with back ground running processes said to be an Oracle Instance.

Oracle Instance with remaining resources said to be as Oracle Database



MEMORY STRUCTURE

Oracle memory structure is organized in the manner shown above.

1) System global area seems to be the primary memory component (SGA).

This is further classified into
a) Buffer Cache.
b) Shared Pool
1. Library Cache.
2. Dictionary or Row Cache.
3. Sessions (User processes connect to oracle) (Optional).
c) Redo Log buffer.


2) Program Global Area (PGA).


1 - A) SGA – Buffer Cache.


This memory structure consists of buffers, each buffer size of a database block.
This stores data needed by sql statements issued by user processes.

This has multiple units of and each unit considered as a block. So the size of buffer cache is always mentioned as blocks but not bytes.


In each block oracle can store several rows of data. So more the size of buffer more the information it can sore in it during the execution cycle of the select statement.
Its main purpose is to
i) Improve performance of subsequent repeated select statements on the same data.
ii) Oracle users can perform data changes quickly in memory and writes those changes to disk later.

1 – B) Shared Pool.

This is further classified into 2 mandatory structures and 1 optional structure.

1 – B – 1) Library Cache.

i) This is very vital to parse and store sql statement text.
ii) Also stores statements execution plan for reuse.

1 – B – 2) Dictionary Cache or Row Cache.

i) This stores recently accessed information.
Ex: - Username, password, table, columns, privileges etc…

These 2 components help there most to improve performance of Oracle.
1 – B - 3) Sessions (Optional)
This optional structure is to save the sessions like user process connected to oracle.


1 – C) Redo Log Buffer.

Actions performed by user on data through sql statements like delete, update, insert, create, alter, drop are not posted and saved to disk immediately. Instead they are maintained in this redo log entry till those changes are saved on to the disk.
Advantage of this functionality is DBA can use Redo information to recover the Oracle database to the point of database failure.


2) Program Global Area (PGA).


i) This memory structure helps in aspects like cursor handling, sort areas, sorting information like bind variable values. This differs from library cache in holding or saving the real values in place of bind variables for executing sql statements.