Oracle monolithic architecture-memory structure, physical structure, logical structure, process

Oracle's architecture is broadly divided into two parts: Instance (instance) and database.

• Instance (example) : The SGA is mainly included in Oracle Instance and some processes (for example: Pmon, Smon, Dbwn, LGWR, CKPT, etc.). If a user's process is connected to Oracle server, it is actually connected to Oracle Instance. The SGA also contains 5 parts: Share pool, Database buffer Cache, Redo Log buffer, Java Pool, Large pool.

• database: Databases is actually a bunch of files, mainly used to store data, the database contains three types of files mainly: data files, Control files, Redo Log files.

Here are a few important concepts for Oracle:

Oracle Server (Oracle servers)
Oracle server also contains two parts: Oracle instance and Oracle Database (Oracle server is a relatively large concept, which we typically refer to as Oracle servers).

Oracle Instance (Oracle instance)
Oracle server is used to manage data, which is stored in Oracle database as a stack of files stored on disk and requires a medium to access this heap of files------Oracle Instance,oracle Instance is a means of accessing Oracle database.
An Oracle instance corresponds to one and only one Oracle database, and an Oracle database can have multiple instance to access him, which means Oracle instance and Oracle Database is a many-to-one approach. In general, we install Oracle on a single machine in instance and database one-on-one, but in the case of an Oracle cluster (Oracle RAC Environment) He is a multiple instance corresponding to a database.

Oracle instance has two components:

• memory structures (RAM structure) : The structures is the SGA in Oracle instance.

• Background Process Structures (background progress structure) : Background process structures refers to Pmon, Smon, DBWR, LGWR, ckpt and other background processes.

Oracle instance starts off with some background process and allocates memory, and Oracle instance is easy to disappear. (as opposed to the database file, for example: a power outage is not).

Oracle Database Connection & Session

Oracle Connection
Connection in Oracle refers to a TCP connection established by an Oracle client and a backend server, which must be a TCP connection established by the Oracle client and the background process (server process).

There are three types of Oracle process:

1. Background process (daemon) He is the basic part of Oracle instance
2. The server process, in fact, background process and server processes are both background processes for Oracle. The background process is the one that focuses on the core database service, while the server process processes the connection between the client and the server.
3. When the user process client is connected to the server, the process that is initiated by the client is user process (for example: Sql*plus, Toad, and so on). Users logging on to Oracle server is the user process and server process establishment connection.

Oracle Session

The process of establishing a connection is to establish a TCP connection first, Oracle authenticates the identity of the user, carries out security audits, and so on, and when these are passed, Oracle's server process allows the client to use the services provided by Oracle. When Oracle's connection is established, it means that a session is started and the reply disappears when the connection is broken. The session is complementary to the connection. Session information is stored in Oracle's data dictionary.

To start Oracle Instance in Linux

Enter a "!" to switch to the shell. Later, if you want to return to Sqlplus, enter "Exit".

Oracle Process (Oracle Instance) can be viewed with linux command ps–ef

Oracle Database (Oracle databases)

Oracle database is actually a bunch of files, and this heap of files is a bunch of data that they are as a whole. Oracle database contains three basic core file types, data file, Control file, Redo Log file.

• Data files , which are used to store data, are stored in the data file in the table.
• control Files : In order to manipulate the data file, Oracle provides some control files, which are primarily information about the records of the database.
• Redo log files (redo log file) : Redo log Documents records the changes in the database, if you put data into the database or modify the data inside, as long as the database has been modified, then you will modify the previous state, The modified state is recorded in the Redo log files, which is the function of recovering the data File. For example: The database has a transaction that needs to be committed, but the commit fails and the transaction is rolled back, then the transaction rollback is based on the redo Log Files. Redo log files record the changes in the database, the change in this transaction, if the need to roll back, you need to Redo log files in the data, according to Redo log files in the data files to revert to the state before the change.
• Parameter file (parameter files) : Any database must have a parameter file, which specifies some basic parameters in Oracle, the values of the initialized parameters.
• Archived log files (archive log file) : Archived logs files and Redo log documents are mutually reinforcing, and Redo log files are actually a process of reuse, He will have a few (usually 3) fixed files, these fixed files he will be used in turn, after the full, Oracle will write this file in the head, the previous things washed away. In order to further enhance the backup and recovery capability of the database, the modified information is archived to archived Log files before overwriting.
• Password File : Stores the password when connecting to the backend database system with the client.

