Shulou(Shulou.com)06/01 Report--

Oracle interview Treasure Book-process structure

Does the Oracle database run in a single-process or multi-process mode?

Under the Windows platform, the Oracle database runs in a single-process (oracle.exe) multi-thread mode.

Under the Linux/Unix platform, the Oracle database usually runs in a multi-process manner.

With the 12c release, Oracle has made some changes on the Unix/Linux platform, introducing a multithreaded mode.

This feature is controlled by the threaded_execution parameter, which controls the database to run in multiprocess mode or multithreaded mode, which is false by default. The database runs in a multi-process manner.

How to understand processes and threads?

Process is not only the basic unit of resource allocation, but also the basic unit of scheduling. A thread is the smallest unit that performs operations in a process, that is, the basic unit that performs processor scheduling. If a process is understood as a task logically completed by the operating system, then a thread represents one of the many possible subtasks to complete the task. A thread can only belong to one process, and a process can have multiple threads, but at least one thread. Threads are the smallest execution and scheduling units recognized by the operating system.

When he saw Zhihu, an author named Nanli replied that it was particularly novel:

Open a QQ, start a process; open a Thunderbolt, start a process. In this process of QQ, a thread is opened for transferring text, a thread for transmitting voice, and another thread for pop-up dialog boxes. So running some software is tantamount to starting a process. In the process of running the software (in this process), multiple work supports to complete the running of the QQ, so the "multiple jobs" each have a thread. So a process manages multiple threads. Popular saying: "the process is the parents, in charge of many thread sons."

What types of processes are there in the Oracle database?

1 customer process

2 Service process

3 background process

Please introduce the client process, server process and background process respectively.

1 customer process

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 will connect to the Oracle database to provide the api needed to communicate with the database.

The customer process differs from the Oracle process that interacts directly with the instance in important respects.

Oracle processes that serve client processes can read and write SGA, while client processes cannot. Client processes can run on hosts other than the database host, while Oracle processes cannot.

For example, suppose a user on the client host starts SQL*Plus and then connects to the database sample on another host over the network when the database instance is not started:

SQL > CONNECT SYS@ cjc AS SYSDBA

Enter password: *

Connected to an idle instance.

On the client host, the search for the sqlplus or sample process shows only the sqlplus client process:

On the database host, the search for the sqlplus or sample process shows a server process with a non-local connection, but no client process:

2 Service process

The Oracle service process is used to process requests from client processes that connect to the instance.

Dedicated server processes (dedicated server), each serving a client process.

Shared server processes (shared server), each serving multiple client processes.

In a shared server connection, the client application connects to the dispatcher process over the network, not the 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 a large pool. The first available shared server process takes the request from the queue and processes it. The shared server then puts the results into the dispatcher response queue. The dispatcher process monitors this queue and transmits the results to the client.

3 background process

The background process performs the maintenance tasks required to operate the database and maximizes performance for multiple users.

Each background process has a separate task, but can work with other processes. For example, the LGWR process writes redo data from the redo log buffer to the online redo log. When a full redo log file is ready for archiving, LGWR signals another process archiving process to archive the redo log file.

The Oracle database automatically creates background processes when the database instance starts. An instance can have many background processes, but not all background processes always exist in each database configuration. The following query lists the background processes running on the database:

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

There are three types of background processes:

3.1 Force the background process

3.2 optional background processes

3.3 Slave background process

Force the background process:

3.1.1 process Monitoring process (PMON) group

3.1.2 process Manager (PMAN)

3.1.3 listener Registration process (LREG)

3.1.4 system Monitoring process (SMON)

3.1.5 Database write process (DBW)

3.1.6 Log write process (LGWR)

3.1.7 checkpoint procedure (CKPT)

3.1.8 manageability monitoring processes (MMON and MMNL)

3.1.9 Recycler process (RECO)

3.1.1 process Monitoring process (PMON) group

The PMON group includes PMON, cleanup main process (CLMN), and cleanup worker process (CLnn). These processes are responsible for monitoring and cleaning up other processes.

