Oracle Process Structure

Oracle Process Structure

A process is created dynamically and disappears after the task is completed. A program is a static entity and can be copied and edited. The process emphasizes the execution process, while the program is only an ordered set of commands. The process is in the memory and the program is in the external memory.

ORACLE is divided into user processes and ORACLE processes.

LUser Process

When a user executes an ORACLE database-based application, the client creates an ORACLE user process (for example, the running sqlplus and OEM are called user processes ).

User processes log on to the ORACLE server through a connection, and a corresponding server process is generated on the ORACLE server (the Oracle server's "Foreign Ministry "); the server process indicates that the user process running on the client communicates with the server.

LORACLE Process

Oracle processes can be divided into server processes and backend process server processes.

2. server process

It mainly serves user processes on the client. Server processes can also be divided into dedicated server processes (providing services for only one user process) and Shared Server Processes (providing services for multiple user processes ).

2. server process tasks:

A. parse and execute the SQL statement submitted by the user;

B. Search the database cache in the SGA area to determine whether to read data files. If the data block is not in the database cache of the SGA region, it will be read from the data file;

C. Return the structure data of the query execution to the user.

2. BackgroundProcess)

Server processes are supported by background processes. The main tasks of background processes are:

A. Perform I/O operations between memory and external memory;

B. Monitor the status of each process;

C. Coordinate the tasks of various processes;

D. Maintain system performance;

E. ensure system reliability;

You can use the following query to check the number of background processes started in the database and their names.

SQL> SELECT * FROM V $ BGPROCESS

Common background processes:

N SMON (SystemMonitor) system monitoring process:

Ü startup condition: It is started with the ORACLE database system. During running, ORACLE can wake up the SMON process to check whether it needs to perform the work it is responsible; if any other process needs to use the SMON process function, it will wake up the SMON process at any time.

Ü role:

A. Recover the database when the routine is started. If the database is not shut down normally, the SMON process will automatically read the redo log file when the routine is started next time, recover the database (that is, execute operations such as writing committed transactions into the data file and rolling back uncommitted transactions );

B. Clear the temporary segments in the allocated but no longer used tablespace (if there are a large number of Disk Partitions In the tablespace, clearing will take a lot of time and affect the performance of the database at startup ).

C. Merge the adjacent idle disk areas in each tablespace Based on Data dictionary management to make it easier to allocate space.

Ü Note: If the storage parameter PCTINCREASE of a tablespace is set to 0, the SMON process will not merge the idle disk areas in the tablespace.

N PMON (Process Monitor) Process Monitoring;

Ü startup conditions:

A. When A user process needs to be started, cleared, or failed (that is, the connection to the ORACLE database crashes, suspends, or ends abnormally ); the cleanup operation also includes the orphan sessions left by user processes that are not interrupted abnormally, roll back uncommitted transactions, and release the locks, SGA, and PGA resources occupied by sessions.

B. During running, ORACLE can wake up the PMON process to check whether it needs to perform the work it is responsible for. If any other process needs to use the PMON process function, it will wake up the PMON process at any time.

C. PMON is started in a separate database, while the distributed database uses the RECO recovery process to complete the PMON process tasks.

Ü role:

A. Recover user processes or server processes that are interrupted or failed;

B. Clear orphan sessions left by abnormal interrupted processes;

C. Roll Back uncommitted transactions, reset the status of active transactions, and delete the user process ID (ID) from the system active processes );

D. Release various resources occupied by the process, and automatically roll back the transaction to solve the deadlock, releasing the table and row locks owned by the user;

E. Regularly check server processes and scheduling processes. If they are suspended due to exceptions due to failure, they will be restarted automatically.

N DBWn (Database Writer) Database write process;

Ü startup Conditions

A. When the number of DIRTY cache blocks in the DIRTY list reaches A certain value (that is, half of the value specified by the initialization parameter db_block_write_batch ), the DBWn process writes dirty cache blocks to data files (ORACLE also modifies the synchronization sequence number SCN in the header of the control file and data file, and records the structure and status of the current database, to ensure synchronization between physical files in the database );

B. When you find a certain number of idle cache blocks in the LRU list (that is, the value specified by the initialization parameter db_block_max_scan), but it is not enough, to obtain more idle cache blocks, the DBWn process writes dirty cache blocks to data files;

C. When a checkpoint LGWR process occurs, it notifies the DBWn process to perform write operations;

D. When a tablespace is in the backup mode, offline, or read-only state;

E. When the DBWn process times out (Time_out), that is, it is not started in about 3 seconds.

Ü role:

A. Manage High-Speed buffers to ensure that the server process can always find idle cache blocks to save data blocks read from data files;

B. Write DIRTY cache blocks in the DIRTY list to the data file to obtain more idle cache blocks;

C. Use the LRU algorithm to hit the recently used cache block and keep it in the LRU list without re-reading the data file;

D. The DBRn process optimizes disk I/O operations by writing delayed data, while the server process only makes modifications in the data cache.

Ü remarks:

A. It is recommended that the number of DBWn processes not exceed the number of CPUs, because each processor can only run one DBWn process at the same time;

B. By default, ORACLE starts a DBWn process, which is limited by the initialization parameter DB_WRITER_PROCESS. You can set the initialization parameter DB_BLOCK_CHECKPOINT_BATCH to set the maximum number of dirty cache blocks written by the DBWn process whenever a Check Point occurs. Increasing this value can prolong the interval between DBWn processes.

