Reading summary: Oracle software Architecture

Oracle
The Oracle database server consists primarily of two components: databases and instances.

Instance
. Instance Memory Structure: SGA (System global Areas) and PGA (program global Areas)
. Instance's process structure: service processes and background processes

Memory structure:
SGA (System global Areas)
1. Database Cache Db_cach_size
. Keep Cache Pool Buffer_pool_keep
. Recycle Cache Pool Buffer_pool_recycle
. Default Cache Pool db_cache_size-(buffer_pool_keey+buffer_pool_recycle)
2, redo log cache Log_buffer
3. Shared Pool Shared_pool_size
Library caching: Used to cache SQL and Pl/sql program code that has been parsed and executed
. Enjoy SQL workspace: Syntax analysis results and execution plans that contain SQL statements. If there is no free space in the shared SQL workspace, Oracle will use the LRU algorithm to swap out the most recently used SQL statements----->SGA
. There are SQL workspaces: Another part of the SQL statement is saved in a private SQL workspace, including information such as binding variables, environment, and session parameters in the SQL statement, which are private to the user who executed the statement, and cannot be used by other users even if they execute the same SQL statement. The private SQL Workspace is created in the SGA only when the shared server (servers=shared) is available. Typically, the private SQL Workspace is located in the PGA.
#库缓存的大小与open_cursors密切相关. The open_cursors parameter is used to specify the number of cursors that can be opened simultaneously in the database, and each query statement requires a cursor.
Database Dictionary caching: This is a "row cache" in which a record is saved, rather than a cache block that is saved like other caches.
4. Java Pool
5. Large Pool Large_pool_size
. Perform database backup and restore operations
. Execute SQL statements with a large number of sorting operations
. Entertain Concurrent Database operations
#如果没有在SGA区中创建大型池, the cache space required for these operations will be allocated in the shared pool or PGA.


PGA (Program global Areas)
The memory structure that holds the data and control information of the service process, the memory structure is unshared, and only the service process itself can access its own PGA area. Each service process has its own PGA area, and the total PGA of each service process is the size of the PGA area of the instance.
1. Private SQL Work Area
When performing more complex queries, such as query statements in decision support systems, it is often necessary to create a larger dynamic buffer in the PGA to specifically perform some of the following operations that must be done in memory:
. Sort (Order By,group by)
Sort_area_size
Sort_area_retained_size
. Connection (inner join)
Hash_area_size
. Set Operations (Union,interset,minus)
Create_bitmap_area_size

#servers =dedicated
Private SQL workspaces will be saved in the PGA of the service process
#servers =shared
Part of the private SQL workspace will be saved in the SGA area


2. Session Memory Area
Variables for saving user sessions (login information) and other session-related information
#servers =dedicated
The session memory area is stored in the PGA of the dedicated service process that serves this session. Only this service process needs to use session information for that session.
#servers =shared
The session memory area will be in the SGA area instead of the PGA area because session information is used by all shared service processes.

Workarea_size_policy=auto
Pga_aggregate_target=xxx


Process structure

Service process:
1. Dedicated service process: A dedicated service process can only serve one user
2. Shared Services Process: A shared service process can provide services to multiple users
Resolve and execute the SQL statements submitted by the user.
To search the database cache in the SGA area for data accessed by the user process, and if the data is not in the cache, you need to read the required data from the hard disk data file and copy them to the cache.
. Returns data to the user process.

Background process
Dbwn
Db_writer_processes
. When a user performs an operation such as insert or update, the inserted data is first written to the database cache. In this process, if Oracle searches for a cache block of certain data (Db_block_max_scan) in the LRU list of the database cache, the available free cache blocks are still not found, and the DBWR process starts, after the DBWR process writes the dirty cache block to the data file. The database cache will get more free cache blocks.
. When a checkpoint occurs, the DWBR process is started
. When the length of the database cache LRU list reaches half the Db_block_write_batch specified value, the DBWR process starts.
The DBWR process is started if a timeout occurs (not started in about 3 seconds).
Lgwr
. The user submits the current transaction through a COMMIT statement
. Redo Log Cache is One-third full
. DBWR process begins writing dirty cache blocks to data files
If a timeout occurs (approximately 3 seconds), the LGWR is started.
CKPT
Log_checkpoint_timeout
Log_checkpoint_interval
Log_checkpoint_to_alert

Smon
Pmon
ARCn
Archive_log_start

Reco
dnnn