The following shows the database portion of Oracle installed on Linux. The large stack of files shown below is Oracle database (typically located under the directory of the instance name under the database installation path).

• *.TCL Control files
• *.dbf Data files
• *.log Redo log Files

Memory Structures

Oracle's memory structures actually consists of two parts: the SGA and the PGA:

• SGA (System global Area) an Oracle instance corresponding to a SGA,SGA is assigned when Oracle instance is started and the SGA is Oracle The basic component of instance. An Oracle instance only one SGA,SGA is a very large memory space that can even occupy 80% of the physical memory.
• PGA (Program Global Zone) when a server process starts, it assigns a PGA.

The entire Oracle instance has only one SGA, and one server process will have a PGA. There may be a lot of PGA in Oracle instance. For example, starting a 10 server process will have 10 PGA. No matter how many processes are started, as long as there is an Oracle instance then he has only one SGA.
Oracle's usage scenarios typically manage massive amounts of data, so large amounts of data are stored on disk, and to improve storage and access efficiency, Oracle is bound to open up a large memory area in memory (memory accesses thousands of times times tens of thousands of times times faster). Oracle is a memory-intensive software that typically runs out of usable memory, and his memory is typically consumed on the SGA.

When the Oracle process under Linux/unix is started, we can enter the "IPCS" command in Linux/unix's shell to see how the System Shares memory allocations (IPC is a mechanism for interprocess communication, IPCS commands are used to view IPC resources, IPC status). In Linux/unix, Oracle's SGA is implemented in a way that uses shared memory segments, which can be implemented differently for Windows. The size of the SGA can be configured, and as the database load increases, Oracle instance needs more memory, and the SGA expands and even consumes the entire memory.

SGA (System Global area)

The SGA (System global Area), which contains the following chunks of basic memory:

• Shared Pool
• Database Buffer Cache
• Redo Log Buffer
• Large Pool
• Java Pool

The shared Pool, Database buffer Cache, and Redo Log buffer are the core memory areas, where large pool and Java pool are optional (and string pool is included in Oracle 11g).
For a startup Oracle database, how do I check his SGA? In Linux, for example, you can start with the command "PS–EF | grep Oracle "View Oracle's background Process.

Enter the command "show SGA;" To see information about the SGA.

In earlier versions of Oracle, the SGA was fixed after Oracle started, and could be changed at runtime after Oracle 9i, dynamically adjusting the value of the SGA online. Oracle instance is assigned an SGA when it is started, and the user can send requests to the database service, and as the user requests load increases, Oracle needs more memory resources, so the SGA needs to expand, so there is a need to dynamically change the SGA.
There is an important parameter in the SGA Sga_max_size, which determines the maximum value of the Oracle Instance SGA, within which the SGA can allocate how much memory each of his parts occupies. The SGA can grow or shrink dynamically, and the SGA increases or decreases each time the unit granules (granules is the basic unit of the SGA allocation memory, usually GRANULES=4M,SGA more than 128M for cases where the SGA is less than 128M granules= 16M ). The SGA is a contiguous allocated area of memory, and his minimum allocation is granules but his maximum size cannot exceed sga_max_size.
Execute the SQL statement to see the granules of the current Oracle system. Shows the components of the SGA and the granules of each component.

The maximum value of the SGA is determined by sga_max_size, which contains different components (Shared Pool, Database Buffer Cache, Redo Log buffer, Large pool, Java pool). These internal components also have parameters for their memory size settings:

