Background Processes

An Oracle instance is the combination of the background processes and memory structures. The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability. Many other processes exist that are used by other options within Oracle, such as Advanced Queuing, Real Application Clusters, Shared Server, Advanced Replication, and so on. The background processes in an Oracle instance can include the following:

Database Writer Process (DBWn)

Log Writer Process (LGWR)

Checkpoint Process (CKPT)

System Monitor Process (SMON)

Process Monitor Process (PMON)

Recoverer Process (RECO)

Archiver Processes (ARCn)

Queue Monitor Processes (QMNn)

Read More

Server Process

A server process is a program that directly interacts with the Oracle server. Once a user has established a connection, a server process is started to handle the user processes requests. A server process can be either a dedicated server process or a shared server process. In a dedicated server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated. In ashared server environment, the server process handles the request of several user processes. The server process communicates with the Oracle server using the Oracle Program Interface Server processes (or the server portion of combined user/server processes) created on behalf of each user's application can perform one or more of the following:

• Parse and run SQL statements issued through the application
• Read necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA
• Return results in such a way that the application can process the information

Related Post:- 

• User Process
• Background Process 

Read More

User Process

User Processes

When a user runs an Oracle tool Oracle creates a user process to run the user's application. A database user who needs to request information from the database must first make a connection with the Oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle server. Rather it generates calls through the user program interface (UPI), which creates a session and starts a server process

Connections and Sessions

The terms "connection" and "session" are closely related to the term "user process", but are very different in meaning.

A connection is a communication pathway between a user process and an Oracle instance. A communication pathway is established using available interprocess communication mechanisms (on a computer that executes both the user process and Oracle) or network software (when different computers execute the database application and Oracle, and communicate via a network).

A session is a specific connection of a user to an Oracle instance via a user process. For example, when a user starts SQL*Plus, the user must provide a valid username and password and then a session is established for that user. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

Related Posts:

• Server Process 
• Background Process 

Read More

Introduction to processes

All connected Oracle users must execute two modules of code to access an Oracle database instance:

• Application or Oracle tool 
• Oracle server code 

