Oracle Architecture Implementation principle (RPM)

Oracle RDBMS Frame composition

In general, Oracle refers to Oracle RDBMS (relational Databases Management system), a set of Oracle database management systems, also known as Oracle Server. There are two main parts of Oracle server:

Oracle Server = instance + databases (instance and database are independent of each other)

Database = Data file + Control file + log File instance = Memory Pool + background process

So can be subdivided into: Oracle Server = memory Pool + background process + data files + control files + log files

A single Oracle server supports the creation of multiple database, and each datacase is isolated from each other. Different database has its own complete set of related files, such as: have their own password files, parameter files, data files, control files and log files.

Database consists of a number of physical files, such as a two-dimensional table file stored in a storage device. The two-dimensional table is stored in the database, but the database content can not be read directly by the user, the user must be able to access the database through Oracle instance, a instance can only connect to a database, But a database can be connected by multiple instance.

By abstracting the Oracle RDBMS schema diagram above, you can abstract Oracle architecture into: Oracle architecture = Memory Structure + process structure + storage structure

Memory structure

Oracle instance is one of the core of Oracle RDBMS and is responsible for the management capabilities of the RDBMS. Oracle instance consists primarily of memory pool SGA and background processes.

System Global Area SGA

The SGA (System Global area) is the most important part of Oracle's performance relationship and is the primary consideration for tuning Oracle. The SGA memory pool is allocated at instance startup and is freed when instance is closed. Within a certain range, the SGA can respond to the DBA's instructions in an automated manner when the instance is running.
If you want to tune the SGA, you must also understand the following data structures that are included in the SGA:

Cache buffers (Database buffers)

The database buffer is the region where Oracle executes the SQL statement.

For example, when updating data, the SQL statements executed by the user do not make changes directly to the data file on disk, but rather first copy the data file to the database buffer cache (that is, the database buffer holds a copy of the SQL-related data file), and then change the copies of those blocks that are applied to the database buffer cache. And the copy of The block will remain in the cache for a period of time until the buffer it occupies is overwritten by another database (the buffer size is limited).

When querying data, the data being queried is also cached in order to improve execution efficiency. The established session calculates the data blocks that contain the critical rows and copies them to the database buffer for caching. Thereafter, the relevant key rows are transferred to the PGA of the session for further processing. These blocks are also retained for a period of time in the database buffer cache.

In general, data blocks that are accessed frequently are present in the database buffer cache, minimizing the need for disk I/O.

When will a copy of the updated block of data be written to the data file on disk?

The answer is: if a block of data stored in the buffer cache is not the same as a block of data on disk, such a buffer is often referred to as a "dirty buffer", and a copy of the block of data in the dirty buffer must be written back to the disk's data file.

Tuning: The size of the database buffer cache can have a critical impact on performance, which requires a large size to be the best match and the actual production environment. In general, it can be judged according to the following two basic requirements:

1. The cache should be large enough to be able to cache all frequently accessed blocks of data. If the cache is too small, it will result in too much disk i/0 activity because frequently accessed chunks are continuously read from disk and used and rewritten by other blocks of data and then read from disk.

2. But it can't be too big to add a few blocks of access to the cache, which increases the time it takes to search in the cache.

The database buffer cache is allocated when instance is started. Starting with Database 9i, you can turn it up or down at any time. You can re-tune manually or automatically resize (transactions) based on your workload.

Log buffers

The log buffer is a small, temporary area that is used for short-term storage of the change vectors that will be written to the redo log on disk. The main role is to provide faster log processing efficiency.

Shared pool

The size of the shared pool also has a significant impact on performance

1. It should be large enough to cache all frequently executed code and frequently accessed object definitions. If the shared pool is too small, performance degrades because the server session will repeatedly rob the space to parse the statements, and thereafter the statements will be rewritten by other statements and will have to be parsed again when re-executing. If the shared pool is less than the optimal capacity, performance will decrease. But there is a minimum capacity, and if below this limit, the statement will fail.

2. But it cannot be too big to be cached even for statements that are executed only once. Too large a shared pool can adversely affect performance because the search takes too long.

Determining the optimal capacity is a performance tuning issue, and most databases require a hundreds of MB shared pool. Some applications require more than 1GB of shared pools, but few applications can run fully when the shared pool is less than 100MB. The following three types of data structures are available in the shared pool:

Library Buffering: Stores the most recently executed code data dictionary cache: Stores recently used object definitions PL/SQL buffers: stored PL/SQL objects are procedures, functions, packaged procedures, packaged functions, object type definitions, and triggers.

To manually adjust the size of the shared pool:

Select Component,current_size,min_size,max_size from V$sga_dynamic_components; Displays the current maximum and minimum capacity of an SGA component that can be dynamically resized