The PMON group monitors the cleanup of the buffer cache and the release of resources used by client processes. For example, the PMON group is responsible for resetting the state of the active transaction table, releasing locks that are no longer needed, and removing the process ID that terminates the process from the list of active processes.

The database must ensure that resources held by terminated processes are released so that they can be used by other processes. Otherwise, the process may be blocked or stuck in contention.

Process Monitor process (PMON)

The process Monitor (PMON) detects the termination of other background processes. If the server or scheduler process terminates abnormally, the PMON group is responsible for performing process recovery. There may be several reasons for process termination, including operating system termination commands or changing system termination session statements.

Function:

1. Perform cleanup after other processes fail: roll back things, release locks, and release other resources.

Before the 12C version, PMON was responsible for the tasks related to database instance listening registration.

3. Check the idle connection time of the session.

Trigger condition: it will be awakened regularly, and other processes will wake it up actively.

Clean up the main process (CLMN)

PMON delegates cleanup to the cleanup master process (CLMN). The task of detecting abnormal termination is still done by PMON.

CLMN periodically performs cleanup of terminated processes, terminated sessions, transactions, network connections, idle sessions, detached transactions, and detached network connections that have exceeded their idle timeouts.

Clean up worker process (CLnn)

CLMN delegates cleanup to the CLnn worker process.

The CLnn process helps clear terminated processes and sessions. The number of auxiliary processes is proportional to the amount of cleanup work to be completed and the current cleanup efficiency.

The cleanup process may be blocked, preventing it from continuing to clean up other processes. In addition, if multiple processes need to be cleaned, the cleanup time can be long. For these reasons, the Oracle database can use multiple worker processes to perform cleanup in parallel, thereby alleviating slow performance.

The V$CLEANUP_PROCESS and V$DEAD_CLEANUP views contain metadata about CLMN cleanup. The V$CLEANUP_PROCESS view contains a row for each cleanup process. For example, if V$CLEANUP_PROCESS. If the state is busy, the process is currently being cleaned up.

Database resource isolation

If the process or session terminates, the PMON group releases the resources it holds to the database. In some cases, the PMON group can automatically isolate corrupted, unrecoverable resources so that the database instance is not forced to terminate immediately.

The PMON group will continue to perform as much cleanup as possible on processes or sessions that hold isolated resources. The V$QUARANTINE view contains metadata, such as the resource type, the amount of memory consumed, the Oracle errors that caused isolation, and so on.

3.1.2 process Manager (PMAN)

Process Manager (PMAN) monitors multiple background processes, including shared servers, pool servers, and job queue processes.

PMAN monitors, starts, and stops the following types of processes:

1 scheduler and shared server process

2 connection agent and pool server process pooled server process for database resident connection pooling

3 Job queue processes of the work queue

4 restartable background processes

3.1.3 listener Registration process (LREG)

12C begins to introduce the LREG process, which is responsible for the tasks related to database instance listening registration. In versions prior to 12c, the PMON process was responsible for this part of the work. The listener registration process (LREG) registers information about the database instance and the scheduler process with the Oracle Net listener. When the instance starts, LREG polls the listener to determine if it is running. If the listener is running, LREG will pass the relevant parameters. If it is not running, LREG tries to contact it on a regular basis.

3.1.4 system Monitoring process (SMON)

The system Monitoring process (SMON) is responsible for various system-level cleanup tasks.

The responsibilities assigned to SMON include:

1 perform instance recovery, Roll Forward to the state in which the instance is shut down, and redo it using the log process after the last checkpoint. In the Oracle RAC database, the SMON process of a database instance can perform instance recovery on failed instances.

2 restore terminated transactions that were skipped due to offline errors in text data files or tablespaces during instance recovery. When the tablespace or file comes back online, SMON resumes the transaction.

(3) clean up unused temporary segments and free up space. For example, the Oracle database allocates extents when the index is created. If the operation fails, SMON cleans up the temporary space.

4 merge spaces and merge contiguous free areas in dictionary-managed table spaces.

3.1.5 Database write process (DBW)

This process is responsible for writing dirty data from buffer cache to disk. The DBW process writes the least recently used buffer to disk according to the LRU algorithm.

