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

This article introduces the relevant knowledge of "what are the knowledge points of Oracle memory and architecture". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The memory configuration of Oracle is closely related to oracle performance. With regard to memory configuration, it is the configuration that most affects the performance of Oracle. Memory also directly affects the consumption of two other important resources: CPU and IO.

Let's first take a look at what the main content of Oracle memory storage is:

Program code (PLSQL, Java)

Information about connected sessions, including all currently active and inactive sessions

Relevant information necessary for a program to run, such as a query plan

Information shared and communicated between Oracle processes, such as locks

Data that is permanently stored on peripheral storage media and cache in memory (such as redo log entries, data blocks).

Each Oracle database is composed of Oracle Instance (instance) and database (data file, control file, redo log file). The so-called instance is the medium through which the user interacts with the database, and the user operates the database by connecting to an instance. The instance is composed of a unified memory structure (SGA,PGA,UGA) and a number of memory resident processes. Instances are identified by ORACLE_SID in the operating system and by the parameter INSTANCE_NAME in Oracle, both of which have the same value. When the database is started, the system first allocates the system global area (SGA) in the server memory to form the memory structure of Oracle, and then starts several operating system processes resident in memory, that is, the process structure of Oracle, and the memory area and background process are called an Oracle instance.

I. SGA

A SGA is a set of shared memory structures allocated to a system that can contain data or control information for a database instance. If multiple users connect to the same database instance, the data can be shared by multiple users in the SGA of the instance. SGA memory is automatically allocated when the database instance is started, and SGA memory is reclaimed when the database instance is shut down. SGA is not only the largest area of memory, but also an important factor affecting the performance of the database.

The SGA area is readable and writable. All users who log in to the instance can read the information in the SGA, and when the oracle performs the operation, the service process will write the modified information to the SGA area.

SGA mainly includes the following data structures:

Data buffering (Buffer Cache)

Redo log buffering (Redo Log Buffer)

Shared Pool (Shared Pool)

Java Pool (Java Pool)

Daichi (Large Pool)

Stream pool (Streams Pool-only available after 10g)

Data dictionary cache (Data Dictionary Cache)

Other information (such as database and instance status information)

SQL > show sga

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 192940932 bytes

Database Buffers 411041792 bytes

Redo Buffers 7135232 bytes

The data dictionary cache and other information in SGA are accessed by the background process of the instance, and they are fixed in the SGA after the instance is started, and will not change, so this part is also called fixed SGA (Fixed SGA). The size of this part of the area is generally less than 100K.

The size of Shared Pool, Java Pool, Large Pool and Streams Pool is changed by the corresponding system parameters, so it is commonly known as variable SGA (Variable SGA).

Query through the following statement

SQL > show parameter sga

NAME TYPE VALUE

Lock_sga boolean FALSE

Pre_page_sga boolean FALSE

Sga_max_size big integer 584M

Sga_target big integer 584M

Let's first explain these parameters:

SQL > select name,value, ISSYS_MODIFIABLE from v$parameter where name like 'sga%'

NAME VALUE ISSYS_MOD

Sga_max_size 612368384 FALSE

Sga_target 612368384 IMMEDIATE

If false is returned by ISSYS_MODIFIABLE, the parameter cannot be dynamically modified with the alter system statement, and the database needs to be restarted.

Therefore, sga_max_size cannot be adjusted dynamically. But we can adjust sga_target dynamically.

SGA_MAX_SIZE:

The SGA zone includes a variety of buffers and memory pools, most of which can be specified by specific parameters. However, as an expensive resource, the physical memory size of a system is limited. Although for CPU memory addressing, there is no need for the actual physical memory size, excessive use of virtual memory leads to page in/out, which will greatly affect the performance of the system, and may even lead to system crash. So you need to have a parameter to control the maximum size of virtual memory used by SGA, and this parameter is SGA_MAX_SIZE.

When the instance is started, each memory area only allocates the minimum size needed by the instance, and then expands their size as needed in the subsequent operation, and their total size is limited by SGA_MAX_SIZE.

When trying to increase the size of a memory, and if this value causes the sum of all memory sizes to be greater than SGA_MAX_SIZE, oracle prompts an error and does not allow modification.

Of course, if you set the parameter and specify the area as spfile (including modifying the SGA_MAX_SIZE itself), you will not be subject to this restriction. This makes it possible that in spfile, the sum of SGA memory area settings is greater than SGA_MAX_SIZE. At this point, oracle will handle the following: when the instance starts again, if the total memory of SGA is greater than SGA_MAX_SIZE, it will change the value of SGA_MAX_SIZE to the value of the sum of memory areas of SGA.

SGA allocates virtual memory, but when we configure SGA, we must make sure that the entire SGA area is in physical memory, otherwise, it will lead to frequent page in / out of SGA, which will greatly affect system performance.

For OLTP systems, the general recommendation is to set SGA_MAX_SIZE to 60% of physical memory and 20% of physical memory. But now the server memory is quite large. Hundreds of gigabytes of memory can be seen everywhere. 60% is a few hundred gigabytes of memory. Obviously, this is also inappropriate. So set this value according to your own system. This is what we call the experience of DBA. This is the accumulation of experience.

Several values in the following table are for reference.

System memory

SGA_MAX_ size value

1G

400-500m

2G

1G

4G

2500M

8G

5G

The actual size of the SGA can be estimated by the following formula:

SGA actual size = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE (new memory pool in 10g) + LOG_BUFFERS+11K (protection page for Redo Log Buffer) + 1MB + 16m (SGA internal memory consumption, suitable for 9i and previous versions)

PRE_PAGE_SGA:

When the oracle instance starts, only the smallest size of each memory area is loaded. Other SGA memory is allocated only as virtual memory and is replaced into physical memory only when the process touch to the corresponding page. We can set the PRE_PAGE_SGA parameter so that once the instance is started, all SGA are allocated to physical memory.

The default value for this parameter is FALSE, which means that all SGA is not placed in physical memory. When set to TRUE, instance startup places all SGA in physical memory. It allows the instance to start up to its maximum performance state, but it also takes longer to start (because the oracle process needs all the SGA pages of the touch in order for all SGA to be in physical memory).