ALTER SYSTEM SET shared_pool_size = 110M;

Other structures

This is not a detailed introduction here for the time being.

Large pool

The primary purpose is for shared server processes to use.

Java Pool

The Java pool is required only if the application needs to run Java storage programs in the database.

Process structure

The process structure mainly has the background process and the user connection process two categories.

User Connection Process

The user connection process is a bridge between connecting users and Oracle instance. User will be able to operate on Oracle server only after the user has established a connection with instance.

User connection process = User Process + Service process + PGA

User process

When a database user requests a connection to Oracle server, Oracle Server creates a user Process.

The role of User process:

Establishing a connection to database user with server process does not directly interact with Oracle server

Connect connection: Is the communication channel between user and server process.

Server Process Service Processes

Used to process connections between database user and Oracle server.

When a user has established a connect with user process, Oracle server creates a server process. After the user process has established a connection with the server process, the server process determines that a session is established with the Oracle instance through the request information submitted by users.

The role of Server process:

Direct interactive replication with Oracle Server performs and returns results

Session Sessions: When a user connects to Oracle instance through user process (essentially through server process), it is called a session, a user can establish multiple sessions, That is, you can connect to the same instance multiple times using the same user, which means you can use the same connect for more than one session.

Program Global Zone PGA

The pga:oracle Server process is assigned to the memory area dedicated to the current user session. The area is private and different users have different PGA.

The PGA contains the memory area of the server process data and control information. , consisting of the following 3 parts:

1. Stack space: Memory space for variables, arrays, etc. stored in session.

2. Session Info: If you are not running a multi-threaded server, the sessions information will be saved in the PGA, or in the SGA if it is a multithreaded server.

3. Private SQL Zone: used to hold information such as binding variables (binding variables) and runtime buffers (runtime buffers).

Oracle's connect connection and session sessions are closely related to user process

Note: In RDBMS, a database is determined by db\_name and instance\_name, so instance_name is used for the connection between Oracle and the OS while also being used for Oracle server and external connections.

So when user submits the connection request, the user process first establishes connect with the server process, and then the server process passes through the Db\_name and instance\_ contained in the request. Name to determine the database that is needed and can be connected (the RDBMS can have multiple databases), which ensures that the RDBMS can also guarantee the independence of each database in the case of multiple databases. And the same database can be connected by multiple instance that belong to different users of this databse. This feature is very necessary because each of the different databases contains system users with the same name as SYS, systems, and so on.

Background process

Background process is mainly to complete the database management task, the background process is Oracle instance and Oracle database link, divided into core processes and non-core processes.

1. Core processes: Core processes, must exist, have a termination, all database processes terminate, instance crashes! Five of these processes are all core processes.

2. Non-core process: Complete database additional functions, non-core process death database does not crash!

Common Core processes:

When a user accesses a database, the request is first committed, the SGA memory is allocated, the background process and instance are created and started, and the connection and session are finally established. The first five processes above must be started during Oracle server operation. Otherwise the instance cannot be created

Database write process (DBWN)

After the Server process connects to Oracle, the data block of the "dirty buffer" in the data buffer is written to the storage structure (data file, disk file) through the database write process (DBWN)

Database writer (DBWN) writes the process:

Do one thing and write the data to disk. is to write changes to the database to the data file.

The process can be up to 20, even if you have 36 CPUs and only up to 20 database write processes.

Process name Dbw0-dbw9 DBWA-DBWJ

Note: The more database write process, the more efficient it is to write data. The number of the process should correspond to the number of CPUs, if the number of database write process is more than the number of CPUs will not have too obvious effect, because the CPU is tick.

Checkpoint (CKPT)

Checkpoint (CKPT) checkpoint process:

The primary user updates the header of the data file, updates the control file, and triggers the DBWN database write process.

The CKPT process degrades database performance, but improves the performance of self-recovery when the database crashes. We can understand that as the stage of the preservation of data, a certain condition to meet the trigger, execute DBWN disk operation.

Process monitoring process (Pmon)

Process Monitor (Pmon) process monitoring:

Pmon is responsible for cleaning up the database cache and idle resources after a failed background process execution, and is an automatic maintenance mechanism for Oracle.

Clears the dead process from restarting a partial process (such as a dispatch process) listens for auto-registration ROLLBACK TRANSACTION Release lock release other resources

System monitoring Process (Smon)

System Monitor (Smon) systems monitoring process:

Smon is automatically used when the instance crashes to automatically recover the DB instance when it is started.

Clears the obsolete sort staging segment, reclaims the defragmentation, merges the free space, releases the temporary segment, and maintains the point in time of the flashback.

In the old database version, when we delete a large number of tables, we observe that the Smon process is busy until we have all the debris space sorted out.

