Oracle learns a

First, 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

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:

1	selectename,salary from emp wherename=‘东方不败‘;

　　

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, is not persisted to the disk, then suddenly the system loses power, 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.

Oracle learns a