An explanation of Oracle database architecture

1,oracle architecture, process management, and structural components in detail

User process: A process that connects to a database server through which the user interacts with the database. However, the user process cannot interact directly with the database, and it must be connected to the server's component server process.

Server process: An explanation of the SQL statements passed by the user process, responsible for commit and rollback, participating in IO. When the server Process is started, a memory area is allocated, called the PGA (Process Global area), divided into four parts: stack (pointers to other PGA memory structures), session info (save session information), cursor info (Information for each session cursor), and the sort area (sort region). The PGA Zone is a private zone and cannot be shared by other processes

2, according to the diagram, to facilitate our memory, the diagram is divided into three parts, the left user process, the Server process, the PGA can be seen as the clinet end, the above example (Instance) and the following database and parameter file ( parameter file), password files (password file), and archived log files (archived logfiles) compose Oracle Server, so the entire diagram can be understood as a C/s architecture. Oracle server consists of two entities: instance (instance) and database. The two entities are independent, but connected together. During the database creation process, the instance is first created before the database is created. In a typical single-instance environment, the relationship between the instance and the database is one-to-one, an instance connects to a database, and the instance and the database can also be many-to-one relationships, that is, multiple instances on different computers open a common database on the shared disk system. This many-to-one relationship is known as the actual application cluster (real application Clusters,rac) RAC greatly improves database performance, fault tolerance and scalability (which may consume more storage space) and is an essential part of the Oracle grid concept.

Let's look at the architecture of the Oracle database in detail

Oracle architecture has two main components: DB instance (Instance) and database file

DB instance

Refers to the memory and related handlers of the database server, which is the heart of Oracle. The most common performance relationship with Oracle is the SGA (System global area, which is the live shared memory area of the systems globally), and the SGA contains three parts:

1, data buffer, can avoid repeated reading of commonly used data;

2, log buffer, improve the speed of data deletion and change, reduce disk read and write and speed up;

3, shared pool, so that the same SQL statement no longer compile, improve the speed of SQL execution.

Another part of the Oracle DB instance is the background process, which mainly includes:

1. System monitoring Process (Smon)

2. Process monitoring (Pmon)

3. Database write process (DBWR)

4. Log write process (LGWR)

5. Inspection point process (CKPT)

6. Other processes (Others)

These background processes combine to complete database administration tasks

When accessing the database. Start the instance first. The memory area should be allocated before starting the instance. Then start the background process. The first five processes above must be started during database startup. Otherwise, the instance cannot be created.

Note: When you start the Oracle database. Because some processes are started automatically in the boot entry. So it takes up a large part of the memory. If you have other programs on your computer that are more memory-intensive. When starting the Oracle database, it is possible to start failing due to insufficient memory.

Oracle Server

The Oracle server consists of two parts: a DB instance and a database file. They constitute the so-called Data management System (DBMS)

Physical structure: The physical structure of Oracle refers to a number of files that exist on a hard disk. The ultimate goal is to store and retrieve relevant data. The files that exist on the hard disk make up the physical structure of the Oracle database

Oracle consists of three types of files:

1. Data files

2. Control files

3. Redo Log Files

parameter file and password file:

Parameter file: Defines the attributes of the DB instance, which is the body file

Password file: Gives the user the instance to start and close the database. Mainly consists of binary data

Memory Structure of Oracle database: SGA and PGA

SGA: Part of a database instance. The start of a DB instance allocates the system global Zone (SGA) first

1. Shared pools: Shared SQL and PL/SQL code, where the parsed SQL code is cached, and PL/SQL is not only cached but also shared. This greatly improves the speed of direct access to the disk for SQL.

1) Library cache: Stores recently used SQL statements. Use the LRU (least recently used) algorithm to manage the cache. The least recently used algorithm is a more commonly used algorithm. Here when the library cache is close to full. It clears the cache of the least recently used data in the cache. Add a new cache SQL.

To view the size of a shared pool use parameter shared_pool_size

Sql>show parameter Shared_pool_size

Set the shared pool size

Sql>alter System Set shared_pool_size=16m

Note: In the following content. If the relevant parameters are mentioned. If no special instructions are specified. Setting and viewing parameter values is a similar approach. Will not be repeated in the future. And these parameters must be logged on as System administrator to view and modify. It is not possible to view and modify with a similar Scott user.

Oracle provides db_cache_size (database size) and db_block_buffers (number of database blocks) to set the size of the library cache, which is the result of both.

2) Data dictionary cache

Stores the definition of data files, tables, indexes, columns, users, permissions information, and other database objects. The relevant data dictionary information is put into the cache to improve the response time of the query. Its size depends on the size of the shared pool.

2. Database Cache

Stores data that has recently been read from the data file or that the user has changed to write back to the database. Memory reads are much faster than disk reads. Increased access speed. This access also uses the LRU (least recently used) algorithm.

The Oracle database introduces the buffer advisor parameter (buffers cache advisory parameter) for starting and closing statistics. This information is used to predict the different behavior characteristics caused by the size of different buffers. Three states: OFF:ON:READY

After the advisor cache is set to open. Dynamic performance View available: V$db_cache_advice View recommended information for buffers

3. Redo the log cache

After you perform an INSERT, update, and so on. The data has changed. These changed data is written to the redo log buffer before it is written to the database cache. Include data before the change. You can view its size by initializing the parameter log_buffer. This parameter is a static parameter and cannot be modified.

4. Large pool and Java pool

Large pools (Large pool)