SQL > alter system set pre_page_sga=true scope=spfile

LOCK_SGA

To ensure that the SGA is locked in physical memory without having to page in / out, it can be controlled by the parameter LOCK_SGA. The default value of this parameter is FALSE, and when TRUE is specified, all SGA can be locked in physical memory. Of course, some systems do not support memory locking, so this parameter is invalid.

SGA_TARGET

A very important parameter introduced in Oracle10g. Before 10g, the size of each memory area of SGA needs to be specified by its own parameters, and none of them can exceed the value of the specified size, although their sum may not reach the maximum limit of SGA. In addition, once allocated, the memory of each area can only be used by this area and cannot be shared with each other. Take the two most important memory areas in SGA, Buffer Cache and Shared Pool, for example, they have the greatest impact on the performance of the instance, but there is such a contradiction: in the case of limited memory resources, there is a great demand for data by cache. In order to improve buffer hit, it is necessary to increase Buffer Cache, but because SGA is limited, it can only be "grabbed" from other areas-- such as reducing Shared Pool and increasing Buffer Cache. Sometimes there are large chunks of PLSQL code parsed into memory, resulting in insufficient Shared Pool, or even a 4031 error, and the need to expand Shared Pool, which may require human intervention to take back memory from Buffer Cache.

After 10g, there is a new feature: automatic shared memory Management (Automatic Shared Memory Management ASMM). It is only this parameter SGA_TARGE that controls this feature. Once this parameter is set, there is no need to specify the size for each memory area. SGA_TARGET specifies the maximum amount of memory that SGA can use, while the size of each memory in SGA is controlled by Oracle itself and does not need to be artificially specified. Oracle can adjust the size of each area at any time to achieve the most reasonable size of the best performance of the system, and control the sum of them within the value specified by SGA_TARGET. Once a value is assigned to SGA_TARGET (the default is 0, that is, ASMM is not started), the ASMM feature is automatically started. If SGA_TARGET is not set, the automatic shared memory management feature is disabled.

Once SGA_TARGET is set, the following SGA memory areas can be automatically adjusted by ASMM:

Shared Pool (Shared Pool)

Java Pool (Java Pool)

Daichi (Large Pool)

Data cache area (Buffer Cache)

Stream pool (Streams Pool)

For the limit of SGA_TARGET, its size cannot exceed the size of SGA_MAX_SIZE.

Note that when the specified SGA_TARGET is less than SGA_MAX_SIZE, when the instance is restarted, SGA_MAX_SIZE automatically changes to the same value as SGA_TARGET.

SGA_TARGET, whose value can be modified dynamically (within the range of SGA_MAX_SIZE). Before 10g, if you need to change the size of SGA (that is, the value of SGA_MAX_SIZE), you need to restart the instance to take effect. Of course, in 10g, changing the value of SGA_MAX_SIZE still requires a restart. However, with SGA_TARGET, you can set the SGA_MAX_SIZE too high, and then adjust the value of SGA_TARGET according to the actual needs (I personally do not recommend frequently changing the size of SGA. SGA_TARGET is set when the instance is started, and do not modify it in the future).

An important benefit of SGA_TARGET is that it optimizes the utilization of SGA, thereby saving memory costs. Because after ASMM starts, Oracle will automatically adjust the size of each area as needed, which greatly reduces the shortage of memory in some areas and the contradiction that memory is idle in some areas. This also greatly reduces the chance of 4031 errors.

1.1 Database Buffer Cache

Buffer Cache is the area of the SGA zone dedicated to storing copies of blocks read from data files. If the Oracle process finds that the data block that needs to be accessed is already in the buffer cache, it directly reads and writes the corresponding area of memory without reading the data file, thus greatly improving performance (the reading efficiency of memory is 14000 times that of disk reading efficiency). Buffer cache is shared by all oracle processes, that is, it can be accessed by all oracle processes.

Like Shared Pool, buffer cache is divided into multiple sets, which can greatly reduce contention problems in multi-CPU systems.

1.1.1 Management of Buffer cache

Oracle manages buffer cache through two important linked lists: write linked lists and recently use the least linked lists (the Least Recently Used LRU). The write-linked list points to all dirty block caches (that is, blocks that have been modified by the process but have not been written back to the data file, where the data in the buffer is inconsistent with the data in the data file). The LRU linked list points to all free caches, pinned caches, and dirty caches that have not yet come and moved into the write list. There is no useful data in the free cache and can be used at any time. The cache where pin resides is the cache that is currently being accessed. The two ends of the LRU linked list are called the nearest end (the Most Recently Used MRU) and the least recently used side (LRU), respectively.

1) Block access of Buffer cache

When an Oracle process accesses a cache, the process moves the cache to MRU in the LRU linked list. As more and more buffer blocks are moved to the LRU side, those obsolete dirty buffers (that is, data changes have been written to the data file, and the data in the buffer is consistent with the data in the data file) are moved to the LRU side of the MRU linked list.

When an Oracle user process accesses a block for the first time, it first looks for the existence of a copy of the block in buffer cache. If it is found that the data block already exists in buffer cache (that is, hit cache hit), it directly reads and fetches the data block from memory. If the block is not found in the buffer cache (that is, the cache miss is missed), it needs to read the block from the data file into the buffer cache before accessing it. The ratio of hits to process reads is an important indicator of database performance: buffer hit ratio (buffer hit rate). You can obtain the buffer hit rate since the instance was launched with the following statement:

