Oracle BASICS (9) Oracle architecture and oracle infrastructure

Oracle BASICS (9) Oracle architecture and oracle infrastructure

1. Oracle Architecture Overview:

Oracle architecture refers to the composition, working process and principle of the database, as well as the organization and management mechanism of data in the database. To understand the architecture of Oracle databases, you must understand important concepts and main components of the Oracle system.

The Oracle system architecture consists of three parts: memory structure, process structure, and storage structure .. As shown in:

　　

1. Memory Structure (SGA, PAG)

The memory structure includes the System Global Area (SGA) and Program Global Area (PGA ).

　　

　　2. process Structure)

The process structure includes foreground and background processes. Frontend processes are service processes and user processes. The foreground process runs according to actual needs and ends immediately after the end of the process. Background processes are several operating system processes that are automatically started after the Oracle database is started.

 

　　3. Storage Structure (Database)

The storage structure of Oracle mainly includes the logical structure and physical structure. A physical structure refers to a group of files in the system. A logical structure is a hierarchical structure. It consists of the following concepts: tablespace, segment, partition, and data block.

 

Ii. Memory Structure

The memory structure is the most important part of Oracle, and the memory is also the first factor affecting database performance.

The main content of Oracle memory storage is as follows:

1. program code (PL-SQL, Java ).

2. Information about connected sessions, including all active and inactive sessions.

3. Information required when the program is running, such as the query plan.

4. Information shared and exchanged between Oracle processes, such as locks.

5. What types of data are stored permanently on the peripheral storage media and cached in the memory. (Such as data blocks ).

 

Oracle memory is divided into the system global zone (SGA) and program global zone (PGA) according to the memory usage method)

　　1. SGA :( System Global Area, SGA)Shared Memory area of the instance that all users can access. Data blocks, transaction processing logs, and data dictionary information are all stored in SGA.

SGA is a group of shared buffer storage areas allocated to instances by the Oracle system. It stores database data and control information to manage and operate database data.

SGA mainly includes:

　　1) Data Buffer Zone:　　　　

Datablockbuffercache is a high-speed cache area in SGA that stores data blocks (such as tables, indexes, and clusters) that read data segments from the database ). The size of the data block cache is determined by the DB_LOCK_BUFFERS parameter in the init. ora file of the database server (expressed by the number of database blocks ). When adjusting and managing databases, adjusting the size of the data block cache is an important part.

Because the size of the data block cache is fixed, and its size is usually smaller than the space used by the database segment, it cannot load all the database segments in the memory at a time. Generally, the data block cache is only 1%-of the database size ~ 2%. Oracle uses the least recently used (LRU, leastrecentlyused) algorithm to manage available space. When the storage area requires free space, the minimum block used recently will be removed, and the new data block will be replaced in the storage area. This method saves the most frequently used data in the storage area.

However, if the size of the SGA is insufficient to accommodate all the most commonly used data, different objects will compete for the space in the data block cache. This is likely to happen when multiple applications share the same SGA. In this case, the most recent use segments of each application compete for space in the SGA with those of other applications. The result is that data requests to the data block cache area will have a low hit rate, leading to a reduction in system performance.

　　

　　2) Dictionary Buffer:

The database object information is stored in the data dictionary table. The information includes the user account data, data file name, segment name, disk location, table description, and permissions, when the database needs this information (such as checking the authorization for a table to be queried by the user), it will read the data dictionary table and store the returned data in the SGA of the dictionary cache.

The data dictionary cache area is managed by least recently used (LRU) algorithms. The size of the dictionary cache is managed by the database. The dictionary cache is part of the SQL sharing pool. The size of the Sharing pool is set by the SHARED_POOL_SIZE parameter in the database file init. ora.

If the dictionary cache is too small, the database has to repeatedly query the data dictionary table to access the information required by the database. These queries are called recuesivecall ), the query speed is lower than that in the dictionary cache area when the query is completed independently.

　　

　　3) log Buffer:

The redo item description modifies the database. They are written to online redo log files to be used for forward scrolling during database recovery. However, before being written to the online redo log file, transactions are first recorded in the SGA called redologbuffer. The database can periodically and in batches write the content of the redo item to the online redo log file to optimize this operation. The size (in bytes) of the redo log buffer is determined by the LOG_BUFFER parameter in the init. ora file.

 