Redo log files and log write processes

It is primarily used to record database changes and to record the original state of the database before it has been changed, so it should be used for multiple backups for recovery and troubleshooting.

To activate LGWR:

Commit the instruction log buffer more than 1/3 every three seconds before each dbwn execution

Archive process (ARCn)

The archive process (ARCN) is a non-core process.

Storage structure

The Oracle RDBMS storage structure is primarily composed of database.

The database can be divided into physical structure and logical structure to understand.

Physical Structure

Database Physical Structure: the database in the operating system of the file collection, namely: the physical files on the disk, mainly by data files, control files, redo log files, archive log files, parameter files, password file composition.

Data Files

A data file is a storage repository for data.

? Include all database data

? Can belong to only one database

? From a database storage logical unit called a "tablespace"

? Can be read directly into the memory, the execution of the SQL statement, the relevant data file copy is loaded as a data buffer.

? Data files can be protected by a backup policy

Redo Log Files

The Redo log file contains a record of the changes that were made to the database and the ability to recover the data in the event of Oracle failure.

The ability to recover data: The Redo log files are in the order of time, storing a series of change vectors (what do you do) that are applied to the database and marking them in the place of change. It contains information about all the operations that have been completed and the state of the database before the operation is completed. If the data file is damaged, you can apply these change vectors to the data file backup for Redo (rebuild) work and restore it to the state of the moment that the failure occurred. Redo log files are divided into the following two types:

Online redo log file: Records continuous Database Operations archive log file archived log files: For Point-in-time recovery, when Redologfiles is full, these logs are archived for later use when restoring data.

View Redo Log Info:

Sql> Select member from V$logfile; # V$logfile data dictionary, which records a list of redolog files

MEMBER

--------------------------------------------------------------------------------

/u01/oradata/demo/redo03.log

/u01/oradata/demo/redo02.log

/u01/oradata/demo/redo01.log

Control Files

The control file contains the necessary information to maintain and verify the integrity of the database.

It records the location of the online redo log files, the location of the data files, and the updated archive log files. It also stores the information needed to maintain database integrity, such as the database name. The control file is stored in binary form, and the user cannot modify the contents of the control file. The control file is a few megabytes, but it plays a vital role.

Parameter File

Instance parameter file, when the Oracle instance is started, the SGA structure will be set up according to this parameter file, and the background process will start accordingly.

Password File

The user submits the Username/password to establish the session, and Oracle validates the user name and password based on the user definition stored in the data dictionary.

Logical Structure

A tablespace is a typical type of Oracle logical Structure--it contains several data files

Tablespace: A logical space for storing database objects, a tablespace is a space in a database that is used to hold a database object, which is the largest logical unit of information storage and where the database files are stored, where the data is stored in a data file in the tablespace. A database can consist of multiple table spaces, and Oracle tuning is done through tablespaces. (Oracle database's unique advanced application)

The role of table space: Classification management, batch processing; The trivial disk file consolidation, abstraction processing becomes the logical structure. This makes it easier for us to manage the database.

Mapping of logical space to physical space

segments, areas, and blocks:

What happens in an RDBMS when you execute a written SQL statement

1. Loading SQL statements into the database buffer

2. Load the copy of the data file to be manipulated by the SQL statement into the database buffer

3. Execute the SQL statement and modify the copy of the data file to form a "dirty buffer"

4. Ckpt detected a "dirty buffer" and called DBWN

5. Before dbwn runs, LGWR is run, recording the original state of the data file and the changes to the database to redo log files

6. Run dbwn to write "dirty buffer contents to data file"

7. Simultaneously ckpt modify control file and data file header

8. Smon Reclaim unnecessary idle resources

At last

Finally, let's take a look at how Oracle RDBMS works

User submits a request before accessing Oracle server (including information such as db_name, instance_name, username, password, etc.), and Oracle server receives the request and passes the validation of the password file , allocate the SGA memory pool, and start the background process to create and launch the instance at the same time. After launching the instance, user process establishes connect with the server process. Build sesscion through server process and Oracle instance. The user executes the SQL statement that is received by the server process and interacts directly with Oracle. The SQL statement arrives at Oracle Instance through the server process, and then loads SQL into the database buffer. The Server process notifies Oracle database to load a copy of the data block associated with the SQL statement into the buffer. Executes the SQL statement in the database buffer and produces a "dirty buffer". The CKPT checkpoint process checks to the "dirty buffer" and calls the DBWN database write process, but before Dbwn executes, the original state of the data file, the change of the database, and so on should be recorded to redo log files prior to execution by LGWR. Writes the updated content to a data file on disk. Returns the result to the user

Transfer from http://www.2cto.com/database/201606/515344_3.html

Oracle Architecture Implementation principle (RPM)