Oracle memory structure and background process


Oracle memory structure and background process

Oracle instance = memory + background process

Oracle database = instance + physical storage structure

It can be seen from the above figure that Oracle instance (an instance) is composed of memory structure and program structure. The memory structure is mainly SGA, and the program structure is mainly background process.

The physical storage structure is mainly database file.

This time, I carefully studied the specific content of Oracle instance.

Why is Oracle database so complicated?


  • Speed of data query
  • Better improve the speed of data processing

Background process

  • A service process to accomplish a specific task

data file

  • Permanent preservation of data
  • It is also the main reason for the decline of database performance

Memory structure of instance

System global area(SGA)

SGA area includes a series of memory components required by Oracle instance to store data information and data control informationMemory information is shared by all processes。 The components of SGA include:

form describe
Database buffer cache The database cache contains data blocks read from disk to memory, which can be accessed by all sessions and shared globally. Buffer cache is divided into three parts: default pool, keep pool and recycle pool. The default pool is the memory area where data blocks are stored under normal conditions. The default pool will write expired dirty data (modified data, unmodified data can not be written to disk) according to an expiration algorithm (LRU, least recently used). This area is used to keep some data in memory. The recycle pool stores some infrequently used data blocks to avoid occupying space in the default pool.
Shared pool The shared pool caches some information that users can share: 1. SQL statements that can be reused; 2. Storing some data dictionary information, including user account data, tables, indexes, permissions, etc. 3. Stores executable code stored in the database.
Redo log buffer The redo log buffer stores the redo data generated by database operations. The redo log buffer is written in a circular manner. When the redo log has been written to the disk, it can be covered by subsequent log data.
Large pool The LRU algorithm is not suitable for data processing. It is a more funny way to collect memory than shared pool. Large pool is used for parallel execution. When the parallel backup mode is started during RMAN backup, large pool is used.
Java pool This memory area is used to store java code and data in the JVM (Java virtual machine) of all specific sessions.
Streams pool It stores stream related information, such as stream queue, which also provides process memory space for capture process in stream replication. Streams pool only provides memory space for stream replication. If there is no manual configuration or configuration for stream replication, this space will be set to 0.
Result cache Result cache: when the access mode of the table is mainly read, a small number of records are filtered from a large table as the result set, and the query result set is put into the result cache. The subsequent same query statements can directly obtain the desired results from the result cache, which saves the CPU and I / O overhead. This SGA component speeds up the execution of frequently running queries.


Program global area(PGA)

Different from SGA, PGA belongs to exclusive memory area. Its data and control information are unique to a session. When a session is generated, Oracle will allocate a PGA memory area for the session. It can be understood that the memory space allocated by the operating system to a process when it starts is a memory area in the sense of operating system.

User global Area(UGA)

UGA stores information related to the current session, such as session login information, PL / SQL variables, bound variable values, etc. UGA can be in SGA or PGA depending on the connection mode.

Software code areas

Oracle stores a part of the memory area of its own software code, which is not allowed to be accessed by other sessions

Background process

The process of Oracle

User process

Server process

Background process

Windows to see what background processes Oracle has

SQL> select program from v$session where program like 'ORACLE.EXE%';




26 rows selected.

Let’s focus on some Oracle daemons (bridges linking memory and disk)

System monitoring process SMON

Oracle database is an important background process, SMON is the abbreviation of system monitor, which means: system monitoring.

The main work of SMON is as follows:

  • Instance recovery when the database is started. In RAC environment, the SMON of one node can restore the instance of another node
  • Clean up and release data on temporary segment (sort, temporary table…)
  • For DMT (Dictionary managed table space), SMON can merge continuously idle extents
  • Maintain the online, offline and space recovery of the rollback segment

Process monitoring process PMON

PMON is the abbreviation of process monitor. PMON is mainly used for the following purposes:

  • In the process of abnormal interruption, do cleaning work
  • After the process abort, PMON cleans up.
  • The third purpose of PMON is to register instance information with Oracle TNS listener.

Database write process DBWn

DBWn is the abbreviation of database writer. N means that multiple write processes can be set.

DBWn is responsible for writing the dirty data of the buffer to the disk, and the DBW process writes the data to the disk in a decentralized manner. LGWR writes redo log continuously. Distributed writing takes more time than continuous writing.

DBWn trigger condition:

  • Triggered when buffer cache space is insufficient.
  • Triggered when DBWn receives a checkpoint instruction.

Log writing process LGWR

LGWR is a process that writes the information of redo log buffer in SGA to redo log file. LGWR is written to redo log file in sequence, so it is very fast. LGWR occurs when:

1. LGWR is performed every 3 seconds

2. Any transaction has been committed

3. When the redo log buffer is 1 / 3 full, or there is 1MB of data in it

For the above reasons,It is unnecessary to set the redo log buffer to a large size

Checkpoint process CKPT

CKPT is the abbreviation of checkpoint. According to checkpoint information and DBW write data block signal to disk, CKPT updates control file and data file header. Checkpoint information includes the checkpoint position, SCN, location in online redo log to begin recovery, and so on.

Archive process ARCN

ARCN, archive archive process. The work of ARCN is that after LGWR fills the onlone redo log, ARCN copies the contents of the redo log file to other places. (that is to say, to turn the online redo log into an archive log). Online redo log is used to recover data files when an instance fails. The archive log is used to recover data files during media recovery.

The difference between checkpoint and commit

commitIt is used to commit the logs generated by those transaction modified data, that is, to trigger LGWR to write the contents of redo log buffer to redo log files. At this time, the real data is not written to the disk.The purpose of commit is to write to redo log files to protect data.

checkpointThe DBWn process is triggered to write dirty data blocks to the data file. If the dbwr process wants to write the result of the transaction to the data file, but finds that the redo information related to the dirty data block to be written is still in the redo log cache, it will notify Oracle to start the LGWR process and write the redo information to the redo log file first. After all the redo information has been written, the dbwr process will start writing the dirty cache to the data file. thereforeThe purpose of checkpoint is to ensure data consistency.

reference resources:

Oracle official website – managing the Oracle instance

Oracle official website – Process Architecture

Remember to give me some compliments!

Carefully sorted out the various directions of the computer from the entry, advanced, actual combat video courses and e-books, according to the catalog reasonable classification, you can always find the learning materials you need, what are you waiting for? Pay attention to download now!!!

Oracle memory structure and background process

Never forget, there must be a response, friends help me point out a praise, thank you very much.

I am a professional bright brother, YY Senior Software Engineer, four years of work experience, refused salted fish strive to be the leading slash programmer.

Listen to me, more progress, a shuttle program life

If you are lucky enough to help you, please give me a “like” and give me a concern. If you can give me a comment and an encouragement, I will be very grateful.

List of articles by bright brother in the workplace:More articles

Oracle memory structure and background process

All my articles and answers have cooperation with the copyright protection platform. The copyright belongs to the workplace Liang brother. Without authorization, reprint must be prosecuted!

Recommended Today

Solutions to leetcode problems and knowledge points 1. Sum of two numbers

Title Link 1. Two Sum  difficulty: $/ color {00965e} {easy}$ Knowledge points 1. Hash (hash) function The classic hash function times33 is widely used, and the core algorithm is as follows: hash(i) = hash(i-1) * 33 + str[i] Laruence has an article about:Hash algorithm in PHP 2. Hash collision processing method 2.1 chain address method […]