　　4) Shared Pool:　　

The SQL Shared Pool stores the data dictionary cache and library cache, that is, the statement information for database operations. When the data block buffer and dictionary cache can share the structure and data information between database users, the database cache can share common SQL statements.

The SQL sharing pool includes the execution plan and the syntax analysis tree of SQL statements running the database. When you run the same SQL statement for the second time (by any user), you can use the available syntax analysis information in the SQL shared pool to speed up execution.

The SQL sharing pool is managed using the LRU algorithm. When the SQL sharing pool is full, the execution path and syntax analysis tree that are least recently used will be deleted from the database cache to free up space for new entries. If the SQL sharing pool is too small, the statement will be continuously loaded into the database cache, thus affecting operation performance.

The size (in bytes) of the SQL sharing pool is determined by the SHARED_POOL_SIZE parameter of the init. ora file.

　　

　　5) large pool:

LargePool is an optional memory area. If you use the thread server option or frequently perform backup/recovery operations, you only need to create a large pool to manage these operations more effectively. The large pool will be dedicated to supporting large SQL commands. By using a large pool, you can avoid these large SQL commands from overwriting entries into the SQL Shared Pool, thus reducing the number of statements that are then loaded into the database cache. The size (in bytes) of the large pool is set through the LARGE_POOL_SIZE parameter of the init. ora file. You can use the LARGE_POOL_MIN_ALLOC parameter of the init. ora file to set the minimum position in the large pool. This parameter is no longer required for Oracle8i. As a solution for using LargePool, you can use the SHARED_POOL_RESERVED_SIZE parameter of the init. ora file to reserve some SQL sharing pools for large SQL statements.

　　

　　6) Java pool:

According to its name, the Java Pool provides syntax analysis for Java commands. The Java pool size (in bytes) is set by the JAVA_POOL_SIZE parameter of the init. ora file introduced in Oracle8i. The default value of JAVA_POOL_SIZE In the init. ora file is 10 MB.

　　

　　7) Multiple Buffer pools:

You can create multiple buffer pools in SGA. Multiple Buffer pools can be used to separate large datasets from other applications to reduce their possibility of competing for the same resources in the data block cache zone. For each created buffer pool, you must specify the LRU latch size and quantity. The number of buffers must be at least 50 times more than the number of LRU latches.

When creating a buffer pool, you must specify the size of the keeparea and the recyclearea. Like the retention area of the SQL sharing pool, the retention area maintains entries, while the recycling area is frequently recycled. You can use the BUFFER_POOL_KEEP parameter to specify the size of the storage area. For example, the capacity of the storage and recycle buffer pool reduces the available space in the data block buffer area (set through the DB_BLOCK_BUFFERS parameter ). For tables that use a new buffer pool, specify the buffer pool name through the buffer_pool parameter in the storage clause of the table. For example, if you want to quickly delete a table from the memory, assign it to the RECYCLE pool. The DEFAULT pool is called DEFAULT, so that a table can be transferred to the DEFAULT pool by using the altertable command later.

 

　　2. PGA :( Program Gloabl Area, PGA)A type of memory that is not shared, dedicated to a specific server process, and can only be accessed by this process.

PGA contains the data and control information required by a single server process or a single background process. PGA is automatically allocated when a user process connects to the database and creates a session. This region retains the memory required by each user process connected to the Oracle database. The PGA is not a shared zone and can only be used by a single process. However, after a user session ends, the PGA is released.

Note: The difference between PGA and SGA:

1. Similar to SGA, PGA is the Region allocated by the Oracle Database System for sessions in the server memory. The two have different roles and different degrees of sharing.

2. The Global zone of the SGA system shares all processes in the system. The global area of the PGA program serves a user process.

 

　　3. UGA :( User Global Area, UGA)This memory area stores session status for user processes. UGA can be used as part of SGA or PGA, depending on whether your database is configured as a dedicated server or shared server. It stores data for user sessions.

 

 

3. Process Structure

In an Oracle instance, there are two types of processes: User processes and Oracle processes. There are two types of Oracle processes: Server Processes and background processes. The following describes the three processes.

　　1. User Process

