Oracle Database Introduction

The combination of background processes and memory structures is called an oracle instance.

A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. 

When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:

• ·Maintaining internal data structures that are accessed by many processes and   threads concurrently
• ·Caching data blocks read from disk
• ·Buffering redo data before writing it to the online redo log files
• ·Storing SQL execution plans

Types of Processes:

• A database instance contains or interacts with the following types of processes:
• Client processes run the application or Oracle tool code.
• Oracle processes run the Oracle database code. Oracle processes including the following subtypes:
• Background processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
• Server processes perform work based on a client request

Instance startup phase:

Basically starting an Oracle database is divided in 3 steps:

NOMOUNT---in the NOMOUNT level, only the Oracle INSTANCE is started. 

To do so Oracle looks for the Instance spfile or pfile and starts all Processes and Memory Structures by looking in Oracl_home/dbs location

MOUNT----Once taking the database server from NOMOUNT to MOUNT, Oracle access the control files, as declared in the spfile, and mount the database.

OPEN--The last step is to open the database, which means accessing the data files and redo logs. 

Once the database is OPEN, all users can log on again and go ahead with their normal activities.

Shutdown Normal:

This is the default. No new user connections will be allowed, but all current connections continue normaly. Once all users have logged off, the database will finally be allowed to shutdown.

Shutdown Transactional:

No new user connections are permitted and existing sessions that are not involved in active transactions will be terminated. However sessions currently involved in a transaction are allowed to complete the transaction and will then be terminated. Once all sessions are terminated, the database will shutdown.

Shutdown Immediate:

No new sessions are permitted, all currently connected sessions are

terminated an any active transactions are rolled back. Then the database will go down.

Shutdown Abort:

As far as Oracle is concerned, this is the equivalent of a power failure. The instance terminates immediately (instance “crash”). Nothing is written to disk, no file handles are closed and there is no transactions are terminated, even not in a orderly way. A shutdown abort will not damage the database, but some operations like backups are not advisable after an abort.

During the close phase:

all sessions are terminated PMON roll back any incomplete transactions.

A checkpoint is issued, which forces the DBWn process to write all updated data from the db buffer cache to the datafiles.

LGWR flushes any change vectors still in memory to the logfiles

The file headers are updated, and the file handles closed.

At this point the database is in a consistent state: all datafiles and logfiles are synchronized.

During the dismount phase the control files are closed

Then the instance is stopped by deallocating the SGA memory and terminating the background processes.

In case of the Abort mode, it leaves the database in an inconsistent state:

Committed transactions have been lost, because they were only in memory and DBWn had not yet written them to the datafiles

Uncommitted transactions in the datafiles may not yet have been rolled back..

Process Global Area (PGA): This is memory that is private to a single process or thread; it is not accessible from other processes/threads.

Or 

PGA is the memory reserved for each user process connecting to an Oracle Database and is allocated when a process is created and deallocated when a process is terminated.

Contents of PGA:-Private SQL Area: stores information for a parsed sql statement and stores bind variable values and run time memory allocations. The number of Private SQL areas that can be allocated to a user process depends on the OPEN_CURSORS initialization parameter.

Session Memory: Consists of memory allocated to hold a session’s variable and other info related to the session.SQL Work Areas: Used for memory intensive operations such as: Sort, Hash-join, Bitmap merge, Bitmap Create.

User Global Area (UGA) : The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state

The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.

SGA is used to store database information that is shared by database processes. It contains data and control information for the Oracle Server and is allocated in the virtual memory if the computer where Oracle resides.                        Or  

System Global Area (SGA):- This is a large, shared memory segment that virtually all Oracle processes will access at one point or another. 

                                    Or  

The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA Is allocated when an oracle instance (database)started up based on the values specified on Pfile or Spfile  

SGA consists of several memory structures:-

Redo Buffer:  The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily, before it is written to disk. Since a memory-to-memory transfer is much faster than a memory-to-disk transfer, use of the redo log buffer can speed up database operation. The data will not reside in the redo buffer for very long. In fact, LGWR initiates a flush of this area in one of the following scenarios:

• Every three seconds

• Whenever someone commits

• When LGWR is asked to switch log files

• When the redo buffer gets one-third full or contains 1MB of cached redo log data 

In short it contains committed transactions that are not yet written to the redo log files.

1.Buffer Cache: The block buffer cache is where Oracle stores database blocks before writing them to disk and after reading them in from disk. There are three places to store cached blocks from individual segments in the SGA:

• Default pool (hot cache): The location where all segment blocks are normally cached.

• Keep pool (warm cache): An alternate buffer pool where by convention you assign segments that are accessed fairly frequently, but still get aged out of the default buffer pool due to other segments needing space.

• Recycle pool (do not care to cache): An alternate buffer pool where by convention you assign large segments that you access very randomly, and which would therefore cause excessive buffer flushing of many blocks from many segments. There’s no benefit to caching such segments because by the time you wanted the block again, it would have been aged out of the cache. You would separate these segments out from the segments in the default and keep pools so they would not cause those blocks to age out of the cache.

