Tuesday 26 June 2018

Overview to Oracle Instance Memory Structures - SGA Explained

What is SGA or System Global Area in Oracle Database? A complete explanation guide to Oracle Memory Structures? Various components of SGA Explained in detail.



SGA also known as Shared Global Area is a very important part of Oracle Memory Architecture. As I have mentioned in one of my previous posts regarding the Explanation of Oracle Database Server that SGA is the part of Oracle Instance which includes some memory components and some background process which work together to keep the Oracle Database Server running.

SGA (System Global Area) is the part of memory which handles the complete operations of one single instance. Let me explain this further, every instance has it's own SGA memory which is divided into all the connected users to help them to their stuff. Whether the user is trying to fetch the information out of the database or trying to insert some records into it. All the data is flown via this SGA. This SGA is further divided into 6 major parts known as:
  • Database Buffer Cache
  • Redo Buffer Cache
  • Shared Pool
  • Java Pool
  • Large Pool
  • Streams Pool
Once I'll explain all of these components, I hope you'll have some idea regarding the functioning of Oracle Memory Architecture.

1. Database Buffer Cache

Whether you are trying to insert the data into the database or you are trying to get the data out of it, it will pass through the Database Buffer Caches only. That means DB Buffer Cache handles the data fetched from data blocks as well as the the data you are trying to write in those DB Blocks.
        Let me explain the process in more simple scenario. When you execute an INSERT statement in Oracle Database, the query doesn't directly enters the data into the database. It first gets stored in the memory i.e. Database Buffer Cache and waits for the DBWr (Database Writer) process to get executed and enter that data into the Datafiles (Data Blocks) which holds the data permanently or you can simply say that you data gets stored into the Oracle Database.
       In opposite, when you try to fetch the data using SELECT query, it first gets loaded into the memory from the Data Blocks and the gets displayed on your screen. So Oracle Memory is playing a very important part here. Also these buffers doesn't get cleaned up that fast. Yes, there is particular process with which these Buffers get handled but that is out of scope of this article. I am just giving you an idea about all the SGA Memory Components here.

2. Redo Buffer Cache


Oracle Database keeps the record of the changes going on inside it. There are Different types of data handling Language commands in Oracle database which you use to perform your operations with it for example DDL (Data Dictionary Language), the DML (Database Manipulative Language) and the DCL (Data Change Language). Well, I am not going to explain these here, may be in some of my upcoming posts, but these different commands performs different kind of operations.
                         The point to be noted here is not all types of queries are necessary to be kept track of. WHY WOULD I NEED TO KEEP TRACK OF A SELECT QUERY. Of course there are permissions inside Oracle Database which helps you control the access provided to a particular user to get information out of the database. And most importantly, SELECT queries are not making any change inside the database (Let's just ignore the RESOURCE UTILISATION part here because I am not supposed to make this article a Short Book).
             So Oracle database keeps track of the DMLs or the queries that bring some changes to the database and making its SCN (System Change Number) go forward. With every single change, the SCN grows. Redo Buffers stores all such kind of transactions and RLWr (Relo Log Writer) Process writes those transactions from Redo Buffer Cache to Redo Log Files. This Redo Log information comes in handy at the time of Oracle Database Recovery.

3. Shared Pool

It is also the most important Buffer Pool which is also further sub-divided into 2 major components: Database Dictionary Pool and Library Cache. The operations like checking the Syntax, Privileges and Execution Plan for an Oracle Query are done here. This pool helps in selecting the best Optimizer planning for your SQL or PLSQL query. And as I said the buffers are not released that easily, the optimizer or execution doesn't get updated or altered for a particular query until or unless the query is modified (there are also other different conditions when optimizer changes it's execution plan).

4. Java Pool

Java Pools are required for Session related Java Code running or if you have something to do with JVM (Java Virtual Machines). Well, this pool is not always required so let's just know waste our time for this anymore. Frankly speaking, I am not that much aware with its usage as I have not personally used this pool for doing my stuff for now. But I am planning to do that in the upcoming future.

5. Large Pool

As its name suggest, Large pool serves as a safeguard for the operations needing Large Memory utilization. The most common scenario is Oracle Database Backups. Backup is a very important activity that needs to be frequently done in  order to make sure that we can always recover and restore our Oracle Database in case if something wrong happens with our database. Also we don't want any interruption in other users working (Backup use a large memory area and so if it will use the memory from some other major buffers like DB Buffer Cache, Redo Buffer Cache or Shared Pool then that can slow down the all over working of Oracle Database). That's why we configure this pool separately so that the other Memory Pools remain untouched and the Oracle functions go on smoothly.

6. Streams Pool

This pool is configured to be used by Oracle Streams. Oracle Streams are a generic mechanism for sharing data which can be used as the basis of many processes including messaging, replication and warehouse ETL processes. They are an extension of a number of existing technologies including Advanced Queuing, LogMiner and Job Scheduling.

No comments:

Post a Comment