N LGWR (Log Writer) Log writing process;

Ü startup conditions:

A. the user process submits the current transaction using the COMMIT statement;

B. When the cache of redo logs is full 1/3 or contains 1 MB of redo information;

C. The DBWn process needs to clear dirty cache blocks for the checkpoint to write dirty cache blocks into data files;

D. The LGWR process times out, that is, when the LGWR process is started in about 3 seconds.

Ü role:

A. manages the redo log high-speed buffer, which writes redo log records from the cache area to redo log files;

B. If the CKPT Checkpoint Process is not started, the LGWR Process completes the checkpoint execution task.

Ü remarks:

A. Each routine has only one LGWR process;

B. The LGWR process is started before the DBWn process.

N CKPT (CheckPoint) CheckPoint Process

Ü startup conditions:

A. When the database is closed;

B. When a redo log file is fully written, log switching is generated;

C. Meet the parameter LOG_CHECKPOINT_TIMEOUT (specify the time interval between checkpoints (in seconds ));

D. The LOG_CHECKPOINT_INTERVAL parameter specifies a checkpoint when a certain number of operating system data blocks (non-ORACLE data blocks) are written to the redo log file)

Ü role:

A. trigger the DBWn process to write all the modified data blocks (dirty cache blocks) after the last checkpoint to the data file;

B. Maintain data synchronization between the database high-speed buffer zone and data files (use the latest checkpoint information to update the file headers of control files and database files );

C. The checkpoint information will be used during database restoration. When the SMON process recovers the database, the SMON determines the checkpoint in the data file. (The transaction numbers in the online redo log file after the file header of the data file and the checkpoint recorded in the control file must be resubmitted to the data file .)

Ü remarks:

A. Do not set unnecessary checkpoints or force unnecessary checkpoints;

B. Shortening the interval between checkpoint execution and shortening the time required for database recovery;

C. If the interval between checkpoint execution is short, too many I/O operations will be generated;

D. Make sure that the number of operating system blocks set by LOG_CHECKPOINT_INTERVAL matches the size of the redo log file.

Ü ORACLE executes checkpoints of different levels at different times. Therefore, there are four types of checkpoints:

A. Database checkpoint: the database checkpoint is executed every time the log file is switched over. A checkpoint is executed when the NORMAL \ TRANSACTIONAL \ IMMEDIATE option is used to close the database; in this case, the DBWn process writes all dirty cache blocks in the data cache area to the data file.

B. tablespace checkpoint: If a tablespace is set to offline or the remarks are set to BACKUP mode, a tablespace checkpoint is executed; at this time, DBWn only writes dirty cache blocks related to the tablespace in the cache area to the data file.

C. Manual CHECKPOINT: Use the alter system checkpoint statement to manually set a CHECKPOINT.

D. Time checkpoint: The interval at which the checkpoint is executed. You can adjust the parameters (LOG_CHECKPOINT_TIMEOUT and LOG_CHECKPOINT_INTERVAL) to change the interval at which the checkpoint is implemented.

N ARCn (Archiver) archiving process;

Ü startup conditions:

When switching the redo log (it must be in archive mode, and the initialization parameter LOG_ARCHIVE_START is TRUE. If this parameter is set to FALSE in archive mode, when all the redo log files are full, the database will be suspended .)

Ü function: prevents database crashes and cannot be recovered.

Ü remarks:

A. By default, A routine starts only one Archiving process, ARCn. When ARCn is archiving A redo log file, no other process can access this redo log file.

B. To prevent the LGWR process from suspending the database by using the redo log files being archived, The LGWR process will start more archiving processes as needed. ORACLE can enable up to 10 ARCn instances.

C. The initialization parameter LOG_ARCHIVE_DEST specifies the location of the archived log file. LOG_ARCHIVE_FORMAT specifies the naming rules for archived log files.

N RECO (Recoverer) Restoration Process

N LCKn (Lock) Lock Process

N RVWR (Recovery Writer) flash back the Recovery process

N CTWR (Change Tracking Writer) flash back Change Tracking write process

N CJQn (Job Queue Monitor) Job Queue monitoring process

N Jnnn (Job Queue) Job Queue Process

N Dnnn (Dispatcher) scheduling process

N Snnn (Shared Server) Shared Server process

2 Comprehensive Cases

Case: query the ID, name, and description of the background process.

Col spid format a10;

Col name format a10;

Col description formata30;

Select p. spid, B. name, B. description from v $ bgprocess B, v $ process p where p. addr = B. paddr;

Case: Query

Col spid format a10;

Col username formata10;

Col terminalformat a10;

Col program format a10;

Select spid, username, terminal, program from v $ process;

Case: view the user process. Username indicates the user name, and process records the operating system process number.

1) Run cmd, enter sqlplus/nolog, enter conn/as sysdba, and establish connection 1.

2) execute the following command in sqlplus:

Col username formata10;

Col process format a10;

Col machine format a10;

Col program format a10;

Select username, process, machine, program from v $ session where username is not null;

3) Run cmd, enter sqlplus/nolog, conn scott/tiger as sysdba, and establish connection 2.

4) execute the following command in connection 1 created in Step 1:

Select username, process, machine, program from v $ session where username is not null;

5) Compare the preceding two query results.