Oracle background processes background process

Oracle background processes background process

Oracle process type:
For the database process, the database instance contains or interacts with it.
ORACLE processes are divided into client processes (ORACLE processes), background, server, and slave processes (slave ). The server process executes client-based requests.
For example, the server process parses SQL queries and stores these query statements in the shared pool to create and execute a query plan for each query. And read data from the buffer cache or disk.
 
The process structure depends on the operating system and the optional ORACLE database mode.
Service (server) processes can be divided into exclusive (dedicated) and shared (shared) services.
Process Structure in exclusive mode

 
Process Structure in Shared Mode

View the PROCESS information in the V $ PROCESS view:
Col addr for a16
Col spid format a8 -- System Process
Col stid format a8 -- System thread
Col usrname for a20
Col program for a25
Select addr, pid, spid, stid, usrname, program from v $ process order by stid;

Client process:
Ps-ef | grep-e sample-e sqlplus | grep-v grep

The following commands on the client and server display different results. The client only displays the sqlplus process. The server displays as a non-local connection.
 
About session connection:
A scenario is automatically created when ORACLE autotrace is used to collect statistics of executed statements.
SQL> select sid, serial #, paddr from v $ session where username = user;
Sid serial # PADDR
------------------------------------
96 3 100000001f051c858
SQL> set autotrace on statistics; --- tracks SQL statement execution plans and creates an additional scenario.
SQL> select sid, serial #, paddr from v $ session where username = user;
Sid serial # PADDR
------------------------------------
96 3 100000001f051c858
98 5 100000001f051c858

SQL> SELECT PROGRAM FROM V $ PROCESS WHERE ADDR = hextoraw ('00000001f051c858 ');
PROGRAM
---------------------------------------------------------------------
Oracle @ 021Y-SH-BKAP (TNS V1-V3)
Background Process
1234 SELECT PNAME
From v $ PROCESS
WHERE PNAME IS NOT NULL
Order by pname;

Background processes can be divided into mandatory (mandatory) processes, Optional (Optional) processes, and Slave (Slave) processes.
Mandatory processes include:
PMON: monitors other background processes. When a service or scheduling process is abnormal, it is terminated. PMON is used to clear and release the data buffer cache and resources used by client processes. For example, PMON resets the status of the transaction table, releases the idle lock, and removes the idle process ID.
The LREG instance dynamically registers the listening process. When the instance starts, the LREG process determines whether the listener is running. This process is introduced in 12c.

The SMON system monitoring process is responsible for cleaning multiple system levels, including:
1. Restore the instance.
2. Restore the transaction that is terminated due to a file read or tablespace offline error.
3. Clear temporary unused segments.
4. Use the tablespace dictionary management to merge consecutive idle intervals.
SMON regularly checks whether it is needed and calls SMON when other processes need it.

The DBW database write process writes the modified content in the Buffer cache to the data file. Generally, a DBW process can meet the write requirements of the system or enable multiple processes to improve performance. Range from DBW0-9, DBWa-z, DBW36-99
Modify the DBW process count parameter db_writer_processes, which is a dynamic parameter and takes effect after restart.
1 show parameter db_writer_processes

The following two conditions trigger the write operation of the DBW process:
1. When the service process scans the buffer pool and the available buffers cannot be found within the threshold value, DBW is triggered to asynchronously write dirty buffers to the disk.
2. DBW regularly writes buffers and triggers checkpoints. These checkpoints are the starting point of Strength Recovery in the redo thread. The position of the checkpoint of the record depends on the earliest dirty buffer.
In most cases, the data blocks written to the disk by DBW are hashed. This speed is slower than that of sequential data writing. It is executed through LGWR and DBW.
Multiple Parallel writes to improve efficiency. The number of written blocks is determined by the operating system.

LGWR this process is used to manage online redo log buffer. LGWR records part of the buffer to the redo log. Detach and modify the buffer operation. Writes dirty buffer to the disk in a distributed manner, and writes redo to the disk in a fast and continuous manner.
In the following cases, LGWR writes all the modified buffers after the last write operation.
1. the user submits the transaction (commit command ).
2. online redo log switching occurs.
3. It takes another three seconds to write data from the last LGWR.
4. 1/3 or 1 m cached data in the redo log.
5. DBW must write the modified buffer to the disk.
Before DBW writes dirty data, the database must write the redo records related to the changed buffer to the disk (write-ahead protocol). If these redo records are not written, DBW sends a signal to the LGWR process and writes dirty data to the disk after the LGWR Process completes recording writing.
 
CKPT this process is mainly used to update the checkpoint information of the control file and data file header, and trigger the DBW write operation. The checkpoint information includes the checkpoint count point, SCN, and the position recovery start point. Its working mode is shown in:

 
MMON/MMNL is related to AWR (Automatic Workload Repository) tasks. For example, when the calculation result exceeds the threshold value, MMON records the status and creates a status snapshot to capture the statistics of recent SQL object changes. MMNL writes the ASH (Active Session History) state in the SGA buffer to the disk and writes it when the ASH buffer is full.

RECO
In distributed databases, the RECO (recoverer process) automatically handles errors in distributed transactions, and the RECO of a node automatically connects to an uncertain Distributed Transaction in other databases. When RECO is reconnected, all uncertain transactions are automatically resolved and the rows related to these uncertain transactions in the transaction table suspended in each database are removed.
 
Optional (Optional) process:
When redo log switching occurs in ARCn, the archiving process copies online redo logs to offline storage. This process can also collect and redo transaction data and transmit it to the standby database application. The ARCn process is enabled only when the database is in archive mode and is enabled automatically.
 
CJQ0 and Jnnn
The working queue process is running, and Oracle dynamically manages the queue process. Therefore, when a request occurs, the working queue client is allowed to use multiple working queue processes. When these processes are idle, the database uses a new process to release resources.
A dynamic working queue process can run multiple jobs at a given interval. These event sequences are as follows:
1. CJQ0 when the database schedules tasks, the JOB coordination process starts and closes automatically. The JOB coordination process regularly selects jobs for execution from the JOB $ table, and the new jobs are sorted by time.
2. The work coordination process dynamically generates a work queue to execute the work from the process (Jnnn.
3. The work queue process executes a job selected by CJQ0. Each work queue process only executes one job until the job is completed.
4. After a process completes a single job, it will execute the next job. If there is no executable plan, the process is in sleep state.
The job_queue_processes parameter is used to specify the maximum number of working queue processes.
 
FBDA
SMCO