Oracle architecture and its categories

User Process : Whenever a user wants to connect to the oracle server for extraction of the data or to insert any data using sql statement. The user process then initiates a connection and hence a session is established for the user in the instance. After establishing a session, each user then starts a server process which will actually allow the user to interact to the database and retrieve the data or insert it. To be precise, Server process communicates with the oracle instance on behalf of the user. An additional memory structure called PGA (Program Global Area) is also created during this process. It stores the user session variables and bind variables.

Oracle Instance: Oracle instance is made up of SGA (System Global Area) and Background process. SGA further divided into three main components.

* Shared Pool: Caches the recently used sql statements.
Database Buffer cache: Cashes the data that has been most recently used by the user.
Redo log buffer: Stores transaction information.

Oracle uses LRU (Least Recently Used) algorithm.

It will caches the most recently used sql statements in shared pool and data in buffer cache. If for example we as a user1 are entering a sql statement (select * from emp). This statement will be cached by the shared pool and the data which is extracted will be cached in Database buffer cache. Now if any other user wants the same data, obviously he is going to use the same sql statement, so the data will be presented to the user directly from the database buffer thus improving the performance.

SGA size can be allocated manually or automatically. This is determined by the settings in a configuration file called parameter initialization file.