Shulou(Shulou.com)06/01 Report--

Oracle memory structure Oracle process structure article summary Preface The previous article reflects some of the basic structure of Oracle database and storage structure, this article will talk about Oracle server structure, in fact, is mainly about the structure of the instance. You need to understand these underlying structures, either in a production environment or in Oracle. Oracle Server consists of Oracle Instance +Oracle Database. Oracle instances consist of SGA+ daemons. Each Oracle instance has its own SGA and a separate Oracle process set. Oracle memory structure

The above diagram illustrates the memory structure well, and the memory structure will be described in detail below.

Memory structure is the most important part of Oracle database architecture. When a database instance is started, Oracle database allocates memory areas and starts background processes.

The main contents of Oracle memory storage are:

Program code (PL/SQL, Java); information about linked sessions, including all currently active and inactive sessions; information necessary for program runtime, such as query plans; information about communication and sharing between Oracle processes, such as locks. Oracle's memory structure consists of: 1. System Global Area (SGA): Oracle allocates the system global area when it starts an instance 2. Program Global Area (PGA): allocates the program global area when the server starts 3. User Global Area (UGA): User Global Area stores session state for users

System Global Area (SGA)

System Global Area (SGA): Database information is stored in the system global area and shared by multiple databases. It occupies the largest area of server memory, is the most important indicator affecting the database, and constitutes an Oracle instance with background processes.

The System Global Area (SGA) consists of:

* Shared pool (including: library cache, data cache dictionary cache);* data cache;* redo log buffer;* Java pool;* large buffer.

Shared pool: memory area for parsing, compiling and executing SQL and PLSQL program processes. It consists of two key memory structures:

Library cache: stores information about SQL and PL/SQL statements recently parsed. Oracle needs to parse (including syntax parsing, object confirmation, permission judgment, operation optimization, etc.) and produce execution plans before executing various SQL statements submitted by user processes, occupying certain system resources.

Data dictionary cache: A collection of recently used object definitions, including information about data files, tables, indexes, columns, users, permissions, and database objects.

Data cache: It can speed up access and put some frequently used data into the cache, because the purpose of the database is to access efficiency in addition to security. The server process stores the data it reads in a data buffer and finds it in memory when subsequent requests require it, without having to read it from disk. All users share. It consists of three pools: 1. Default pool, 2. Retention pool (storing commonly used data), 3. Reclaim pool (storing infrequently used data), managed by LRU algorithm.

LRU algorithm management: Its principle is to keep the commonly used data in the pool, and the infrequently used data will be returned.

Redo log buffer: records all changes about the database. When executing DML statements such as:updata, delete, insert or create, alter, drop, etc., Oracle will produce redo records for these operations. The effect is that the data mainly used for database recovery and changes will depend on redo log entries.

Large buffer: Used to provide relatively independent memory space for operations requiring large memory in order to improve performance. Large pool is an optional memory structure. DBA can decide whether large pool needs to be created in SGA, such as database backup and recovery, large number of sorted SQL statements, parallelized database operations.

Java pool: Java pool must be set up when installing Java program, and instructions written in Java language must be compiled.

SQL> show sga; ----------------

Total System Global Area 1593835520 bytesFixed Size 8793256 bytesVariable Size 905970520 bytesDatabase Buffers 671088640 bytesRedo Buffers 7983104 bytes Program Global Area (PGA)

The global area allocated when the server starts is the memory that Oracle server needs to occupy when starting!

It contains the data and control information required by a single server process, which is automatically allocated when a user process connects to the database and creates a session. It maintains the information required by each user process connected to the Oracle database. PGA is a non-shared area and can only be used by a single process. When a user session ends, PGA is released. User Global Area (UGA) user process stores session state, which is which user you log in with, will store some user state! UGA can be part of PGA to SGA, depending on how Oracle is connected: UGA is included in SGA if connected through a shared server; UGA is included in PGA on a proprietary server Oracle process structure if connected through a proprietary server

Oracle instances have the following processes:

* User processes * Server processes * Background processes

user process

A user process is a program that needs to interact with an Oracle server. A user process is created when the user runs a program ready to send a request to the database server!

server process

A server process is used to process requests from user processes that connect to this instance. Server processes created when users connect to Oracle instances to create sessions!

Daemons are set up by Oracle Database to maintain optimal system performance and to coordinate multiple user requests. Oracle instances create a series of background processes when they start! Background processes coordinate the in-memory structure of the database with the physical structure of the database.

The five processes that the background process must start are DBWR, LGWR, PMON, SMON, CKPT.

PMON Process Monitoring Process:

* Clean up failed processes;* Release all currently mounted locks;* Release resources used by failed processes.

SMON System Monitoring Process:

* Automatically restore instances when reopening the database after instance failure;* organize free space in the data file and combine adjacent spaces;* release temporary segments that are no longer in use.

DBWR data write process:

* Manage data buffers to keep recently used blocks in memory;* Write modified buffer data to a data file.

LGWR Log Write Process:

* Responsible for writing log data from log buffers to log files;* The system has multiple log files, and the process writes data to files in a circular fashion.

CKPT verification process:

* Scan the checkpoint queue for dirty data blocks and write them to the data file;* The buffer headers on the checkpoint queue are arranged in the order in which the data blocks were first dirty;* The buffer headers of the data blocks modified earlier are arranged at the front;* At the same time, if a data block is modified many times, it will only appear once on the list. summary

Too many theories, but also need to use pictures to understand memory! So the summary is represented by a graph.

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.