Shulou(Shulou.com)06/01 Report--

I. memory structure

In Oracle database system, the memory structure is mainly divided into system global area (SGA) and program global area (PGA). SGA applies to the operating system for allocating a memory structure with the startup of the database instance, and with the closure and release of the database instance, each Oracle database instance has one and only one SGA. A memory structure that PGA requests to allocate when the Oracle service process starts. If the PGA exists in the SGA in the shared service structure. We will describe the various parts later.

1. System global area (SGA)

It is important to note that increasing the size of SGA can improve the performance of Oracle database system to some extent, but if you set the value of SGA, if it cannot be locked on the physical page in memory, some parts may be swapped into the system's swap file. This way your Oracle database system will slow down. The global area of the system is a group of shared memory structures containing data and control information, which allows many background processes of Oracle services to access or modify the data at the same time, so it is sometimes called the "global shared area". The SGA_MAX_SIZE in the parameter file specifies the dynamic size of the SGA.

The global area of the system consists of the following main parts:

"shared pool SharedPool

"data cache DatabaseBufferCache

Redo log cache RedoLogBufferCache

"Java pool (optional) JavaPool

Daiichi (optional) LagerPool

Shared pool

Shared pools store most recently used execution SQL statements and recently used data definitions. It contains two performance-related memory structures, the library cache and the data dictionary cache. The size of the shared pool can be determined by SHARED_POOL_SIZE in the initialization parameter file (usually init.ora). A shared pool is a very active memory structure that produces a lot of memory fragmentation, so make sure it is as large as possible.

The library cache also includes two component areas: the shared PL/SQL area and the shared SQL area. In order to improve the performance of SQL statements, when submitting SQL statements or PL/SQL blocks, the Oracle server will first use the least recently used (LRU) algorithm to check whether the same SQL statements or PL/SQL blocks exist in the library cache, and if so, use the original analysis tree and execution path.

A data dictionary cache that collects data definition information from a recently used database. It contains information about data files, tables, indexes, columns, users, access rights, other database objects, and so on. Determines the accessible information of database objects during the analysis phase. The response time is effectively improved by using the data dictionary buffer. Its size is determined by the size of the shared pool.

Data cache

It stores copies of data blocks in the data file. The performance of data update operation is greatly improved by using this structure. Data exchange in the data cache also uses the least recently used algorithm (LRU). Its size is mainly determined by DB_BLOCK_SIZE. Data cache is composed of independent sub-buffers such as DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE, and it can grow or contract dynamically.

Redo log cache

The redo log buffer is a ring-shaped buffer that records all data changes for the main purpose of recovery. The changed record is called a redo entry, and the redo entry contains refactoring or redo information. Its size is determined by the LOG_BUFFER in the initialization parameters. If the size of the redo log cache is too small, it will lead to process competition and competition between log writing processes.

Java Pool

The Java pool is a component that appears in SGA only after the installation uses Java, and its size is determined by the JAVA_POOL_SIZE initialization parameters. The Java pool provides analysis and execution memory space for executing Java commands.

Daichi

The database administrator can optionally configure a memory area called a large pool, which is mainly used to store session information for shared servers, Imax O service processes, Oracle backup and recovery operations, parallel message caching, and so on. It is worth mentioning that large pools do not have LRU lists like other memory components.

2. Program global area PGA

The program global area is used to save the information that each user connects to the database. The information connected to the database mainly includes reply information, sorting information and cursor information.

II. Process structure

Oracle processes are mainly divided into three types: user processes, service processes and background processes. User processes run in applications or Oracle tools; service processes are created after an Oracle database instance is started when a user establishes a connection; and background processes perform different specific tasks.

The background process of Oracle is as follows:

Write data DatabaseWriter (DBW0 or DBWn)

Write log LogWriter (LGWR)

Checkpoint Checkpoint (CKPT)

"the system monitors SystemMonitor (SMON)

The monitor process monitors ProcessesMonitor (PMON)

Archive Archive (ARCn)

Restore Recover (RECO)