The user process is started when the database user requests to connect to the Oracle server. When a user runs an application, Oracle creates a user process for the user.

　　

　　2. server process

Server processes are used to process requests from user processes connected to the instance. The SQL statement sent by the customer to the database must be received and executed by the process. The server process can only process requests from one user process or requests from multiple user processes.Dedicated serverAndShared Server.

Listener. ora file, code server = dedicated, meaning to set as a dedicated server.

It can execute the following tasks:

1) analyze and execute the SQL statement issued by the application lock.

2) read necessary data blocks from the disk (data file) to the shared database buffer of the SGA (when this speed is not in the buffer zone ).

3) return the result to the application for processing.

 

　　3. background processes

The background process starts with the database and is used to complete various maintenance tasks, such as writing fast data to the disk, maintaining online redo logs, and clearing abnormal and aborted processes. A single Oracle instance can use many background processes, but they do not always exist.

Background processes include:

　　1) PMON process monitoring process　　

When a user process encounters a fault, the process recovers and cleans up the internal storage zone and releases the resources used by the process. For example, it resets the status of the active transaction table, releases the block, and removes the ID of the faulty process from the active table. PMON also periodically checks the status of the scheduling process (DISPATCHER) and the server process. If it is dead, it restarts (excluding processes intentionally deleted ).

PMON is wake up regularly to check whether it is needed or other processes can be called when it is found necessary.

　　

2) SMON system monitoring process

　　When the process instance is started, the instance recovers and is also responsible for clearing temporary segments that are no longer in use. In an environment with the parallel server option, SMON recovers faulty CPU or instances. The SMON process is periodically woken up to check whether it is needed or whether it can be called when other processes discover it is needed.

　　

3) DBWR database write process　　

This process writes the buffer to a data file. It is an Oracle background process responsible for Buffer Storage Management. When a buffer in the buffer zone is modified, it is marked as "dirty". The main task of DBWR is to write the "dirty" buffer to the disk to keep the buffer clean ". The number of unused buffers is reduced because the buffer zone is filled in the database or the user process is dirty. When unused buffers are dropped to a very small level, and user processes cannot find unused buffers when they want to read blocks from the disk to the memory storage area, DBWR manages the buffer areas, allows user processes to get unused buffers.

Oracle uses the LRU (least recently used) algorithm (the least recently used algorithm) to keep data blocks in memory recently used, minimizing I/O. In the following cases, DBWR needs to write dirty buffers to the disk:

When a server process moves a buffer into the dirty table, the dirty expression is critical, the service process will notify DBWR to write. The critical length is half the value of the parameter DB-BLOCK-WRITE-BATCH.

When a server process looks for the DB-BLOCK-MAX-SCAN-CNT buffer in the LRU table, there is no unused buffer, it stops searching and notifies DBWR to write. If a timeout occurs (3 seconds each time), DBWR notifies itself. When a checkpoint occurs, LGWR notifies DBWR. In the first two cases, DBWR dirty blocks in the table are written to the disk, with each writable block specified by the initialization parameter DB-BLOCK-WRITE-BATCH. If the dirty table does not have a buffer with the specified number of blocks, DBWR looks for another dirty buffer from the LUR table.

If DBWR is not active within three seconds, a timeout occurs. In this case, DBWR searches for a specified number of buffers for the LRU table and writes any dirty buffers to the disk. When timeout occurs, DBWR searches for a new buffer group. The number of buffers each time searched by DBWR is twice the value of the sleep parameter DB-BLOCK-WRITE-BATCH. If the database is transferred by air, DBWR eventually writes all the buffer zones to the disk.

When a Check Point occurs, LGWR specifies that a buffer table must be written to the disk. DBWR writes the specified buffer to the disk.

On Some platforms, one instance can have multiple DBWR instances. In such instances, some blocks can be written to one disk, while others can be written to other disks. The number of DBWR processes is controlled by the parameter DB-WRITERS.

　　

　　4) LGWR log Writing Process

This process writes the log buffer to a log file on the disk. It is an Oracle background process responsible for managing the log buffer. The LGWR process outputs all log entries since the last write to the disk, and the LGWR outputs:

◆ When a user process commits a transaction, a commit record is written.
◆ Output the log buffer every three seconds.
◆ Output the log buffer when 1/3 of the log buffer is full.
◆ When DBWR writes the modification buffer to the disk, It outputs the log buffer.