These code modules are executed by processes. A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. (Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

The process structure varies for different Oracle configurations, depending on the operating system and the choice of Oracle options. There are some processes 

• User Process 
• Server Process
• Background Process 

Read More

Stream Pool

This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly, specify the size of the pool in bytes using the streams_pool_size initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

Read More

Large Pool & Java Pool

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.

• Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved
• I/O Server Processes
• Parallel Query Buffers
• Oracle Backup and Restore Operations using RMAN

 Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead casued by shrinking the SQL Cache Area.

This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query.

By issuing a summation select against the v$sgastat view, a DBA can quickly determine the size of the large pool area currently being used.

SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool='LARGE POOL' GROUP BY ROLLUP(name);

The select above should be used when an "ORA-04031: Unable to allocate 16084 bytes of shared memory  large pool unknown object large pool hea PX large pool error is received with a configured large pool the number of bytes specified may differ

Read More

Data Dictionary Cache

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:

• The definitions of every schema object in the database, including default values for columns and    integrity constraint information.
•  The amount of space allocated for and currently used by the schema objects
• The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users

The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:

• Accesses the data dictionary to find information about users, schema objects, and storage structure
• Modifies the data dictionary every time that a DDL statement is issued

Contents of the Data Dictionary

The data dictionary consists of the following:

Base Tables

The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.

 Views

The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.

Related Post:- What Is View?

Storage of the Data Dictionary

The data dictionary base tables are the first objects created in any Oracle database. All data dictionary tables and views for a database are stored in the SYSTEM tablespace. Because the SYSTEM tablespace is always online when the database is open, the data dictionary is always available when the database is open.

How Oracle Uses the Data Dictionary

Data in the base tables of the data dictionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary information. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded. During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.

For example, if user Kathy creates a table named parts, then new rows are added to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that Kathy has on the table. This new information is then visible the next time the dictionary views are queried.

Dictionary Cache is place in Shared Pool which contains Data Dictionary. Oracle frequently requires Data Dictionary. Most parts of Data Dictionary are cached into Dictionary Cache. Oracle utilizes Dictionary Cache information in query parsing. Dictionary cache is also called Row Cache as data inside Dictionary Cache is maintained into rows instead of buffer

Read More

Library Cache

The library cache stores information about the most recently used SQL and PL/SQL statements. Library cache is very important part of Oracle Sharedpool. Shared Pool controls execution of SQL statements. Shared pool is divided into Data dictionary Cache and Library Cache. In Dedicated server configuration Private SQL area is created in PGA of server process. Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool with in the SGA.

Shared SQL Area

Shared SQL Area contains parse tree and execution plan of SQL cursors and PL/SQL programs. So executable form of SQL statements is available here which ca be reused. 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. 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.

As Library cache is kept inside Shared Pool so use SHARED_POOL_SIZE initialization parameter to increase the size of Shared Pool. It will indirectly increase memory available for Shared SQL Area.

Private SQL area

Each session issuing a SQL statement has a private SQL area in its PGA (see "Private SQL Area"). Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.

The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements. The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.

Read More

Shared Pool

The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions. The shared pool caches various types of program data. The shared pool stores parsing, interpreting, and executing all of the SQL statements and data dictionary information. The shared pool includes the following components:

• The library cache
• The dictionary cache
• Reserved Pool

The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.

Related post:-  Introduction to SGA

Read More

Redo Log Buffer

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary. Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process log writer (LGWR) writes the redo log buffer to the active online redo log group on disk

If database is running in archiving mode then archive log files are created from redo log files. These archive log files are used to in various database recovery techniques.

LOG_BUFFER initialization parameter is used to set the size Redo Log buffer

Related Post :- System Global Area (SGA)

Read More

Buffer Pools

A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools.

You can manually configure separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. You can then assign specific schema objects to the appropriate buffer pool to control how blocks age out of the cache.

The possible buffer pools are as follows:

Default pool

This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.

Keep pool

This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.

Recycle pool

This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.

You can create a tablespace with a block size that differs from the standard size. Each non default block size has its own pool. Oracle Database manages the blocks in these pools in the same way as in the default pool. The structure of the buffer cache when multiple pools are used. The cache contains default, keep, and recycle pools. The default block size is 8 KB. The cache contains separate pools for tablespaces that use the nonstandard block sizes of 2 KB, 4 KB, and 16 KB.

Read More

Database Buffer Cache

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes. The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list.

The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk.

The LRU list holds pinned buffers,clean, free and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed

When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list. When oracle server process requires a specific data block, it first searches it in Buffer cache. If it finds required block, it is directly accessed and this event is known as Cache Hit. If searching in Buffer cache fails then it is read from datafile on the disk and the event is called Cache Miss. If the required block is not found in Buffer cache then process needs a free buffer to read data from disk. It starts search for free buffer from least recently used end of LRU list. In process of searching, if user process finds dirty block in LRU list it shifts them to Write List. If the process can not find free buffers until certain amount of time then process signals DBWn process to write dirty buffers to disks.

When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.


Size of the Database Buffer Cache :- 

Oracle allows different block size for different tablespaces. A standard block size is defined in DB_BLOCK_SIZE initialization parameter . System tablespace uses standard block size. DB_CACHE_SIZE parameter is used to defiane size for Database buffer cache.

you can also set the size for two additional buffer pools, KEEP and RECYCLE, by setting DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. These three parameters are independent of one another.

Read More

Program Global Area (PGA)

Program Global Area Components

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.

Content of the PGA

The content of the PGA memory varies, depending on whether the instance is running the shared server option. But generally speaking, the PGA memory can be classified as follows.

Private SQL Area

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area. The private SQL area of a cursor is itself divided into two areas whose lifetimes are different: The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed. The run-time area, which is freed when the execution is terminated

Cursors and SQL Areas

The application developer of an Oracle precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared SQL areas. The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50

PGA Memory Management for Dedicated Mode

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.

Related Post :- 

• Introduction to an Oracle Instance 
• System Global Area (SGA)
• Dynamic SGA

Read More

SGA_TARGET Initialization Parameter

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)
• Shared pool (SHARED_POOL_SIZE)
• Large pool (LARGE_POOL_SIZE)
• Java pool (JAVA_POOL_SIZE)
• Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