Lock Management Service LockManagerServer (LMS)-RealApplicationClusters only

Queue Monitoring QueueMonitor (OMNn)

Dispatching Dispatcher (Dnnn)

Subscription Service Server (Snnn)

1. Write data (DBWn)

The data writing process is responsible for writing the rewrite block (dirtyblock) from the data cache to the data file on disk. In order to minimize the performance of the database, DBWn does not write every block to disk immediately when it is modified, but reads the list of rewritten blocks in the cache when certain conditions are met, and writes its specified blocks to the data file in batches.

The conditions for the data writing process activity are as follows:

The cache service process could not find the available cache

"the checkpoint appears

The rewrite block reaches its limit.

Duration timeout

Demand issued by ARC

The table space is offline

Read-only tablespace

Table discard or table truncation

"Tablespace starts backup

Although a database write process (DBW0) is sufficient for most systems, if your system modifies a large amount of data to improve performance, you can configure more write processes (DBW1 to DBW9 and DBWa to DBWj). These additional DBWn processes cannot be used in a single processing system. The initialization parameter DB_WRITER_PROCESSES specifies the sequence number of the DBWn process, and the maximum allowable value for this parameter is 20.

2. Write log (LGWR)

The write log process is responsible for managing the redo log cache-writing the redo log from the cache to the disk redo day to file. LGWR writes all redo entries that have been copied to the cache since the last write. Remember that the Oracle database system does not consider the transaction complete and sends the successful code until LGWR writes the redo information from the cache to the online redo log. The LGWR process has little impact on database performance.

Logging is active under the following conditions:

In COMMIT

When the usage of the cache reaches 1/3

When the usage of the buffer reaches 1m

Every 3 seconds

Before the DBWn event

3. Checkpoint process (CKPT)

The checkpoint process is responsible for sending signals to DBWn, updating data file headers with checkpoint information, and updating control file information with checkpoint information. When checkpoints occur frequently, logs are switched frequently, or the database has many data files, this process can reduce the workload of LGWR. The database administrator can determine whether the database instance uses the checkpoint process by setting the initialization parameter CHECKPOINT_PROCESS to TRUE or FALSE. LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT in the initialization parameters to change the frequency of checkpoints. Setting these two parameters should be careful. Although more checkpoints can reduce the workload of the LGWR process, too many checkpoints will cause the system processing time and Imax O time to be wasted on unnecessary opening and closing checkpoints.

4. System Monitoring process (SMON)

The process of system monitoring is a regular periodic activity.

It is responsible for the following purposes:

"if you need to perform a restore during instance startup

"reassign temporary segment

Merge adjacent free zones in the tablespace every 3 seconds

"restore tablespaces or data files to online status as needed

For an application cluster system, it performs the recovery of a failed CPU or Oracle instance.

The system monitors that if this process fails when the database system instance starts, the database system will not be able to start working. If any stopped transaction skips during instance recovery due to a file read-only or offline error, SMON brings the tablespace or file back online. The SMON process can perform instance application clustering for an instance, and a SMON can restore an instance to a failed CPU or instance for an instance.

5. Process monitoring process (PMON)

The process monitoring process is responsible for releasing related objects after the following procedures fail:

"transaction fallback

Release the related lock

Release related resources

"reassign the schedule with the error

PMON periodically checks the scheduling status and service processes, and resets stopped processes other than Oracle's intentional termination; PMON also registers information about instances and scheduling processes for network listening. If this process fails when the database system instance starts, the database system will not be able to start working.

6. Recovery process (RECO)

The recovery process is responsible for automatically solving the failed distributed transactions in the recovery step-by-step database system. Details on this process are described in the distributed database section.

7. Archiving process (ARCn)

This is an optional background process. Occurs only when the Oracle database instance is running in archive mode. The archiving process is responsible for copying all redo log information to the specified device file when the log file is switched. An Oracle database instance can have up to 10 archiving processes (ARC0 to ARC9). Other Oracle background processes, such as lock management services, queue monitoring, scheduling, etc., are not required for an Oracle database instance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.