In the SGA, a large pool is an optional buffer. It can be configured with administrative authority as needed. It can provide a large area for operations like backup and restore of database.

This is enabled in several scenarios:

One, backup and restore operations

Second, when starting UGA, I/O slaves

Third, when setting up the MTS server, the user information is also used to large Pool

You can change the size of the large pool by using the following command:

ALTER SYSTEM SET large_pool_size = 64m;

Java pools are used to compile instructions written in the Java language. View and modify size by java_pool_size. The size is automatically assigned and managed by the database in the SGA. So the value is 0 when viewed;

Oracle Connections and Sessions

Connection (connection) The two concepts of Session (session) are closely related to the user process, but they have different meanings.

Connection: The communication channel between the user process and the Oracle instance (communication pathway). This communication channel is through inter-process communication mechanisms (interprocess communication mechanisms) (running user processes and Oracle processes on the same computer) or networking software (network software) ( When a database application is running on a different computer than the Oracle server, it is required to communicate over the network.

Session: A connection that a user establishes with an Oracle instance through a user process [the connection here is different from the connection above, mainly referring to the relationship between the user and the database]. For example, when a user starts Sql*plus, a valid user name and password must be provided before Oracle establishes a session for this user. The session lasts from the time the user starts connecting to the user disconnecting (or exiting the database application).

The same user in the Oracle database can create multiple sessions at the same time. For example, Scott/tiger users with username/password can connect to the same Oracle instance multiple times.

When the system is not running in Shared services mode, Oracle creates a service process (server processes) for each user session. When a system is running in Shared services mode, multiple user sessions can share the same service process.

5, as an example of executing an SQL statement, from issuing an SQL statement to returning a result set, can be divided into three parts:

5.1, Parse:

(1) Restore to ASCII code, generate Hash_value

(2) to the PGA matching Hash_value, for fast soft parse, if not found in the PGA, to the library cache matching Hash_value, for soft parsing

(3) If none are found, turn to hard parse (parsing: Whether SQL statements conform to syntactic requirements, semantic analysis: Whether an object exists in an SQL statement, a security audit: whether the user issuing the SQL has permission to perform an operation, is optimized by the optimizer, and the row resource is generated).

5.2. Execution: Find the required data and objects according to the execution plan

5.3. Get: Return the found data to the user process

Instance (instance): means of accessing the database, including the SGA (System Global area), instance process

The SGA includes:

Shared pool:

Library cache: Execution plan for cached SQL statements

Data Dictionary Cacche: The row that holds the most recently accessed dictionary

Database buffer cache: Caches a copy of the hard disk data (buffer io bottleneck) in 8K, using the LRU (least recently used principle) mechanism for space management.

Large Pool: Saves data that is not related to SQL, plsql, such as the block used to restore a data backup.

Java Pool: Providing caching for Java compilation

Streams Pool: The cache stream replicates the data, and if stream replication is enabled and not defined Streams Pool,oracle assigns 10% of the shared Pool as the stream pool.

Log Buffer: Save redo entries, change all entries, and participate in recover in the future.

Background process

Pmon Process Monitor: Monitor other non-major core processes, if the exception is restarted, clean up the garbage data due to user process exception, in the dynamic registration of the instance, is responsible for registering the instance to the listener process, in the cluster environment, Collects the current node's CPU pressure every 60 seconds, enabling load balancing of the load balance based on CPU pressure,

DBWN (Database Writer): The database Buffer cache inside the dirty data synchronized to the physical hard disk, so that the data synchronization, where n value from 0~9,a~z, altogether 36, the default is one. Trigger conditions include: CKPT (checkpoint), database buffer cache reaches storage limit (10% of storage capacity), database buffer cache does not have free space for write operations, timeout, RAC Ping Request Ping requests in a clustered environment, table-level truncate or drop operations, tablespace ReadOnly, tablespace offline, begin backup.

Smon (System Monitor) systems monitoring processes: space management (merging idle temporary segments, recovering temporary segments), instance recovery (involving roll forward, lock mechanism)

CKPT (CheckPoint) Checkpoint: Dispatches the data write process, writes the completed checkpoint to the data file header, and writes the completed checkpoint to the control file.

LGWR (log writer) Log write process: Writes the contents of log buffer to the online log binaries of the hard disk. Trigger conditions include: Commit, 1/3,timeout of the log buffer space, and write the log before any data is written.

RECO (Recover): In Distributed systems, complete the recovery of transactions in a distributed environment.

CJQ0: Job Queue scheduling process: Scheduling the job queue.

JNNN: The job dependent process for the job service process.

ARCn: Archive process.

QMNN: Queue monitor.

Mman: Memory management.

Mmon: Memory monitoring.

MMNL: Memory LED.

How the database is stored on the hard disk

Controlfile control files: Record the physical information of the database, database properties, database name, ID number, creation time, resetlog change numbers, role, etc. record database physical information consistency file, at least one.

Redlogfile online log files: Record all changes in data blocks (entries saved in Logbuffer), at least two.

datafile data files: A database can contain 65,535 files, each of which can manage up to 4M of Oracle blocks.

Passwordfile password file: Record the Super User's name and password, used to do the super user audit.

Parameterfile parameter file: Records the initialization parameters of all non-default values, constraining the behavior of the instance.

Tracefile trace File: Asii code format text file, record database work some information and some error content, used to do trouble shooting.

Archivedlogfile Archive log file: A mirrored backup of the redologfile.

An explanation of Oracle database architecture