SQL > select (1-(sum (decode (name, 'physical reads',value,0)) / (sum (decode (name,' db block gets',value,0)

+ sum (decode (name,'consistent gets',value,0) * 100 "Hit Ratio" from v$sysstat

Hit Ratio

-

98.3471481

For a system with good performance, the hit rate is generally maintained at about 95%.

For the hit rate, please refer to my blog:Oracle to check the SQL of the hit rate.

Http://blog.csdn.net/xujinyang/article/details/6830457

As mentioned above, if you miss (missed), you need to read the block into the cache first. At this point, the oracle process needs to find a free cache of appropriate size from the free list. If there is no free buffer of the right size in the free list, it starts looking for the LRU linked list from the LRU side until it finds a reusable cache block or reaches the maximum number of lookup blocks. During the lookup process, if the process finds a dirty cache block, it moves the cache block to the write list and continues the search. When it finds a free block, it reads the data block from disk into the cache block and moves the cache block to the MRU side of the LRU linked list.

When a new object needs to request the allocation of buffer, the memory management module requests the allocation of idle or reusable buffer. "free buffer requested" is the number of times such requests are made.

When you request to allocate a buffer, when there is no free buffer of the right size, you need to get the reusable buffer from the LRU linked list. However, not all the buffer on the LRU linked list is immediately reusable, and there will be blocks that are being read and written or have been waiting for other users. According to the LRU algorithm, the search for reusable buffer starts from the LRU side of the linked list, and if there is such an incomprehensible reused buffer in front of this paragraph, you need to skip to find the next buffer in the linked list. "free buffer inspected" is the number of buffer skipped.

If the Oracle user process reaches the lookup block limit and does not find the free cache, it stops looking up the LRU linked list and writes the dirty cache to disk through the signaling comrade DBW0 process.

2) full table scan

When a full table scan (Full Table Scan) occurs, the user process reads the data blocks of the table and places them on the LRU side of the LRU linked list (unlike above, not on the MRU side). The purpose of this is to make the data scanned by the full table removed as soon as possible. Because full table scans generally occur less frequently, and most of the data blocks scanned by full tables will not be used frequently in the future.

If you want the data scanned by the full table to be held by cache so that it can be placed on the MRU side when scanning, you can specify the CACHE parameter when creating or modifying the table (or cluster).

3) Flush Buffer

Looking back at the process of a previous user process accessing a data block, if the accessed data block is not in buffer cache, you need to scan the LRU linked list, and when you have not found the free buffer after reaching the scan block limit, you need to tell DBW0 to write back the dirty cache to disk. If there are a lot of dirty buffers in a system, it may lead to a decline in the performance of user processes in accessing data.

We can write all dirty buffers back to disk through human intervention, which is flush buffer.

In 9i, you can use the following statement:

Alter system set events = 'immediate trace name flush_cache';-- 9i

At 10g, you can use the following ways (9i is still valid at 10g):

Alter system flush buffer_cache;-10g

In addition, 9i can set events for all of the system or for the session (that is, dirty buffer writeback caused by the session).

1.1.2 configuration of important parameters for Buffer Cache

1) size configuration of Buffer Cache

Because what is stored in Buffer Cache is a copy of a block from a data file, its size is also calculated on the basis of the block size. The size of the block is specified by the parameter db_block_size. After 9i, the size of the block defaults to 8K, and its value is generally set to the same size as the operating system's block or its multiple.

The parameter db_block_buffers specifies the number of cache blocks in Buffer Cache. Therefore, the size of buffer cache is equal to db_block_buffers * db_block_size.

After 9i, Oracle introduced a new parameter: db_cache_size. This parameter can directly specify the size of the Buffer Cache without having to calculate it in the above way. Its default value is 48m, which is generally not enough for a system.

Automatic memory management is provided in 10g to ensure the most efficient memory use by automatically allocating memory among multiple components using sga_target. For example, shared pool javapool largepool buffer cache does not need to explicitly set the size of these components, the default is 0, when a component needs memory, you can request memory transfer through the internal automatic adjustment mechanism.

Note: db_cache_size and db_block_buffers cannot be set at the same time, otherwise an error will be reported when the instance is started.

SQL > alter system set db_block_buffers=16384 scope=spfile

System altered.

SQL > alter system set db_cache_size=20M scope=memory

System altered.

SQL > startup force

ORA-00381: cannot use both new and old parameters for buffer cache size specification

In 9i, it is recommended to use db_cache_size to specify the size of buffer cache.

In OLTP systems, the recommended configuration for DB_CACHE_SIZE settings is:

DB_CACHE_SIZE = SGA_MAX_SIZE/2~ SGA_MAX_SIZE*2/3

Finally, the DB_CACHE_SIZE can be modified online, that is, the instance does not need to be restarted unless increasing the Buffer Cache causes the actual size of the SGA to be larger than the SGA_MAX_SIZE.

2) configuration of Buffer Cache in multiple block size systems

Starting with 9i, Oracle supports the creation of tablespaces of different block sizes, and you can specify different sizes of buffer cache for data blocks of different block sizes.

After 9i, all other tablespaces can specify up to four different block sizes except that SYSTEM and TEMPORARY tablespaces must use standard block sizes. The standard block size is still specified by the parameter db_block_size mentioned above. Db_cache_size, on the other hand, is the size of a block-sized buffer cache.

The block size of a non-standard block size can be specified by the BLOCKSIZE parameter when creating a table space (CREATE TABLESPACE). The size of the buffer cache with different block sizes is specified by the corresponding parameter DB_nK_CACHE_SZIE, where n can be 2, 4, 8, 16 or 32. For example, if you create a non-standard block size table space with a block size of 16K, you can specify the size of the buffer cache that caches the tablespace block by setting DB_16K_CACHE_SIZE to.

No Buffer Cache of any size can cache data blocks of other sizes. Therefore, if you plan to use multiple block sizes for your database storage, you must set at least one parameter in DB_CACHE_SIZE and DB_nK_CACHE_SIZE (after 10g, specify SGA_TARGET so that you do not need to specify the size of Buffer Cache). Also, you need to specify the corresponding Buffer Cache size for the non-standard block size blocks you want to use. These parameters allow you to specify up to 4 different block sizes of Buffer Cache for the system.

Also, note that the DB_nK_CACHE_SIZE parameter cannot set the buffer size of the standard block size. For example, if DB_BLOCK_SIZE is set to 4K, you can no longer set the DB_4K_CACHE_SIZE parameter.

3) multiple buffer pools

Different buffer cache can be configured to achieve the purpose of different cache data. For example, you can set part of the buffer cache cached data to be released immediately after use, so that the later data can be used in the buffer pool immediately; you can also set the data to be resided by keep and no longer released after entering the buffer pool. Some database objects (tables, clusters, indexes, and partitions) can control their data cache behavior, and these different cache behaviors use different buffer pools.

