system global area (SGA)

What is system global area (SGA)

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance.

In more simple words, as we have RAM for our computer, same is SGA for a database. As good the RAM as fast computer is, same way as good SGA is as good database performance is. Though, this is not the case for each and every situation. This is basically made to use database RAM for faster processing of database.

Few properties of SGA:

SGA always resides in system RAM, as per recommendation SGA+PGA size should not be more than 60 to 65% of RAM.

This is shared among multiple users at same time. So called Shared Global area as well.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. Any change in database is first of all is done into SGA then into database.

Components of SGA:

As shown in the diagram are the main components of SGA. Let's discuss them

1.               Database Buffer Cache: This holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache. For e.g. “select * from emp” is fired by a user let's see how it works in Buffer cache

·                Since all data reside in datafiles and datafiles are on Hard Disk, so as required data is fetched in buffer cached for operations. first of all, data is crosscheck into buffer cache, it might be some other user has already used this table, so that will be reused. It means data in already in RAM (Since SGA is part of RAM) this is called Cache HIT.

·                If table data is not available in buffer cache it is fetched by user process from Hard Disk to buffer cached and processed this is called Cache MISS.

Data used to get flush out from buffer cache in Least Recently Used basis to give place for new data blocks.

2.               Redo buffer: Any change made into the database changes made to the database byINSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations are fist of all logged into redo log buffer and then copied into redo log files by LGWR process. The main purpose of this operation is to recover any changes done in DB in case of DB failure of DB crash.

To understand, Support users are working on DB few transactions are commit, few are committed but data is still not written to Hard Disk and few are in update mode and suddenly power down and your server database are down.

Now when restart DB it will read all the transactions from redo log files and implement it either by writing to hard disk for committed but not written to disk, or roll back which are in update mode but not committed.

3.               Shared Pool: The shared pool portion of the SGA contains the library cache and dictionarycache.

The library cache includes the shared SQL areas, private SQL areas. A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing.