Oracle 11g Architecture--sga PGA front and back process

Oracle servers are primarily made up of instances, databases, program global, foreground processes

Example: used to provide the ability to administer a database

Database: Consists of an Oracle database file that is used to store system data, typically: Data files, control files, redo log files

The instance can be further divided into: System global Area (SGA) and background process (Pmon,smon, etc.)

While the SGA is a memory resource for the operating system, the background process uses the CPU and memory Resource program Global (PGA) is a non-shared memory area that is used to manage private resources for user processes

System global Area (SGA)

System Global area is a chunk of memory shared by all user processes

The main components are:

1. High-speed data buffer (database buffer cache) holds Oracle's most recently accessed block DBWR

To facilitate the memory data of the SGA, Oracle divides the high-speed data buffer into the following 3 parts:

A dirty data area: Stores the data that has been modified and waits to be written to the data file.

B Idle Area: Data blocks in the idle area do not contain any data, and these blocks can be written to data, and Oracle can read blocks of data from the data file and store them in the area

C Reserve: Contains data blocks that are accessed by the user and explicitly reserved for future use

2. Log information generated when the redo log buffer (Redo log buffer cache) database is modified LGWR

The size is specified by the Log_buffer parameter, or it can be modified dynamically after the instance is started. It's usually bigger.

3. Shared Pool

is the SGA reserved area, which is used for caching SQL statements, PL/s statements, data dictionaries, resource locks, character sets, and other control structures. Include two parts:

a--Library cache: Includes both the shared SQL area and the private SQL zone. The library cache area holds the most recently used SQL statements, the text of the PL/SQL statements, and the execution plan. The next time that you execute the same SQL statement or PL/SQL statement, you can find the execution plan that was previously generated directly in the library cache, without having to parse the same SQL statement or SQL/PL statement again to improve system execution efficiency.

Each buffered SQL or PL/SQL statement is divided into two parts, each of which is stored in a shared SQL area and a private SQL zone. The syntax parsing results and execution plan for SQL or PL + + statements in the shared SQL area can be used to take advantage of cached parsing results and execution plans in the shared SQL area if you want to execute similar statements again later. The Private SQL zone holds information such as binding variables, environments, and sessions in the SQL statement that are private to the user who executes the statement, and that other users cannot share the information.

b--Dictionary cache area: used to store data dictionary information required for internal management of Oracle systems, such as user names, data objects, and permissions.

The size of the memory space of a shared pool can be changed dynamically, typically by modifying the value of the parameter shared_pool_size.

such as: Alter system set shared_pool_size=30m;

Note: The size of the shared pool is not as large as possible because the system's memory resources are limited and the operating system itself consumes some memory space.

4. Large pools (Large pool)

The memory structure is not required in the SGA, and the instance will use a large pool to alleviate the access pressure of the shared pool only in one of the following special cases:

a--when using Recovery Manager for backup and restore operations, large pools are used as I/O buffers

b--when I/O Slave emulate asynchronous I/O functionality, large pools are used as I/O buffers.

c--performing SQL with a large number of sort jobs

d--when using parallel queries, large pools serve as a place for the query process to exchange information with each other.

The buffer size of a large pool is defined by the Large_pool_size parameter, in which the user can modify its size using the ALTER system

Use: Show parameter large_pool_size; can view the memory size of the large pool of the current system

Note: If a large pool is not set up in the SGA area, the Oracle system allocates a certain amount of cache space in the shared pool or PGA when the instance needs it, which inevitably affects the productivity of the shared pool or PGA.

5.Java Pool

Used to provide memory space for use with Java virtual machines in order to support the running of Java packages in the database, which is determined by the Java_pool_size parameter.

6. Flow Pool

Used to share information between databases and databases. If the Oracle stream is not used, you do not need to set up the pool. The size of the flow pool is determined by the parameter streams_pool_size.

Program Global Zone (PGA)

Program Global area, also known as the user process Global zone. The memory area is in the private zone of the process. Although the PGA is a global zone, the code, global variables, and data structures can be placed in it, but the resources within the region are not shared by the user as in the SGA, but each Oracle server process is used only for its own portion of the PGA resource. The sum of each service process PGA area is the size of the instance PGA area. Usually there are:

a--Private SQL Zone:

Storage variables and the memory structure information at the time the SQL statement runs, a session is created in the instance when each user connects to the instance. These sessions may create a shared SQL area in the SGA area, but multiple private SQL zones may be created in the PGA Zone. By merging a private SQL area with the corresponding shared SQL area, you can obtain the full cached data for an SQL statement.