The LGWR process synchronously writes the image to the active online log file group. If a file in the group is deleted or unavailable, LGWR can continue to write other files in the group.

The log buffer is a circular buffer. After LGWR writes log entries in the log buffer to the log file, the server process can write new log entries to the log buffer. LGWR is usually written very quickly to ensure that there is always space in the log buffer to write new log entries.

Note:: Sometimes when more log buffers are required, LWGR writes log items before a transaction is committed, and these log items are only made permanent after the transaction is committed.

ORACLE uses the fast Submission mechanism. When a user issues a COMMIT statement, a COMMIT record is immediately placed in the log buffer, but the change in the corresponding data buffer is delayed, they are not written to data files until they are more effective. When a transaction is committed, it is assigned a system modification number (SCN), which is recorded together with the transaction log items in the log. As the SCN is recorded in the log, the restoration operation can be synchronized when the parallel server option is configured.

　　

　　5) ARCH archiving process.

　　This process copies filled online log files to the specified storage device. The ARCH process exists only when logs are used by ARCHIVELOG and can be archived automatically.

　　

6) CKPT checkpoint.　　

The process changes the title of all data files at the check point to indicate the checkpoint. Generally, this task is executed by LGWR. However, if the checkpoint significantly reduces system performance, the CKPT process can run to separate the work of the checkpoint originally executed by the LGWR process, which is implemented by the CKPT process. For many applications, the CKPT process is unnecessary. CKPT runs only when the database has many data files and the LGWR significantly reduces the performance at the check point. The CKPT process does not write blocks to the disk. This task is completed by DBWR. The initialization parameter CHECKPOINT-PROCESS controls the enabling or enabling of CKPT processes. The default value is FALSE.

Because the LGWR and DBWR operations in Oracle are inconsistent, Oracle introduces the checkpoint concept for Database Synchronization to ensure Database Consistency. In Oracle, there are two types of checkpoints: full check points and incremental check points. The functions of these two checkpoints are described as follows:

1. Full checkpoint

Before Oracle8i, all database checkpoints are full checkpoints. Full checkpoints write all the dirty data blocks in the data buffer into the corresponding data files, and synchronize the data file header and control file, ensure Database Consistency. A full checkpoint occurs after 8 I only in the following two cases:

(1) DBA manually executes the alter system checkpoint command;

(2) The database is shut down normally (immediate, transcational, normal ).

Because the full checkpoint will write all the dirty database blocks, a huge IO will often affect the database performance. Therefore, Oracle introduced the incremental checkpoint concept from 8i.

2. incremental checkpoints

Oracle introduced the checkpoint queue concept from 8i, which is used to record the information of all the dirty data blocks in the database. DBWR writes dirty data blocks to the data file according to the queue. The checkpoint queue records the information of dirty data blocks in the database by time. The entries in the checkpoint queue contain RBA (Redo Block Address, the redo log identifies the number of the data block that was changed for the first time in the redo log during the checkpoint) and the data file number and block number of the data block. During the checkpoint period, no matter how many times the data block is changed, its position in the checkpoint queue remains unchanged. The checkpoint queue only records its earliest RBA, this ensures that the earliest changed data blocks can be written as soon as possible. After DBWR writes dirty data blocks in the checkpoint queue to the data file, the checkpoint position also needs to be moved back accordingly. CKPT records the checkpoint position in the control file every three seconds, indicates the log entry that is restored when the Instance Recovery is started. This concept is called heartbeat of the checkpoint ). After the checkpoint position changes, four parameters are used in Oracle to control the distance between the checkpoint position and the last redo log entry. It should be pointed out that most people will regard these four parameters as controlling the time when the incremental checkpoint occurs. In fact, this is incorrect. These four parameters are used to control the number of entries in the checkpoint queue, rather than to control the occurrence of checkpoints.

(1) fast_start_io_target

This parameter is used to represent the total number of I/O operations that need to be generated when an Instance Recovery occurs in the database. It is estimated by AVGIOTIM of v $ filestat. For example, if a database needs to be restored within 10 minutes after the Instance Crash occurs, assume that OS IO is 500 per second, in this case, the database will generate approximately 500*10*60 = 30,000 IO when the Instance Recovery occurs, that is, we can set fast_start_io_target to 30000.

