ORACLE10GR2 architecture-memory, process

Oracle Architecture Diagram 1

Oracle Architecture Diagram 2

User process (The generic name of the client that accesses Oracle) Use of tools: sqlplus, PL/SQL developer How to access the database: This machine is accessed directly via sock (IPC Access-UDP Protocol) (Sqlplus/as SYSDBA). Establish a server connected to Oracle via TCP (Sqlplus Sys/[email protected] as SYSDBA) (Sqlplus sys/[email protected]:[email Protected]_name as sysdba--access to this method requires that the Listener.ora file configuration on the server side of the Oracle database be accessed remotely. Network Configuration for Oracle (Listener.ora and Tnsname.ora). How user processes interact with server processes:

The execution of a simple SELECT statement in Oracle: 1, The user process executes a SELECT statement such as: SELECT * from EMP where empno=7839; 2, the user process has established a connection with the server process and cached the user process information in the UGA of the PGA. 3, the SELECT statement is passed to instance after the PGA processing. 4, the shared pool of instance is responsible for handling this statement, soft parsing or hard parsing. The object in the query statement is stored in which block of which Tablespace's datafile and which segment, which is obtained through the data dictionary buffer. 5, start execution of select 6, determine whether the required block is cached in the database buffer cache. If it is already cached, reading the data in memory gets the desired result back to the user, the user sees the result of the execution, and if the required block is not cached, the server process reads the block from disk into the data buffer cache, and then the undo cache block mirrors the fast And then read the data in the image to get the result of the row returned to the user, the user sees the execution result.

A simple UPDATE statement executes the procedure: 1, the user process executes an UPDATE statement such as: Update emp set sal =5000 where empno=7839; 2, the user process has established a connection with the server process and cached the user process information in the UGA of the PGA. 3, the SELECT statement is passed to instance after the PGA processing. 4, the shared pool of instance is responsible for handling this statement, soft parsing or hard parsing. The object in the query statement is stored in which block of which Tablespace's datafile and which segment, which is obtained through the data dictionary buffer. 5, the server process reads the required block into the data buffer cache. 6, determine the SQL type, select, INSERT, delete, UPDATE, here is the update operation. 7, modify the block in the data buffer cache, save the modified image to undo as a new image, when commit, LGWR write block change process, DBWR write data to the data file (undo and normal data files).

Server process: Interacts with user process to answer requests from the user process process. PGA (Program Global Zone): The server process process owns, is a memory fragment, a private memory area, protection sessions, sorting, user private information. UGA (User Global Zone): A memory area belonging to the PGA that holds session information, sorting information, and hash information. PGA role: An area used by the Oracle user process, one user for a PGA, primarily for use in variable space when the user runs the program. The PGA is automatically assigned by Oracle when the user process connects to Oracle and creates a session, the session ends, and the PGA releases. The PGA stores the contents of the bound variable, the sort area, and the cursor processing. The SGA stores the parsing information for SQL/PL SQL, but the user will not be able to share the values of the Select and UPDATE statements, and the information that cannot be shared is stored in the PGA. 9i after automatic management pga,pga_aggregate_target specifies the upper limit of memory usage, can be changed dynamically, only valid for dedicated server mode. Value range: 10m~ (4096g-1) bytes. The Work_size_policy manual|auto parameter is used to turn the PGA Auto-management feature on or off, which is auto by default. Manually modifying the Sort_area_size and hash_area_size can also control the PGA usage. 10g after the automatic management, dedicated, shared server mode is effective. V$PGASTAT:PGA statistical information; V$pga_target_advice: Predictive information for modifying the PGA size use.

Background process: The processes required to maintain the normal operation of the DB server and some specific functions. Core processes: Smon, Pmon, DBWR, LGWR, ckpt these processes must work correctly or the database will be problematic. View process: Ps-ef|grep Ora_ V$bgprocess

Smon: Instance recovery, combined with free space, free table space.

Pmon: Clear the process of failure (rollback things, release locks, release other resources, restart dead processes), dynamically register listeners.

DBWR: Writes dirty data in the data buffer cache to the background disk quickly. DBWR will trigger LGWR before writing. Launch condition Checkpoint, dirty cache reached limit, no free cache, supermarket occurrence, RAC ping request, table space offline, tablespace read only, table deleted or truncated, start backup table space. Number of DBWR: 32bit:dbw0~dbw9, up to 10 DBWR processes. 64BIT:DBW0~DBW9/DBWA~DBWJ can run up to 20 processes. Sql> Show Parameter Db_w NAME TYPE VALUE ------------------------------------ --------------------------------- Db_writer_processes Integer 1--the database runs up to 1 DBWR processes

LGWR: Writes the order of information in redo buffer to log. Log files are best stored on high-efficiency storage devices, ensuring that LGWR writes faster. LGWR write the conditions: At the time of commit, it reaches One-third full, the log size reaches 1m; every 3 seconds; before DBWR writes. Number of LGWR processes: default is only 1

CKPT: Ensure that the data files, log files, control files in the database 3 major elements of synchronization process. Function: Trigger DBWR write operation, update data file header, update control file header. SCN: System change number to ensure data consistency.

ARCN: Automatically enabled when archive mode is turned on. ARCN the number of parameters Log_archive_max_processes decided, up to 30 ARCN processes can be run. Sql> Show parameter log_archive_max_processes; NAME TYPE VALUE ------------------------------------ --------------------------------- Log_archive_max_processes Integer 4

PNNN: A parallel process for parallel queries or parallel DML operations. DNNN: Scheduler process for Shared mode connection user demand scheduling. SNNN: Contributor server process for Shared mode connection user demand action. Mman: Memory management process for automatic memory management of 10g. Mmon: Memory monitoring process for automatic collection of statistical information in 10g performance optimization. REC0: A distributed recovery process for rolling back incomplete distributed transactions. JNNN: The task execution process, which is used to complete user-customized automated tasks. Cjq0: Task scheduling process for waking a task process at a user-specified time.

Database: Composition: Data file, log file, control file, password file, parameter file, archivelog file, alert log

Server process: Interacts with user process to answer requests from the user process process. PGA (Program Global Zone): The server process process owns, is a memory fragment, a private memory area, protection sessions, sorting, user private information. UGA (User Global Zone): A memory area belonging to the PGA that holds session information, sorting information, and hash information. PGA role: An area used by the Oracle user process, one user for a PGA, primarily for use in variable space when the user runs the program. The PGA is automatically assigned by Oracle when the user process connects to Oracle and creates a session, the session ends, and the PGA releases. The PGA stores the contents of the bound variable, the sort area, and the cursor processing. The SGA stores the parsing information for SQL/PL SQL, but the user will not be able to share the values of the Select and UPDATE statements, and the information that cannot be shared is stored in the PGA. 9i after automatic management pga,pga_aggregate_target specifies the upper limit of memory usage, can be changed dynamically, only valid for dedicated server mode. Value range: 10m~ (4096g-1) bytes. The Work_size_policy manual|auto parameter is used to turn the PGA Auto-management feature on or off, which is auto by default. Manually modifying the Sort_area_size and hash_area_size can also control the PGA usage. 10g after the automatic management, dedicated, shared server mode is effective. V$PGASTAT:PGA statistical information; V$pga_target_advice: Predictive information for modifying the PGA size use.

Part of the sga:instance is the memory structure, and the SGA memory is automatically powered off by the instance startup. The SGA is a readable, writable area of memory. Components: Shared pool, data buffer cache, log buffer cache, large pool, Java pool, streams pool, other pool Fixed area: A portion of the memory in the SGA, used to manage the SGA and background processes. Sql> Show SGA Total System Global area 3741306880 bytes Fixed Size 2258800 bytes--invariant area, not shareable Variable Size 1811941520 Bytes--Variable area for sharing Database buffers 1912602624 bytes--data buffer cache Redo buffers 14503936 bytes--log buffer cache SGA Management: 8i: Manual management, individual component management, i.e. static management. The size of the 9I:SGA is determined by the initialization parameter sga_max_size. Each component needs to be individually modified, and the sum of the individual components cannot exceed the value of sga_max_size, which means that each component can be managed dynamically. 10G:SGA size can be as dynamic as 9i management, you can also try the SGA automatic management, only need to set the initialization parameters SGA_TARGET,SGA of the individual components can be automatically set by the database size, set from the system automatically collected statistical information. (The SGA jitter may occur, and the workaround can set the size of the individual components as in 9i, which is the initial value of each component to mitigate the SGA jitter). 11g:memory_target automatically manages the PGA and SGA. Granularity: The smallest unit of memory allocation. The size of the granularity can be viewed through the V$sgainfo view. S GA Size Setting formula: Sga_max_size= (80%memory) *2/3 Manage the initialization parameters for the SGA size: sga_target>0--SGA Automatic Management, otherwise dynamic management. Sql> Show Parameter SGA NAME TYPE VALUE ------------------------------------ --------------------------------- Sga_max_size Big Integer 3584M Sga_target Big Integer 3584M

Shared Pool: Function: Cache recently executed statements;   Cache Most recently used data definition Composition: library Cache   data dictionary buffers Size: Initialize parameter shared_pool_size control, 10g and later automatically assign management by Sga_target. Library Cache: Stores the most recently used SQL and PL/SQL statement information.    The statement with the longest contribution. LRU algorithm management.    includes two parts: contribute to the SQL Zone Shared PL/A area    size is determined by the size of the shared pool. Dictionary buffers: Store the most recently used definitions in the database Includes database object information such as data files, tables, indexes During the analysis phase, the server looks up the data dictionary to verify that the object's name is legitimate. For queries and DML statements, if the data dictionary is cached in the dictionary buffer, the response time can be increased. The size of the is determined by the shared pool size. SQL statement execution procedure: Shared pool setting Size principle: Shared_pool_size Sets an initial size, Sga_target sets a total size.

Database Buffer:--logical read physical reading Stores data mirroring of data blocks obtained from a data file. The LRU algorithm is managed. When processing data in the DB buffer cache, you can obtain data directly from memory, greatly improving system efficiency. The initialization parameter db_block_size determines the size of the data block, which is also the smallest unit of database I/O. The Db buffer cache consists of several self-buffers: --default Pond Db_cache_size --keep Pond Db_keep_cache_size --recycle Pond Db_recycle_cache_size Only the default pool can be automatically managed by the SGA. DBWR writes the dirty data block in the data buffer cache to disk. Data Buffer Cache Size Management: Sets the db_cache_size size and sets the size of the sga_target. You can use the V$db_cache_advice view to roughly determine how much you need to set for data buffer.

Log buffer: Can not be automatically managed by the SGA, need to be allocated separately size, size general 1~5m enough. A description of the change of all data blocks in the staging database. The memory management method is FIFO. Redo logs provide data recovery capabilities. The purpose of staging redo logs is to improve the execution efficiency of the statements. The size is determined by Log_buffer (1~2m), but the memory area cannot be resized dynamically. LGWR writes the data in the log buffer to the disk's journal file.

Large Pool: An optional memory area that supports dynamic management. Contributor Server user Global zone, parallel process, using Rman backup, size determined by large_pool_size, can dynamically change size. General Rman backups feel that database performance is affected because there is no reason to allocate large pool. Large pool not set the above operation will use the shared pool as the default memory area. Large pool size 12~64m almost enough. 10g in the SGA Automatic management It is best to set an initial size for the large pool.

Java pool: optional region; for Java program parsing and execution, size is determined by java_pool_size, support dynamic modification, if granule is 4m,java pool default size is 24m; if granule is 16m,java The pool default size is 32m, and if the database is created with the JVM set, then the pools must be set. It is best to set the initial size in 10g.

Streams Pool: Handles advanced queue-related operations, such as streaming replication technology. It is best to set the initial size in 10g. Streams pool was automatically managed by the SGA at the beginning of 10GR2.

ORACLE10GR2 architecture-memory, process