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

I. Memory structure

SGA(System Global Area): Shared by all service processes and background processes;

PGA(Program Global Area): Dedicated to each service process, background process; each process has a PGA.

II. SGA

Contains instance data and control information, including the following memory structure:

1) Database buffer cache: Cache data blocks retrieved from disk.

2) Redo log buffer: Cache redo information before writing to disk.

3) Shared pool: Cache various structures that can be shared among users.

Large pool: An optional area for caching large I/O requests to support parallel queries, shared server mode, and certain backup operations.

Java pool: Save data and java code for a specific session in the java virtual machine.

Streams pool: Used by Oracle streams.

7) Keep buffer cache: Keep the data stored in buffer cache for as long as possible.

8) Recycle buffer cache: Store data that is about to expire in buffer cache.

9) nK block size buffer: Provides a cache for blocks that differ in size from the database default block size. Used to support table space transfers.

database buffer cache, shared pool, large pool, streams pool and Java pool automatically adjust according to the current database state;

keep buffer cache,recycle buffer cache,nK block size buffer can be dynamically modified without closing the instance.

III. PGA

A memory area private to each service process, containing the following structure:

1) Private SQL area: contains binding information, runtime memory structure. Every session that issues SQL statements has a private SQL area.

2) Session memory: memory area allocated to store variables and other session-related information in the session.

IV. SGA Component

(i) Buffer Cache

1、DB_CACHE_SIZE

The DB buffer cache size can be specified with the DB_CACHE_SIZE parameter

ALTER SYSTEM SET DB_CACHE_SIZE=20M scope=both;

Service processes read data from data files to buffer cache;DBWn writes data from buffer cache to data files.

Buffer cache has four states:

Pinned: The current block is being read to cache or written to disk, and other sessions are waiting to access the block.

2）clean：

3) free/unused: buffer is empty, which is the state when the instance is just started.

4) Dirty: Dirty data, data blocks are modified and need to be flushed to disk by DBWn before expiration processing can be performed.

Multiple block sizes are supported in the same database. Specified by the DB_nK_CACHE_SIZE parameter, such as

? DB_2K_CACHE_SIZE

? DB_4K_CACHE_SIZE

? DB_8K_CACHE_SIZE

? DB_16K_CACHE_SIZE

? DB_32K_CACHE_SIZE

The standard block buffer size is specified by DB_CACHE_SIZE. If the standard block is nK, the size of the standard block buffer cannot be specified by DB_nK_CACHE_SIZE, it should be specified by DB_CACHE_SIZE.

For example, if the standard block is 8K, the parameters of the block cache size that the database can set are as follows:

? DB_CACHE_SIZE (specifies buffer area for standard blocks (here 8K))

? DB_2K_CACHE_SIZE (specifies a cache with a block size of 2K)

? DB_4K_CACHE_SIZE (specifies a cache with a block size of 4K)

? DB_16K_CACHE_SIZE (specifies a cache with a block size of 16K)

? DB_32K_CACHE_SIZE (specifies a buffer with a block size of 32K)

2. Multiple buffer pools

1) Keep: specified by db_keep_cache_size parameter.

The data in this buffer may be reused to reduce I/O operations. The size of the pool is greater than the sum of the segments assigned to the pool.

Blocks read into keep buffer do not require expiration.

Recycle: Specified by db_recycle_cache_size parameter.

The pool has a small chance of data being reused, and the pool size is smaller than the sum of the segments allocated to the pool. Blocks read into the pool require frequent expiration processing.

Default: Equivalent to a buffer cache without an instance of Keep and Recycle pool, specified by db_cache_size parameter.

3. Specify buffer pool explicitly for the object

The buffer_pool clause, which specifies the default buffer pool for an object, is part of the storage clause.

Valid for create and alter table, cluster, index statements.

If the existing object does not explicitly specify buffer pool, it is specified as default buffer pool by default, and the size is the value set by DB_CACHE_SIZE parameter.

Grammar:

a.CREATE INDEX cust_idx ON tt(id) STORAGE (BUFFER_POOL KEEP);

b.ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE);

c.ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER_POOL KEEP);

b) Share Pool

1、SHARE_POOL_SIZE

1) Share Pool can be specified with the SHARE_POOL_SIZE parameter:

SQL> alter system set shared_pool_size=20M scope=both;

2) The information stored in the Share Pool is shared by multiple sessions, including:

a.Library Cache

Library Cache also contains shared SQL and PL/SQL sections:

a). The shared SQL area holds parsed and compiled SQL statements.

The PL/SQL section holds parsed and compiled PL/SQL blocks (procedures and functions, packages, triggers, and anonymous PL/SQL blocks).

b.Data Dictionary Cache

Saved definitions for data dictionary objects.

c.UGA(User Global Area)

UGA contains session information in shared server mode.

In Shared Server mode, if the large pool is not configured, the UGA is saved in the Share Pool.

(c) Large Pool