• Log buffer
• Other buffer caches, such as KEEP, RECYCLE, and other block sizes
• Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

Property                         Description

Parameter type                Big integer

Syntax  SGA_TARGET     integer [K | M | G]

Default value                     0 (SGA autotuning is disabled

Read More

SGA_MAX_SIZE Initialization Parameter

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter. If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

Parameter                         Description

DB_CACHE_SIZE              The size of the cache of standard blocks.

LOG_BUFFER                    The number of bytes allocated for the redo log buffer.

SHARED_POOL_SIZE      The size in bytes of the area devoted to shared SQL and PL/SQL statements.

LARGE_POOL_SIZE         The size of the large pool; the default is 0.

JAVA_POOL_SIZE            The size of the Java pool.

Related Post :-

• System Global Area (SGA)
• Program Global Area (PGA)
• SGA_TARGET initialization Parameter  

Read More

Dynamic SGA and SGA Granules

A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. With the dynamic SGA infrastructure, the size of the buffer cache, the shared pool, the large pool, and the process-private memory can be changed without shutting down the instance. Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE. If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted at initialization time, then the setting in the initialization parameter file is ignored.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically, because portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters most affect SGA size:

DB_CACHE_SIZE: The size of the cache of standard blocks.

LOG_BUFFER: The number of bytes allocated for the redo log buffer cache.

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.

LARGE_POOL_SIZE: The size of the large pool; the default is zero

JAVA_POOL_SIZE  :- The size of the Java pool.

Dynamic SGA Granules

With dynamic SGA, the unit of allocation is called a granule. Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and free SGA space in units of granules. Oracle tracks SGA memory use in integral numbers of granules, by SGA component. All information about a granule is stored in a corresponding granule entry. Oracle maintains the state of each granule in the granule entry and the granule type.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, you will actually be allocated 12 MB.

Related post:

• System Global Area (SGA)
• Program Global Area (PGA)
• SGA_MAX_SIZE Initialization Parameter
  

Read More

System Global Area (SGA)

 The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle instance. All Oracle processes use the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle  initialization parameters.

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. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:

Database buffer cache

Redo log buffer

Shared pool

Java pool

Large pool

Data dictionary cache

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. 

Related posts:- 

• Introduction to an Oracle Instance 
• Program Global Area (PGA)
• Dynamic SGA

Read More

Instance Memory Structure

The size of these structures affects the performance of the Oracle database server and is controlled by initialization parameters. These initialization parameters can be categorized as memory parameters. When a database is created with DBCA, the memory parameters are automatically set to optimal values based on your specification of the database workload. However, as your database usage expands, you might find it necessary to alter the settings of the memory parameters. Oracle provides alerts and advisors to identify memory sizing problems and to help you determine appropriate values for memory parameters. Oracle’s memory structure consists of two memory areas known as:

• System Global Area (SGA): Allocated at instance startup, and is a fundamental component of   an Oracle Instance

• Program Global Area (PGA): Allocated when the server process is started

Read More

Setting Initial Database ArchiveLog Mode

You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode. If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archive log files

Changing the Database Archiving Mode

To change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause. To change the archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).

The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG:

1) Shut down the database instance

 SHUTDOWN

 An open database must first be closed and any associated instances shut down before you can switch   the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.

2) Back up the database.

Before making any major change to a database, always back up the database to protect against any problems. This will be your final backup of the database in NOARCHIVELOG mode and can be used if something goes wrong during the change to ARCHIVELOG mode

3) Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archive log files

4) Start a new instance and mount, but do not open, the database.

STARTUP MOUNT

To enable or disable archiving, the database must be mounted but not open.

5) Change the database archiving mode. Then open the database for normal operations.

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

6) Shut down the database.

SHUTDOWN IMMEDIATE

7) Back up the database.

Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode.

Read More

ArchiveLog Mode

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

•  A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure. 
• If you keep an archived log, you can use a backup taken while the database is open and in normal system use.
• You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. 

If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database is in NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode

Related Post :- Setting the Initial Database Archiving Mode

Read More