In addition, each private SQL area can be divided into two parts: static and dynamic zones. The static zone information remains constant during the session, and the static zone is freed only when the session ends, and the dynamic zone information is constantly changing throughout the session, and once the SQL statement is specified, the dynamic zone is freed even if the session is not finished.

b--Session Area

Used to hold the user's session information (such as a login user name). If the database is in Shared Server connection mode, the session area will be located in the SGA area instead of the PGA

Changed with: Alter system set pga_aggregate_target=18m; View Pga:show parameter PGA;

The foreground process is not part of the instance, including

a--User Process

Applications that produce SQL statements, whether SQL *plus or other applications, are called user processes as long as they are capable of generating or executing SQL statements

Two concepts: Connection and session

Connection: A communication between a user process and an instance is taken, and this channel can be implemented through the relevant communication or network connection on the operating system.

Session: The interaction between a user and an instance after a connection is made between a user process and an instance, typically the way a user makes a request and a database instance returns a response message to the user.

b--Server process

A SQL statement or SQL *plus command issued to a DB instance during a user session, divided into private server mode and shared server mode.

Background process

is a set of background programs running on the Oracle server side and is an important part of Oracle instances. Smon,pmon,dbwr,lgwr,ckpt These 5 background processes must start normally, or it will cause the DB instance to crash.

Data Write process (DBWR):

The primary task is to write the "dirty" chunks of memory back into the data file. Normally writes are not made at any time, so generally the DBWR process writes "dirty" chunks to the data file when the situation occurs:

1. When the user process performs such operations as inserting or modifying, the "new data" needs to be written to the high-speed data buffer, and if no large enough free blocks are found in the high-speed data buffer to hold the "new data", then the Oracle system will start the DBWR process to write the "dirty" data block to the data file. To get free blocks of data to store these "new data"

2. When the checkpoint process starts, it forces DBWR to write some "dirty" chunks of data to the data file

3. When the "dirty" data block is stored in the high-speed data buffer for more than 3 seconds, the DBWR process automatically initiates the file to write some "dirty" blocks to the data file.

Note: You can modify the db_writer_processes parameter in the SPFile to allow multiple DBWR processes to be used (no more than the number of system processors)

Checkpoint process (CKPT)

Can be seen as an event that initiates a checkpoint process when a log switch occurs. The DBA can start the checkpoint process by modifying the ckeckpoint_process in the initialization parameter SPFile to True

Log write process (LGWR)

Writes data from the redo log buffer to the redo log file. The Oracle system first writes the user's modification log information to the log file, and then writes the result of the modification to the data file.

Oracle instances generate a large amount of log information in the run, which is first recorded in the redo log buffer of the SGA, when the commit command occurs, or the redo log buffer is over 1/3, or the log information is stored for more than 3 seconds. LGWR reads the log information from the redo log buffer and writes it to a file with a smaller sequence number in the log file group, and one log group is written and then another group is written. Once the LGWR process has written all the log files once, it will again turn to the first log filegroup to overwrite again.

Archive process (ARCH)

Optional process, which can be useful when Oracle is in archive mode. When each log file group is full and is about to be overwritten, the archive process reads the log information of the log file that is about to be overwritten, and then writes these "read log messages" to the archive log file.

When the system is busy, it may cause LGWR to wait for the arch process, you can modify the Log_archive_max_process parameter to start multiple archive processes, thereby increasing the speed of the archive write disk.

System monitoring Process (Smon)

A mandatory process to perform recovery work when the database system is started. For example, in parallel server mode, Smon can restore another database that is in failure, and then switch the system to another normal server.

Process monitoring process (Pmon)

Used to monitor the state of other processes, Pmon clears the failed user process and frees the resources used by the user process when a process fails to start.

Lock Process (LCKN)

Optional process, multiple locking processes can occur in parallel server mode for the benefit of the database classmate

Recovery process (RECO)

An optional process in distributed database mode that restores work when data is inconsistent.

Scheduling Process (DNNN)

Optional process, used in Shared server mode, can start multiple scheduling processes

Snapshot process (SNPN)

Used to process automatic refreshes of database snapshots and to run scheduled database stored procedures through the Dbms_job package.

By default, Oracle 11g launches more than 200 background processes. --295--(V$bgprocess[name,description])

　　

Oracle 11g Architecture--sga PGA front and back process