/ * Oracle Software Structure * /
Oracle instance consists of a series of background processes and memory structures. It consists of two parts: 1. Instance memory structure: SGA / PGA2. Process structure of the instance: service process and background process
/ *** SGA *** / SGA zone can be shared by all Oracle process, mainly including the following memory structure: * Database Coke * Reduce days Cache * Shared pool * large pool * Java pool * Data Dictionary cache * Structure to save other control information
The size of the SGA area is mainly determined by the following parameters: db_cache_size / log_buffer / shared_pool_size / large_pool_size
Oracle 9i can dynamically change the initialization parameters related to the SGA area during the instance operation
Database Coke (DB_CACHE_SIZE): Oracle manages data blocks in database caches by maintaining two lists 1. Write list (CD block list) 2. Recently, minimum list (LRU list)
Database Coke Structure: Depending on the characteristics of the cache block, you can store the cache category into different cache pools: KEEP Cache Bool / Recycle Cache Bool / Default Cache Pool
KEEP Cache Pool Size Recycle Cache Pool Size Default Cap Pool Size = DB_CACHE_SIZE
Redo the log cache (log_buffer): Dynamic changes in the database running
Shared_Pool_Size: Shared Pool mainly includes: library cache, data dictionary cache, cache for storing parallel operation information and control structure
The library cache: The SQL and PL / SQL program code used to cache the resolved and executed, mainly including two structures shared SQL workspace and private SQL workspace, each cached SQL statement is divided into two parts, respectively There is a shared SQL work area and private SQL workspace. * Share SQL workspace: Syntax analysis results and execution plan stored with SQL statements * Private SQL workspace: including bind variables and environmental session parameters in SQL statements, general situation Lower private SQL workspace in PGA
The size of the library cache is closely related, and the Open_Cursors parameter is used to specify the number of cursors that can open at the same time in the database, and each query statement needs to open a cursor.
Data Dictionary Cache: Save the most common data dictionary information, road database user account information, database structure information.
Large_pool_size: When performing the following, you need to create a large pool, 1. Perform a database backup or recovery operation 2. Execute the SQL statement with a large number of sorting operations 3. Perform parallelized database operations
/ *** PGA *** / Program global zone PGA is a memory structure that saves data and control information with a specific service process. This memory structure is not shared. Only service processes can access its own PGA zone. General circumstances Next, the PGA zone is composed of private SQL workspaces and session memory regions.
Private SQL Workspace: Multi-identical SQL statements will only create a shared SQL workspace, but you can correspond to multiple private SQL workspaces. You can combine a private SQL workspace with the corresponding shared SQL workspace. Get complete cache data for a SQL statement.
The private SQL workspace of each session can be divided into static zones and dynamic zones. The operation of the private SQL workspace is done by the cursor. Therefore, the number of private SQL workspaces that users can use depends on he can open. Number of cursors, by setting Open_Cursors, can limit the number of cursors that users can open at the same time. The static area of the private SQL workspace is created when the cursor is opened until the cursor is turned off, and the dynamic area is executed in the cursor Immediately released immediately.
Session Memory: Session Memory Area is used to save the variable of the user session (login information) and other information related to the session.
Setting SQL Workspace: When performing some SQL statements that include special operations, users need to create some dedicated memory structures in the PGA area of the service process for the user session, which is collectively called SQL workspaces. These operations include sorting, Connection and collection operation. The most common SQL workspace when the zone is sorted, and the maximum sort area that each user can use is specified by the initialization parameter sort_area_size. /*** Service Process *** / Service Process Mainly completed the following tasks: * Resolution and execute the SQL statement submitted by the user * Search the data to be accessed * * Return the data to the user
/ *** Backstage process *** / dBWR, LGWR, CKPT, SMON, PMON is the back-end process required for Oracle instances
/ * DBWR process * /
Oracle can start up to 10 DBWR processes.
Only when the following case occurs, the DBWR process begins writing a dirty caclet block to the data file: * If Oracle searches for a certain number of cache blocks (DB_BLOCK_MAX_SCAN) initialization parameter settings), there is still no available The idle caclet block, the DBWR process will be started * When the checkpoint occurs, the DBWR process will start when the database cache LRU list reaches half of the initialization DB_BLOCK_WRITE_BATCH specified value, that is, if the database is cacked There are too many dirty cache blocks, which will start the DBWR process to write some of the dirty cache blocks * * When the timeout occurs (not started within approximately 3 seconds), the DBWR process will be started.
Initialization parameters related to DBWR processes 1.DB_Writer_Processes: Set the number of DBWR background processes when creating instances 2.db_block_checkpoint_batch: Set the maximum number of dirty cache blocks written in each checkpoint, increase this Parameters, can extend the time interval of starting the DBWR process
/ * LGWR process * /
Only when the following cases occur, the LGWR process begins writing cache data to redo log files: * Users submitted the current transaction through the commit statement * Redo the log cache is written to one-third * DBWR process starts to write the dirty block Into the data file * Every time three seconds, the LGWR process will be started at once.
After the transaction is submitted, Oracle will generate a system change code (SCN) for this transaction, and the SCN of the transaction will be recorded in its submission record and recording.
It is not necessary to worry that the redo record of the uncommitted transaction is also written in group submission, because the redo record of these transactions is not recognized by Oracle because of the lack of a submission record.
/ * CKPT process * /
"Checkpoint" is an event. When the event occurs, the dirty cache block in the database cache will be written to the data file, while Oracle will update the database control file and data file to record the current database structure and status. After executing a checkpoint, Oracle knows all the changes made to the database for the database have been written to the hard disk. At this time, the database is in a full state. After the database crashes, only the database will be restored to The last checkpoint is time.
The CKPT process itself completes two pieces: * Perform checkpoints and update control files and data files * Hand it to the DBWR process to write the task of the dirty block to the DBWR process
Oracle performs different levels of checkpoints at different times, and there are many checkpoints based on a specific operation inside the database. For example: Oracle When you switch to each redo log, perform a database checkpoint. When performing a database checkpoint, the DBWR process writes all the dirty blocks in the cache into the data file; and in a table space is set to off When the operation is status, ORACEL performs a tablespace checkpoint. When performing a tablespace checkpoint, the DBWR process will only write the dirty cache block associated with the table space in the cache, in addition, it can also set the time-based The checkpoint is permitted for a long time.
In Oracle 8i previous versions, the CKPT process does not start by default. To start the CKPT process, you must set the initialization parameter checkpoint_process to True. In Oracle 9i, the initialization is discarded from the parameters. The CKPT process will be automatically started when the instance is started. Initialization parameters related to the checkpoint: * log_checkpoint_timeout is used to specify the maximum interval (in seconds) of the checkpoint execution time (in seconds), If this parameter is 0, the time-based checkpoint * log_checkpoint_interval user specifies that the number of operating system blocks in the redo log file must be written before the checkpoint appears.
/ * SMON process * /
System Monitoring Process SMON is responsible for restoring the database at the time of instance, and SMON is also responsible: * Recycled storage debris in the temporal or temporary table space * combining the idle space in one tablespace
/ * PMON process * /
Process Monitoring Process PMon is responsible for recovering those failure user processes or service processes, and releases the resources occupied by the process.
/ * RECO process * /
Recovery process RECO is responsible for automatically restoring those failed distributed transactions in a distributed database environment
/ * ARCN process * /
To launch the ARCN process, you need to set the initialization parameter archive_log_start to true. After the ARCN process is started, the database will have an automatic archiving function.
