Oracle architecture implementation principles, including five process resolution

Directory

• Directory
• Objective
• Oracle's architecture
  • Oracle RDBMS Frame composition
  • Storage structure
    • Physical Structure
      • Data Files
      • Redo Log Files
      • Control Files
      • Parameter File
      • Password File
    • Logical Structure
    • Mapping of logical space to physical space
  • Memory structure
    • System Global Area SGA
      • Cache Buffer Database Buffers
      • Log buffers
      • Shared pool
      • Large pool
      • Java Pool
  • Process structure
    • User Connection Process
      • Program Global Zone PGA
      • User process
      • Server Process Service Processes
    • Background process
    • Database Write Process Dbwn
    • Check Point Ckpt
    • Process monitoring Process Pmon
    • System Monitoring Process Smon
    • Redo log files and log write processes
    • Archive Process ARCN
  • What happens in an RDBMS when you execute a written SQL statement
• At last

Objective

Oracle architecture, which describes the underlying implementation principles of Oracle RDBMS, is the basic theory of Oracle dba** tuning and debugging. An in-depth understanding of Oracle architecture allows us to go farther on Oracle's path. Understanding the architecture of Oracle is a far-reaching process that requires constant updates and corrections, if not, as well. ：）

Oracle's architecture Oracle RDBMS frame composition

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

• Database = Data file + Control file + log file
• Instance = Memory Pool SGA + background process

==> Oracle Server = memory Pool + background process + data files + control files + log files

A set of Oracle RDBMS supports the creation of multiple database, and each datacase is independent of 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 (two-dimensional table files stored in the storage device), and our tables are stored in the database. Database content can not be read directly by us, we must 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 schema diagram above, you can abstract Oracle architecture into:Oracle architecture = Memory Structure + process structure + storage structure

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 files: Record continuous database operations
• Archive log file archived log files: Used for Point-in-time recovery, and when Redologfiles is full, these logs are archived for backup to be used later when the data is restored.
  
  • View Redo Log Info:

SQL> select member from v$logfile;    # v$logfile数据字典，记录了redolog文件的列表 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. 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, abstract processing into a logical structure. This makes it easier for us to manage the database.

Mapping of logical space to physical space

segments, areas, and blocks :

Memory structure

Oracle instance is one of the core of Oracle RDBMS as the Central management Center for the entire RDBMS. Oracle instance is primarily composed of memory pools and background processes .

System Global Area SGA

View SGA: The essence is the memory pool

Sql>Conn/as sysdbaconnected.Sql>Show user; USER is"SYS"Sql> Select *From V$SGA; NAME VALUE-------------------- ----------Fixed Size2022144VariableSize503317760Database buffers1627389952Redo buffers14753792Sql>Show Sgatotal SystemGlobalArea2147483648 bytesFixed Size2022144 bytesVariableSize503317760 bytesDatabase buffers1627389952 bytesRedo buffers14753792 bytes

The SGA (System Global area) is the most important part of the Oracle performance relationship, and the SGA memory pool is allocated at instance startup and released when it is closed. Within a certain range, the DBA's instructions can be responded to in an automated manner while the instance is running. The SGA contains the following data structures:

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?
A: 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.

Note : The size of the database buffer cache can have a critical impact on performance.
1. The cache should be large enough to be able to cache all frequently accessed blocks of data.
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.
3. 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.

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.
to modify the buffer Db_cache_size method :

