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

The Oracle database contains the following basic memory components

System global area (SGA)

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

Program global area (PGA)

A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.

One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

User Global Area (UGA)

The UGA is memory associated with a user session.

Software code areas

Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically ata different location from user programs-a more exclusive or protected location.

Memory management

Oracle relies on memory-related initialization parameters to control memory management.

There are three options for memory management

Automatic memory management

You specify the target size for instance memory. The database instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.

Automatic shared memory management

This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.

Manual memory management

Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.

Overview of UGA

UGA is session memory, which is used to hold session variables such as login information and other information needed by the database session.

When the PL/SQL package is loaded into memory, the UGA contains package state, which is the variable value specified when PL/SQL is called.

Overview of PGA

The PGA buffer is mainly served by a user process. This memory area is not shared, and only the user's service process itself can access its own PGA area. To make a visual analogy, SGA is like a shared folder on the operating system, which different users can use as a platform for data exchange. PGA is like a private folder on the operating system. Only the owner of this folder can access it, and no other users can access it. Although the program cache is not open to other users' processes, this memory area still shoulders some important missions, such as data sorting, access control and so on.

PGA component

The private SQL area contains data such as the value of the bound variable and runtime memory structure information. Each session that runs the SQL statement has a block private SQL zone. The private SQL area of a cursor is divided into two zones with different lifecycles:

A permanent section that contains binding variable information. Is released when the cursor is closed.

Runtime area, which is released at the end of execution.

Cursor

A cursor is a name or handle to a specific private SQL area

SGA contains the following components

Database Buffer Cache

Redo Log Buffer

Shared Pool

Large Pool

Java Pool

Streams Pool

Fixed SGA

Buffer cache

Buffer Cache is the area of the SGA zone dedicated to storing copies of blocks read from data files. If the Oracle process finds that the data block that needs to be accessed is already in the buffer cache, it directly reads and writes the corresponding area of memory without reading the data file, thus greatly improving performance. Buffer cache is shared by all oracle processes, that is, it can be accessed by all oracle processes.

The Buffer is the same size as the block.

Buffer cache is divided into 3 pools by type

Default pool

This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.

Keep pool

This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.

Recycle pool

This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.

Oracle also provides buffer cache with a non-standard block size. If you create a tablespace that specifies a block size that is not a database block size, then these buffer cache will be used to cache the blocks.

First of all, Oracle does the hash operation based on the file number, block number and type of each data block to get the hash value.

For blocks with the same hash value, put them in a Hash Bucket.

Because the size of buffer is limited after all, the data blocks in buffer need to provide memory according to certain rules.

Oracle uses the LRU algorithm to maintain a LRU linked list to determine which blocks are eliminated.

The general elimination algorithm is as follows

Oracle improves the LRU algorithm, introduces the concept of Touch count, and the LRU linked list is divided into hot end and cold end.

Touch count:

Used to record the frequency of block access, this value is not protected in memory and can be modified by multiple processes at the same time. This value is not an accurate representation of the number of times the block is accessed, it is just a trend. No matter how many users and how many times the block is accessed in 3 seconds. This value is added by 1.

When the data block is placed in the buffer for the first time, the Oracle places it on the head of the cold end.

If buffer has no free space, how do you phase out data blocks? Oracle scans the data block from the tail of the cold end of the LRU, and when it is found that the Touch count of the data block is greater than or equal to 2, move the data block to the hot end header and set the Touch count to 0. When Oracle finds that the Touch count is less than 2, the data block is eliminated.

When the data block is modified, we call it a dirty block. Dirty blocks will not be kicked out of buffer until they are written to disk.

If there are a lot of dirty blocks in LRU, every time you apply for a new space, you have to scan a lot of dirty blocks, but you can't be eliminated. It's inefficient.

For this reason, Oracle has a dirty LRU linked list. Specially used to record dirty data blocks.

When a block is modified, it is not immediately moved from the LRU list to the LRUW. Only when the Oracle needs to eliminate the data block will the LRU linked list be scanned. At this time, the block is found to be dirty, and the data block is moved to the LRUW linked list.

Checkpoint linked list

From the above description, we know that dirty blocks are found in both LRU and LRUW linked lists. So when dbwr writes data, both linked lists are scanned. First of all, the efficiency is relatively low, and there is no guarantee that the data blocks modified first will be written to disk first.

For this reason, Oracle introduces a checkpoint queue, which links dirty blocks together in the order in which the blocks are first modified.

When dbwr writes dirty blocks, you only need to read the checkpoint queue.

And each data block and the location where the log entry is recorded

Redo log buffer

The user process copies the redo entries to the redo log buffer. LGWR is responsible for writing it to disk.

Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.

Shared pool

Library Cache:

It mainly stores the information of shared curosr (SQL) and PLSQL objects (function,procedure,trigger), as well as the information of table,index,view and other objects on which these objects depend.

The relationship between Private SQL Areas and Shared SQL Area

Data dictionary cache

Used to cache the contents of the system data dictionary table, unlike the cache of ordinary tables, which are cached in blocks to buffer cache. The data dictionary cache is cached in behavioral units to the data dictionary cache in shared pool.

Server result cache

Used to cache the execution results of sql or plsql.

Daichi:

The large pool can provide large memory allocations for the following:

UGA for the shared server and the Oracle XA interface (used where transactions interact with multiple databases)

Message buffers used in the parallel execution of statements

Buffers for Recovery Manager (RMAN) I am O slaves

Configure memory

Oracle provides two initialization parameters to configure automatic memory management

The sum of MEMORY_TARGET:sga+pga memory, Oracle automatically allocates the size of SGA and PGA.

MEMORY_MAX_TARGET:MEMORY_TARGET can set an upper limit on the size.

SGA automatic memory management

Set the initialization parameter SGA_TARGET to a non-0 value, and set the value of STATISTICS_LEVEL to TYPICAL or ALL.

PGA automatic memory management

PGA_AGGREGATE_TARGET is set to a non-0 value.

Parameter settings such as sort_area_size,hash_area_size are ignored if workarea_size_policy is auto, and parameter settings such as sort_area_size,hash_area_size take effect if workarea_size_policy is manual.

You can also manually configure the size of each other memory pool. When automatic memory management is configured, the size of the specific pool is configured, so the configuration is the minimum size for automatic memory allocation.

Check the memory condition

The following views provide information about dynamic resize operations:

V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.

V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

V$MEMORY_RESIZE_OPS displays information about the last 800completed memory resize operations (both automatic and manual). This does not include in-progress operations.

V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.

V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.

V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.

V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.

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.