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

I. memory structure

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

PGA (Program Global Area): proprietary by each service process and background process; each process has a PGA.

II. SGA

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

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

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

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

4) Large pool: an optional area for caching large Icano requests to support parallel queries, shared server mode, and some backup operations.

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

6) Streams pool: used by Oracle streams.

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

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

9) nK block size buffer: provides caching for blocks that are different from the default block size of the database. Used to support tablespace transfer.

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

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

III. PGA

The private memory area of each service process, which contains the following structure:

1) Private SQL area: contains binding information and runtime memory structure. Each session that issues a sql statement has a private SQL area (private SQL zone)

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

IV. SGA COMPONENT

(1), Buffer Cache

1 、 DB_CACHE_SIZE

You can specify the size of the DB buffer cache through the parameter DB_CACHE_SIZE

ALTER SYSTEM SET DB_CACHE_SIZE=20M scope=both

The service process reads data from data files to buffer cache;DBWn and writes data from buffer cache to data files.

There are four states of buffer cache:

1) pinned: the current block is reading to cache or writing to disk, and other sessions are waiting to access the block.

2) clean:

3) empty in the free/unused:buffer, indicating the status of the instance when it was launched.

4) dirty: dirty data. Data blocks are modified and need to be flushed to disk by DBWn to perform expiration processing.

Block caching of multiple sizes is 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 cache size is specified by DB_CACHE_SIZE. If the standard block is nK, the size of the standard block cache cannot be specified through DB_nK_CACHE_SIZE, but should be specified by DB_CACHE_SIZE.

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

DB_CACHE_SIZE (specifies the cache area of the standard block (in this case, 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 cache with a block size of 32K)

2. Multiple buffer pools (buffer pool)

1) Keep: specified by the db_keep_cache_size parameter.

The data within this buffer may be reused to reduce the Imax O operation. The size of the pool is greater than the sum of the segments specified to the pool.

Blocks read into keep buffer do not need to expire.

2) Recycle: specified by db_recycle_cache_size parameter.

The data in this pool is less likely to be reused, and the pool size is less than the sum of the segments assigned to the pool. Blocks that are read into the pool need to perform expiration processing frequently.

3) Default: the buffer cache equivalent to an instance without Keep and Recycle pool, specified by the db_cache_size parameter.

3. Explicitly specify buffer pool for the object

The buffer_ buffer pool clause, which is used to specify a default 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 the DB_CACHE_SIZE parameter.

Syntax:

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)

(2), Share Pool

1 、 SHARE_POOL_SIZE

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

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

2) the information saved by Share Pool is shared by multiple sessions, including:

A.Library Cache

Library Cache also includes shared SQL zone and PL/SQL zone:

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

The definition of the data dictionary object is saved.

C.UGA (User Global Area)

UGA contains session information in shared server mode.

When sharing server mode, if large pool is not configured, UGA is saved in Share Pool.

(3) 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. Allocates memory for the Icano service process

b. Allocate memory for backup and restore operations

c. Allocates memory for online transactions between Oracle shared server mode and multiple databases.

By allocating session memory for shared server mode from large pool, you can reduce the fragmentation caused by share pool frequently allocating and reclaiming memory for large objects. Separating large objects from the share pool can improve the efficiency of shared pool, allowing it to serve new requests or retain existing data as needed.

(IV) Java Pool

1 、 JAVA_POOL_SIZE

Specify the java pool size through the JAVA_POOL_SIZE parameter.

The java code and data for a specific session in the jvm are saved.

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

Java's class loader uses about 8K of space for each loaded class.

Share pool is also used when the system tracks the dynamically loaded java classes during operation.

(5) Redo Log Buffer

1. The service process copies the redo entry of each DML/DDL statement from user space to redo log buffer.

2. Redo log buffer is a buffer that can be recycled. 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.

3. Causes LGWR to execute the conditions for writing redo log buffer to online redo log

a. User executes transaction commit commit

b. Reach 1 full or contain 1MB data every 3 seconds or within redo log buffer

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

(VI), ASMM (Automatic Shared Memory Management)

1 、 SGA_TARGET

1) the default value of SGA_TARGET is 0, that is, ASMM is disabled. You need to manually set the size of each component in the SGA.

2) when SGA_TARGET is non-0, enable ASMM and 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 nonstandard block buffers

Fixed SGA and other internal assignments.

2. To enable ASMM, you need to set STATISTICS_LEVEL to TYPICAL or ALL

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

If you set the SGA_TARGET=8G,SHARE_POOL_SIZE=1G manually, ASMM will automatically resize the internal components of the SGA to ensure that the share pool will not be less than 1G.

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 that can be allocated to SGA in memory.

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

5. PGA

(1) Private SQL Area

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

2. Each session that executes a sql statement has a private sql area.

3. When multiple users execute the same sql statement, the sql statement is saved in a file called shared sql area. This share sql area is assigned to the private sql area of these users

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

Dedicated server mode: private sql area is located in PGA

(2) Cursor and SQL Areas

(3) Work Area

A large portion of PGA is assigned to Work Area to do the following:

a. Operator-based sorting, group by, order by, rollup, and window functions.

Parameter is sort_area_size

B.hash hash join

Parameter is hash_area_size

c. Bitmap merging

Parameter is bitmap_merge_area_size

d. Bitmap creation

Parameter is create_bitmap_area_size

e. Write cache used by bulk load operation

(4) Session memory

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

(5) automatic PGA management

Setting PGA_AGGREGATE_TARGET to non-0 enables PGA automatic management and ignores all settings for * _ area_size. Such as sort_area_size,hash_area_size, etc.

Automatic management of PGA is enabled by default, and Oracle dynamically adjusts the memory size of dedicated and Work Area parts of PGA according to 20% of SGA, with a minimum of 10MB.

The total amount of PGA used for each active workspace (work area) in the instance, minus the PGA memory allocated by other components for the PGA_AGGREGATE_TARGET. The result is dynamically assigned to the corresponding workspace according to specific requirements.

1) to set the 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 value of PGA_AGGREGATE_TARGET through the pga statistics collected by oracle.

c. Resize the PGA_AGGREGATE_TARGET according to oracle's pga recommendations.

2) disable automatic pga management

For backward compatibility, set PGA_AGGREGATE_TARGET to 0, which disables automatic management of pga. You can use the associated * _ area_size parameter to adjust the maximum size of the corresponding workspace.

Bitmap_merge_area_size

Create_bitmap_area_size

Hash_area_size

Sort_area_size

1.jpeg

2.jpeg

3.jpeg

4.jpeg

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.