The retention buffer pool (Keep Buffer Pool) is used to cache blocks of data that are permanently resident in memory. Its size is controlled by the parameter DB_KEEP_CACHE_SZIE.

The reclaim buffer pool (Recycle Buffer Pool) immediately clears those data cache blocks that are not in use. Its size is specified by the parameter DB_RECYLE_CACHE_SIZE

The default standard cache pool, which is specified by DB_CACHE_SIZE above.

These three parameters are independent of each other. And they are only suitable for data blocks of standard block size. Corresponding to the 8i compatibility parameter DB_BLOCK_BUFFERS, DB_KEEP_CACHE_SIZE should have BUFFER_POOL_KEEP and DB_RECYLE_CACHE_SIZE should have BUFFER_POOL_RECYCLE. Again, these parameters are mutually exclusive, that is, only one can be set between DB_KEEP_CACHE_SIZE and BUFFER_POOL_KEEP.

4) buffer pool advisor

Starting with 9i, Oracle provides some automatic optimization tools to adjust the system configuration and improve system performance. The advisor is one of them. The role of the advisor is to monitor the relevant statistical data, give the performance effect of the relevant configuration in different situations, and provide it to the DBA to make decisions in order to select the best configuration.

In 9i, Buffer Cache has a corresponding advisor. The parameter db_cache_advice is used for the advisor's switch, and the default value is FALSE (that is, off). When it is set to TRUE, after the system has been running for a period of time, you can query the view v$db_cache_advice to determine how to make it DB_CACHE_SIZE. We will describe this advisor and view in the following content.

5) other related parameters

DB_BLOCK_LRU_LATCHES

As a memory object, access to the LRU linked list needs to be latch-controlled to prevent multiple user processes from using an idle cache block at the same time. DB_BLOCK_LRU_LATCHES sets the number range of LUR latch. Oracle uses a series of internal tests to decide whether to use this parameter value. If this parameter is not set, Oracle automatically calculates a value for it. Generally speaking, the value calculated by oracle is reasonable and does not need to be modified.

After 9i, this parameter is implied. For implicit parameters, I recommend that you do not modify them without Oracle support, otherwise, Oracle can refuse to support you if you change them.

DB_WRITER_PROCESSES

In the previous analysis of Oracle reading Buffer Cache, I mentioned an important Oracle background process, DBW0, which (or these) is responsible for writing dirty cache blocks back to the data file, called the database writer process (Database Writer Process). The DB_WRITER_PROCESSES parameter configures the number of processes to be written. Each process is distinguished by DBWn, where n > = 0 is the process sequence number. In general, DB_WRITER_PROCESSES = MAX (1, TRUNC (number of CPU / 8)). That is, when the number of CPU is less than 8, the DB_WRITER_PROCESSES is 1, that is, there is only one write process DBW0. This is also sufficient for general systems. You can adjust this parameter when your system has a heavy task of modifying data and has affected performance. This parameter should not exceed the number of CPU, otherwise the extra processes will not work, and its maximum value cannot exceed 20.

In addition to the above mentioned, the DBWn process is triggered when the user process reads the buffer cache, and it can also be triggered by the Checkpoint (Checkpoint is the starting point for the instance to recover from the redo log).

1.2 Share Pool

The shared pool in SGA consists of a library cache (Library Cache), a dictionary cache (Dictionary Cache), a buffer for parallel execution of messages, and a control structure.

The size of the Shared Pool is determined by the parameter SHARED_POOL_SIZE. In 9i, the default value of this parameter is 8m on a 32-bit system and 64m on a 64-bit system. The maximum is 4G. After 10g, it can be adjusted automatically by SGA_TARGET parameter.

For the memory management of Shared Pool, it is realized through the modified LRU algorithm table.

1.2.1 Library caching (Library Cache)

Library Cache includes shared SQL zones (Shared SQL Areas), PL/SQL stored procedures, and control structures such as locks, repository cache handles.

Any user can access the shared SQL area (which can be accessed through v$sqlarea, and this important view is described later). Therefore, the library cache exists in the shared pool of SGA.

1) share SQL zone and private SQL zone

Oracle provides a shared SQL area (Shared SQL Areas) and a private SQL area (Private SQL Areas belongs to PGA) for each SQL statement run (each statement Oracle opens a cursor). When two (or more) users are found to be running the same SQL statement, Oracle reorganizes the SQL zone so that those users can reuse the shared SQL zone. But they also keep a copy of the SQL statement in the private SQL area.

A parse tree and query plan for a statement are saved in a shared SQL area. In a multi-user system, Oracle saves memory by running multiple times using the same shared SQL zone for SQL statements.

When a new SQL statement is parsed, Oracle allocates a piece of memory from the shared pool to store the shared SQL area. The size of this memory is related to the complexity of the statement. If the Shared Pool does not have enough space to allocate to the shared SQL area, Oracle releases the least recently used block of memory found in the LRU linked list until there is enough space for the new statement's shared SQL area. If Oracle frees memory for a shared SQL zone, the corresponding statement needs to be parsed and reallocated again the next time it is executed. From parsing statements to allocating shared SQL regions is a relatively CPU-consuming project. This is why we advocate the use of bound variables. When the value of the variable in the statement is different when the bound variable is not used, oracle is regarded as a new statement (9i can be controlled through cursor_sharing). Repeating the above actions of parsing and memory allocation will greatly consume system resources and reduce system performance.

2) PL/SQL program unit

Oracle's handling of PL/SQL program units (stored procedures, functions, packages, anonymous PL/SQL blocks, and triggers) is similar to that of individual SQL statements. It allocates a shared area to store parsed and compiled program units. At the same time, a private area is allocated to store the parameter values of the program unit specified by the session running the program unit (including local variables, global variables, and package variables-- also known as package instantiation) and the memory needed to execute the program. If multiple users run the same program unit, they share the same shared area and each maintains a private area for the value of the variable specified in the user's session.

The processing of each single SQL statement in a PL/SQL program unit is the same as that of the SQL statement described above. Note that although these statements come from the PL/SQL program unit, Oracle allocates a shared SQL zone for these statements and a corresponding private SQL zone for each user.

1.2.2 Dictionary Cache (Dictionary Cache)

The data dictionary is a set of tables and views with reference information about the database, the structure information of the database and the user information in the database. For example, the V$ view and DBA_ view that we often use belong to the data dictionary. During the parsing of SQL statements, Oracle can access these data dictionaries very quickly (if necessary), and in SQL Trace, this access to data dictionaries is counted as recursive calls.

Because Oracle accesses data dictionaries so frequently, there are two places in memory dedicated to data dictionaries. One place is the data dictionary cache (Data Dictionary Cache). Data dictionary caching is also called Row Cache because it stores data in recording behavior units, unlike Buffer Cache, which stores data in blocks. Another place in memory where data dictionaries are stored is the library cache. All Oracle users can visit these two places to get data dictionary information.

1.2.3 memory management of shared pools

Generally speaking, the shared pool is based on the modified LRU algorithm to determine whether the objects (shared SQL area and data automatic recording rows), otherwise these objects will remain in the shared pool all the time. If the shared pool needs to allocate memory for a new object, and there is not enough memory in the shared pool, those infrequently used objects in memory are released. For a shared pool object that has been used by many sessions, the modified LRU algorithm remains in the shared pool as long as it is useful, even if the process that originally created it is over. This minimizes the processing and memory consumption of SQL statements in a multi-user Oracle system.

Note that even if a shared SQL zone is associated with an open cursor, it may still be released from the shared pool if it has not been used for a long time. At this point, if the open cursor still needs to run its related statements, Oracle reparses the statements and allocates a new shared SQL area.

When a SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:

1. Oracle checks the shared pool to see if there is already a shared SQL area for this statement. If it exists, the shared SQL area is used to execute the statement. If it does not exist, Oracle allocates a new shared SQL zone from the shared pool to the statement. At the same time, regardless of whether the shared SQL zone exists or not, Oracle allocates a private SQL zone to the user to store information about the statement (such as the value of a variable).

2. Oracle assigns a private SQL zone to the session. The connection mode of the session where the private SQL zone is located is related.

Oracle also frees the shared SQL zone from the shared pool in the following cases:

1) when the statistics of a table, cluster, or index are updated or deleted using the ANALYZE statement, all shared SQL areas associated with the analyzed object are released from the shared pool. When the next released statement is executed, it is re-parsed in a new shared SQL area based on the updated statistics.

2) when the structure of the object is modified, all common SQL areas associated with the object are identified as invalid (invalid). Reparse the statement the next time you run it.

3) if the global database name (Global Database Name) of the database is modified, all information in the shared pool will be emptied.

4) DBA clears the shared pool manually: ALTER SYSTEM FLUSH SHARED_POOL

Shared Pool can be divided into several regions, which are protected by different latch (the maximum number of latch is 7, which can be set by the implicit parameter _ kghdsidx_count).

The table x$kghlu can view the list of LRU in shared pool. Shared pool is divided into multiple zones with different LRU linked list management when one of the following conditions is met:

1) before 10g, if the shared pool is greater than 128m and the number of CPU is greater than 4

2) Oracle database version is 10g

At this point, there will be different records in the x$kghlu.

1.2.4 Reserve shared pool

As mentioned earlier, if Oracle parses a PL/SQL program unit, it also needs to allocate memory to those program unit objects from the shared pool. Because these objects are generally large (such as packages), the memory space allocated is also relatively large. After the system has been running for a long time, there may be a large number of memory fragments in the shared pool, making it impossible to meet the allocation of large memory segments.

In order to have enough space to cache large chunks, Oracle specially built an area from the shared pool to allocate memory to maintain these chunks. The default size of this reserved shared pool is 5% of the shared pool. Its size can also be adjusted by the parameter SHARED_POOL_RESERVED_SIZE. The reservation is allocated from the shared pool, not directly from the SGA. It is the reserved part of the shared pool and is used to store large segments.

Large segments of Shared Pool with more than 5000 bytes of memory are stored in the reserved portion of the shared pool. This size limit is set by the implicit parameter _ SHARED_POOL_RESERVED_MIN_ALLOC (as mentioned earlier, the implicit parameter does not modify it). Except during instance startup, all memory segments less than this number will never be placed in the reserved section, and large memory segments greater than this value will never be stored in the unreserved area, even if there is not enough space in the shared pool.

The free memory of the reserved area is also not included in the free list of normal shared pools. It maintains a separate free list. The reserved pool also does not store reconstructable segments in its LRU list (the various states of Recreatable about memory segments will be covered later). These large segments are not cleared when the memory on the free list of the normal shared pool is freed, nor is the memory in the normal shared pool when the large memory segment on the free list of the reserved pool is freed.

The statistics of the reserved pool can be found through the view V$SHARED_POOL_RESERVED. The field REQUEST_MISSES records the number of requests for large memory segments that were not immediately available from the free list. This value should be 0. Because the reservation must have enough free memory to accommodate those short-term memory requests, there is no need to clear the reconstructable segments that need to be cacheed for a long time and not occupied by pin. Otherwise, you need to consider increasing the SHARED_POOL_RESERVED_SIZE.

You can determine whether the size of the reserved pool is appropriate by looking at the MAX_USED_SPACE field of the view V$SHARED_POOL_RESERVED. In most cases, you will observe that the reserve pool is rarely used, which means that 5% of the reserve pool space may be wasteful. However, this requires long-term observation to determine whether the retention pool needs to be resized.

Reservations use shared pool's LRU linked list to manage memory blocks, but are not affected by each other when scanning. For example, the memory manager scans shared pool's LRU linked list to clear space for a memory request that is less than 5000 bytes, and vice versa.

1.2.5 keep important, commonly used objects (Keep) in a shared pool

According to the LRU algorithm, some blocks of memory that have not been used for a while will be released by the situation. This may cause some important objects (such as a package containing a large number of general algorithm functions, a sequence of cache) to be purged from memory. These objects may only be used intermittently, but because their processing is complex (not only the package itself reallocates memory, parses, but also checks all the statements in it), it is very expensive to rebuild them in memory.

We can reduce this risk by calling the stored procedure DBMS_SHARED_POOL.KEEP to keep these objects in the shared pool. This stored procedure immediately loads the object and its engaged objects into the library cache and marks them all as Keeping. For such objects, we recommend that you Keep when the instance is started to reduce the chance of memory fragmentation.

There is a view that those large objects (such as packages) do not need to be occupied by Keep because they are kept on the reservation of the shared pool (as mentioned earlier, this area is usually underutilized), so it is generally impossible to clear out. This view is wrong! Because most large objects are actually divided into small memory segments and loaded into shared pools, they are not specially protected at all because of the size of the object.

In addition, do not call certain objects frequently to prevent them from being purged from the shared pool. If the shared pool size is set properly, the LRU linked list will be relatively short during peak periods of system operation, and objects that are not occupied by pin will be quickly cleared out unless they are occupied by keep.

1.2.6 important parameters about Shared Pool

1) SHARED_POOL_SIZE

It specifies the size of the Shared Pool. Under 9i, the default value of this parameter is 8m in a 32-bit system and 64m in a 64-bit system.

However, there is also a piece of memory in SGA called Internal SGA consumption (Internal SGA Overhead) that is placed in the shared pool. In 9i and previous versions, the statistical size of the shared pool (counted through the v$sgastat view) was the SHARED_POOL_SIZE + internal SGA consumption. After 10g, SHARED_POOL_SIZE already contains this part of the memory size. Therefore, in 10g, the actual usage size of the shared pool is the SHARED_POOL_SIZE-internal SGA consumption, which needs to be taken into account when configuring the shared pool size, otherwise, if the SHARED_POOL_SIZE setting is too small, an ORA-00371 error will be reported when the instance is started.

2) SHARED_POOL_RESERVED_SIZE

As mentioned earlier, this parameter specifies the size of the reserve for caching large memory objects in the shared pool. I won't repeat it here.

3) _ SHARED_POOL_RESERVED_MIN_ALLOC

As described earlier, this parameter sets the threshold for the size of the object entering the reservation.

1.3 redo log cache (Redo Log Buffer)

Redo Log Buffer is a cache in SGA that holds database modification information. This information is stored in a redo entry (Redo Entry). The redo entry contains the necessary information that needs to be reorganized or redone to the database due to modifications made by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP. Redo entries can also be used for database recovery if necessary.

The redo entry is copied by the Oracle database process from the user's memory to Redo Log Buffer. Redo entries are connected continuously in memory. The background process LGWR is responsible for writing the information in Redo Log Buffer to the active redo log file (Redo Log File) or filegroup on disk.

The parameter LOG_BUFFER determines the size of the Redo Log Buffer. Its default value is 512K (usually this size is sufficient), and the maximum can be 4G. 10g can be automatically set by parameters. When there are a lot of large transactions or a very large number of transactions in the system, it may lead to an increase in log file IO and a decline in performance. At this point, consider adding LOG_BUFFER.

However, the actual size of the Redo Log Buffer is not the set size of the LOB_BUFFER. To protect Redo Log Buffer,oracle, a protection page (usually 11K) is added to it:

SQL > select * from v$sgastat where name = 'log_buffer'

POOL NAME BYTES

Log_buffer 7135232

SQL > show parameter log_buffer

NAME TYPE VALUE

Log_buffer integer 7024640

SQL >

1.4 large Pool (large pool)

A large pool is an optional memory pool in SGA and is configured as needed. Large pools need to be configured in the following situations:

1) session memory for shared services (in Shared Server MTS mode) and Oracle XA interface for Oracle distributed transaction processing

2) when using parallel queries (Parallel Query Option PQO)

3) IO service process

4) Oracle backup and restore operations (when RMAN is enabled)

By allocating session memory from large pools to shared services, Oracle XA, or parallel queries, oracle can use shared pools primarily to cache shared SQL to prevent performance consumption due to shared SQL cache shrinkage. In addition, the memory allocated for Oracle backup and restore operations, IO service processes, and parallel queries is typically a few hundred kilograms, and such a large memory segment is easier to allocate from a large pool than from a shared pool.

The parameter LARGE_POOL_SIZE sets the size of the large pool. Large pools belong to the variable zone (Variable Area) of SGA, and it does not belong to shared pools. Access to large pools is protected by large memory latch. There are only two types of memory segments in a large pool: free and freeable. It does not have a recreatable memory segment, so it is not managed by a LRU linked list (unlike other memory areas). The maximum size of the large pool is 4G.

To prevent fragmentation in a large pool, the implicit parameter _ LARGE_POOL_MIN_ALLOC sets the minimum size of the memory segment in the large pool, and the default value is 16K (again, it is not recommended to modify the implicit parameter).

In addition, large pool does not have an LRU linked list.

1. 5 Java pool (Java Pool)

The Java pool is also an optional memory area in SGA, and it also belongs to the variable area in SGA.

The memory of the Java pool is used to store specific Java code in all sessions and data in JVM. The way the Java pool is used depends on the mode of operation of the Oracle service.

The size of the Java pool is set by the parameter JAVA_POOL_SIZE. The maximum Java Pool can be up to 1G.

After Oracle 10g, a new advisor, the Java pool advisor, is provided to assist DBA in resizing the Java pool. The statistics of the advisor can be queried through the view V$JAVA_POOL_ADVICE. How to tune the Java pool with the advisor is similar to using the Buffer Cache advisor, you can refer to the section on the advisor in Buffer Cache.

1.6 Stream Pool (Streams Pool)

Stream pools are new in Oracle 10g. Is to increase the support of convection.

The stream pool is also an optional memory area and belongs to the variable area in SGA. Its size can be specified by the parameter STREAMS_POOL_SIZE. If not specified, the oracle is automatically created the first time the stream is used. If the SGA_TARGET parameter is set, Oracle allocates memory from SGA to the stream pool; if no SGA_TARGET is specified, a portion of memory is converted from buffer cache to the stream pool. The converted size is 10% of the shared pool size.

Oracle also provides a stream pool advisor-- the stream pool advisor. The statistics of the advisor can be queried through the view V$STREAMS_POOL_ADVICE. For usage, see the optimizer section of Buffer Cache.

II. PGA

PGA (Program Global Area program global area) is a memory area that contains the data and control information of a service process. It is created by Oracle at the start of a service process and is not shared. An Oracle process has a PGA memory area. A PGA can only be accessed by the service process that owns it, and only the Oracle code in that process can read and write it. Therefore, the structure in PGA does not require Latch protection.

We can set the total PGA memory for all service processes to be limited by the overall PGA (Aggregated PGA) allocated by the instance.

In proprietary server (Dedicated Server) mode, Oracle starts an Oracle process for each session, while in multithreaded service (Multi-Thread Server MTS) mode, multiple sessions share an Oracle service process.

PGA contains information about the operating system resources used by the process, as well as some information about the state of the process. The information about the Oracle shared resources used by the process is in SGA. This makes it possible to release and clear these resources in a timely manner when they are aborted outside the process.

Stack Space is the storage area used to store user session variables and arrays

User Session Data is an additional store for user sessions.

|-- Session Information

|-- Sort Area

|-- Cursor Information

Note that Session information (user session information) is different in the area of memory in the exclusive server than in the shared server.

2.1 composition of PGA

PGA consists of two sets of areas: fixed PGA and variable PGA (or PGA heap, PGA Heap [heap-Heap is a managed memory area]). Fixed PGA is similar to fixed SGA in that it is fixed in size and contains a large number of atomic variables, small data structures, and pointers to variable PGA.

A variable PGA is a memory heap. Its memory segment can be found through the view X$KSMPP (the other view X$KSMSP can find the memory segment information of the variable SGA, which has the same structure). The PGA heap contains memory for holding the X$ table (dependencies and parameter settings, including DB_FILES, CONTROL_FILES).

Generally speaking, the variable region of PGA is mainly divided into the following three parts:

1) Private SQL zone

2) Vernier and SQL area

3) session memory

2.1.1 Private SQL Zone (Private SQL Area)

The private SQL area contains data such as the value of the bound variable and runtime memory structure information. Each session that runs the SQL statement has a block private SQL zone. All users who submit the same SQL statement have their own private SQL zone, and they share a shared SQL zone. Therefore, a shared SQL zone may be associated with multiple private shared areas.

The private SQL area of a cursor is divided into two zones with different lifecycles:

Permanent zone: contains binding variable information. Is released when the cursor is closed.

Runtime: released at the end of execution.

Creating a runtime is the first step in executing a request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime at the end of the statement run. For query operations, Oracle releases the runtime only when all records are fetch or when the query is canceled.

2.1.2 Vernier and SQL area (Cursors and SQL Areas)

An application developer of an Oracle precompiler or OCI program can explicitly open a cursor or control a specific private SQL area as a named resource for the program to run. In addition, oracle's implicit recursive calls to some SQL statements (as described earlier, reading data dictionary information) also use the shared SQL area.

The private SQL zone is managed by the user process. How to allocate and release private SQL zones depends heavily on the utilities you use. The number of private SQL zones that the user process can allocate is controlled by the parameter OPEN_CURSORS, which defaults to 50.

The private SQL zone will exist until the cursor is closed or the statement handle is released (but the runtime zone is released at the end of statement execution, and only the permanent zone exists). Application developers can reduce the memory consumed by user programs by closing all open cursors that are no longer in use.

2.1.3 session memory (Session Memory)

Session memory is a piece of memory used to hold session variables (such as login information) and other pre-session related information. For shared server mode, session memory is shared, not private.

For complex queries (such as queries in decision support systems), a large portion of the runtime is allocated to the SQL workspace (SQL Work Area) by memory-intensive operations. These actions include:

Sort-based operations (ORDER BY, GROUP BY, ROLLUP, window functions)

Hash Join

Bitmap merge

Bitmap create

For example, a sort operation uses a workspace (also known as a sort area Sort Area) to sort some of the data rows in memory, while a Hash Join operation uses a workspace (also known as the Hash area Hash Area at this time) to create a Hash table. If the amount of data processed by these two operations is larger than that of the workspace, the input data is divided into smaller slices so that some slices can be processed in memory, while others are processed later on the disk of the temporary tablespace. Although the workspace is too small for Bitmap operations to put data on disk for processing, their complexity is inversely proportional to the size of the workspace. So, in general, the larger the workspace, the faster these operations will run.

The size of the workspace is adjustable. In general, large workspaces can improve the performance of certain operations, but also consume more memory. The size of the workspace is large enough to accommodate the input data and the auxiliary memory required for the associated SQL operations is optimal. If not, the response time of the operation will increase because part of the data needs to be processed on the temporary tablespace disk.

2.2 PGA memory automatic management

SQL workspaces can be managed automatically and globally. DBA simply sets the parameter PGA_AGGREGATE_TARGET to specify the total size of the PGA memory of an instance. When this parameter is set, Oracle uses it as an overall global limit value so that the total PGA memory of all Oracle service processes does not exceed this value as much as possible.

Before this parameter appears, DBA adjusts the parameters SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE (we'll talk about them later) to optimize performance and PGA memory consumption. The adjustment of these parameters is very troublesome, because it is necessary to consider all the relevant operations to make the workspace suitable for their input data size, and to ensure that the PGA memory consumption does not lead to a decline in the overall performance of the system.

After 9i, the size of the workspace for all sessions is automatically allocated by setting the parameter PGA_AGGREGATE_TARGET. At the same time, all * _ AREA_SIZE parameters are invalidated. At any time, the total amount of PGA memory available to the workspace in the instance is based on the parameter PGA_AGGREGATE_TARGET. The total workspace memory is equal to the value of the parameter PGA_AGGREGATE_TARGET minus the memory consumption of other components of the system, such as the PGA memory allocated to the session. The amount of PGA memory allocated to Oracle processes is based on their memory requirements.

The parameter WORKAREA_SIZE_POLICY determines whether to use PGA_AGGREGATE_TARGET to manage PGA memory. It has two values: AUTO and MANUAL. The default is AUTO, even though PGA_AGGREGATE_TARGET is used to manage PGA memory. In fact, as can be seen from the name of the parameter WORKAREA_SIZE_POLICY, Oracle's PGA memory automatic management will only adjust the workspace part, but the non-workspace part (fixed PGA area) will not be affected.

Another thing to note is that prior to 10g, PGA_AGGREGATE_TARGET only took effect in dedicated service mode. After 10g, PGA memory automatic management works in both proprietary service mode (Dedicated Server) and MTS. In addition, 9i does not support automatic PGA memory management on OpenVMS systems, but 10g does.

After PGA_AGGREGATE_TARGET is set, the size of each process's PGA memory is also limited:

During serial operations, the available PGA memory for each process is MIN (PGA_AGGREGATE_TARGET * 5%, _ pga_max_size/2), and the default value of the implicit parameter _ pga_max_size is 200m. It is also not recommended to modify it.

When operating in parallel, the PGA memory available to parallel statements is PGA_AGGREGATE_TARGET * 30% / DOP (Degree Of Parallelism parallelism).

2.3 proprietary Services (Dedicated Server) and shared Services (Shared Server)

The management and allocation of PGA memory depends largely on the service mode. The following table shows the similarities and differences in the allocation of different parts of PGA memory in different modes:

Memory area

Proprietary service

Shared service

Session memory

Private

Shared

The area where the permanent zone is located

PGA

SGA

The area where the runtime of the SELECT statement is located

PGA

PGA

The area where the runtime of the DML/DDL statement is located

PGA

PGA

III. UGA (The User Global Area)

A PGA is a piece of memory that contains data and control information for an Oracle service or background process. The size of PGA depends on the configuration of the system. In dedicated service (Dedicated Server) mode, a service process is associated with a user process, and PGA includes heap space and UGA. UGA (User Global Area user global area) consists of user session data, cursor state and index area. In MTS mode, a shared service process is shared by multiple user processes, and UGA is part of Shared Pool or Large Pool (dependency and configuration).

Many DBA don't understand the difference between PGA and UGA. In fact, this difference can be simply understood as a direct difference between process and conversation. In dedicated service mode, processes and sessions are one-to-one; in MTS mode, processes and sessions are one-to-many. PGA serves the process and contains the information of the process, while UGA serves the session and contains the information of the session. Therefore, in MTS mode, the relationship between PGA and UGA is also one-to-many.

The UGA contains information about a session, including:

1) Open the permanent area and run area of the cursor

2) the status information of the package, especially the variables of the package

3) Information of Java session

4) activated roles

5) active trace events (ALTER SESSION SET EVENT … )

6) NLS parameters that work (SELECT * FROM NLS_SESSION_PARAMETERS;)

7) all open db link

8) managed access token of the session to the trusted Oracle (mandatory access control (MAC)

Like PGA, UGA consists of two groups of zones, fixed UGA and variable UGA (or UGA heap). The fixed UGA contains about 70 atomic variables, small data structures, and pointers to the UGA heap.

The segments in UGA heap can be found in the table X$KSMUP (its structure is the same as X$KSMSP). The UGA heap contains permanent memory that stores some fixed tables (the X$ table) (depending on the settings of specific parameters, such as OPEN_CURSORS,OPEN_LINKS and MAX_ENABLED_ROLES). In addition, most of the UGA is used for private SQL zones. The location of UGA memory depends on the setting of the session. In dedicated service mode, the session and process have an one-to-one relationship, and the UGA is located in the PGA. A fixed UGA is a segment of memory in PGA, while the UGA heap is a subheap of PGA. In MTS mode, the fixed UGA is a memory segment in shared pool, while the UGA heap is a subheap of Large Pool, and if allocation fails from large pool, it is allocated from shared pool.

In MTS mode, the total size of SGA occupied by each UGA can be controlled through the PRIVATE_SGA entry in Profile (viewed through dba_profiles), but this is not recommended.

After Oracle 9.2, there is a new implicit parameter: _ use_realfree_heap. When this parameter is set to true, Oracle allocates the heap separately for CGA and UGA, not from PGA. Its default value is false, and when pga_aggregate_target is set, its value is automatically changed to true.

IV. CGA (The Call Global Area)

Unlike other global areas, the existence of CGA (Call Global Area calling global area) is instantaneous. It exists only in one call. Some low-level calls to the instance require CGA, including:

1) parse a SQL statement

2) execute a SQL statement

3) take the output value of a SELECT statement.

If the statement generates a recursive call, you need to allocate a CGA for each recursive call. As mentioned above, recursive calls are calls that need to query or modify data dictionary information during statement parsing, optimizer generating statement query plans, and DML operations.

Whether UGA exists in PGA or SGA,CGA is the subheap of PGA. Because no matter which mode, the session always needs to be processed when making a call. This is important, especially in MTS mode, where you may need to increase the size of the PGA if you find that a call has not responded for a long time.

Of course, calls do not work only through data structures in CGA. In fact, most of the important data structures required for the call come from UGA. For example, private SQL fetch and sort areas are stored in UGA because they are reserved after the call ends. The CGA contains only those data that can be released after the call is completed. For example, CGA contains a direct IO cache, information about recursive calls, heap space for expression evaluation (when generating a query plan), and other temporary data.

Java call memory is also allocated in CGA. It is divided into three parts of space: heap space, new space and old space. During the call (the length of the call depends on the length and size of the usage), memory segments that are no longer used in the new space and the old space are reclaimed by the garbage collector.

V. Software code area (Software Code Area)

The software code area is part of the memory area that stores code that is running and can be run (Oracle's own code). Oracle code is generally stored in a different software code area from the user program store, which is an exclusive, protected area.

The size of the software area is generally fixed and will not change until the Oracle software is upgraded or reinstalled. The required size of this area varies from operating system to operating system.

The software area is read-only and can be installed as shared or non-shared. Where possible, the Oracle code is shared so that all Oracle users can access the code directly without having to keep a copy in their own memory. This saves a lot of memory and improves overall performance.

User programs can also be shared or non-shared. Some Oracle tools (such as SQL Plus) can be installed as shared, but some cannot. If a machine is running multiple instances, the instances can use the same Oracle code area.

It is also important to note that not all operating systems can install software areas into shared ones, such as Windows.

This is the end of the content of "what are the knowledge points of Oracle memory and architecture". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.