Tutorial corner

Informatica,ETL,oracle,sql/plsql

Oracle database architecture tutorial

Spread the love

Oracle database architecture tutorial : Oracle is a RDBMS (a Relational Database Management System),  which uses Relational Data Model to store its database and SQL (commonly abbreviated as Structured Query Language) to process the stored data. Every oracle developer is keen to know about oracle database architecture and it most commonly asked oracle interview question. So in below tutorial we will give you some insight over Oracle Database architecture. Don’t forget to give your feedback :).

Oracle database architecture tutorial

Oracle Instance

An oracle instance is a set of memory structures and operating system processes which manages all database activities, such as transaction processing, database recovery, form generation, and so on. The instance structure is loosely styled after UNIX’s implementation of multitasking operating system. Discrete processes perform specialized tasks within the RDBMS that work together to accomplish the goals of the instance. Each process has a separate memory block that it uses to store local and private variables, address stacks and other runtime information. The processes use a common shared memory area for processing data concurrently. This memory block is called the System Global Area (SGA). Figure 1 illustrates the architecture of an instance. Each component of the instance is described below.

Oracle database architecture tutorial

Oracle database architecture tutorial

 

System Global Area (SGA)

The SGA is the primary memory component of the instance. It provides the memory structure necessary for data manipulation , SQL statement parsing, and redo caching. As the name suggest ,  SGA is shared, which means that the multiple processes can access and modify the data contained in it in a synchronized manner.

Shared Pool:

library cache:

  • Stores the text, parsed format, and execution plan of SQL statements that have been submitted to the RDBMS
  • It also store the headers of PL/SQL packages and procedures that have been executed
  • For each coming SQL statement to the server , it first checks the library cache to see if an identical statement has already been submitted and cached and then use it if  exists. Hence it enhance the performance of SQL.

The library cache can be further classified as below.

Shared  Cache : This area contains the parse tree and execution path for SQL statement

Private Cache: This area contains session specific information, such as bind variable, environment and session parameters, run time stacks and buffers, etc  A private SQL area is created for each transaction initiated, and it is de-allocated after the cursor corresponding to that private area is closed.

Using these two structures, the server can reuse the information common across all execution of an SQL statement, while session specific information to the execution can be retrieved from the private SQL area.

Check here for System Global Area in Oracle

 Data Dictionary Cache

The Data Dictionary Cache is a memory structure that caches data dictionary information that has been recently used.

  • This cache is necessary because the data dictionary is accessed so often.
  • Information accessed includes user account information, data file names, table descriptions, user privileges, and other information.

The database server manages the size of the Data Dictionary Cache internally and the size depends on the size of the Shared Pool in which the Data Dictionary Cache resides.  If the size is too small, then the data dictionary tables that reside on disk must be queried often for information and this will slow down performance.

 Server Result Cache

The Server Result Cache holds the result sets and not data blocks. The server result cache contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure

SQL Query Result Cache

This cache stores the results of queries and query fragments.

  • Using the cache results for future queries tends to improve performance.
  • For example, suppose an application runs the same SELECT statement repeatedly. If the results are cached, then the database returns them immediately.
  • In this way, the database avoids the expensive operation of rereading blocks and recomputing results.
PL/SQL Function Result Cache

The PL/SQL Function Result Cache stores function result sets.

  • Without caching, 1000 calls of a function at 1 second per call would take 1000 seconds.
  • With caching, 1000 function calls with the same inputs could take 1 second total.
  • Good candidates for result caching are frequently invoked functions that depend on relatively static data.
  • PL/SQL function code can specify that results be cached.

Buffer Cache

  • The buffer cache is composed of memory blocks. All data updates are performed in the buffer blocks
  • The data movement (swapping and loading) between buffer and disk or other parts of RAM is by least recently Used (LRU) algorithm.
  • The LRU list keeps track of what data blocks are accessed and how often.
  • Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora  initialization parameter file

Redo Log Buffer

  • Each DML statement (select, insert, update, and delete) executed by users will generates entry in the redo logs .
  • The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the data files, then the changes can be obtained from the redo log, so work is never lost.
  • To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

Large Pool

  • Large pool is an optional area of memory in the SGA.
  • It is used to relieves the burden place on the shared pool. It is also used for I/O processes.
  • The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java Pool

  • As its name, Java pool is used to services parsing of the Java commands.
  • Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program Global Area

  • The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.
  • A PGA is created by Oracle when a server process is started
  • Although the result of SQL statement parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Check here for Process Global Area in Oracle

Oracle Background Process

An Oracle database uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system.

Process Description
DBWR The database writer process. This background process handles all data block writes to disk. It works in conjunction with the Oracle database buffer cache memory structure. It prevents users from ever accessing a disk to perform a data change such as update,insert, or delete.Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.
LGWR  It writes the redo entries from redo log buffer into the redo log files.
CKPT The checkpoint process. In high-activity databases, CKPT can be used to handle writing log sequence numbers to the datafile headers and control file, alleviating LGWR of that responsibility
SMON System Monitor (SMON) process is used to recover the system crash or instance failure by applying the entries in the redo log files to the datafiles.
PMON Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.
ARCH The ARCH background process is invoked when your database is running in ARCHIVELOG mode. If you are archiving your redo logs, the redo logs are touched by several background processes
DISPATCHER PROCESSES The dispatcher process passes user requests to the SGA request queue and returns the server responses back to the correct user process
RECO Recovery Process is responsible for recovering failed transactions. In distributed database systems. It is automatically started when the database is configured for distributed transactions. The RECO process operates with little or no DBA intervention when an in-doubt transaction occurs in a distributed system. The RECO process attempts to connect to the remote database and resolves the in-doubt transaction when a database connection is successful.
SNPn The Snapshot Process, handles the automatic refreshing of database snapshots and runs the database procedures scheduled through the database system’s job package.
LCKn The lock process is responsible for managing and coordinating the locks held by the individual instances. Each instance in the parallel server installation has 1-10 lock processes assigned, and each instance must have the same number. This process has no purpose in a non-parallel server environment
Pnnn Parallel query processes are named Pnnn. These processes are involved in parallel index creations, table creations, and queries.

Sources :

https://docs.oracle.com/en/
www.orafaq.com

Incoming search Terms:

  • oracle database architecture tutorial
  • oracle database architecture explanation
  • oracle database architecture diagram
  • oracle database architecture in detail

Hope you enjoyed the above article , in which we have gone through the  Oracle database architecture tutorial in details.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL

1 Comment

  1. Pingback: Oracle Database Architect |

Comments are closed.

Copyright 2015 - Tutorial Corner Frontier Theme