Oracle Database Instance Architecture
In this article, I am going to discuss Oracle Database Instance Architecture in Depth. Please read our previous article where we discussed Oracle Database vs Oracle Database Instance. At the end of this article, you will understand the following pointers.
- What is Oracle Database Instance and its Architecture?
- What are Server Processes?
- Tasks of Server Processes?
- What is the PGA?
- Oracle dedicated server process
- Oracle Shared Server Processes
- What is System Global Area (SGA)?
- Main tasks of SGA
- Shared Pool
- Database buffer cache
- Redo Log Buffer
- Large Pool
- Java Pool
- Fixed SGA
- Streams Pool
- Sizing SGA and PGA
- What are the Background processes?
- The main purpose of each Background process
What is Database Instance in Oracle?
A Database Instance is the combination of memory structure and process. That means a database instance is a set of memory structures and processes that manage database files. For a better understanding of Oracle Database Instance Architecture, please have a look at the following diagram.
As you can see in the above diagram, the Oracle instance contains two types of memory. They are as follows:
- System Global Area or Shared Global Area (SGA)
- Program Global Area (PGA)
Again, you can see the SGA containing some components like Shared Pool, Large Pool, Redo Log Buffer, etc. and don’t worry we will discuss these things in detail. And I have another memory called PGA.
And inside the Database Instance, there are two types of processes present. They are as follows:
- Server Processes
- Background Process
Let us understand each of the components in detail one by one.
What is Server Process?
Oracle starts up the server processes to handle the requests of client processes connected to the oracle database. What do you mean by client processes? Client Processes are nothing but user sessions. Whenever a user is connected to the oracle database and until it is disconnected from the oracle database is nothing but one user session. So, whenever the user connects to the database and tries to run any SQL statements, oracle creates the server process. This is the main purpose of the server process.
It also performs tasks for the client program. For example, the server process parsing and running SQL statements, retrieving results to the client program. What is the meaning of retrieving results from the client program? It means reading database data from the database storage and loading that data into the Oracle instance buffer cache which will be learned in detail in our upcoming article.
Another task of the server process is that it acts on behalf of client sessions.
What is PGA?
Each server process has its own cache dedicated for each connecting client. This cache is also known as the PGA.
Let us understand what is PGA with an example. Think you are a user. You have connected to the Oracle database using the username and password. After connecting to the database, you have executed a query to retrieve data from the student table. The statement that you have entered will check for any mistakes in the query or else check whether the table is present or not and if all the things are correct then you will get the output that you are looking for. So, all these processes will be managed by Server Process.
So, in order to execute any SQL Statement, Oracle will create one Server Process and we need a Memory to handle that server process we call this memory PGA. If more users are connected to the database there will be a more no. of PGA’s i.e. more dedicated server processes.
Full form of PGA- Program Global Area. PGA is a non-shared memory region reserved only for the specific user session connected to the specific server process. PGA contains data and control information that is used by Oracle Server processes when sorting data, joining large tables together as part of a SQL statement, and so on.
For example, I have a user connecting to the database. Oracle will create the server process and the Server process has its own PGA.
Types of Server Processes in Oracle:
There are two types of Server processes
- Dedicated Server Process
- Shared Server Process
Dedicated Server Process: In this case, if two users are connected to the database, there will be two server processes, which means each user has its own dedicated server process. There i. This is more likely called as one to one relation. If there are 100 users the oracle allocates 100 users which is not much efficient.
If there are 100o users, then oracle will create 1000 server processes which is not much efficient. For this, the second type of server process is provided which is the Shared Server Process.
Shared Server Process: In this case, the client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. So, the shared server architecture eliminates the need for a dedicated server process for each connection. The dispatcher can support multiple user connections parallelly which is more efficient.
Note: Usually the database is set into the Dedicated Server Process but if we need the Shared Server Process, we need to change the initialization parameters in the parameter file. This change is done by an Oracle DBA.
What is System Global Area (SGA) in Oracle?
The SGA is a group of shared memory structures that contain data and control information for one database instance. So, it contains sub-component and each sub-component performs a specific task.
Main Tasks of SGA:
- Maintaining internal data structures that many processes access concurrently
- Caching data blocks read from disk
- Buffering redo data before writing it to the online redo log files
- Storing SQL execution plans
Now, you might be confused about what are all these tasks. But believe me, once we discussed the sub-components of SGA in detail, then you will understand the above key points.
The components of SGA are
- Shared Pool
- Database Buffer cache
- Redo log buffer cache
- Large Pool
- Java Pool
- Streams Pool
- Fixed SGA
What is Shared Pool?
The most important component of SGA
The main purpose of Shared Pool is to cache non-user data like data dictionaries (metadata: data about data. for example, DBA_Tables). So, when the user executes a select statement, the oracle has a lot of jobs to do. He needs to know, is this table exists or not? From where he will know? He will know from the Data Dictionary. So, it will cache the data dictionary into a path in the System Global Area called Shared Pool. So, the data will be faster.
The Shared Pool has its sub-components as follows:
- Data dictionary cache
- Library cache
- Server result cache and many others.
It stores the cached information about each SQL statement that is executed, like the execution plan. So, what is the execution plan? Actually, it is the information about the SQL Statement and it finds out the best way to execute the SQL Statement.
What is Data Dictionary Cache?
The data dictionary cache stores the information about tables present inside the database. For example, whenever the user executes a select statement the server process needs to check whether the table is present or not. We cannot go to the database and check for all the tables and find the table that we need. It consumes a lot of time, so now the shared pool comes into the picture.
The shared pool has a data dictionary cache which stores information about all the tables present in the database. So, whenever the user executes any statement the server process checks whether the table is present or not in the data dictionary cache i.e present in the shared pool.
What is a Library cache?
Library cache stores the SQL statement that is executed named execution plan.
What is an Execution Plan?
The execution plan is the procedure of how to execute the statement step-wise i.e it describes how fast a SQL statement can be executed.
How SQL statements are stored in Library Cache?
Whenever a user executes any SQL statement for example a select statement, it undergoes parsing which means checking for any mistakes in the statement. If there are any mistakes then it will issue an error to the output. If there are no errors then it will save in the library cache. Here we got a new word called parsing.
What is Parsing?
Parsing is an action breaking the statement and performing a Syntax check and Semantic Check.
- Syntax Check: Checking whether the statement is valid or not and following the rules of SQL syntax
- Semantic Check: It checks whether the tables are valid or not, do the users have access to those tables, and check if the data types are correct or not.
Parsing is of two types.
- Hard Parsing
- Soft Parsing
Let us understand parsing with a real-time example. Please have a look at the below diagram.
Suppose, user Jay is executing a select statement (select * from students). So, the server process will check for Syntax check and Semantic and check whether the tables are present in the database or not. After undergoing all the processes, the execution plan of the statement is stored in parsed SQL which is present in the shared pool. This process is called hard parsing. So, the user Jay has executed the select query and he is satisfied with the output. Now the second user Ravi connected to the database and he also executes the same statement (select * from students). Now the server process takes the execution plan which is stored in parsed SQL and displays the output without following all the processes again. This is soft parsing.
Database Buffer Cache:
The database buffer cache is responsible for caching the database user data. The buffer cache frequently accesses the database data into the buffer memory which improves performance. The data is stored in the buffer cache in the form of blocks instead of individual rows (1 block = 8kb by default). We can change the value of each block. This is the smallest unit in the oracle database.
All users who are concurrently connected to the database share and access a single database buffer cache. The buffer cache includes the keep pool, recycle pool, and others
For example, in order not to confuse we will take the same example from the above. User Jay executed a statement (select from students) after finishing all the parsing the server process copies the table students from the database-to-database buffer cache and stores it in it. After storing it displays the output. Due to this, the statement output will be much faster than the first time.
Redo Log Buffer:
This is a circular buffer that holds information about changes made to the database. This is designed to store redo entries (small records that reflect any changes made to the database as part of transactions or database structure changes which includes DML and DDL). Redo entries are used for database recovery when and if needed.
Note: The Redo Log entries in the Redo Log buffer are also written on a periodic basis to a set of files in our Oracle database storage known as Redo logs files
For example, from the previous example itself, User Jay executed a select statement (select * from students) and then he changed his mind and he needs to add a new row in the table. So, now he executed an update statement to add a new row in the table. The table is already present in the database buffer cache and a new row is added to the table in the buffer cache. So now there is a change in the database. Now the information of changes is stored in the redo log buffer which is again stored in the database. When the user enters commit the data present in the redo log buffer is stored in the database.
Now there should be a question why can’t the data be stored from the database buffer cache to the database. Because the redo log buffer is only for the changes made to the database. Storing the data is faster from the redo log buffer to the database compared to the database buffer cache database and it is more secure to take the information from the redo log buffer rather than the database buffer cache.
What is a Large Pool?
A large pool is a memory present in the SGA which allocates memory for special oracle features like the shared server process and the parallel queries. The Large pool contains memory to store database backup and recovery operations. It also contains the memory I/O server process for large transactions.
What are Parallel Queries?
Running the single SQL statement with multiple server processes in order to complete it faster.
Example: select/+PARALLEL(students,3)*/* from students;
In this select statement, there is a term parallel which is used for optimization in oracle which helps to obtain the output a bit faster. When we execute this statement oracle will open three processes to execute this statement to display the output faster.
What is Java Pool?
The memory is used to parse java code and scripts. When you execute a java code in the Large Pool database it is parsed and stored in the java pool just like a database buffer cache. JAVA_POOL_SIZE is the parameter that is used to assign memory or make any modifications to the memory.
What is Streams Pool?
The memory that provides memory for stream processes. STREAMS_POOL_SIZE is the parameter to set the value for the streams pool.
What are Streams?
Streams are nothing but sharing information. Sharing information is calculated as messages. The messages can be shared between two databases of different regions or within the databases.
What is Fixed SGA?
It contains the general information of the database and the components of the instance. A small amount of SGA is fixed internally for housekeeping by Oracle.
Sizing SGA and PGA:
There are two methods to resize SGA.
- Using Automatic Shared Memory Management (ASMM)
- Using Manual Shared Memory Management
Automatic Shared Memory Management:
We have two parameters to change the size of SGA.
The SGA TARGET is the actual memory used by the current SGA. SGA_MAX_SIZE will be the maximum size that is available for the instance. After the DBA initializes the parameter for SGA_TARGET the Oracle will automatically allocate memory and distributes the memory to all the components in the SGA.
ALTER SYSTEM SET SGA_TARGET= value [SCOPE [SPFILE | MEMORY | BOTH]]
alter system set SGA_MAX_SIZE=2048M scope=spfile;
alter system set SGA_TARGET-1500M scope=spfile;
Manual Shared Memory Management.
This process is mostly done by Expert DBA. Because we need to have a clear idea of how much memory each component present in the SGA uses.
In the manual, we must manually configure. Set the MEMORY_TARGET to 0. SGA_TARGET to 0. Then he needs to configure each component. For example, he needs to configure memory individually for a shared pool, large pool, and redo log buffer.
The other memory PGA also follows the same procedure in sizing. It also consists of two types.
- Automatic PGA Memory Management
- Manual PGA Memory Management
Automatic PGA Memory Management:
This deals with only one parameter
PGA_AGGREGATE_TARGET. It is the total amount of PGA memory allocated across all the database processes and server processes. Oracle recommends allocating PGA memory automatically. alter system set PGA AGGREGATE_LIMIT=10G scope=both sid=”*”;
Note: this allocated memory for all the connections not for any single user.
Manual PGA Memory Management:
Manually configure several PGA component sizes. This is not recommended by Oracle.
What are Background Processes?
Bunch of dedicated server-side processes running in the background to manage the database. It helps in transferring the data from the database instance to the files in the database. In these background processes, we have both required and optional background processes.
Tasks Performed by Background Processes in Oracle:
- Writing database blocks to disk
- Writing redo entries to disk
- Make sure all of the database files on disk are synchronized
- Perform maintenance tasks
Types of Background Process in Oracle Database Instance Architecture:
- Database Writer Process
- Log writer process
- System Monitor
- Process Monitor
- Recovery Process
- Listener Registration Process
- Archiver Process
Let us discuss each background process in detail.
Database Writer Processes (DBWn):
Database Writer is responsible for writing the contents of the database buffers to data files on disk. We see in DBWn ‘n’ it indicates we can have many database writers. It writes the modified data i.e updated, inserted, and deleted data from the database buffer cache to datafiles.
Log Writer (LGWR):
Log Writer is responsible for writing redo records from the redo log buffering memory into a physical disk. It will be discussed in detail further in our upcoming articles.
Checkpoint Process (CKPT):
This process handles database checkpoints. An Oracle checkpoint is a database event that synchronizes modified data blocks. In memory from the buffer cache with the data files on disk. It makes LGWR (log writer) and DBWR (database writer) writes to the database.
System Monitor Process (SMON):
SMON performs recovery during the start-up sequence of the Oracle Instance if required. It is also responsible for cleaning up any unused temporary segments. It also maintains System Change Number which will be explained further.
Process Monitor (PMON):
It performs process recovery when a user process or a session fails. It is also responsible for cleaning up any changes made to blocks in the database buffer cache and releasing resources that were previously used by a failed user session.
Recover Process (RECO):
RECO is used as part of distributed database transactions.
What are Distributed Transactions?
Distributed transactions are transactions that involve multiple databases, and should either commit a rollback on both databases at once. For example, we have two databases. If we need to save any changes to both databases at the same time, we need to give commit to both databases at once. If we need to revert any changes, we need to do rollback at once.
Listener Registration Process (LREG):
It is responsible for registering the Oracle instance with the Oracle network listener. The listener accepts remote incoming user connections. Whenever the user is connecting to the database the listener will be accepting the incoming connections to the database. This process helps the users to connect to the database remotely.
Archiver Process (ARCn):
It is responsible to copy the Oracle redo log files to a remote storage device after a redo log switch has occurred. We call this FRA (Flash Recovery Area) which is used for backup and restore methodology. Here ‘n’ in ARCn indicates multiple archiver processes.
Note: Copying the database relogs to another storage system is very important from a backup and recovery perspective
In the next article, I am going to discuss Oracle Database Files in detail. Here, in this article, I try to explain Oracle Database Instance Architecture and I hope you enjoy this Oracle Database Instance Architecture article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.