Oracle structure-instance process structure

The process structure of the Oracle instance here refers to the process in the Unix system. In Windows, Oracle is an operating system service.
An Oracle instance consists of a memory area and background process.
Oracle instances are divided into single-process instances (a process executes all the Oracle code and only allows access by a single user) and multi-process instances (different parts of Oracle code executed by multiple processes, each connected user has a process that can be used by multiple users at the same time ).
In the multi-process structure, there are other background processes, including: dnnn, DBWR, LGWR, ARCH, PMON, SMON, CKPT, RECO, etc. After the database is started, these processes are resident in the operating system. The sum of the number of all processes in Oracle cannot exceed the value set by the PROCESS parameter:
SQL> show parameter process
When an Oracle client application is connected to an Oracle instance, Oracle generates a server process to serve these customers. This process is represented as a thread in widnows. This process performs syntax analysis, compilation, and execution of user SQL statements. If the data block is not in the data buffer zone, then, the data is read from the disk data file into the shared data buffer of the SGA, And the execution result of the SQL statement is returned to the group application. The following statement is used to query user session information in the database.
SQL> select substr (s. sid, 1, 3) sid, substr (s. serial #,) ser, spid, substr (schemaname,) schema, substr (type,) typ, substr (decode (consistent_gets + block_gets), 0, 'none', (100 * (consistent_gets + block_gets-physical_reads)/consistent_gets + block_gets),) "% hit", value cpu, substr (block_changes) bchng from v $ process p, v $ sesstat t, v $ sess_io I, v $ session s where I. sid = s. sid and p. addr = paddr (+) and s. sid = t. sid and t. statistic # = 12;
Result:
Sid indicates the session ID,
Ser indicates the session serial #, and serial # And sid are combined to uniquely represent a session,
Spid indicates the operating system process number,
Schema indicates the database user name,
Typ indicates the process type. use indicates the user process, and bac indicates the background process.
% Hit indicates the data buffer hit rate,
Cpu indicates the time when the cpu is used,
Bchng indicates the number of modified data blocks.

The following describes various background processes.
1. DBWR Process
The DBWR (Database Writer) process writes data in the buffer into a data file and manages the data buffer zone.
When a data in the data buffer is modified and marked as "dirty", the DBWR process is responsible for writing "dirty" data to the disk.
DBWR uses the LRU algorithm to replace data in the data buffer.
DBWR has the following functions:
1) manage the data buffer so that the server process can always find the idle buffer zone
2) write all the data in the modified buffer to the database file
3) use the LRU algorithm to keep recently used blocks in the memory.
4) Optimize disk I/o by writing latency.
In the following cases, DBWR needs to write dirty data to the disk data file:
1) when a server process moves the buffered data block to the "dirty table", the dirty data is expressed to a certain extent. (The critical length is determined by the DB_BLOCK_WRITE_BATCH parameter)
2) When a server process looks for the DB_BLOCK_MAX_SCAN_CNT buffer in the LRU table, no idle buffer is found.
3) Timeout
4) When a checkpoint occurs
In an Oracle instance, multiple DBWR processes can be started. The number of DBWR processes is specified by the DB_WRITE_PROCESSES parameter. The maximum value is 20. The process names are automatically named DBW0, DBW1, and DBW2... DBW19.
SQL> show parameter processes

2. LGWR Process
The LGWR (Log Writer) process writes Log data from the Log buffer to the disk Log file.
For the working time of the LGWR process, refer to the first transaction process I wrote.
The write conditions for LGWR are:
1) commit, that is, commit;
2) When the log buffer reaches 1/3 of the total number;
3) Timeout;
4) when the DBWR process needs to clear the buffer for the check point.
Each Oracle instance has only one LGWR process.
LGWR synchronizes log information to multiple log members in the log file group. If one of the member files in the group is deleted or unavailable, LGWR writes the log information to other files in the group without affecting the database operation.

3. SMON Process
SMON (System Monitor), a System monitoring process that executes instance recovery when the instance is started, and is responsible for clearing temporary segments that are no longer in use.

4. PMON Process
PMON (Process Monitor) is used to Monitor processes and resume processes when a server Process fails. It is responsible for clearing the memory and releasing the resources used by the Process.

