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

Overview of System Global Area (SGA)

SGA is a read-write memory area that, together with the oracle database background process, forms an oracle database instance. Server processes corresponding to all user processes can read information in SGA. Some processes write to the SGA area during database operations.

SGA has a variety of functions, as follows:

Maintain internal data structures accessed concurrently by multiple processes and threads

Cache blocks from disk

Cache the redo log before writing the redo log to the online redo log file

Storage SQL execution plan

Server and background processes do not reside in the SGA zone, but in a separate memory space.

Each database instance has its own SGA memory area. When the instance is started, the oracle database automatically allocates the SGA memory area to the oracle instance, and the SGA memory area is reclaimed when the instance is closed.

When you start an instance using SQL*Plus or Oracle Enterprise Manager, the size of the SGA is displayed, as shown in the following figure:

As shown in the following figure, SGA consists of several memory components, which are memory pools allocated to meet specific needs. Except for the redo log buffer, all SGA components allocate space in a contiguous memory cell called granules. The particle size is determined by the total SGA size, the larger the SGA, the larger the particles, and the smaller the SGA, the smaller the particles.

The main SGA components are as follows:

Database buffer (Database Buffer Cache)

Column storage in memory (In-Memory Column Store)

Redo log buffer (Redo Log Buffer)

Shared Pool (Shared Pool)

Daichi (Large Pool)

Java Pool (Java Pool)

Stream pool (Streams Pool)

Fixed SGA area (Fixed SGA)

Detailed explanation of database buffer (Database Buffer Cache)

A data buffer, also known as a buffer, is a memory area that stores blocks of data copied from a data file.

A buffer is a main memory address where the buffer manager temporarily caches current or recently used blocks of data. All users connect concurrently to the database instance shared cache cache.

The purpose of database buffer caching

Oracle database can achieve multiple goals by using buffer cache.

The objectives include:

Optimize physical IPUBO

The database updates the blocks in the cache and stores metadata about the changes in the redo log buffer. Upon submission, the database writes the contents of the redo log buffer to the online redo log file but does not write the block to the data file at this time, and the block is then lazily written to the data file by the DBWn background process.

Keep frequently accessed data blocks in the buffer cache and write rarely accessed data blocks to disk

When database intelligent Flash caching is enabled, a portion of the buffer cache can reside in the Flash cache. This buffer cache extension is stored on one or more flash devices, which are solid-state storage devices that use flash memory. Databases can improve performance by caching rather than reading data directly from disk.

Configure multiple flash devices using DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE initialization parameters. The buffer cache tracks each device and distributes buffers uniformly to the device.

Database intelligent Flash caching can only be used in Solaris and Oracle Linux.

Buffer state

The database uses internal algorithms to manage buffers in the cache.

Buffers can exist in any of the following mutually exclusive states:

Unused statu

The buffer is available because it has never been used or is not currently used. This type of buffer is the easiest to use in the database.

Clean statu

This buffer has been used before and now contains a readable and consistent version of a block on the time node that contains data but it is "clean" and therefore does not require checkpoints. The database can determine the block and reuse it.

Dirty statu

The buffer contains data that has been modified but has not been written to disk. The database must check the block before reusing it.

Each buffer has an access mode: fixed or free (not fixed). The buffer is "pinned" to the cache so that it does not run out of memory when the user session accesses memory. Multiple sessions cannot modify a fixed buffer at the same time.

Buffer mode

When the client requests data, the oracle database retrieves the buffer from the database cache in the current mode or in consistent mode.

The differences between these patterns are as follows:

Current mode

The current mode is the retrieval of a block that currently appears in the buffer cache. For example, if an uncommitted transaction updates two rows in a block, the current schema uses those uncommitted rows to retrieve the block. The most common database modification statement is the db block, which must only update the current version of the block.

Consistency mode

A consistent read get is a retrieval of a read-consistent version of a block. This retrieval can be used to undo data. For example, if an uncommitted transaction updates two rows in a block, and if a query in another separate session requests the block, the database uses undo data to create a consistent version of the block (called a consistent read clone), excluding uncommitted updates. Typically, the query retrieves the block in consistent mode.

Ipaw O buffer

The logical Ithumb O, also known as the Imax O buffer, refers to reading and writing in the buffer cache.

When the requested buffer is not found in memory, the database performs a physical Iswap O to copy the buffer from flash or disk to memory. The database then executes a logical Ithumb O to read the cached buffer.

Buffer replacement algorithm