In short Data buffer cache - cache data and index blocks for faster access

2.Shared Pool: The shared pool is where Oracle caches many bits of “program” data. When we parse a query, the parsed representation is cached there. Before we go through the job of parsing an entire query, Oracle searches the shared pool to see if the work has already been done. PL/SQL code that you run is cached in the shared pool, so the next time you run it, Oracle doesn’t have to read it in from disk again. PL/SQL code is not only cached here, it is shared here as well. If you have 1,000 sessions all executing the same code, only one copy of the code is loaded and shared among all sessions. Oracle stores the system parameters in the shared pool. The data dictionary cache (cached information about database objects) is stored here. Shared Pool manages memory on an LRU basis, similar to buffer cache, which is perfect for caching and reusing data.

      In short Shared pool - cache parsed SQL and PL/SQL statements.

3.Large Pool: The large pool is not so named because it is a “large” structure (although it may very well be large in size). It is so named because it is used for allocations of large pieces of memory that are bigger than the shared pool is designed to handle. Large memory allocations tend to get a chunk of memory, use it, and then be done with it. There was no need to cache this memory as in buffer cache and Shared Pool, hence a new pool was allocated. 

So basically Shared pool is more like Keep Pool whereas Large Pool is similar to the Recycle Pool. Large pool is used specifically by:

• Shared server connections, to allocate the UGA region in the SGA.

• Parallel execution of statements, to allow for the allocation of interprocess message buffers, which are used to coordinate the parallel query servers.

• Backup for RMAN disk I/O buffers in some cases.

   In short Large pool - used for backups, UGAs, etc

4.Java Pool: The Java pool is used in different ways, depending on the mode in which the Oracle server is running. In dedicated server mode the total memory required for the Java pool is quite modest and can be determined based on the number of Java classes you’ll be using. In shared server connection the java pool includes shared part of each java class and Some of the UGA used for per-session state of each session, which is allocated from the JAVA_POOL within the SGA.

In short JAVA pool - caching parsed Java programs

5.Streams Pool: The Streams pool (or up to 10 percent of the shared pool if no Streams pool is configured) is used to buffer queue messages used by the Streams process as it moves or copies data from one database to another 

In short (Streams pool - cache Oracle Streams objects.)

Mandatory Background Processes in 11gR2 Database

Oracle Database creates background processes automatically when a database instance starts. An instance can have many background processes, not all of which always exist in every database configuration. The following query lists the background processes running on your database:

SELECT PNAME FROM   V$PROCESS WHERE  PNAME IS NOT NULL ORDER BY PNAME;

The mandatory background processes are present in all typical database configurations. These processes run by default in a database instance started with a minimally configured initialization parameter file.

This section describes the following mandatory background processes:

1. Process Monitor Process (PMON)
2. System Monitor Process (SMON)
3. Database Writer Process (DBWn)
4. Log Writer Process (LGWR)
5. Checkpoint Process (CKPT)
6. Manageability Monitor Processes (MMON and MMNL)
7. Recoverer Process (RECO)

1.Process Monitor Process (PMON):

•  Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally
•  Responsible for cleaning up the database buffer cache and freeing resources that the client process was using
•  Resets the status of the active transaction tables
•  Releases locks that are no longer required
•  Removes the process ID from the list of active processes
•  Registers information about the instance and dispatcher processes with the Oracle Net listener
•  When an instance starts, PMON polls the listener to determine whether it is running. If the listener is running, then PMON passes it relevant parameters. If it is not running, then PMON periodically attempts to contact it.

2.System Monitor Process (SMON):

•  Responsible for system-level cleanup
• Performs instance recovery, if necessary, at instance startup.
•  If its RAC Database, the SMON process of one database instance can perform instance recovery for a failed instance.
•  Cleaning up unused temporary segments.
•  Coalescing contiguous free extents within dictionary-managed tablespaces.

3.Database Writer Process (DBWn)

• DBWn writes the contents of database buffers to data files. DBWn processes write modified buffers in the database buffer cache to disk
•  Although one database writer process (DBW0) is adequerfect ate for most systems, you can configure additional processes—DBW1 through DBW9 and DBWa through DBWj—to improve write performance if your system modifies data heavily however these additional DBWn processes are not useful on uniprocessor systems.

The DBWn process writes dirty buffers to disk under the following conditions:

• When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk asynchronously if possible while performing other processing.
• DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread from which instance recovery begins. The log position of the checkpoint is determined by the oldest dirty buffer in the buffer cache.
• Note: In many cases the blocks that DBWn writes are scattered throughout the disk. Thus, the writes tend to be slower than the sequential writes performed by LGWR. DBWn performs multiblock writes when possible to improve efficiency. The number of blocks written in a multiblock write varies by operating system.

4.Log Writer Process (LGWR):

