"Turn" Oracle infrastructure cognition-process and logical structure eight mesh 2017-12-17 19:33:21

Original address: https://www.toutiao.com/i6500477672349499917/

I. Process structure of structure processes

Oracle has two types of processes: Server process and background process (server processes and background processes).

1. Server process The Oracle server process is the transaction that handles the connection of the user to the instance.

The tasks are:

(1) Analyze and Execute SQL statements issued by the application.

(2) Read the necessary data from the data file to the shared data area of the SGA area.

(3) Return the necessary information to the application.

2. Background processes The Oracle system uses some additional processes to handle the necessary work of the system, with 5 mandatory processes (Dbwn,lgwr,ckpt,pmon,smon).

(1) The task of the DBWN database writer is to write the modified, changed, revoked (in memory) data block back to the data file. In some operating systems, Oracle can have two BDWN processes.

(2) The LGWR log writer is used to write the process that exists in the SGA area to the log file (Redodlog file) in the Redo log buffer (redo log buffer) log information. Typically, the user's modified values are recorded in the log file first. Wait until a certain time to actually write the result of the modification back to the data file.

(3) CKPT Detection points (check point) when a lookup data request occurs in the system, the system locates the data from the database and stores it in the memory area so that the user can modify the memory area data. A check point occurs when the modified data needs to be written back to the data file to produce an alternating write (Switch) of the redo log. The system writes the information in the in-memory gray data (modified) block back to the disk's data file, and the system also notifies the control file of the redo log. The DBA can change the parameter file in Checkpoint_process true to enable (make valid or invalid) the process.

(4) The Smon System Monitor is a mandatory process that performs recovery work when the database (Database=control file+data file+redolog file) system is started. For example, in parallel server mode (two servers share one disk group), Smon can recover another failed database, and the system switches to another normal server.

(5) The Pmon Process Monitor is used to terminate those users who have failed, release the resources occupied by the user, roll back the current user's execution, reset the lock table and row-level locks occupied by the current user, and release other resources reserved by the current user.

(6) ARCn archiver (Archive) is an optional process that is used when the database system is in archive (ARCHIVELOG) mode.

(7) The RECO Recovery (Recover) distributed database (different locations have different machines and different Oracle systems) is an optional process used for recovery work when data is inconsistent. Before reco resolves the recovery, the marks for the modified data are labeled "suspect".

(8) LCK0 Lock is an optional process when multiple locking processes can occur in parallel server mode to facilitate database communication.

(9) scheduling (dnnn) optional process, used in multi-threaded, that is, for each communication protocol in use (D000,...,DNNN) to create at least one scheduling process, each scheduling process is responsible for from the connected user process to the available server process routing requests. Return the response to the appropriate user process.

(10) The snapshot process (SNPN) snapshot process processes the automatic refresh of the database snapshot and runs the scheduled database process through the Dbms_job package. The Initsid.ora parameter job_queue_process sets the number of snapshot processes, and the parameter job_queue_interval determines the number of seconds that the snapshot process sleeps before it wakes up to handle a pending job or transaction.

(11) The parallel query process (PNNN) can start or stop the query process based on the setting of the database's active parallel query option. These processes involve the creation of parallel indexes, creation of tables, and querying. The number of launches is the same as the number specified by the parameter parallel_min_servers, and cannot exceed the value specified by the parameter.

#查看oracle后台进程和连接数

1. View the Oracle background process

SELECT * from v$bgprocess where paddr <> ' 00 '; Or on a Linux system: Ps-ef |grep Ora_

Using V$bgprocess to view Oracle processes

Using PS-EF to view Oracle processes

2. View the number of Oracle connections

Select COUNT (*) from v$session;

3. View the number of Oracle concurrent connections

Select COUNT (*) from v$session where status= ' ACTIVE ';

4. View the maximum number of connections

Show parameter processes;

5. Modify the number of connections, restart the database to take effect

Alter system set PROCESSES=500 Scope=spfile;

Ii. Oracle Logical Structure

The Oracle database contains at least one tablespace, one table space for multiple data files (data tables), one table space (consisting of multiple data files) with multiple segments, one segment consisting of multiple extents, one region containing several blocks, and the basic unit of Oracle Storage data is block, The size of the block can range from 2k to 64k, typically a piece of =8k=7192 bytes.

Table: The space created and allocated to hold specialized data.

Tablespace (tablespace): is a collection of one or more data files, all of which are stored in the specified table space, but are primarily tables, so called table spaces.

Table spaces can be divided into:

1. System tablespace: It is a necessary part of various versions of Oracle that holds some information about the Oracle system, typically with only one system table space.

2. Temporary tablespace (temporary tablespace): Because Oracle often needs some temporary disk space when working, these spaces are used primarily as algorithms for queries with sorting (Group by,order by, etc.) and are released as soon as they are exhausted. The information that is recorded on the disk area is no longer used, so it is called a temporary tablespace. After a general installation, there is only one temp temporary table space.

3. Tool Tablespace: The database objects that are required to hold the Oracle database Tools, which are stored in the dedicated tables used at work. There is no such table space for the old Oracle version. The tool table space after installation is tools.

4. User table space (users tablespace): used to store some experimental examples of space, of course, can also be used to store the data of the application system. The old version has no users table space. The user table space is now users.

5. Data and Index Table space: The table space for data and index, Oracle7 is advocated in the application system, especially with a large number of data systems, to separate the data and indexes in different partitions, in order to improve the query speed. The index table space may be indx after installation. The data table space is the users.

6. Rollback segment Tablespace (Rollback Segment): The Oracle database system requires a disk space to hold the recovery information (Undo information) when running INSERT, Update, delete. So that the original information can be read out while the recovery is in progress. The rollback segment table space after installation is the RBS tablespace. The database administrator can also create additional rollback segment table spaces based on the needs of the application. Oracle stores logical data in a tablespace and connects it to physical data.

#查询表空间剩余情况

Select Tablespace_name, sum (bytes)/1024/1024/1024 as G

From Dba_data_files

GROUP BY Tablespace_name

Order BY Tablespace_name;

"Turn" Oracle infrastructure cognition-process and logical structure eight mesh 2017-12-17 19:33:21