• db_cache_size To set the memory size of the database Buffer CACHE
• Log_buffer used to set the memory size of redo LOG BUFFER
• shared_pool_size To set the memory size of the SHARED POOL
• large_pool_size To set the memory size of the LARGE POOL
• java_pool_size To set the memory size of the JAVA POOL

ORACLE10G introduces a function, that is, automatic memory allocation function, for example: has developed a sga_max_size, in this upper limit, the specific size of each part of the optimal configuration of Oracle automatically to allocate. This reduces the problem of setting the memory size of each component within the SGA. As Oracle loads change, Oracle automatically adjusts the optimal layout of memory allocations.

Shared Pool

A shared pool is the primary function of storing SQL-parsed content. For example: issue an SQL statement or command to the database execution, SQL needs to be parsed within the database, establish an execution plan, and then follow the execution plan to execute, each SQL statement will be parsed into the original operation to execute, the parsed SQL statement will be stored in the shared pool.
Shared pool is very common in Oracle instance, which is related to the performance of the database, which consists of two shared memory, which is related to the performance of the database.

• Library Cache : The contents of the library cache are stored in compiled, parsed SQL and PL/SQL statements. If the next time you execute the same SQL statement, you do not need to parse it and get execution from the library cache immediately. The size of the Library cache determines the frequency with which the SQL statements are compiled, which determines performance. The Library cache contains two parts: Shared SQL area and shared PL/SQL area. The library cache is a sub-component of the shared pool, and the size of the library cache is determined by the shared pool.

• Data Dictionary Cache : Data Dictionary is the storage of database control information. In order to increase the speed of accessing data dictionary, a cache is required and the memory is accessed when needed. The information in the data Dictionary cache contains database files, tables, indexes, columns, users, privileges, and other databases objects. Data dictionaries are most frequently used, and almost all operations need to be queried in a data dictionary (for example, to operate on a table, to query the data dictionary for access to the table, etc.), and his size is determined by the shared pool.

You can change the size of the shared pool online by simply executing the following SQL statement:

1. ALERT SYSTEM SET shared_pool_size=64m

Database Buffer Cache

The task of the database Buffer cache is primarily to store data in data files, where the data is stored on disk, and it is not possible to put all of the data file contents into memory, but the data on the disk can be accessed slowly, Then you need to put some of the data into memory, when the user to access the information that needs to be accessed in memory, then there is no need to access the disk, which increases the speed of access.
The database buffer cache contains the data from the file and is going to be written to the data file (also dirty buffer), which will be saved to the database buffer cache. and the database Buffer cache size is the largest. For example, when a user accesses a record in a table, when the database receives the request, he first looks in the cache for a record of the database table, and if the record is read directly from memory, it is returned to the user, otherwise it can only be read on disk. Obviously, if you want to improve performance, you need to increase the cache hit ratio.
Data Blocks (blocks) are stored in the database Buffer cache. Data in Oracle is stored by block, which is the most basic unit of Oracle storage. The setting for the SIZE of BLOCK in the database Buffer cache is the corresponding parameter: db_block_size.

The database Buffer cache contains three parts:

• Db_cache_size
• Db_keep_cache_size
• Db_recycle_cache_size

The Database Buffer cache can be used to change the size online, simply by executing the following SQL statement:

1. ALERT SYSTEM SET db_cache_size=64m

In the performance tuning process, you need to monitor the behavior of db_cache_size and his statistics, you can use the Db_cache_advice parameter to set whether to collect information, if the information collected then the collected information will be placed in the V$db_cache_advice table.

Redo Log Buffer

The database is in constant change, for example, sometimes the transaction fails to rollback, you need to redo log files to record the changes in the database, if you want to restore the database files with the contents of redo Log files to recover. Redo log file has a corresponding cache in memory which is called Redo log Buffer. Each time the data changes, in the redo log there will be a corresponding record, this record is called Redo Entries, a redo Entries can restore a database changes. The size of the Redo Log buffer is determined by the parameter log_buffer.

Large Pool

Large pool is primarily designed to handle some additional work, such as using Rman for backups that require the use of the Large pool or for parallel processing when Large pool is used. Large Pool is also used when doing some IO operations. The size of the Large POOL is determined by the parameter large_pool_size.