To make buffer access more efficient, the database must decide which buffers are cached in memory and which buffers are accessed from disk.

The database uses the following algorithms:

LRU-based,block-level permutation algorithm (block level)

This complex algorithm defaults to a recently used table (LRU) that contains pointers to dirty and non-dirty buffers. Cold buffers are buffers that have not been used recently, while hot buffers are buffers that are frequently accessed and recently used. In theory, there is only one recently used table (LRU), but from the perspective of data concurrency, the database actually uses a lot of recently used tables (LRU).

Temperature-based,object-level permutation algorithm (object level)

Starting with Oracle Database 12c Release 1 (12.1.0.2), the automatic large table cache feature allows table scans to use different algorithms in the following scenarios:

Parallel queries (parallel query): in single instance and Oracle RAC cluster databases, when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a non-zero value, parallel queries can use large table caching, while PARALLEL_DEGREE_POLICY is set to automatic or adaptive.

Serial queries (Serial query): in a single instance configuration, serial queries can use large table caching when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a non-zero value.

When a table is larger than a memory block, the database determines which buffers to cache based on the access pattern. For example, if only 95% of the popular tables are suitable for memory, the database can choose to place the remaining 5% of the data blocks on disk instead of periodically reading the blocks into memory and writing them to disk-a situation known as jitter. When caching multiple large objects, the database considers caching hotter tables rather than colder tables, which affects which blocks are cached. The DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter sets the percentage of buffer caches under this algorithm.

Buffer Writes (buffer write)

The database write (DBW) process periodically writes cold end data and dirty buffer data to disk.

DBW will trigger a write operation under the following circumstances:

The server process could not find a clean buffer to cache the data block.

When buffers are soiled, the number of free buffers is reduced. If the number falls to a threshold and a clean buffer is needed, the server process signals the DBW process to work.

The tablespace is changed to read-only or offline.

The database must advance the location of the checkpoint in the redo thread, and instance recovery must begin at that checkpoint.

Buffer Reads (buffer read)

When the number of buffers available is low, the database must remove buffers from the buffer cache.

The algorithm depends on whether flash caching is enabled:

Flash cache is not available

The database reuses each clean buffer when needed, overwriting it with new blocks of data. If a block of data is then needed to be overwritten, the database must be read from disk again.

Flash cache is available

The DBW process can write the body of a clean buffer to the flash cache so that its memory buffer can be reused. The database keeps a buffer header in the in-memory LRU list to track the status and location of the buffer body in the flash cache. If this buffer is needed later, the database can read data from the flash cache instead of from disk.

When a client process requests a buffer, the server process searches for the cache of the buffer. If a buffer is found in memory, a cache hit occurs. The order of the search is as follows:

1. The server process searches the entire buffer cache for buffers. If the server process finds the entire buffer, the database performs a logical read of the buffer

2. The server process looks for the buffer header in the flash cache LRU table. If the process finds the buffer header, the database performs a physical read operation to read the data from the flash cache to the memory cache.

3. If the server process cannot find the buffer in memory, the server process will perform the following actions:

A. Perform a physical Ihammer O read operation to copy a block of data files from the disk into memory.

B. perform a logical read operation to the buffer in memory

The following figure illustrates the buffer search sequence. The extended buffer cache includes the memory buffer cache (which contains the entire buffer) and the flash cache (which contains the buffer body). In the figure, when the database searches for a buffer in the buffer cache but cannot find it, it reads blocks of data from disk into memory.

Generally speaking, accessing data through cache hit is faster than accessing data through cache miss. The buffer cache hit ratio measures how often the requested data block is found in the buffer cache without the need to read data from disk.

The database can perform physical reads from data files or temporary files. Read the data from the data file and follow the logic Icano. Read from a temporary file, a temporary file is generated when insufficient memory forces the database to write data to the temporary table and then read the data. These physical reads bypass the buffer cache and do not produce logical I _ hand O.

Buffer Touch Counts (buffer Touch count)

The database uses a touch count to measure the buffer access frequency on the LRU list. This mechanism enables the database to increase the counter when the buffer is fixed, rather than constantly adjusting the buffer on the LRU list.

The database does not physically move the location of the data block in memory, which means changing the position of the pointer in the table.

When the buffer is fixed, the database determines whether the touch count was last increased. If the count value is increased 3 seconds ago, the count value will increase; otherwise, the count value will remain the same. This 3-second rule will prevent the buffer touch count from exploding. For example, a session may insert many rows in a data block, but the database will treat these rows as a touch value.