5. ARCH Process
ARCH (Archive Process) writes the log information to a disk or tape when the log is full for restoration when the media fails.
Log information is first generated in the log buffer, and then written to the log file group by the log writing process LGWR, the archive process ARCH writes log data from the log file group to the archive log.
In a database instance, up to 10 archiving processes can be started. The process names are named ARC0, ARC1, and ARC2... ARC9: add the LOG_ARCHIVE_MAX_PROCESSES parameter to the parameter file. For instructions on how to enable automatic archiving of databases, see the previous article "physical structure". Here we describe several related parameters:
1) LOG_ARCHIVE_DEST: Specifies the file storage directory for archiving logs.
2) LOG_ARCHIVE_DUBLEX_DEST: used to set the image storage directory for archiving log files. This parameter is supported in oracle 8 and later versions.
3) LOG_ARCHIVE_DEST_n: Used to set more image directories. Among them, 1 <=n <= 10. This parameter has the following three options:
(1) MANDATORY: indicates that the archiving of this directory must be completed before switching.
(2) REOPEN: How long does it take to re-write logs after the archiving directory or media fails? The default time is 300 seconds.
(3) OPTIONAL: Switch whether or not the archiving is complete. This is the default method.
Example:
LOG_ARCHIVE_DEST_1 = 'location =/u01/oracle/archive/
MANDATORY
REOPEN = 500'
LOG_ARCHIVE_DEST_2 = 'location =/u02/oracle/archive/
Optional'
LOG_ARCHIVE_DEST_n is only applicable to oracle 8i and later versions.
4) LOG_ARCHIVE_DEST_STATE_n: Used to invalidate or take effect of the archived log directory. This parameter corresponds to LOG_ARCHIVE_DEST_n. For example:
LOG_ARCHIVE_DEST_STATE_1 = DEFER
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
This parameter can be set online:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER;
This parameter is only applicable to oracle 10 Gb or later versions.

6. CKPT Process
When a CKPT (CheckPoint) process appears, the system modifies the synchronization numbers of all database files and control file headers to ensure Database Synchronization.
The following two parameters affect CKPT:
LOG_CHECKPOINT_TIMEOUT: determines the time interval for generating a checkpoint. The unit is autumn. The default value is 1800;
LOG_CHECKPOINT_INTERVAL: Number of log file blocks to be filled at a checkpoint. The default value is 0, indicating that the block does not work.
The system expresses the database change information with the system change number SCN. The SCN is also called the Check Point number, which is indicated by CHECKPOINT_CHANGE # in the log. It is a data file, log file, synchronization basis of control files, and stored in these three, when the system changes, the check point number will also change.
The main reasons for CKPT generation are:
1) log Switching
2) The interval specified by LOG_CHECKPOINT_TIMEOUT is reached.
3) The number of log blocks specified by LOG_CHECKPOINT_INTERVAL is reached.
4) shut down the database
5) Mandatory DBA generation
6) when the tablespace is OFFLINE

7. RECO Process
The RECO (recovery) process is used in the distributed database system. The RECO process automatically resolves transaction faults in the distributed database.

8. LCKn Process
LCKn is used to block Multiple instances in the Oracle Parallel Server environment. A maximum of 10 processes can be started.

9. Dnnn Process
Dnnn (Despatcher, scheduling) process. Used in the multi-threaded server architecture. For details, see the next Oracle connection configuration structure.

10. SNP Process
SNP is a job process. It is mainly used in distributed databases to automatically refresh snapshots between databases, and to access the DBMS_JOB package to automatically run predefined stored procedures, SQL, and PL/SQL programs. There are two parameters used to control the job Process Plan:
1) JOB_QUEUE_PROCESS: used to set the number of job Processes
2) JOB_QUEUE_INTERVAL: used to set the interval at which job processes are periodically woken up.

Background process tracking information
All commands that modify the database structure are automatically tracked and recorded by the background process. The trace information is automatically stored by the oracle background process and the trace file name is alter_SID. The storage directory of this file is specified by the parameter BACKGROUND) DUMP_DEST.
You can use the tool command TKPROF to format the trace file, for example:
$ Tkprof oralogfile1.log
In addition to tracking background processes, you can also enable the SQL _TRACE parameter to trace user statements. Modify the parameter file:
TIMED_STATISTICS = TRUE
USER_DUMP_DEST = directory name
MAX_DUMP_FILE_SIZE = 5 M
SQL _TRACE = TRUE
Or:
SQL> alter session set SQL _TRACE = TRUE: SQL tracking of the current SESSION.

Lock wait problem and KILL SESSION
To ensure data consistency, the system provides a lock mechanism. The concept of locks can be referred to in Database System introduction. You need to know the users waiting to lock resources:
SQL> select a. username, a. sid, a, serial #, B. id1 from v $ session, v $ lock where a. lockwait = B. kaddr;
To learn about the user processes that lock other users:
SQL> SELECT. USERNAME,. SID, A, SERIAL #, B. ID1 from v $ session a, V $ lock B where B. ID1 IN (select distinct e. ID1 from v $ session d, V $ lock e where d. LOCKWAIT = E. KADDR) and. SID = B. sid and B. REQUEST = 0;

Kill session: SQL> alter system kill session sessionid, serial #