Java Pool

Oracle supports Java programming, such as Java-written stored procedures, and the size of the Java POOL is determined by the parameter java_pool_size.

PGA (Program Global area)

Unlike the SGA, the PGA (Program Global Area procedure), each background process corresponds to a PGA (for example: Pmon will have a PGA corresponding to it). The PGA and SGA are equal relationships, and there is no inclusion relationship.

Process Structures

Oracle is divided into three types of processes: User process, Server process, Background process. Where both the server process and background process belong to the background process. In order to connect to an Oracle backend server, user process is a required process on the client. The process of Oracle itself is subdivided into two categories, the previously mentioned server process and the background process. Oracle can run on a variety of platforms, while Linux and Windows process differently, and in Linux the process is the basic unit, so Oracle embodies multiple processes in Linux (or Unix). While Windows typically runs multiple threads in a process, Oracle is represented in Windows as a large process that runs multiple threads.

User Process

Oracle User process is the Oracle client process, and any program that wants to connect to the backend of Oracle is the client of Oracle (typically compiled with the class library implementation provided by Oracle). For example, using Sql*plus to log in below, you can see from Windows Task Manager that this is Oracle User Process.

Server Process

Oracle's background processes fall into two categories, one for background process and the other for server process. The Server process is primarily responsible for connecting to the user process. There are two ways to connect Oracle: Dedicated server mode and shared server mode. The server process is processes that process connections between the client and the server.
Take Linux for example, first by command Ps–ef | grep Oracle View Oracle process.

When a user process is connected to the server process, take Linux as an example, via Ps–ef | grep Oracle then discovers that there is a process in the background process (in this case: ORACLEDLW) that the client can connect to Oracle server, which is the process of communicating with the client process, which is one of the Server process.

Servers in Oracle can also connect to server process via the Sql*plus command.

So what's the difference between a client machine accessing Oracle server using client tools and using Sql*plus directly on an Oracle server? The two large rectangular boxes represent two physical machines, the yellow box represents the network card, the circle in the host indicates the process, the two machines are communicating, the connection is required through the network card, and the connection is usually established via TCP/IP. There are two ways to communicate between processes on the same computer: the first is to communicate using IPC, and the other is to simulate TCP/IP, and in Linux there is a special Nic "Lo" which is called the local Loopback (LAN). His IP address is always 127.0.0.1, even if the computer does not have a physical network card. This approach is actually local to local communication.

Ipc:inter Process Communication, including several forms of shared memory, queues, semaphores, and so on.

Background Process

Background process is at the heart of Oracle instance, and there are many Background processes in Oracle, with commands ps–ef | grep Oracle to see the Oracle process, O Racle Background process is prefixed with "Ora_ ", with "_ DB Instance name " as the suffix.

Oracle Background process is divided into two types:

• Mandatory Background Processes (required background process): These processes must be present when Oracle instance boot is complete (for example: DBWN where n means starting from 0, there may be multiple processes).

• Optional Background process (optional background processes): Depending on the configuration, these processes may start or not start.

Dbwn (Database Writer)

DBWN is the most demanding process in Oracle. His task is to write the modified content (Dirty buffer) in the database buffer cache in the SGA to the data file, which has already been modified in the buffer, known as Dirty buffer (dirty data) in Oracle. Data buffer cache dirty buffer through the DBWN process to write to the data file, if the load of the database is relatively large, the request from the client is more, there are a large number of IO operations, it is necessary to frequently write the contents of the buffer to disk file, Then you can configure multiple DBWN (Oracle supports 20 DBWN,DBW0-DBW9,DBWA-DBWG). Typically, a small to medium Oracle requires only one DBW0 process.

The condition that triggers the DBWN process to write the contents of the buffer to the disk file:

• Checkpoint occurs
• Dirty Buffer reach threshold
• There is no free buffers
• Timeout occurs
• RAC Ping request is made
• Tablespace OFFLINE
• Tablespace READ only
• Table DROP or TRUNCATE
• Tablespace BEGIN
• BACKUP