If the buffer is on the cold side of the LRU, but its touch count value is high, then the buffer will be moved to the hot side. If the touch count value is low, the buffer is removed from the cache.

Buffer Pools (buffer pool)

A buffer pool is a collection of buffers.

The database buffer cache is divided into one or more buffer pools that manage blocks in the same way. There is no essential difference between the algorithms of buffer pool aging block and buffer block.

You can manually configure separate buffer pools that store data in the buffer cache. You can also specify available buffer pools for new blocks as soon as you finish using them. Specific schema objects are then assigned to the appropriate buffer pool to control how blocks are used from the cache. For example, segments can be divided into hot, warm, and cold buffer pools.

The buffer pools are classified as follows:

Default pool (default pool)

This pool is the location where blocks are usually cached. Unless you manually configure a separate pool, the default pool is the only buffer pool. The optional configuration of other pools has no effect on the default pool.

Since oracle Database 12c Release 1 (12.1.0.2), large table caching has been an optional part of the default pool, using an object-level heat-based replacement algorithm. In single instance and oracle rac databases, parallel queries can use large table caching when the value of DB_BIG_TABLE_CACHE_PERCENT_TARGET is not zero and PARALLEL_DEGREE_POLICY is set to automatic. In a single instance configuration, serial queries can use large table caching when the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter is set to a non-zero value.

Keep Pool (reserved pool)

This pool is reserved for blocks that are accessed frequently but have to age out of the default pool due to lack of space. The purpose of the reserved pool is to keep objects in memory, thus avoiding the Icano operation.

Reserved pools manage buffers in the same way as other pools: it does not use special algorithms to fix buffers. "Keep" is a naming convention. You can place tables that you want to keep in a large retention pool, or you can place tables that you don't want to keep in a small circular pool.

Recycle Pool (circular pool)

This pool is for blocks that are used less frequently. Circular pooling prevents objects from consuming unnecessary space in the cache.

The database has a standard block size. You can also create a tablespace with a block size different from the standard size. Each non-default block size has its own pool. The oracle database manages blocks in these pools in the same way as default pools.

The following figure shows the structure of the buffer cache when multiple pools are used. The cache contains default pools, reserved pools, and circular pools. The default block size is 8KB. The cache structure contains separate pools for tablespaces, using non-standard block sizes of 2Kb, 4Kb, and 16Kb.

Buffer and full table scan

The database uses a complex algorithm to manage table scans. By default, when a buffer must be read from disk, the database inserts the buffer in the middle of the LRU list. In this way, hot blocks can be left in the cache so that they do not need to be read from disk again.

If a full table scan is performed, all the rows in the table under the high water level need to be read sequentially. Assume that the total size of all blocks in the table segment is greater than the size of the buffer cache. A full scan of the table clears all buffer caches, which will prevent the database from maintaining caches that are frequently accessed to blocks.

Default mode of full table scan

By default, the database performs a full table scan in a conservative way, and small tables are loaded into memory only if the table size is a small part of the buffer cache.

To determine whether medium-sized tables should be cached, the database uses an algorithm that includes the time interval between the last table scan, the aging timestamp of the buffer cache, and the remaining space in the buffer cache.

For very large tables, the database usually uses direct reads to load the large tables directly into the PGA cache, bypassing SGA, so as to avoid filling up the buffer cache. For medium-sized tables, the database uses either direct reads or cached reads, and if you decide to use cached reads, the database places blocks at the end of the LRU table to prevent the scan from effectively clearing the buffer cache.

Starting with Oracle Database 12c Release 1, the buffer cache of the database instance automatically performs internal calculations to determine whether SGA has enough memory to cache the entire database, and what performance benefits are if caching the tables being accessed. If the entire database size is smaller than the memory size and other internal conditions are met, the database treats all tables as small tables and thinks they can be cached, but the database does not cache LOBs with NOCACHE tags.

Parallel Query Execution (parallel query execution)

In the process of performing a full table scan, the database can sometimes improve response speed by executing multiple parallel server processes.

In some cases, when the database has a large amount of memory, the data can cache parallel query data in SGA instead of reading the data into PGA through a direct path. In general, parallel queries appear in low-concurrency data warehouses due to the use of potential resources.

CACHE Attribute (cache properties)

In cases where default caching behavior is not required, you can use ALTER TABLE... CACHE to change the way blocks in large tables are read into the database buffer cache.

