Oracle Architecture Detailed

　　It is important to understand the architecture of a technology, especially one that is so complex as the knowledge system of Oracle database. At the same time, personally, it is not necessarily a professional DBA personnel need to understand its architecture (although for database professionals, these are necessary knowledge), the general technical staff if they have a more in-depth understanding of it is also a great benefit, after all, technical thinking is often connected. This article describes the different dimensions, such as Oracle's memory structure, process structure, storage structure and so on.

First, let's talk about the basic concept.

I. What is an Oracle database?

As we all know, Oracle database is a relational data management system (do not know what is a relational database of children's shoes self-google,baidu), similar products and mysql,sqlserver, many times, The system that carries our core data is generally a database server, but in a strict sense Oracle database is made up of two parts:

Instance: an instance is a set of processes and memory structures initialized at database startup

Database: A database refers to some physical files that a user stores data

That's why we usually say shut down and start the instance, loading the unload database, that's the truth.

From the concept of instance and database, we can know that the instance is just a set of logically divided memory structure and process structure, it will disappear with the database shutdown, and the database is actually a heap of physical files (control files, data files, log files, etc.), it is permanent (unless the disk is corrupted). Databases and instances are usually one-to-many, and this structure makes us a single-instance architecture, and of course there are some complex distributed structures where a database can be used on multiple instances, like the Oracle RAC (which can be understood by interested children's shoes).

Second, the interactive process

Here is a diagram from the Internet that describes the approximate interaction flow of a single-instance architecture

　

1. User and user process interaction

User processes can be generic client software, such as Oracle's Sqlplus,sql developer, or some drivers and so on that belong to the user process

2. User process and server process interaction

Server processes are sometimes referred to as foreground processes, of course, relative to the background process (the database writer, log writer, and so on), the primary role of the server process is to process the request of the user process connected to the current instance, execute the SQL sent by the client, and return the execution results. In a proprietary server structure, the user process and the server process are one-to-the-other, that is, when the listener listens to the client for a request, it assigns a corresponding server process to it. There is also a structure for the shared server, this structure is not a user process corresponding to a server process, will be coordinated through the scheduler processing, about the shared server connection, this article is not redundant.

3. Server process and instance process interaction

4. Instance and database process interaction

The above describes what the approximate interaction process is when we are doing database connection operations. Let's take a look at Oracle's instance memory structure

Third, instance memory structure and process structure

( because the memory structure and the process structure are closely related, the process will affect the corresponding memory region, such as the database writer acting in the database buffer cache, the log writer will act on the log buffer, so the memory structure and process structure will be described in conjunction with each other)

The Oracle instance memory structure consists of two parts, the SGA (System global Zone) and the PGA (User Global Zone), which is a shared memory area and the largest chunk of memory, and the PGA is a dedicated memory area for the user session, and each session has a proprietary memory area on the server side that is the PGA. This paper mainly analyses and describes the SGA. The SGA consists of the following

　　

Database buffer Cache & database writer

The buffer cache is the working area that Oracle uses to execute SQL, and when the data is updated, the user session does not directly update the data on the disk, and if so, the impact of frequent disk IO on system performance is devastating. So, the actual processing flow is this:

Select Ename,salary from emp where name= ' oriental unbeaten ';

　　

Let's take a look at a simple query statement, how Oracle handles it. First, when the user submits the SQL statement, which is sent to the server by the corresponding user process (such as our usual SQL developer), the listener listens to the request, establishes a corresponding server process for it, and then the server process scans the buffer for the inclusion of the critical row (" "East unbeaten") data block, if any, this even if a cache hit, and then the relevant rows transferred to the PGA for further processing, and finally formatted to show to the user; if there is no hit, then the server process will first copy the corresponding row into the buffer and then back to the client.

DML (Insert,update,delete) operation in the same way, join the user sends an UPDATE statement, the service process still first to scan the buffer, if the cache hit, the direct update, the data dirty; if there is no hit, the corresponding data block is copied from the disk into the buffer by the server process first , and then update the operation.

Dirty buffers

If the block stored in the buffer is inconsistent with the block on the disk, the buffer is called a "dirty buffer" and the dirty buffer is eventually written to disk by the database writer (dbwn).

Database writer (DBWN)

The database writer is a background process for Oracle, and the so-called background process is relative to the foreground process (the server process). Dbwn "n" means that an instance can have more than one database writer.

　　function : In short, Dbwn's role is to write a dirty buffer from the database buffer cache to a data file on disk.

Database buffer cache This chunk of memory and database writer is an important concept, other database products like MySQL also have a corresponding implementation, but the name is not the same. When you understand this piece, be aware that the session is not directly updating the disk data , the session updates, insertions, deletions, including queries, etc. are first acting on the buffer, and then dbwn will dump the dirty buffers into the disk.

　　When does Dbwn write?

Dbwn is a lazy process that writes as little as possible , in the following four cases it performs writes:

A. There is no buffer available (you have to write it)

B. Too many dirty buffers

C.3 seconds Timeout (writes are performed once at the latest 3 seconds)

D. A checkpoint, checkpoint (checkpoint) is encountered, the checkpoint is an Oracle event, and a checkpoint is encountered, and Dbwn performs the write. For example, when an instance is closed in an orderly manner, there will be checkpoints, DBWN will write all the dirty buffers to disk, which is easy to understand, to maintain the consistency of data files.

　　　　Attention:

From the several write times of the above dbwn, we can realize that the write of DBWN is not directly dependent on the update operation of the session. Does not have a dirty buffer, it executes the write. Also,DBWN does not have any relation to the commit operation , and does not assume that the effect of the commit operation will flow into the disk in real time.

DBWN uses the extremely lazy algorithm writes, the reason we should be clear: the frequent disk IO to the system pressure is very big, if dbwn very actively writes to the disk, that to the system performance influence is too big, changes the angle to think, if DBWN diligently writes the disk, then the database buffer existence significance also is not big.

Of course, here we may be aware of a problem, dbwn so lazy data dump, if in a moment, the database buffer cache has a large number of dirty buffers (in production, this is the norm), that is, a large number of uncommitted and commit data is still in memory , did not persist to the disk, and then suddenly the system power off, in this case, the data is not lost? the data is certainly not lost, which leads to the concept of redo logs (redo log), and then we'll talk about the memory structure and background processes corresponding to the redo log.

Log Buffers & Log writers

When we perform some DML operations (Insert,update,delete), the data blocks change and the resulting change vectors are written to the redo log file. With these records, when the system due to power outages and other factors suddenly down, the database buffer cache of large amounts of dirty data has not yet been written to the data file, at the time of reboot, there will be an instance recovery process, in the process of applying redo log records to keep the data consistent; or the database is experiencing physical damage, For example, if the disk is damaged, data recovery can be done through Oracle's backup recovery tool (such as Rman), by extracting the backup set--and by applying the change record in the Redo log file.

　　　　Log buffers

The log buffer is a small area of memory that is used for short-term storage of the change vectors in the redo log files that will be written to disk.

The significance of the log buffer is still to reduce the disk IO, reduce the user's waiting time , imagine how bad the experience would be if every user DML operation had to wait for the redo record to be written to disk.

　　　　Log writer (LGWR)

As the name implies, the log writer (LGWR) writes the contents of the log buffer to the disk's redo log file, and the log writer is much more diligent than the database writer (dbwn).

In the following three scenarios Lgwr writes are performed:

Write when A.commit

As mentioned earlier, Dbwn write and commit have nothing to do, if the commit when the database does not have any records, then the data is really lost, Oracle's redo log is to ensure the security of the data exist, commit, the session will be suspended first, Wait for LGWR to write these records to the Redo log file on disk before notifying the user that the submission is complete. Therefore, Lgwr writes on commit to ensure that transactions are never lost.

B. The log buffer occupancy rate reaches 1/3.

C.dbwn to write the dirty buffer before

This write is for data rollback considerations. DBWN is fully likely to write transactions that have not yet been committed (referring to the write time mentioned above), How do you ensure that the transaction is rolled back?

The first thing to know is that Dbwn writes the undo data in addition to the actual data (the unknown classmate can refer to the description of the undo Section in my other blog post for an explanation of Oracle Flashback technology. Simply stated, transaction rollback requires data to be revoked, and before the revocation data is written, a log record of the revocation data (a bit around) is written, and if the user wants a transaction rollback, the log records can be applied to construct the revocation data and then rollback.

We summarize the two most important areas of memory and the corresponding background process:

　Both the database buffer cache and the log buffers are designed to improve performance and avoid frequent IO. The log buffer is much smaller than the database buffer cache and cannot be automatically managed, and the database buffer cache can be automatically managed for changes to the log buffers that require the instance to be restarted. The DBWN process that acts on the database buffer cache, in order to avoid the frequent disk IO causes the system performance to degrade, will write as little as possible, and the DBWN writes and the commit operation has nothing to do;

The LGWR process, which acts on the log buffers, writes very aggressively, and in general, it dumps redo log records to disk in almost real time. LGWR is one of the largest bottlenecks in the Oracle architecture. DML speed cannot exceed the speed at which LGWR writes change vectors to disk.

We're looking at other memory areas and background processes.

Shared pool

The shared pool is the most complex SGA structure, and it has many sub-structures, so let's look at a few common shared pool components:

　　1. Library Cache : library cache This area of memory caches the most recently executed code in the parsed format, so that when the same SQL code executes multiple times, it does not have to be repeated for code analysis, which can improve the performance of the system to a large extent.

　　2. Data dictionary caching : Store object definitions (tables, views, synonyms, indexes, and so on) in Oracle so that when parsing SQL code, you don't have to go to the disk to read data from the data dictionary frequently.

　　3.pl/sql : Cache stored procedures, functions, triggers, and other database objects, which are stored in a data dictionary and can be used to improve performance when repeated calls, by caching them in memory.

Large pool: large pool is an optional memory area, before we mentioned the private server connection and the shared server connection, if the database uses the shared Server connection mode, then to use to the large pool, RMAN (Oracle's Advanced Backup recovery Tool) backup data also need a large pool.

Java Pool

Many of Oracle's options are written in Java, and the Java pool is used as the heap space required to instantiate Java objects

Flow Pool

The process of extracting the change record from the redo log and the process applying the change record will be used for the stream pool (such as an unhealthy instance shutdown, such as a power outage that causes the instance to shut down, and Oracle automatically performs the instance recovery process when restarting, which requires extracting redo logging and applying redo log two actions)

The above lists the common memory structure of Oracle, it is important to note that the memory area listed above, in addition to the log buffer is fixed, can not be dynamically adjusted or automatic management, other memory areas can be dynamically adjusted, can also be automated management.

In the case of Oracle's several background processes (DBWN and LGWR are more important, they have already been understood, not to mention here)

Smon (System Monitor): Installing and opening databases, instance recovery is also done by this process

Pmon: Process Monitor, the primary monitoring server process. As mentioned earlier, in the proprietary server architecture mode, the user process and the server process are one-to-one relationships, and if a session has an exception, Pmon destroys the corresponding server process, rolls back uncommitted transactions, and reclaims the session's proprietary PGA memory area.

CKPT (Checkpoint Process): CKPT is responsible for initiating the checkpoint signal and manually setting the checkpoint syntax:

Sql>alter system checkpoint;

Checkpoints can force dbwn to write dirty buffers, and when a database crashes, because a large number of dirty buffers are not written to the data file, the instance recovery needs to be performed by Smon when restarting, and instance recovery needs to extract and apply redo log records. The extracted location starts at the location where the last checkpoint originated (the data before the checkpoint has been forcibly written to the data file), which is called RBA (Redo byte address), CKPT will constantly update this location to the control file (to determine where the instance recovery needs to start extracting the log records).

Mmon (Manageability Monitor):

Database self-monitoring and self-tuning support processes. As the instance runs, it collects a lot of statistics about the activity and performance of the instance that are collected into the SGA, which Mmon periodically captures these statistics from the SGA and writes them to the data dictionary for subsequent analysis of these snapshots. (By default, Mmon collects snapshots every one hours)

ARCn (archiver)

Archive process, this process is optional, and if the database is configured as an archive mode, this process is necessary. The so-called archive is to permanently save the Redo log file (the production library is typically configured as an archive mode) into the archive log file. Archiving log files and redo log files work the same way, except that redo log files are not short-rewritten, while archived log files retain a complete history of data changes.

At this point, Oracle's underlying memory structure and process structure we have a general understanding of the process and memory of the completion of the interaction, can be based on the previous understanding of the entire interaction process series.

Iv. Oracle Storage Architecture

Physical storage structure

　　

　　

The so-called external files mean that these files are not strictly part of the Oracle database.

Control files:

The control file is small but significant, it contains pointers to the rest of the database (including the location of redo log files, data files, archived log files, etc.), stores important serial numbers and timestamps, and stores the details of the Rman backup. Once the control file is compromised, the instance is terminated immediately, and the data file is generally protected using a multiplexing mechanism, which is redundant multiple copies in different physical locations.

Redo log Files

Redo log files are mentioned when explaining memory and process structure, and redo logs store a sequence of change vectors (including online redo log files and archive log files) that are applied to the database in chronological order. Extract backup restores required by Smon for instance recovery and disk corruption that are automatically performed when the database is started are applied to the redo log for appropriate data recovery

Redo log files are also recommended for multiplexing, and a database must have at least two sets of redo log files. A set for LGWR to write, the log file is fixed size, the business peak will soon be full, write full after the switch to the second group, in the database configured as the archive mode, the archive process (ARCN) begins to archive the first set of content to be archived backup, so that the write and archive in a loop. It is important to note that LGWR is not allowed to overwrite the current group's log until the archive process has finished archiving it.

Data files

The data file stores the actual data, Dbwn writes the contents of the database buffer to such files, and the size and number of data files is unrestricted. Oracle starting at 10g, creating a database requires at least two data files, one for the system tablespace, which is used to store data dictionaries, and one for the Sysaux table space, which is used to store auxiliary data for some data dictionaries.

The data file consists of one Oracle block, which is Oracle's I/O base unit, which is different from the operating system block, the Oracle block is larger than the operating system block, which of course has some performance considerations, but we consider a situation where When a user uses an operating system command to make a backup of a data file (assuming 1 Oracle blocks = 8 operating system blocks), 4 operating system blocks have been replicated, and the CPU is preempted by DBWN, and dbwn again updates the Oracle block, When the copy command gets the CPU time to replicate the remaining 4 blocks, it causes the data inconsistency of the entire Oracle block, so it is also necessary to do some extra processing when performing such a backup (user self-backup), such as the reason for placing the tablespace in Backup mode. Of course, there is no such problem with Rman, and the backup mechanism of Rman is sure to get the data consistent blocks. (This piece of content for the understanding can be)

For data File Protection, regular backups can be made, or raid can be used.

Instance parameter file

This file stores some of the parameter settings required for the database, such as the size of each memory area, the maximum number of processes allowed, the maximum number of sessions, the location of the control file, the name of the database, and so on, and the parameter file is the first file to be loaded when the instance starts.

Password file

Commonly referred to as an external password file. The generic user name and password are stored in the data dictionary and are not stored in this file. In some special scenarios, such as the instance has not been started, at this time, I may need to log into the system as an administrator to perform some recovery or start operation, however, the data dictionary because the instance does not start is not exist, then need to external password file for user authentication.

Archive log files

ARCN Archive The online redo log files to such files, and the archived log files retain complete historical information about the data changes.

Logical storage structure

Oracle abstracts its physical structure from the logical storage structure, which is what the system administrator can see, and the logical structure that the user can perceive. The typical logical structure is "segment" and "Table space".

　　Paragraph

A segment is a logical structure that contains all the data, and a typical segment is a table, called a table segment, an index segment, a undo segment, and so on.

　Table Space

A tablespace is logically a combination of multiple segments, which is physically a collection of multiple data files, equivalent to adding a middle layer to the correspondence of the segment and data file to resolve this many-to-many relationship.

In some early database design, the segment and data file is a one-to-one relationship, a piece of data file, this design has a lot of drawbacks, first, the number of segments is not fixed, there may be thousands of tables in a system, it requires thousands of data files, the system administrator to manage so many files will be crazy There is also the case that some history tables can be particularly large, large enough for the underlying system to restrict a single file, and it is certainly not possible to use a data file to carry it. The table space solves this problem perfectly.

There are also logical structures such as intervals and Oracle blocks (the Oracle block is mentioned earlier, the interval is a collection of blocks), and the following is an overall understanding of Oracle's storage structure through a single graph, further deepening the understanding

　

Summarize:

This article Bo Master of Oracle's architecture has made a relatively comprehensive introduction, including memory structure, process structure, storage structure and so on, I believe you have a basic understanding of its overall picture. Of course, the blogger himself is not an Oracle professional (though he has had the idea of becoming a DBA.) ), but also because I think of the structure of the learning list can not go deep to every point, so some concepts did not do a special in-depth interpretation, there are some examples such as the recovery process, the various stages of database startup, dynamic parameters and static parameters, etc. are not mentioned, the subsequent time will be appended. Thanks for coming. Today is the first entry into the blog Park's second blog post, follow-up will continue to update the blog, I hope you are more than welcome??

　　

　　　　

　　　　

Oracle Architecture Detailed