(2) fast_start_mttr_target

We can see from the above that fast_start_io_target is difficult to estimate the checkpoint position. To simplify this concept, Oracle introduced a parameter named fast_start_mttr_target from 9i to indicate the time when the Instance Recovery ing occurred in the database, in seconds. We can understand this parameter literally. mttr is short for mean time to recovery. In the preceding example, we can set fast_start_mttr_target to 600. After fast_start_mttr_target is set, the parameter fast_start_io_target will no longer take effect. From 9i, the parameter fast_start_io_target is abolished by Oracle.

(3) log_checkpoint_timeout

This parameter indicates the interval between the checkpoint position and the end of the redo log file, in seconds. The default value is 1800 seconds.

(4) log_checkpoint_interval

This parameter indicates the checkpoint position and the number of redo log blocks at the end of the redo log, expressed as OS blocks.

(5) 90% OF SMALLEST REDO LOG

In addition to the above four initialization parameters, Oracle actually sets the first 90% position at the end of the redo log file as the checkpoint position. In each redo log, the locations specified by these parameters may be different. Oracle determines the location closest to the end of the log file as the checkpoint location.

　　

7) RECO resumes the process.　　

This process is used when distributed options are available. It automatically resolves faults in distributed transactions. A node RECO background process automatically connects to other databases that contain pending distributed transactions, and RECO automatically resolves all pending transactions. Any row corresponding to the suspended transactions that have been processed is deleted from the suspended transaction table of each database.

When the RECO background process of a database server tries to establish communication with the same remote server, if the remote server is unavailable or the network connection cannot be established, the RECO will automatically connect again after a time interval.

The RECO background process only appears in the system that allows distributed transactions, and the distributed c transactions parameter is greater than 0.

 

8) LCKn Process: Used in an environment with parallel server options. Up to 10 processes (LCK0, LCK1 ......, LCK9), used for blocking between instances.

　　

9) Dnnn process (scheduling process ):

This PROCESS allows user processes to share limited SERVER processes ). When no scheduling PROCESS exists, each user PROCESS requires a special service PROCESS (dedicatedserver process ). A multi-threaded SERVER (MULTI-THREADED SERVER) supports multiple user processes. If there are a large number of users in the system, the multi-clue server can support a large number of users, especially in the client _ server environment.

You can create multiple scheduling processes in a database instance. Create at least one scheduling process for each network protocol. The database administrator determines the optimal number of schedulers to be started based on the number of connections allowed by each process in the operating system. The scheduling process can be added or deleted when the instance is running. The multi-clue server requires SQL * NET version 2 or a later version. In the configuration of the Multi-clue server, a network receiver process waits for the client application connection request and sends each request to a scheduling process. If the client application cannot be connected to a scheduling process, the network receiver process starts a dedicated server process. The Network receiver process is not a component of an Oracle instance. It is a component of a network process related to Oracle. When the instance is started, the network receiver is opened, establishing a communication path for the user to connect to Oracle, and then each scheduling process sends the address of the scheduling process connecting the request to its receiver. When a user process initiates a connection request, the network receiver process analyzes the request and determines whether the user can use a scheduling process. If yes, the network receiver process returns the address of the scheduling process, and then the user process is directly connected to the scheduling process. Some user processes cannot communicate with scheduling processes (if users of SQL * NET versions are used), the Network receiver process cannot connect the user to a scheduling process. In this case, the network receiver establishes a dedicated server process and establishes a suitable connection.

 

Iv. Storage Structure

The storage structure of Oracle databases is divided into logical and physical storage structures.

1. Physical storage structure
The physical storage structure mainly describes the external storage structure of the Oracle database, that is, how to organize and manage data in the operating system.
Physically, databases are composed of control files, data files, redo log files, parameter files, and other operating system files.
Therefore, the physical storage structure is related to the operating system platform.

　　1) Data File ):

Is a file that physically stores Oracle database data. Each data file is associated with only one database. Once a data file is created, its size cannot be modified. A tablespace can contain one or more data files. A data file can belong to only one tablespace.

 

　　2) Redo Log File)