For tables with cached property sets, the database does not force blocks to be placed in the buffer cache. Instead, the database removes the block from the cache in the same way as other table blocks. Be careful when doing this, as a full scan of a large table may clear other blocks in the cache.

Ps: execute ALTER TABLE... CACHE does not cause tables to be cached.

KEEP Attribute (reserved attributes)

For large tables, you can execute ALTER TABLE... STORAGE BUFFER_POOL KEEP scans the blocks of these tables and loads the tables into the retention pool.

By placing a table in the reserved pool, you can change part of the storage block buffer cache and the data caches them in the default buffer pool. There is no separate algorithm to control the reserved pool cache.

Force Full Database Caching Mode (enforce full database caching mode)

To improve performance in some cases, you can execute ALTER DATABASE... FORCE FULL DATABASE CACHING statement to start the mandatory full database caching mode.

Compared to the default automatic mode, the forced full database caching mode assumes that the entire database, including NOCACHE LOBs, can be cached in a buffer. This mode begins with Oracle Database 12c Release 1 (12.1.0.2).

Starting full database caching mode does not force the database into memory. It means that the entire database is fully cached, and the oracle database caches these tables only when they are accessed.

Oracle recommends that full database cache mode be enabled only if the buffer cache size of each individual instance is larger than the database size. This guideline applies to single instance and oracle RAC databases. However, full database caching mode can be enabled when the oracle RAC application is well partitioned and the combined buffers of all instances (areas that handle duplicate cache blocks between instances) are larger than the database size.

In-Memory Column Store (column storage in memory)

Starting with oracle 12c, in-memory column storage (IM column storage) is an optional static SGA pool that stores copies of tables and partitions in a special column format for quick scanning.

IM column storage is not a substitute for buffer caching, but rather a supplement so that two memory regions can store the same data in different formats. By default, only data objects specified as INMEMORY using DDL are populated into the IM column store.

Objects that are populated in the IM column store do not need to be loaded into the database buffer cache.

The column format exists only in memory. The following figure shows three tables of the sh schema stored in the IM column store: customer, product, and sales. IM column storage stores data through columns rather than rows. The database keeps the columnar data consistent with the buffer cache.

Benefits of IM Column Store

The IM column store supports database scanning (scans), joins (joins), and aggregates (aggregation), which is much faster than using only on-disk mode.

Typically, IM columns can be used in the following situations:

Queries that scan a large number of rows and will use filters such as the following operator: =

A query that selects a small column from a table or materialized view, with a large number of columns, such as a query that selects 5 columns from 100 columns in the table

Join a small table to a query of a larger table

Query of aggregation class

Business applications, special analytical queries, and data warehouse workloads benefit the most. Pure OLTP databases that use indexes to find short transactions benefit less.

IM columnar storage has the following advantages:

Support for all existing database features, including highly available features

No need to change the application

The optimizer automatically uses the column format

Simple configuration

The INMEMORY_SIZE initialization parameter specifies the amount of memory reserved in the IM column store. The DDL statement can specify the tablespace, table, partition, or column to read into the IM column store.

Compression optimization to improve query performance

These compression techniques enable the session to read more data into memory, thereby increasing the effective memory bandwidth.

Fewer indexes, materialized views, and OLAP cubes are required

The reduction of the number of pre-built objects leads to a reduction in storage space and a significant reduction in processing overhead.

Dual memory format: columns and rows

When getting data, the oracle database can read the IM column store or database buffer cache, or read both the column cache and the buffer cache in the same query.

The database sends OLTP queries, such as primary key lookups, to the buffer cache and parsing and query reports to the IM column store. Therefore, dual memory format is the best choice.

In the execution plan, "TABLE ACCESS IN MEMORY FULL" can specify the use of IM column storage.

The following figure shows a sample IM column store. The sales table is stored on disk in the traditional row format. SGA stores data in cylindrical format in the IM column store and in row format in the database buffer cache.

The IM column stores the on-disk data format that supports each persistent, organized table. Column format does not affect the data format stored in the data file or buffer cache, nor does it affect undo, online redo logging, and so on.

Regardless of whether the IM column store is in use or not, the database handles DML changes in the same way: by updating the buffer cache, redoing log files online, undoing tablespaces, and so on. The database uses an internal mechanism to track changes to ensure that the IM column storage is consistent with the rest of the database. For example, if the sales table resides in the IM column store and the row in the sales table is updated, the database automatically ensures that the copy of the sales table in the IM column store is consistent with the transaction. Query results that access the IM column repository always return the same results as the query buffer cache.