#Step1.To view the size of the SGA: Because Db_cache_size's size is affected by the SGA SQL>Show parameter sga_max_size; NAMETYPEVALUE------------------------------------ ----------- ------------------------------Sga_max_size Biginteger 2G#Step2.View the size of show parameter Shared_pool_size SQL>Show parameter shared_pool_size; NAMETYPEVALUE------------------------------------ ----------- ------------------------------Shared_pool_size Biginteger 0#Step3.Calculate the size of the db_cache_size: shared_pool_size+Db_cache_size=Sga_max_size*  -%#Step4.Modify the size of Db_cache_size SQL>Alter systemSetDb_cache_size=1433M Scope=SPFile SID=' demo '; System Altered.Sql>Conn Sys/as sysdbaenter Password:********Connected.Sql>Shutdown Immediatedatabase Closed.Database dismounted.ORACLE instance shut down.Sql>Startuporacle instance started.Total SystemGlobalArea2147483648 bytesFixed Size2022144 bytesVariableSize503317760 bytesDatabase buffers1627389952 bytesRedo buffers14753792 bytesDatabase Mounted.Database opened.Sql>Show Parameter Db_cache_size

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 has a significant impact on performance
1. It should be large enough to cache all frequently executed code and frequently accessed object definitions.
2. But it cannot be too big to be cached even for statements that are executed only once.
3. If the shared pool is too small, performance degrades because the server session will repeatedly rob the space to parse the statement, and thereafter the statements will be rewritten by other statements and will have to be parsed again when re-executing.
4. Too large shared pools can adversely affect performance because the search takes too long.
5. 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.

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: Storing recently executed code
• Data dictionary caching: Storing 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;   //显示可以动态重设大小的SGA组件的当前最大和最小容量ALTER SYSTEM SET SHARED_POOL_SIZE = 110M;

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 is primarily composed of background processes and user-connected processes .

User Connection Process

Connect and manage user access = User process + service process + PGA

Program Global Zone PGA

The PGA is the bridge between connecting users and Oracle.

The PGA is used to allocate memory for user connections, Oracle RDBMS needs to use these memory to create instence, and only after instance is created can users connect and manage Oracle Database via instance.

User process

Oracle's Connect connection and session sessions are closely related to user process :
1. Connect connection: Is the communication channel between user process and Oracle instance, one connection corresponds to one instance.
2. Session Sessions: When a user establishes a connection with Oracle instance through user process, a user can establish multiple sessions that can be connected to the same instance multiple times using the same user. Multiple sessions can use the same connection.

Server Process Service Processes

For handling connections between user and server

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 process: Core process, 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 you access the database, the instance is started first. Allocate the SGA memory pool before starting the instance and then start the background process. the first five processes above must be started during database startup. Otherwise, the instance cannot be created.

To view the background core process:

Sql>SelectName,description from v$bgprocess where paddr<> 'xx'; NAME DESCRIPTION----- ----------------------------------------------------------------PmonProcessCleanupPSP0ProcessSpawner0Mman Memory ManagerDBW0 db writerProcess 0LGWR Redo etc. CKPT Checkpointsmon System MonitorProcessRECO distributed recoveryCJQ0 Job Queue COORDINATORQMNC AQ Coordinatormmon Manageability MonitorProcessNAME DESCRIPTION----- ----------------------------------------------------------------MMNL Manageability MonitorProcess 2

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.

• Clear the dead process
• Restart part of a process (such as a scheduling process)
• Automatic registration of Listening
• Rolling back a transaction
• Release lock
• Release of 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 the log write process:
-Submit (Enter)
-Log buffers over 1/3
-Every three seconds
-Before each dbwn execution

Archive process (ARCn)

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

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

1. A user submits a request before accessing the database and needs to start an instance first
2. After launching the instance, the user can connect to the service process and complete the connection
3. The PGA is called by the service process to assign the user the memory that is necessary to create the instance. SGA
4. User executes SQL statement received by the service process
5. SQL statement arrives at Oracle Instance through the service process, and then loads SQL into the database buffer
6. The service process notifies Oracle database to load a block of data into the buffer, generating a copy of the data block
7. and execute SQL statements in the database buffers
8. Log user logins, process calls, raw state of data files, database changes to redo log files
9. Writes the updated content to the data file when a "dirty buffer" occurs in the database buffer

Oracle architecture implementation principles, including five process resolution