• The log writer process (LGWR) manages the redo log buffer. LGWR writes one contiguous portion of the buffer to the online redo log.
• In the following circumstances, LGWR writes all redo entries that have been copied into the buffer since the last time it wrote:
•  A user commits a transaction
•  An online redo log switch occurs
•  Three seconds have passed since LGWR last wrote
•  The redo log buffer is one-third full or contains 1 MB of buffered data.
•  DBWn must write modified buffers to disk
• Note: Before DBWn can write a dirty buffer, redo records associated with changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the records to disk and waits for LGWR to complete before writing the data buffers to disk.

Relation between LGWR and Commits:

• Oracle Database uses a fast commit mechanism to improve performance for committed transactions. When a user issues a COMMIT statement, the transaction is assigned a system change number (SCN). LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the commit SCN and transaction’s redo entries.
• Note: LGWR can write redo log entries to disk before a transaction commits. The redo entries become permanent only if the transaction later commits.
• When activity is high, LGWR can use group commits. For example, a user commits, causing LGWR to write the transaction’s redo entries to disk. During this write other users commit. LGWR cannot write to disk to commit these transactions until its previous write completes. Upon completion, LGWR can write the list of redo entries of waiting transactions (not yet committed) in one operation. In this way, the database minimizes disk I/O and maximizes performance. If commits requests continue at a high rate, then every write by LGWR can contain multiple commit records.

5.Checkpoint Process (CKPT):

•  Checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk.
•  Checkpoint information includes the checkpoint position, SCN, location in online redo log to begin recovery
• Checkpoint automatically occurs at a log switch.
• When we will specify the parameter fast_start_mttr_target=<No of Seconds>.
• When Normally forced by the Database Administrator.
• If the datafile is offline checkpoint will occur.

6.Manageability Monitor Processes (MMON and MMNL):

• The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.
• The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.Recoverer Process (RECO):

7.RECO:

•  Is a background process for distributed transactions. The RECO process manager two-phase commits to track and resolve in-doubt transactions.
•  In a distributed database, the recoverer process (RECO) automatically resolves failures in distributed transactions.

Optional Background Processes

An optional background process is any background process not defined as mandatory. Most optional background processes are specific to tasks or features. For example, background processes that support Oracle Streams Advanced Queuing (AQ) or Oracle Automatic Storage Management (Oracle ASM) are only available when these features are enabled.

This section describes some common optional processes:

Archiver Processes (ARCn)-- The archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs. ARCn processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled.

Job Queue Processes (CJQ0 and Jnnn)-- Oracle Database uses job queue processes to run user jobs, often in batch mode

Flashback Data Archiver Process (FBDA)-- FBDA automatically manages the flashback data archive for space, organization, and retention. Additionally, the process keeps track of how far the archiving of tracked transactions has occurred

Space Management Coordinator Process (SMCO)-- The SMCO process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation

Soft parsing:

When a query is submitted to oracle server for execution, oracle checks if same query has been executed previously. If the parsed execution plan is found then this event is known as Library cache hit or soft parsing.

Hard parse:

If pared form of the statement is not found in the shared pool then new statement is parsed and its parsed version is stored in Shared SQL area. This is known as hard parse.

Oracle allocates memory from shared pool when a new statement is submitted. If required, oracle may deallocate memory from previous statements. As a result of this, deallocated statements shall require hard parsing when re-submitted. More resources are used to perform a hard parse. So it is very important to keep the size for shared pool large enough to avoid hard parsing.

Segment :A set of extents allocated for a specific database object such as a table, index, or table cluster. User segments, undo segments, and temporary segments are all types of segments.

Extent:Multiple contiguous data blocks allocated for storing a specific type of information. A segment is made up of one or more extents.

data block:Smallest logical unit of data storage in Oracle Database. Other names for data blocks include Oracle blocks or pages. One data block corresponds to a specific number of bytes of physical space on disk

Locally Managed Tablespaces:

A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, Oracle Database changes the bitmap values to reflect the new status of the blocks.

Dictionary-Managed Tablespaces:

A dictionary-managed tablespace uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. For example, when a table needs an extent, the database queries the data dictionary tables, and searches for free extents. If the database finds space, then it modifies one data dictionary table and inserts a row into another. In this way, the database manages space by modifying and moving data.

Undo Segments:Oracle Database maintains records of the actions of transactions, collectively known as undo data. Oracle Database uses undo to do the following:

Roll back an active transaction

Recover a terminated transaction

Provide read consistency

Perform some logical flashback operations

Undo data is stored in an undo tablespace. Oracle Database provides a fully automated mechanism, known as automatic undo management mode, for managing undo segments and space in an undo tablespace

What is FAST_START_MTTR_TARGET?

FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.

As we know the FAST_START_MTTR_TARGET initialization parameter specify the maximum no. of seconds for data to stay in memory before DBW0 can write this data to actual data file.

The default is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).

Enjoy the work and do like me if you like this article!!!.