IM column padding

The database reads data from disk in row format, transfers rows to create columns, and then compresses the data into an in-memory compression unit (IMCUs).

The Wnnn process populates the IM column store with data. Each worker process operates on a subset of the data blocks of the object. Population is a streaming media mechanism that compresses data and converts it to a columnar format.

The INMEMORY_MAX_POPULATE_SERVERS initialization parameter specifies the maximum number of worker processes used for IM column storage. By default, it is set to half of cpu_count. Set this parameter to the optimal value in the system environment. More worker processes make population faster, but use more CPU resources; fewer worker processes result in slower population, but this reduces CPU overhead.

If INMEMORY_MAX_POPULATE_SERVERS is set to 0, population is not available.

The population stored in the IM column when the instance is started

Each time the database instance starts, the database must reconstruct the complete IM column format from disk. This refactoring is necessary because the IM column store resides only in memory.

The population stored in the IM column in response to the query

Setting the IM property on an object means that the object is a candidate in the IM column store, rather than that the database immediately populates the object into memory.

By default (memory priority is set to NONE), the database defers populating the table into the IM column store until the database finds the table useful. When the object is set to the IM property and the database believes that memory has a better use elsewhere, the database may not choose to populate all columns into the IM column store. The IM column stores a subset of columns that can be populated from a table.

The following is the process of populating objects in the IM column store:

Suppose you connect to the database as an administrator. To determine whether data from the sh.customers table is populated into the IM column store, you can execute the following query:

In this example, because the table sh.customers has not been accessed before, segments are not populated in the IM column store. Query table sh.customers first, and then query V$IM_SEGMENTS:

The following query results confirm that the database uses the IM column store to retrieve the results:

Cylindrical compression in memory

IM column storage uses a special compression format for access speed rather than reducing storage.

The database improves access speed in the following ways:

The compressed format reduces the total amount of memory that needs to be processed per column, and SQL can be executed directly on the compressed column.

The database uses SIMD vector instructions to process a set of column values in a CPU clock cycle. Storing many values on a VECTOR can make the most of SIMD processing performance.

You can use MEMCOMPRESS statements in the clauses of CREATE and ALTER statements to select different compression ratios.

The default compression option is MEMCOMPRESS FOR QUERY LOW. This option provides the fastest read speed because in this mode the database does not need to decompress the data. Other compression options, such as FOR QUERY HIGH and FOR CAPACITY modes, use layer compression, some of which require decompression. You can make DBMS_COMPRESSION before using the IM column to store the storage table. GET_COMPRESSION_RATIO can report the compression ratio, which is used to estimate how much space is required.

IM column compression is a bit similar to mixed column compression, and both need to deal with a set of columns. The main difference is that the column vector stored in the IM column is optimized for memory storage, while the mixed compressed column vector is optimized for disk storage.

IM columnar compression is closely related to mixed columnar compression. Both techniques involve the processing set of column vectors. The main difference is that the column vector of the IM column is optimized for memory storage, while the column vector of hybrid columnar compression is optimized for disk storage.

Scan operation for IM column storage

The column format allows the query to scan only the columns it needs.

For example, suppose a user executes the following ad and hoc queries:

When using buffer caching, the database usually scans an index to find the product ID, uses rowid to fetch rows from disk to the buffer cache, and then discards unwanted column values. Scanning data in a row format in the buffer cache requires many CPU instructions and can lead to CPU inefficiency.

When using IM column storage, the database only needs to scan the requested sales columns to avoid using the expensive disk Imando O. Scanning data in a column format pipeline only requires CPU to scan the necessary columns to improve efficiency. Each CPU core scans the local memory column using the SIMD vector instruction.

Redo log buffer

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

A redo record is a data structure that contains the necessary information for refactoring or redoing a database through DML or DDL operations. Database recovery is the use of redo logs to reconstruct lost changes in data files.

The database copies the redo record from the user's memory space to the redo log buffer in SGA. Rewriting records takes up contiguous space in the buffer. The LGWR background log writing process writes the log of the redo log buffer to the active online redo log group on disk. The following figure shows how the redo log buffer works:

LGWR writes redo sequentially to disk, while DBWn writes blocks of data to disk separately. Writing separately tends to be much slower than sequential writing. Because the LGWR process enables users to avoid waiting for DBWn to complete its slow write operation, the database has better performance.

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

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.