Although a database write process (DBW0) is sufficient for most systems, if the system modifies a lot of data, you can configure other processes dbw1 to DBW9, DBWa to DBWz, and BW36 to bw99 to improve write performance. These additional DBW processes are of no use on uniprocessor systems.

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

1 when the server process cannot find a clean reusable buffer after scanning the threshold number of buffers, it sends a write signal to DBW. When performing other processing, DBW writes dirty buffers to disk asynchronously as much as possible.

2 when the database performs a checkpoint operation. DBW periodically writes buffers to advance checkpoints, which are where instance recovery begins in the redo thread. The log location of the checkpoint is determined by the oldest dirty buffer in the buffer cache.

3 when modifying tablespaces or data files offline.

Wake up every three seconds to check the number of dirty blocks and decide whether to perform a write operation.

In many cases, blocks written by DBW are scattered across the disk. As a result, writes tend to be slower than sequential writes performed by LGWR. DBW performs multi-block writes where possible to improve efficiency. The number of blocks written in multiple block writes varies from operating system to operating system.

3.1.6 Log writing process (LGWR)

It is responsible for writing redo log buffer data to the online redo log, which records all DML and DDL operations for instance and media recovery.

Trigger condition:

1 the user commits the transaction.

2 online redo log switch occurred.

3 LGWR it has been three seconds since I last wrote.

4 the redo log buffer has reached 1/3, or contains 1 MB of buffered data.

5 DBWR process trigger: the DBWn view writes dirty data blocks to disk, first detects whether his relevant redo records are written to the online log file, and notifies the LGWR process if not. In oracle, it is called early write mechanism (write ahead): redo records are written to disk before data records.

3.1.7 checkpoint process (CKPT)

Responsible for updating the checkpoint information of the control file and data file header, including the checkpoint location, the SCN number, and the location where the online redo log starts to recover. Checkpoints may be initiated in the following situations:

(1) when the log switches alter system switch logfile.

(2) directly use the alter system checkpoint command.

(3) when the database uses immediate, Transaction, Normal or the option Shutdown database.

(4) start and end backup tablespaces (ALTER TABLESPACE BEGIN BACKUP).

(5) switch the tablespace or data file to read-only or offline.

(6) when the DBW process writes, but at this time only the checkpoint information is written to the control file, not to the header of the data file.

(7) when the delay of LOG_CHECKPOINT_TIMEOUT is reached.

(8) it is determined according to the setting of parameter FAST_START_MTTR_TARGT.

If the recovery time (estimated_mttr) required for the Dirty Buffer generated in memory reaches the time specified by the FAST_START_MTTR_TARGET, the checkpoint process is triggered.

Notifies the DBWR process that dirty data will be written to the data file in checkpoint queue order, shortening the distance between the last checkpoint location and the Redo log and reducing the time required for instance recovery.

3.1.8 manageability monitoring process (MM ON and MMNL)

The manageability monitoring process (m m on) performs many tasks related to automatic workload repositories (AWR).

For example, when a measure violates its threshold, m mon writes to take a snapshot and statistics of the recently modified SQL object.

The manageability Monitor streamlining process (MMNL) writes statistics to disk from the active session History (ASH) buffer in SGA. MMNL writes to disk when the ASH buffer is full.

3.1.9 Recycler process (RECO)

RECO is a process that handles process failures in distributed transactions.

The node's RECO process automatically connects to other databases that involve suspicious distributed transactions. When RECO reestablishes the connection between databases, it automatically parses all suspicious transactions, deleting any rows corresponding to resolved transactions from the pending transaction table of each database.

Optional background process

An optional background process is any background process that is not defined as mandatory.

Most optional background processes are task-specific or feature-specific. For example, background processes that support Oracle ASM are only available when this feature is enabled.

This section describes some common optional processes:

3.2.1 Archiving process (ARCn)

3.2.2 Job queue processing processes (CJQ0 and Jnnn)

3.2.3 Flash return file (FBDA)

3.2.4 Space Management Coordinator (SMCO)

3.2.1 archiving process (ARCn)