LGWR (LOG Writer)

There is only one LGWR process in Oracle instance, and this process works like DBWN process, and LGWR process writes the contents of redo log buffer to redo log files. Redo log buffer is a cyclic buffer, the corresponding Redo log files is also a cyclic filegroup, starting from the file header, when the file is full, and will be written from the file header (will overwrite the previous content), in order to avoid the Redo Log File overrides can optionally be written to the archived Redo Log files.

Conditions that trigger the LGWR process:

• At Commit
• When One-third full
• When there is 1 MB of redo
• Every three seconds
• Before Dbwn writes

Smon (System Monitor)

Smon system Monitor, when the Oracle runtime suddenly goes down, the next time Oracle instance is launched, it will have some resources that are not released, which are cleaned by Smon , when some transactions fail and are cleaned by Smon, or because the memory space is scattered (discontinuous) it is necessary to smon this scattered space, for some temporary segment is released by Smon.

Pmon (Process Monitor)

The Pmon Process Monitor, which monitors the individual Oracle daemon processes, checks to see if each process is working properly, discovers that the exception process clears it, and rebuilds the process.

CKPT (Checkpoint)

CKPT (Checkpoint) is mainly used to write Checkpoint (checkpoint), update checkpoint information at file header in data file, and update checkpoint information in control file.

ARCn (Archiver)

ARCn (archiver) is an optional process in Oracle (almost as a required option), Oracle can run in two modes, one is Archivelog mode, and the other is Noarchivelog mode (non-archive mode). Redo log files is a cyclic file set, written from scratch, and then back to the beginning of the writing (i.e. overwrite the original), before overwriting the old information through the ACRN process can be written to archiver Redo log files (archive log file), Basically all production databases will run in Archiver mode, or the database will be hard to recover after a catastrophic event.

Logical Structure

Oracle has a set of logical structures (logical structure), an Oracle server with only one Oracle database, and one Oracle database can consist of multiple tablespace, A tablespace can have multiple segment components, and a segmnet can consist of multiple extent. A extent is a contiguous set of blocks, block is the smallest basic unit in Oracle, the block in Oracle corresponds to the operating system block, and the operating system's file system is made up of block, A block that corresponds to a page,oracle block in memory is an integer multiple of the block of the operating system file system, for example: One block of Oracle corresponds to the operating system's 1 blocks or one block of Oracle corresponding to the 2 blocks of the operating system. Oracle has an important parameter, db_block_size, that sets the SIZE of the Oracle BLOCK (typically Oracle's block=8k). A bunch of contiguous blocks make up the extent. Many extent (not necessarily continuous) make up the segment. One or more segment form a tablespace. One tablespace or more tablespace makes up Oracle Database. Oracle has a lot of data file, and these data file is actually his physical structure. A tablespace can be composed of multiple data file, a segment can also be composed of multiple data file, but segment and data file does not have affiliation, a data file must belong to a tablespace, But a data file does not necessarily belong to a segment,extent cannot cross data file, a extent can only exist in one data file.

Processing SQL Statements

The process of processing SQL statements by Oracle is divided into the following phases: The client connect to the background to establish a connection, set up a session, after the establishment completes, sends the SQL statement to the background, Send to the background of the first stage parseing (in fact, before doing parseing to do security checks, to see the user's permissions), Parseing is to parse the SQL statement, the SQL statement into its ability to perform atomic operations; The next stage is called banding, is to bind some variables to the SQL statement, then start execution, and after executing the SQL statement, return the returned results to the client.
There are several types of SQL statements: One is the query statement, the SELECT, and the second is DML (data manipulation language), which is insert, DELETE, UPDATE. For example, to update a record, this statement through the parseing, after the security check and after parsing into an atomic operation to start execution, if the record is not in the SGA database Buffer cache, will be read from the data file on the disk , and then update the operation.

Reprint: http://jianshi-dlw.iteye.com/blog/1554892