Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are Oracle memory structure interview questions". In daily operation, I believe many people have doubts about what problems Oracle memory structure interview questions have. Xiaobian consulted all kinds of information and sorted out simple and easy to use operation methods. I hope to help you answer the doubts of "what are Oracle memory structure interview questions"! Next, please follow the small series to learn together!

Q: What are the components of Oracle's memory structure?

1 User global area (UGA)

2 Program global area (PGA)

3 System global area (SGA)

4 Software code areas

II: Please introduce UGA, PGA, SGA, software code area respectively?

1 UGA

UGA is the user global area, which mainly stores information related to user sessions, such as login information.

UGA must be available to database sessions during the lifetime of the session.

Therefore, when the connection mode is dedicated server connection mode, that is, one session corresponds to one connection, the UGA is stored in PGA.

However, when the connection mode is shared server, that is, multiple sessions correspond to one connection, the UGA is stored in the large pool in the SGA. If the large pool space is insufficient, the UGA will be stored in the shared pool. Dedicated server connection mode is used in most cases.

2 PGA

PGA(Process Global Area) is a non-shared memory area that contains data and control information specific to Oracle processes. PGA is created at Oracle process startup. Proprietary to each service process and background process, the collection of all individual PGAs is the total instance PGA size.

PGA Content:

PGA has two main areas, the private SQL area and the SQL work area.

The private SQL area stores SQL binding variable values and query execution status information, and the client process is responsible for managing the private SQL area. The number of private SQL zones allocated is limited by the initialization parameter OPEN_CURSORS.

SQL workspace is mainly used for sorting operations, Hash join, bitmap merge join memory used.

For example, order by, group by, etc.

PGA Management:

In Oracle8i, PGA adjustment is very complicated, and parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, etc. need to be adjusted. After ORACLE9I, only PGA_AGGREGATE_TARGET needs to be adjusted. This value is a soft limit, such as setting the size of 2G, which is only a target value. In fact, PGA size can exceed 2G. The PGA_AGGREGATE_LIMIT parameter was introduced at 12C to limit the memory usage of the PGA of the Oracle instance, and if the limit was exceeded, the session was terminated to reduce PGA memory usage.

3 SGA

A system global area (SGA) is a set of shared memory structures shared by all servers and daemons.

Together with Oracle daemons, they form database instances.

Information about SGA components can be queried in the V$SGASTAT view. The most important SGA components are as follows:

·Database Buffer Cache

·Shared Pool

·Redo Log Buffer

·In-Memory Area

·Large Pool

·Java Pool

·Fixed SGA

Optional performance-related SGA sub-area

(1)Database Buffer Cache

A database buffer cache, also known as a buffer cache, is an in-memory copy of data blocks on a data file. The main purpose is to perform high-speed data lookup and update in memory and minimize disk IO operations. It is also a relatively large memory area in SGA. Users can access data in DB Cache several times faster than on disk (memory reads are about 14000 times more efficient than disk reads), so applications should access as much data from DB Cache as possible. In most cases, the higher the DB Cache hit rate, the better the access performance.

Buffer pools include default pool, keep pool, recycle pool

Buffer Cache Management:

Managed by three linked lists: HASH linked list, checkpoint queue linked list, LRU linked list

HASH linked list

The purpose of HASH linked list is to improve the positioning speed of data blocks in DB Cache through HASH algorithm (consuming CPU resources). That is logical reading.

For example, calculate the HASH value according to the block number and file number of the block to be accessed, find the corresponding HASH Bucket through the HASH value, search the linked list after the Bucket, find the target BH(Buffer Header), find the BA(Buffer Address) through the BH, and access the specific Buffer according to the BA. This is the logical reading process.

Checkpoint Queue List (CKPT-Q)

It is mainly used to record dirty blocks.

Buffer Cache is actually the cache of disk data files. Take the operation of modifying a block as an example. For example, update only modifies the Buffer in Buffer Cache. After the modification is completed, the update operation is considered complete. In this way, the data in the Buffer is inconsistent with the blocks in the disk. Such a Buffer is a dirty Buffer. Dirty blocks are uniformly written to the disk by DBWR process, but Buffer Cache is usually very large, with tens or hundreds of thousands of Buffers. How to find which are dirty Buffers in Buffer Cache? This requires a linked list to string all dirty Buffers together. When DBWR writes dirty blocks, it is written in the order of this linked list. There are two such dirty linked lists, one is LRUW and the other is CKPT-Q. In buffer cache, after modifying data, the corresponding data block will be added to checkpoint queue (CKPT-Q).

LRU List (Least Recently Used List)

When physically reading, the server process reads the data block from the data file into the Buffer Cache. If the Buffer Cache has 10000 buffers, which Buffer should the process overwrite?

Simply put, where a process reads a chunk of data into the Buffer Cache.

The answer is, cover the least commonly used Buffer, LRU is mainly to solve how to quickly find the least commonly used Buffer.

Before Oracle 8i, the LRU algorithm was that DB Cache would be moved on the LRU, common buffers would be replaced to the hot end of the LRU, and infrequently used buffers would be squeezed to the cold end of the LRU. Generally speaking, when the session allocated Cache, it would start from the cold end of the LRU. This LRU algorithm creates performance bottlenecks.

