Memory structure in oracle database

In this tutorial we will learn Memory structure in oracle database . Memory structure of oracle database consists of two types .

1. SGA : Shared Global Area

2. PGA: Program Global Area

SGA:

SGA is sets of shared memory structures which stores data (program code)and control information of oracle database instance. There will be store record related to current user session . All data and information are shared by all background processes.

There are various SGA components inside the Shared Global Area. These components are described below:

DB buffer cache:DB buffer cache stores the data blocks which are read from the datafiles of the oracle database upon request by the user. When a user executes the select statement it finds from the db buffer cache if it is available into the db buffer cache then it shows the results otherwise db buffer cache read the data from the datafiles which is stored on the disk.User never access the data directly from the datafile. always it will be accessed through the db buffer cache.If data is modified which is called dirty buffer in the db buffer cache it will be stored into the datafiles on the physical disk through the DBWr process.

Redo Buffer:Redo buffer is the buffer which stores the data which is changed. When a user executes the update statement data will be changed and the updated data will be stored in the redo entries. These redo entries are moved to the redo log files through the lgwr process.

Shared Pool:Shared pool consists of library cache ,data dictionary cache and result cache.SQL or PLSQL code and execution plan is located in the library cache.When a user executes a SQL command it will be executed in the shared SQL area. In the data dictionary cache all data dictionary tables are stored. When a user executes for data dictionary table it will be fetched from it. When a user executes a select statement the result will be stored in the result cache. When another user executes the same query the result will be fetched out from the result cache.

Large pool:Large pool is used for the execution of the parallel query and backup and recovery purpose of the database. It is also required for the distributed database.

Java Pool:Java pool is used for executing the specified java code in a java virtual machine.

Stream pool:Stream pool is used to store queue messages and it is used by the oracle stream processes.

PGA:

PGA stands for program global area. When a user is going to connect to the oracle database, then the listener plays a vital role to connect the user process and server process.once the connection is established then the user process is directly connected to the server process. When the server process starts it allocates PGA for that particular server process so that PGA is non shared memory. Once the server process stops the PGA will be terminated.

In this article we learned the memory structure of oracle database, SGA ,SGA components and PGA. Stay tune with us to know more tutorials.