Record all changes to database data for data recovery. Each database contains at least two Log File groups. Log File groups are written cyclically. Each log file member corresponds to a physical file.

The Log Switch is set to realize the recycling of Log File groups. The log switch is displayed as follows: When a log file group is filled, the database is closed, and the DBA manually transfers the log switch;

Image Log Files are copies of two or more online log files on different disks to prevent log files from being lost.
Each log file group contains at least two log file members. The number of members in each group is the same. All members in the same group are modified at the same time. The size of members in the same group is the same, and the size of members in different groups can be different.

3) Control File)

Is a small binary file used to describe the database structure. Maps physical database files to logical table spaces in the data dictionary and online redo log files.

4) Parameter File)

The configuration database used to start the instance. Parameter files are mainly divided into two types:

One is that when you create a database, you can run the initialization file (that is, a parameter file) to specify the various settings used in the database. The extension name of the text parameter file is init <SID>. ora.

The other is the server parameter file. The extension of the server parameter file is SPFILE <SID>. ora, which can manage database parameters and values.

　　5) temporary File (Temporay File)

The Processing Method of temporary files in Oracle is slightly different from that of standard data files. These files do contain data but are only used for temporary operations. Once a session is established and the operation is completed, the data is completely deleted from the database.

2. Logical Structure

The logical storage structure mainly describes the internal storage structure of Oracle databases, that is, the technical concept of how to organize and manage data in Oracle databases.

A tablespace is the largest logical unit and a block is the smallest logical unit. Therefore, the logical storage structure is independent of the operating system platform and is created and managed by the Oracle database.

　　1) tablespace

A table space is the largest logical unit. It corresponds to one or more data files. The size of a table space is the total size of the corresponding data files.

　　Oracle 10 Gb automatically created tablespaces include:

Example (instance tablespace): Example tablespace.

Sysaux (Auxiliary System tablespace): Auxiliary System tablespace used to reduce system load and improve system operation efficiency

System (System tablespace): A System tablespace that stores management information about the tablespace name, control file, and data file. It is the most important tablespace. it belongs to the Sys and System Schemas and is only used by these two or other users with sufficient permissions. However, the System tablespace cannot be deleted or renamed.

Temp (temporary tablespace): Temporary tablespace stores temporary tables and temporary data for sorting.

Undotbs: When the database table data of our team is added, modified, or deleted, the Oracle system automatically uses the Undotbs to temporarily store the data before modification.

Users (User tablespace): A User tablespace that permanently stores user objects and private information, and is also used as a data table space.

　　Generally: system Users use system tablespaces, while non-system Users use Users tablespaces.

 

　　2) Section

A Segment is a logical storage structure of a specified type in a tablespace. It consists of one or more partitions. segments occupy and increase storage space.

Bootstrap Segment: defines a data dictionary table.

Temporary Segment (Temporary Segment): stores the data of the Temporary table of the resume during the table sorting operation.

Rollback Segment: stores the position and value before modification.

Index Segment: stores all the Index data that is best queried on a table.

Data Segment (Date Segment): stores all data in the table

 

　　3) Zone

A zone (Extent) is the logical unit for database storage space allocation. A zone consists of a group of database blocks and is allocated by segments. The first allocated zone is called the initial zone, the area to be allocated later is called the incremental area.

 

　　4) data blocks

A Database Block is the smallest I/O unit used by a Database, also known as a logical Block or an ORACLE Block. A database block corresponds to one or more physical blocks. The block size is determined by the DB_BLOCK_SIZE parameter.
The block size is an integer multiple of the operating system block size.
Taking Win2K as an example, the size of the operating system block (OS Block) is 4 kb, so the size of the Oracle block can be 4 kb, 8 KB, 16 KB, and so on.
If the block size is 4 kb, the data in each row of a table is 100 bytes ., if a query statement returns only one row of data, when the data is read to the data cache, the read data volume is 4 kb instead of 100 bytes.
A data block consists of the following five parts:
Title: contains general block information, such as block address/segment type. The optimal size is-bytes.
Table Directory: stores information about clustering tables. This information is used for clustering segments.
Row Directory: including valid row Information in this section. 2 bytes starting with each row are allowed.
Free Space: A group of spaces that can be inserted or modified.
Row data: Stores Table or index data.

 

The above content comes from the network!