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

Memory structure of Oracle architecture (SGA, PGA)

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

According to the memory structure of Oracle, in addition to SGA (System Global Area), the Oracle process uses the following three global zones:

PGA: process global area UGA: user global area CGA: call global area

1. PGA and UGA many people don't understand the difference between PGA and UGA. In fact, the difference between them is similar to that between a process and a session. Although there is generally an one-to-one relationship between process and session, it is actually more complicated than this. One obvious case is the MTS configuration, where there are often many more sessions than processes. In this configuration, each process will have a PGA and each session will have a UGA. The information contained in PGA has nothing to do with the session, while the information contained in UGA is based on a specific session.

2. The PGA process global area (PGA) can be understood as either Process Global Area or Program Global Area. Its memory segment is in the process private zone (Process Private Memory) rather than in the shared zone (Shared Memory). The server process of each Oracle has its own PGA, which only contains the specific information about the process. The structure in PGA does not need to be protected by latches, because other processes can not enter it to access.

PGA contains two main regions: Fixed PGA and Variable PGA, or PGA Heap. The function of Fixed PGA is similar to that of Fixed SGA, including atomic variables (indivisible), small data structures and pointers to Variable PGA.

Variable PGA is a heap. Its Chunks can be seen from Fixed Table X$KSMPP, and the structure of this table is the same as the X$KSMSP mentioned earlier. PGA HEAP contains some permanent memory about Fixed Table, which depends on the setting of some parameters. These parameters include DB_FILES,LOG_FILES,CONTROL_FILES.

3. UGA

UGA (User Global Area) contains information about a specific session.

Like PGA, UGA consists of two regions: Fixed UGA and Variable UGA, also known as UGA HEAP. Fixed UGA contains about 70 atomic variables, small data structures and pointers to Variable UGA.

Chunks in UGA HEAP can get relevant information from their own session by looking at the table X$KSMUP, which has the same structure as X$KSMSP. UGA HEAP contains some permanent memory segments about fixed tables, depending on the setting of some parameters. These parameters are OPEN_CURSORS,OPEN_LINKS, and MAX_ENABLE_ROLES.

The location of the UGA in memory depends on how the session is configured. If the session connection is configured in dedicated server mode (DDS), that is, a session corresponds to a process, then UGA is placed in PGA. In PGA, Fixed UGA is one of the Chunk, and UGA HEAP is a Subheap of PGA.

If the session connection is configured in shared server mode (MTS), Fixed UGA is a Chunk in SHARED POOL or Large pool, and UGA HEAP is a Subheap in SHARED POOL or large pool.

4. CGA

Unlike other global areas, Call Global Area is temporary. It exists only during function calls, and generally requires CGA at the lowest level of calls to the instance, such as analyzing a SQL statement, executing a SQL statement, and fetching the output of a SELECT statement.

A separate CGA is required for recursive calls. In the process of parsing SQL statements, recursive calls to data dictionary information are needed because of the syntax analysis of SQL statements and the calculation of execution plans during sentence optimization. When executing PL/SQL blocks, recursive calls are also needed when dealing with the execution of SQL statements, and recursive calls are also needed to deal with trigger execution during the execution of DML statements.

Whether the UGA is placed in PGA or in SGA, CGA is a Subheap of PGA. An important corollary of this fact is that the session must be a process during a call. It is important to understand this when developing applications in a MTS Oracle database process. If there are more calls, you have to increase the number of processes to accommodate the increase in calls.

CALLS cannot work without the data structure in CGA. In fact, the data structures related to a CALL are generally placed in the UGA, such as SQL AREA,PL/SQL AREA, SORT AREA, hash area, bitmap area, they must be in the UGA, because they have to exist and be available all the time between the CALLS. The data structure contained in CGA can be released after the end of a CALL. For example, CGA contains information about recursive calls, direct Imax O BUFFER, and other temporary data structures.

5. To sum up.

PGA includes:

1) Fixed PGA

2) UGA (dedicated server), when the system is shared server, the UGA is located in shared pool or large pool.

3) CGA

UGA and CGA both belong to Variable PGA (PGA heap).

UGA includes:

1) Fixed UGA

2) private SQL area

3) session memory

4) SQL Work Areas

Private SQL area, session memory and SQL work ares all belong to variable UGA (UGA heap).

6. Private SQL area

It can be divided into two parts:

1) permanent memory area: stores some cursor information needed when the same SQL statement is executed many times, such as binding variables, etc. This part of memory is released only when the cursor is closed. So it is called: permanent memory region.

2) Runtime zone: the first step in processing SQL statements is to create a runtime zone, where the information used when SQL is run is stored. For DML statements, the region is released after SQL execution; for select statements, when all data rows are returned to the user.

7. Session area

Save some parameters of the session, such as the modified NLS parameter, the trace information enabled by the modified optimizer parameter optimizer_mode,alter session command, the roles that can be used, the db links opened, the package actually used, and so on.

8. SQL work area

The SQL workspace is the most important part of UGA and accounts for most of the memory of UGA. It is mainly used when sorting sort (ordr by, group by) and hash-join. The size of the SQL workspace has a significant impact on performance.

Generally speaking, a large number of sorting is unlikely to be done entirely in SQL work area, because there is not so much memory, so temporary tablespaces are generally used to write the intermediate results of sorting to the temp tablespace (disk sorting). Depending on the size of the SQL work area, temporary tablespaces can be used once or multiple times. So there are three situations:

1) optimal: sorting is done entirely in memory

2) onepass: sorting is complete, and you need to use disk once.

3) multipass: the disk needs to be used multiple times to complete the sorting.

Optimal is generally not very realistic. We generally try our best to ensure onepass. If it leads to multipass, the performance will degrade a lot. Let's first take a look at the sorting process:

Sorting algorithm uses heap sorting; sorting method is batch sorting, such as 10000 records sorting, it is divided into 100times, each time 100records are sorted; when 100records are sorted, it is written to the temp table space; then the next 100sort is written to the temp table space; finally, the merge operation is performed on the temp table space with 100 locally sorted queues. In order to complete the sorting of 10000 records. This is an ideal situation because it is only read and written to the disk once and belongs to onepass. If sorting involves multiple reads and writes to the disk, it belongs to multipass. Generally, we try to adjust the size of sort_area_size to avoid multipass sorting.

SQL work area can be divided into the following sections:

Sorting area (sort_area_size), hash area (hash_area_size), bitmap area (create_bitmap_area_size,bitmap_merge_area_size), etc., they have a profound impact on the performance of sorting, hash-join, bitmap and so on.

9. Automatic management of PGA

Before Oracle9i, we managed PGA by setting parameters such as sort_area_size, hash_area_size, etc.

SQL > show parameter area_size

NAME TYPE VALUE

-

Bitmap_merge_area_size integer 1048576

Create_bitmap_area_size integer 8388608

Hash_area_size integer 131072

Sort_area_size integer 65536

Workarea_size_policy string AUTO

SQL > show parameter sort_area

NAME TYPE VALUE

-

Sort_area_retained_size integer 0

Sort_area_size integer 65536

Starting from Oracle9i, Oracle introduces the automatic management of PGA. There is no need for us to set the size separately.

Set the parameter workarea_size_policy parameter, which can be selected between PGA automatic (auto defaults to auto) and PGA manual management (manual).

Then set the memory sum of PGA (equivalent to the sga_max_size of SGA) by initializing the parameter pga_aggregate_size. Note that in Oracle9i, PGA automatic management is valid only for dedicate server, not for shared server, but for Oracle10g, PGA automatic management is valid.

10. PGA size setting:

For OLTP systems, the typical PGA memory is:

PGA_AGGREGATE_SIZE = (total_memory * 80%) * 20%

For OLAP systems, because some large queries are run:

PGA_AGGREATE_SIZE = (total_memoery * 80%) * 50%

80% means that 80% of the machine's total memory is allocated to Oracle. Then give 20% of the 80% to PGA.

We can also use some PGA-related views to resize the pga_aggregate_size:

V$pgastat, v$pga_target_advice, v$pga_target_advice_histogram

Use v$pgastat to view statistics for the current PGA (similar to v$sgastat and SGA):

SQL > select * from v$pgastat

NAME VALUE UNIT

Aggregate PGA target parameter 71303168 bytes

Aggregate PGA auto target 27297792 bytes

Global memory bound 14260224 bytes

Total PGA inuse 40966144 bytes

Total PGA allocated 88008704 bytes

Maximum PGA allocated 125034496 bytes

Total freeable PGA memory 4259840 bytes

Process count 23

Max processes count 40

PGA memory freed back to OS 492634112 bytes

Total PGA used for auto workareas 0 bytes

Maximum PGA used for auto workareas 6105088 bytes

Total PGA used for manual workareas 0 bytes

Maximum PGA used for manual workareas 0 bytes

Over allocation count 0

Bytes processed 6275959808 bytes

Extra bytes read/written 0 bytes

Cache hit percentage 100 percent

Recompute count (total) 33814

19 rows selected.

The maximum value maximum PGA allocated:PGA has ever reached

Over allocation count: Over allocating PGA memory can happen if the value ofPGA_AGGREGATE_TARGET is too small. When this happens, the Oracle Database cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated. If over allocation occurs, then increase the value of PGA_AGGREGATE_TARGET using the information provided by the V$PGA_TARGET_ADVICE view. Extra bytes read/written: Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed. (that is, the number of bytes of read-write disks sorted by disks)

Use v$pga_target_advice to estimate the size of PGA, which is the recommended value of PGA (similar to v$sga_target_advice and SGA):

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

SQL > select round (pga_target_for_estimate/ (1024 / 1024)) target_size_M, 2 estd_pga_cache_hit_percentage est_cache_hit_percentage, 3 round (estd_extra_bytes_rw/ (1024 / 1024)) est_extra_read_write_M, 4 estd_overalloc_count est_over_alloc from v$pga_target_advice

TARGET_SIZE_M EST_CACHE_HIT_PERCENTAGE EST_EXTRA_READ_WRITE_M EST_OVER_ALLOC-17 97 186 31 34 97 186 31 51 100 0 2 68 100 00 82 100 00 95 100 00 109 100 00 122 100 00 136 100 00 204 100 00 272 100 00 408 100 00 544 100 00

13 rows selected.

Use v$pga_target_advice_histogram to estimate the size of PGA (similar to v$sga_target_advice and SGA):

V$PGA_TARGET_ADVICE_HISTOGRAM predicts how statistics displayed by the V$SQL_WORKAREA_HISTOGRAM dynamic view would be impacted if the value of thePGA_AGGREGATE_TARGET parameter is changed. This prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

This view can be performed in the workspace in three ways using SQL by providing statistical information for sampling and evaluating different workspace sizes:

Optimal (optimized mode): all processing can be done in memory; Onepass: most operations can be done in memory, but disk sorting is required; Multipass: a large number of operations require disk interaction, resulting in poor performance.

About Me

.

● this article is sorted out from the network

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/abstract/1/

● article blog park address: http://www.cnblogs.com/lhrbest

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● database written examination interview questions database and answers: http://blog.itpub.net/26736162/viewspace-2134706/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (646634621), indicate the reason for adding

● completed in Mordor from 09:00 on 2017-07-01 to 22:00 on 2017-07-31.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

Pick up your phone and use Wechat client to scan the picture on the left below to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, scan the QR code on the right to join the QQ group of wheat seedlings, and learn the most practical database technology.

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.