Shulou(Shulou.com)05/31 Report--

This article mainly explains how to understand the architecture, principle and process of Oracle. The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to understand the architecture, principle and process of Oracle.

For a database system, assuming that the system is not running, all we can see related to the database are several physical files based on the operating system. From a static point of view, if we look at it from a dynamic point of view, that is to say, the database system is running and can provide services, then it is unexpected that the database system starts its own instance. Combining the above two perspectives, how does Oracle define the above description?

Let's introduce the first concept, the Oracle server, the so-called Oracle server is a database management system, which includes an Oracle instance (dynamic) and an Oracle database (static). Oracle instance is a running concept (such as a process of the operating system), which provides a way to access Oracle database, always open one, and only one Oracle database can be opened. Oracle instance is composed of SGA and some background service processes. In background service processes, DBWn PMON CKPT LGWR SMON is a necessary background process, while ad queue, rac, shared server and ad replication are optional. The reason is that Oracle can work properly without it, or some advanced functions can be used.

Oracle database is a collection of uniformly processed data, including three types of file data files, control files, and redo log files from a physical point of view. From a logical point of view, the Oracle database contains at least one tablespace, which contains at least one segment, which is made of extents and which consists of blocks. It should be noted that tablespaces can contain several data files, segments can span multiple data files in the same tablespace, and extents can only be in the same data file.

Oracle also designed other key files to serve the entire system, such as configuration files, password files, archive log files, as well as user processes and service processes, which are now easily understood to be used to execute SQL statements.

SGA

SHARE POOL

(shared pool)

You can adjust with the following command

ALTER SYSTEM SET

SHARED_POOL_SIZE=64M

LIBRARY CACHE

(library cache)

1 stores recently used SQL and PL/SQL statement information

2 including SHARED SQL and SHARED PL/SQL

3. Use LRU algorithm to manage

4 the size is determined by the SHARE POOL size

DATA DICTIONARY CACHE

(data dictionary cache)

1 collection of recently used definitions in the database

2 contains database files, tables, indexes, columns, users, permissions and other information related to database objects

3 in the parsing phase, the server process looks up information for object parsing and authentication access in the data dictionary

4 caching data dictionary information in memory can shorten the response time of query and DML

5 the size is determined by the size of the shared pool

DATABASE BUFFER CACHE

(data buffer cache)

1 store copies of data that have been retrieved from the data file

2 greatly improve the performance of reading and updating data

3 use LRU algorithm to manage

4 the size of the main block is determined by DB_BLOCK_SIZE

REDO LOG BUFFER

(redo log buffer)

1 record all changes made to the database block

2 is mainly used for recovery

3 the changes recorded are called redo entries

4 redo entries contain information for rebuilding or remaking changes

5 size is defined by LOG_BUFFER

LARGE POOL

(large pool)

1 SGA optional memory area

2 shared part of the work of the shared pool

3 UGA for shared server

4 for the Icano server process

5 backup and restore operations or RMAN

6 parallel execution message buffer (premise PARALLEL_POOL_SIZE=TRUE)

7 do not use LRU lists

8 size determined by LARGE_POOL_SIZE

JAVA POOL

(Java Pool)

1 Storage JAVA command service analysis requirements

2 necessary for installation and use of JAVA

3 the size is determined by JAVA_POOL_SIZE

PGA

PRIVATE SQL AREA

(dedicated SQL area)

The location of the dedicated SQL zone depends on the type of connection established for the session. In a dedicated server environment, dedicated SQL zones are located in the PGA of their respective server processes. In a shared server environment, the private SQL zone is located in SGA.

It is the responsibility of the user process to manage the private SQL zone. The number of private SQL zones that can be allocated by user processes is always determined by the

Initialize the parameter OPEN_CURSORS to limit. The default value for this parameter is 50.

PERSISTEN AREA

(permanent zone)

Contains binding information and is released only when the cursor is closed

RUNTIME AREA

(runtime zone)

Created in the first step when the request is executed. For INSERT, UPDATE, and DELETE commands, the zone is released after the statement is executed, and for query operations, it is released only after all rows are extracted or the query is cancelled.

SESSION MEMORY

(session memory)

Contains memory allocated to retain session variables and other information related to the session. For a shared server environment, the session is shared rather than dedicated.

SQL WORK AREAS

(SQL Workspace)

Used for memory-intensive operations such as sorting, hash joins, bitmap merging, and bitmap creation.

The size of the workspace can be controlled and adjusted

The following table is a summary of background processes

DBWn

DBWn delays writing to the data file until one of the following events occurs:

Incremental or normal checkpoint

The number of gray data buffers reaches the threshold

When the process scans a specified number of blocks and cannot find any free buffers

There is a timeout

Ping request in real-time application cluster (Real Application Clusters, RAC) environment

Make general or temporary tablespaces offline

Put the tablespace in read-only mode

Delete or truncate a table

Perform ALTER TABLESPACE tablespace name BEGIN BACKUP operation

LGWR

LGWR performs continuous writes from the redo log buffer to the redo log file in the following cases:

When a transaction is committed

When 1/3 of the redo log buffer is full

When more than 1 MB of changes are recorded in the redo log buffer

Before DBWn writes blocks modified in the database buffer cache to the data file

Every three seconds

SMON

Routine recovery

-roll forward changes in the redo log

-Open the database for users to access

-rollback uncommitted transactions

Merge free space

Recovery temporary section

PMON

After the process fails, the background process PMON cleans up by the following methods:

Roll back the user's current transaction

Release all currently reserved table or row locks

Release other resources currently reserved by the user

Restart the invalid scheduler

CKPT

Signal to DBWn at the checkpoint

Update the header of the data file with checkpoint information

Update control with checkpoint information

Checkpoints are initiated for the following reasons:

Ensure that modified blocks of data are written to the disk periodically so that data is not lost in the event of a system or database failure

Shorten the time required for routine recovery. You only need to process the redo log entries after the last checkpoint to start the restore operation

Ensure that all data submitted is written to the data file during the shutdown

Checkpoint information written by CKPT includes the location of the checkpoint, the system change number, the starting location of the restore operation in the redo log, information about the log, and so on.

Note: CKPT does not write blocks to disk or redo blocks to the online redo log.

ARCn

Optional background process

Automatically archive online redo logs when setting ARCHIVELOG mode

Keep records of all changes to the database

Finally, take an example of a user submitting a SQL statement to end this article. If the user wants to submit a SQL statement, then you must first connect to the Oracle instance. There are three ways to connect to the Oracle instance: if the user logs in to the operating system running the Oracle instance, access the 2C/S structure through inter-process communication. The application or tool that initiates the connection is usually called the user process. After the connection is initiated, the Oracle server creates a process to accept the connection, which becomes the service process. The server process communicates with the Oracle instance on behalf of the user process. In the dedicated server connection mode, the relationship between the user process and the service process is 1-to-1. In the shared server mode, the relationship between the user process and the service process is 1-to-1. Multiple user processes may share a service process. When the server process begins to communicate with the Oracle instance, a session is created. Obviously, processing a query goes through the stages of parsing, binding, execution, extraction, and so on.

Thank you for your reading, the above is the content of "how to understand the architecture, principle and process of Oracle". After the study of this article, I believe you have a deeper understanding of how to understand the architecture, principle and process of Oracle. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.