Since Oracle 8i, the LRU algorithm has been improved, and buffers on the LRU chain no longer need to be moved, but whether a data block is hot is determined by the tch counter size value.

(2)Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that stores redo entries describing database changes.

A redo record is a data structure that contains information needed to redo changes made to the database by DML or DDL operations. Database Recovery applies redo items to the data file to reconstruct missing changes.

LGWR writes blocks to disk sequentially, while DBW writes blocks to disk separately. Scattered writing is much slower than sequential writing. Because LGWR allows users to avoid waiting for DBW to complete slow writes, the database provides better performance.

The LOG_BUFFER initialization parameter specifies the amount of memory used by Oracle Database when buffering redo entries. Unlike other SGA components, redo log buffers and fixed SGA buffers do not divide memory into particles.

(3)Shared Pool

Compared to Buffer Cache, the contents of the shared pool can be described as chaotic. Oracle basically throws data that cannot be placed in Buffer Cache into the shared pool, making the allocation and release of shared pools extremely frequent.

The most basic memory allocation unit in a shared pool is called a Chunk, which is equivalent to the concept of Buffer or block in Buffer Cache.

The size of chunks is extremely inconsistent, the latest chunks can be only ten bytes, and the largest chunks have tens of megabytes or even hundreds of megabytes. The size of basic memory unit is not uniform, and frequent allocation and release operations make it easy to generate memory fragments in the shared pool. Overall, shared pools are the most complex memory pools in Oracle.

Includes the following:

·Library Cache

·Data Dictionary Cache

·Server Result Cache

·Reserved Pool

1 Library Cache

The library cache mainly stores SQL statements submitted by users, SQL statement related resolutions, SQL execution plans, PL/SQL program blocks, etc.

2 Data Dictionary Cache

The dictionary cache stores the memory structure of the data dictionary, including table definitions, Storage information, user permission information, constraint definitions, table statistics, etc. Frequent access to the data dictionary during SQL statement parsing. The dictionary cache is constructed to speed up semantic parsing during SQL parsing.

A data dictionary cache is also known as a row cache because it stores data in units of rows, unlike Buffer Cache, which stores data in units of blocks.

3 Server Result Cache

The server results cache is a memory pool in a shared pool. Unlike buffer pools, server result caches hold result sets, not chunks.

When a query is executed, the database determines whether the query result exists in the query result cache, and if so, retrieves it from the cache rather than executing the query. Caching enables the database to avoid expensive operations of rereading data blocks and recalculating results.

4 Reserved Pool

A retention pool is an area of memory in a shared pool that Oracle databases can use to allocate large contiguous blocks of memory.

If Oracle parses a PL/SQL program unit, memory needs to be allocated from the shared pool to these program unit objects as well. Because these objects are typically large (such as packages), the memory space allocated is relatively large. After a system runs for a long time, there may be a large amount of memory fragmentation in the shared pool, which may not satisfy the allocation of large memory segments. To make room for large chunks to cache, Oracle sets aside a region from the shared pool to allocate memory to hold the chunks.

The database allocates memory from the shared pool in blocks. Chunking allows large objects (more than 5 KB) to be loaded into the cache without the need for a single contiguous region. In this way, the database reduces memory fragmentation.

(4)Large Pool

A large pool is an optional memory region for larger memory allocations than a shared pool.

Large pools can provide large memory allocations for:

·For shared server and Oracle XA interfaces (for transactions interacting with multiple databases)

Message buffers used in parallel execution

·Buffer for Recovery Manager (RMAN) I/O dependencies

(5)Java Pool

A Java pool is an area of memory that stores all session-specific Java code and data in the Java Virtual Machine (JVM). This memory includes Java objects that migrate to Java session space at the end of the call.

For dedicated server connections, the Java pool includes shared parts of each Java class, including methods and read-only memory (such as code vectors), but does not include Java state for each session.

(6)Optional performance-related SGA subregions

Some SGA subregions are enabled only for specific performance characteristics. This section contains the following topics:

·In-Memory Area

·Memoptimize Pool

In-Memory is a newly added memory area starting from 12C in SGA, which can store table data in columns;

In-Memory does not replace traditional Buffer Cache, both exist in SGA. Columnar storage performs better when accessing multiple rows and fewer columns.

memoptimize pool size is set by MEMOPTIMIZE_POOL_SIZE, which stores hash indexes for fast lookup enabled tables.

Memoptimized Rowstore is supported since 18c and can be used to improve query performance. The performance improvement for SQL statements with frequent primary-based queries is significant. Fast lookup of tables can be enabled with the CREATE TABLE or ALTER TABLE…MEMOPTIMIZE FOR READ statements.

(7)Software Code Area Overview

A software code area is a portion of memory that stores code that is running or ready to run. Oracle database code is stored in a software region that is usually more exclusive and protected than the location of user programs. The size of a software region is usually static and changes only when the software is updated or reinstalled. The required size of these regions varies depending on the operating system.

Software zones are read-only and can be shared or unshared. Some database tools and utilities, such as Oracle Forms and SQL*Plus, can be installed as shared, but some cannot. Where possible, database code is shared so that all users can access it without having multiple copies in memory, reducing main memory and improving performance overall. If running on the same computer, multiple instances of a database can use the same database code region for different databases.

At this point, the study of "Oracle memory structure interview questions" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.