After the redo log switch occurs, the archiving process (ARCn) copies the online redo log files to the archive log.

In DG, these processes can also collect transaction redo data and transfer it to an alternate database destination. The ARCn process exists only if the database is in ARCHIVELOG mode and automatic archiving is enabled.

3.2.2 Job queue processes (CJQ0 and Jnnn)

The queue process runs user jobs, usually in batch mode. A job is a user-defined task that is scheduled to run one or more times.

For example, you can use the job queue to schedule long-running updates in the background. Given a start date and interval, the job queue process attempts to run the job the next time the interval occurs.

The Oracle database dynamically manages job queue processes, allowing job queue clients to use more job queue processes as needed. When new processes are idle, the database releases the resources they use.

The dynamic job queue process can run many jobs concurrently at a given interval. The order of events is as follows:

3.2.3 Flash return process (FBDA)

Flashback data archiving is one of the powerful new features of Oracle Database 11g. It can track all data stored in the database transparently in a safe and effective way, and there is no limit of retention period. This feature is easy to configure and has efficient storage and performance. Flashback data archiving does not depend on UNDO, but uses "AS OF" flashback SQL statements to view previous data at a certain point in time to prevent unexpected data updates and deletions (such as user erroneous actions) or malicious data corruption.

Flashback the data archiving process, archiving the history lines of the tracked table into the flashback data archive file. When a dml transaction occurs on the tracked table, the front image of the stored row of the process is mirrored into the flashback data archive. It also holds the metadata for the current row.

3.2.4 Space Management Coordinator (SMCO)

11g introduces the extent pre-allocation feature, which is taken care of by SMCO (space management coordinator). Its dynamic spawn child process Wnnn is used for space allocation and recovery.

The SMCO process coordinates the execution of various tasks related to space management.

1Extent pre-allocation

Autoextend must be enabled for data files. According to historical information, SMCO extends evenly among all data files that have not yet reached maxsize in the table space.

2 recovery temporary section

3Securefile lob pre-expansion / recycling

This feature sometimes produces unexpected results, such as data files being extended to maxsize (but the actual data is not that large)

AUTOEXTEND Grows To Full Size Without Reason [ID 1459097.1]

Why The Data Files Got Extended Up Over The Weekend [ID 1538442.1]

Slave process

Slave processes are background processes that perform work on behalf of other processes.

This section describes some of the slave processes used by the Oracle database.

I / O slave process

The I slave O process (Innn) simulates systems and devices that do not support asynchronous Imax O.

In asynchronous IPUP O, there is no time requirement for the transfer, which allows other processes to start before the transfer is complete.

For example, suppose an application writes 1000 blocks to the disk of an operating system that does not support asynchronous Ihop O. Each write occurs sequentially and waits for confirmation of the success of the write. With asynchronous disks, applications can bulk write blocks and perform other work while waiting for a response from the operating system that has written all blocks.

A process monitors multiple slave processes in order to emulate asynchronous IBO. The caller process assigns work to each slave process, which waits for each write to complete and reports to the caller after completion.

In a real asynchronous Iamp O, the operating system waits for it to complete and reports to the process, while in the simulated asynchronous Iamp O, it waits from the operating system and reports to the caller.

The database supports different types of Imax O slave, including:

The recovery Manager (RMAN)'s iUnix O slave

When using RMAN to back up or restore data, you can use Icano slave devices for disk and tape devices.

Database writer slaves

When a computer has one CPU, it is impossible to write processes using multiple databases, so the database can distribute it across multiple slave processes. DBW is the only process that scans the buffer cache LRU list for blocks to be written to disk. However, the Ipicuro of these blocks is executed from the Ibig O.

Parallel execution (PX) server process

In parallel execution, multiple processes run a SQL statement at the same time.

By assigning work to multiple processes, the Oracle database can run statements faster. For example, four processes deal with four different quarters of the year, rather than one process dealing with all four quarters alone.

Parallel execution contrasts with serial execution, in which a single server process executes all the processing required by executing SQL statements sequentially. For example, to perform a complete table scan, such as SELECT * FROM employees, a server process performs all the work.

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.