TNSLSNR : This is a generic listener for stating and stopping oracle.
Types of Processes :-
A database instance contains or interacts with the following types of processes:
1. Client processes run the application or Oracle tool code.
2. Oracle processes run the Oracle database code. Oracle processes including the following subtypes:
1. 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.
2. Server processes perform work based on a client request. For example, these processes parse SQL queries, place them in the shared pool, create and execute a query plan for each query,and read buffers from the database buffer cache or from disk.
Server processes, and the process memory allocated in these processes, run in the instance. The instance continues to function when server processes terminate.
3. Slave processes perform additional tasks for a background or server process.
Overview of Client Processes
When a user runs an application such as a Pro*C program or SQL*Plus, the operating system creates a client process (sometimes called a user process) to run the user application. The client application has Oracle Database libraries linked into it that provide the APIs required to communicate with the database.
Connections and Sessions
A connection is a physical communication pathway between a client process and a database instance. A communication pathway is established using available interprocess communication mechanisms or network software. Typically, a connection occurs between a client process and a server process or dispatcher, but it can also occur between a client process and Oracle Connection Manager (CMAN).
A session is a logical entity in the database instance memory that represents the state of a current user login to a database. For example, when a user is authenticated by the database with a password, a session is established for this user. A session lasts from the time the user is authenticated by the database until the time the user disconnects or exits the database application.
A single connection can have 0, 1, or more sessions established on it. The sessions are independent: a commit in one session does not affect transactions in other sessions.
Server Processes
Oracle creates server processes to handle the requests of user processes connected to the instance. In some situations when the application and Oracle operate on the same machine, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the application and Oracle operate on different machines, a user process always communicates with Oracle through a separate server process.
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:
1. Parse and run SQL statements issued through the application,including creating and executing the query plan.
2. Execute PL/SQL code
3. 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
4. Return results in such a way that the application can process the information
Dedicated Server Processes
n dedicated server connections, the client connection is associated with one and only one server process On Linux, 20 client processes connected to a database instance are serviced by 20 server processes. Each client process communicates directly with its server process. This server process is dedicated to its client process for the duration of the session. The server process stores process-specific information and the UGA in its PGA.
Shared Server Processes
In shared server connections, client applications connect over a network to a dispatcher process, not a server process For example, 20 client processes can connect to a single dispatcher process.
The dispatcher process receives requests from connected clients and puts them into a request queue in the large pool The first available shared server process takes the request from the queue and processes it. Afterward, the shared server place the result into the dispatcher response queue. The dispatcher process monitors this queue and transmits the result to the client.
Like a dedicated server process, a shared server process has its own PGA. However, the UGA for a session is in the SGA so that any shared server can access session data
Overview of Background Processes
A multiprocess Oracle database uses some additional processes called background processes. The background processes perform maintenance tasks required to operate the database and to maximize performance for multiple users.
Each background process has a separate task, but works with the other processes. For example, the LGWR process writes data from the redo log buffer to the online redo log. When a filled log file is ready to be archived, LGWR signals another process to archive the file.
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;
Mandatory Background Processes
=========================================
1. Process Monitor Process (PMON):
The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. For example, PMON resets the status of the active transaction table, releases locks that are no longer required, and removes the process ID from the list of active processes.PMON also registers information about the instance and dispatcher processes with the Oracle Net listenerWhen 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):
The system monitor process (SMON) is in charge of a variety of system-level cleanup duties. The duties assigned to SMON include:
1. Performing instance recovery, if necessary, at instance startup. In an Oracle RAC database, the SMON process of one database instance can perform instance recovery for a failed instance.
2. Recovering terminated transactions that were skipped during instance recovery because of file-read or tablespace offline errors. SMON recovers the transactions when the tablespace or file is brought back online.
3. Cleaning up unused temporary segments. For example, Oracle Database allocates extents when creating an index. If the operation fails, then SMON cleans up the temporary space.
4. Coalescing contiguous free extents within dictionary-managed tablespaces. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.
Database Writer Process (DBWn) : The database writer process (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 adequate 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. These additional DBWn processes are not useful on uniprocessor systems. The DBWn process writes dirty buffers to disk under the following conditions:
1. 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.
2. DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread from which instance recovery begins (see "Overview of Checkpoints"). The log position of the checkpoint is determined by the oldest dirty buffer in the buffer cache.
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. By separating the tasks of modifying database buffers, performing scattered writes of dirty buffers to disk, and performing fast sequential writes of redo to disk, the database improves performance.
In the following circumstances, LGWR writes all redo entries that have been copied into the buffer since the last time it wrote:
3. Three seconds have passed since LGWR last wrote.
4. The redo log buffer is one-third full or contains 1 MB of buffered data.
5. DBWn must write modified buffers to disk.
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.
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.
The redo log buffer is circular. When LGWR writes redo entries from the redo log buffer to an online redo log file, server processes can copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the online redo log is heavy.
The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle Database returns a success code to the
committing transaction although the data buffers have not yet been written to disk. The corresponding changes to data blocks are deferred until it is efficient for DBWn to write them to the data files.
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.
Memory Structures : The basic memory structures associated with Oracle Database include:
=================
1. Software code areas
Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from users' programs—a more exclusive or protected location.
2. System global area (SGA)
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
3. Program global area (PGA)
A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process.
Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.
Program Global Areas (PGA)
A program global area (PGA) is a memory region containing data and control information for a single process (server or background). Consequently, a PGA is sometimes called a "process global area." A PGA is nonshared memory area to which a process can write. One PGA is allocated for each server process; the PGA is exclusive to that server process and is read and written only by Oracle code acting on behalf of that process.
A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created, though this varies by operating system and configuration.
Contents of a PGA
The contents of a PGA vary, depending on whether the associated instance is running the multi-threaded server.
Stack Space : A PGA always contains a stack space, which is memory allocated to hold a session's variables, arrays, and other information.
Session Information : If the instance is running without the multi-threaded server, the PGA also contains information about the user's session, such as private SQL areas. If the instance is running in multi-threaded server configuration, this session information is not in the PGA, but is instead allocated in the SGA.
UGA Memory :
he UGA, or User Global Area, is allocated in the PGA for each session connected to Oracle in a dedicated server environment. The PGA is memory allocated at the client to hold a stack which contains all of the session's variables, etc. In a Shared Server environment, Oracle allocates this memory in the Shapred Pool (the shared pool is contained in the SGA), for all sessions. This helps to reduce the PGA (client) memory footprint of Oracle, but will increase the SGA (shared pool) requirements.Private SQL Area Components
The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:
1. The persistent area—This area contains bind variable values. It is freed only when the cursor is closed.
2. The runtime area—Oracle Database creates this area as the first step of an execute request. It contains the following structures:
A. Query execution state information
For example, for a full table scan, this area contains information on the progress of the scan
B. SQL work areas
These areas are allocated as needed for memory-intensive operations like sorting or hash-joins. More detail is provided later in this section.
3. For DML, the run-time area is freed when the statement finishes running. For queries, it is freed after all rows are fetched or the query is canceled.
SQL Work Areas
SQL work areas are allocated to support memory-intensive operators such as the following:
1. Sort-based operators (order by, group-by, rollup, window function)
2. Hash-join
3. Bitmap merge
4. Bitmap create
For example, a sort operator uses a work area (sometimes called the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (also called the hash area) to build a hash table from its left input. If the amount of data to be processed by these two operators does not fit into a work area, the input data is divided into smaller pieces. This enables some data pieces to be processed in memory while the rest are spilled to temporary disk storage to be processed later. Although bitmap operators do not spill to disk when their associated work area is too small, their complexity is inversely proportional to the size of their work area. Thus, these operators run faster with larger work area.
The System Global Area (SGA) and the set of database processes constitute an Oracle Database instance. Oracle Database 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 database background processes and all server processes that execute on behalf of users can read information contained within the instance's SGA, and several processes write to the SGA during database operation.
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. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.
Database Buffer Cache
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.
Organization of the Database Buffer Cache :
he 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 free buffers, pinned buffers, 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 Database 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.
The first time an Oracle Database user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data.
Accessing data through a cache hit is faster than data access through a cache miss.
Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.
If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.
If an Oracle Database user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.
The LRU Algorithm and Full Table Scans :
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.
You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.
Redo Log Buffer
The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. 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.
Redo entries are copied by Oracle Database processes from the user's memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.
Shared Pool
The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures
Library Cache :
The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.
logistical processing steps due to the parallel processing that we did not have before.
If you execute a statement in parallel, the Oracle Database will parallelize as many of the individual steps as possible and reflects this in the execution plan. If we were to re-execute the two statements above in parallel we could get the following execution plans1.
These plans look quite a bit different than before, mainly because we are having additionalSQL parallel execution in the Oracle database is based on a few fundamental concepts. The
SQL parallel execution in the Oracle Database is based on the principles of a coordinator (often The QC is easily identified in the parallel execution plans above as 'PX COORDINATOR' The process acting as the QC of a parallel SQL operation is the actual user session process itself.