1) The Large Pool size is specified by the LARGE_POOL_SIZE parameter:

SQL> alter system set large_pool_size=20m scope=both;

2) Function:

a. Allocate memory for I/O service processes

b. Allocating memory for backup and restore operations

c. Allocate memory for online transactions between Oracle Shared Server schema and multiple databases.

By allocating session memory for shared server patterns from large pools, you can reduce fragmentation of the share pool due to frequent allocation and reclamation of memory for large objects. Separating large objects from the shared pool increases the efficiency of the shared pool, allowing it to service new requests or retain existing data as needed.

4) Java Pool

1、JAVA_POOL_SIZE

Specify the java pool size via the JAVA_POOL_SIZE parameter.

Java code and data for a particular session in jvm are saved.

Share pool is used when compiling java code in the database and using java resource objects in the database.

Java class loaders use approximately 8K of space for each class loaded.

During system tracing, java classes loaded dynamically will also be used to share pool.

(v) Redo Log Buffer

The service process copies the redo entry for each DML/DDL statement from user space into the redo log buffer.

The redo log buffer is a buffer that can be recycled, and the service process copies the new redo to overwrite the entries in the redo log buffer that have been written to disk (online redo log) through LGWR.

Conditions that cause LGWR to write redo log buffer to online redo log

a. The user executes the transaction commit

b. Every 3 seconds or redo log buffer is 1/3 full or contains 1MB of data

c. When the DBWn process writes the modified buffer to disk (if the corresponding redo log data has not been written to disk)

ASMM(Automatic Shared Memory Management)

1、SGA_TARGET

1) SGA_TARGET defaults to 0, i.e. ASMM is disabled. You need to manually set the size of each component in the SGA.

2) When SGA_TARGET is non-zero, ASMM is enabled to automatically resize the following components:

DB buffer cache（default pool）

shared pool

large pool

streams pool

java pool

However, in ASSM, the following parameters still need to be specified manually:

log buffer

keep, recycle, and non-standard block buffers

Fixed SGA and other internal allocations.

2. Enabling ASMM requires setting STATISTICS_LEVEL to TYPICAL or ALL

3. Enable ASMM to automatically resize SGA internal components. If you specify a component value manually, it is the minimum value for that component. as

Manually set SGA_TARGET= 8G, SHARE_POOL_SIZE =1G, then ASMM will ensure that the share pool will not fall below 1G when automatically adjusting the size of SGA internal components.

SQL> SELECT component, current_size/1024/1024 size_mb FROM v$sga_dynamic_components;

4、SGA_MAX_SIZE

SGA_MAX_SIZE Specifies the maximum value in memory that can be allocated to SGA.

SGA_TARGET is a dynamic parameter with a maximum value of the value specified by SGA_MAX_SIZE.

V. PGA

A. Private SQL Area

1. The binding information of the current session and the runtime memory structure are saved. this information

Every session that executes SQL statements has a private SQL area.

When multiple users execute the same SQL statement, the SQL statement is stored in a shared SQL area. This share sql area is assigned to the private sql area of these users

Shared server mode: private sql area is located in the share pool or large pool of SGA

Private server mode: private sql area in PGA

Cursor, SQL Areas

(c) Work Area

A large portion of PGA is allocated to the Work Area, which is used to perform the following operations:

Operator-based ordering, group by, order by, rollover, and window functions.

The parameter is sort_area_size

b.hash concatenation,

The parameter is hash_area_size

c. Bitmap merging,

Parameter is bitmap_merge_area_size

d. Bitmap creation,

The parameter is create_bitmap_area_size

e. Write cache used by bulk load operations

4) Session memory

Save session variables, such as login information and other session-related information. In shared server mode, Session memory is shared.

(v) Automatic PGA management

Set PGA_AGGREGATE_TARGET to non-zero to enable PGA auto-management and ignore all *_area_size settings. Such as sort_area_size,hash_area_size, etc.

By default, PGA auto-management is enabled, and Oracle dynamically adjusts the memory size of the dedicated and Work Area portion of PGA based on 20% of the SGA, to a minimum of 10MB.

The total PGA used for each active work area in the instance is PGA_AGGREGATE_TARGET minus PGA memory allocated by other components. The results obtained are dynamically distributed to corresponding workspaces according to specific requirements.

1) Steps to set PGA_AGGREGATE_TARGET size

a. Set PGA_AGGREGATE_TARGET to 20% of SGA, which may be too low for DSS systems.

b. Run a typical load and adjust the PGA_AGGREGATE_TARGET value with PGA statistics collected by oracle.

c. Adjust PGA_AGGREGATE_TARGET size according to oracle's PGA recommendations.

2) Disable automatic PGA management

For backward compatibility, set PGA_AGGREGATE_TARGET to 0 to disable automatic management of PGA. The maximum size of the corresponding workspace can be adjusted using the associated *_area_size parameter.

bitmap_merge_area_size

create_bitmap_area_size

hash_area_size

sort_area_size

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.