Summarize and organize memory allocation and tuning of oracle learning

This article brings you relevant knowledge about Oracle, which mainly introduces issues related to memory allocation and tuning. Oracle's memory can be divided into system globals from the perspective of shared and private The area and process global area, that is, SGA and PGA, let's take a look at them together. I hope it will be helpful to everyone.

## Recommended tutorial: "

Oracle Learning Tutorial"

1. Overview

Oracle's memory can be divided into system global area and process global area from the perspective of shared and private, that is, SGA and PGA (process global area or private global area). For the memory in the SGA area, it is shared globally. On UNIX, a shared memory segment (can be one or more) must be set for Oracle, because Oracle is a multi-process on UNIX; while on WINDOWS, Oracle is Single process (multiple threads), so there is no need to set up shared memory segments. PGA is a process (thread) private area. When Oracle uses shared server mode (MTS), part of the PGA, that is, UGA, will be placed in the shared memory large_pool_size.

Post a picture of the Oracle memory architecture. According to the display above, you can see the key parameters and parameter names at a glance:

For the SGA part, we can see through query in sqlplus:

SQL> select * from v$sga; 
  
NAME                VALUE 
  
----------             --------------------
  
Fixed Size                   454032 
  
Variable Size             109051904 
  
Database Buffers             385875968 
  
Redo Buffers                667648

Fixed Size:

Oracle may be different on different platforms and versions, but it is a fixed value for a certain environment. It stores information about each component of SGA and can be regarded as an area to guide the establishment of SGA.

Variable Size :

Contains shared_pool_size, java_pool_size, large_pool_size and other memory settings

##Database Buffers:

refers to the data buffer:

In 8i, it includes three parts of memory: db_block_buffer*db_block_size, buffer_pool_keep, and buffer_pool_recycle.         

In 9i, it includes db_cache_size, db_keep_cache_size, db_recycle_cache_size, db_nk_cache_size.

Redo Buffers :

refers to the log buffer, log_buffer. An additional point to note here is that the query values ​​​​for v$parameter, v$sgastat, and v$sga may be different. The value in v$parameter refers to the user’s initial

The value set in the initialization parameter file, v$sgastat is the log buffer size actually allocated by Oracle (because the allocation value of the buffer is actually discrete, and it is not based on block as the minimum unit. allocated),

v$sga The value queried in is that after Oracle allocates the log buffer, in order to protect the log buffer, some protection pages are set up. Usually we will find that the size of the protection page is about 11k( May vary in different environments).

2. SGA internal parameters and settings:

#2.1 Log_buffer

As for the size setting of the log buffer, I usually don’t think there are too many suggestions, because after referring to the trigger conditions written by LGWR, we will find that usually exceeding 3M is not very meaningful. As a formal system,

You may consider setting this part to log_buffer=3-5M size first, and then adjust it according to the specific situation.

log_buffer is the buffer of Redo log.

So here you must understand the trigger event (LGWR) of Redo Log

1. When the capacity of redo log buffer reaches 1/3

2. The set redo log writing time interval is reached, which is usually 3 seconds.

3. The redo log capacity in the redo log buffer reaches 1M

4. Write the data in the buffer to the data in DBWn Before the file

5. Every commit--submit the transaction.

The above conclusion can be expressed in other words

1. The content in log_buffer is 1/3 full, and the cache is refreshed once.

2. The maximum interval is 3 seconds, and the cache is refreshed once

3. When the data in log_buffer reaches 1M, the cache is refreshed once.

4. Each time a "transaction" is submitted, the cache is refreshed

2.2 Large_pool_size

For the setting of a large buffer pool, if MTS is not used, it is recommended that 20-30M is enough. This part is mainly used to save some information during parallel queries, and may be used by RMAN during backup.

If MTS is set up, since the UGA part will be moved here, the size of this part needs to be comprehensively considered based on the number of server processes and the settings of related session memory parameters.

2.3 Java_pool_size

If the database does not use JAVA, we usually think that retaining 10-20M is enough. In fact, it can be less, even at least 32k, but it depends on the components when installing the database (such as http server).

2.4 Shared_pool_size

The overhead of Shared_pool_size should usually be maintained within 300M. Unless the system uses a large number of stored procedures, functions, and packages,

For example, applications such as oracle erp may reach 500M or even higher. So we assume a system with 1G memory, we may consider

Set this parameter to 100M. For 2G systems, consider setting it to 150M. For 8G systems, consider setting it to 200-300M

2.5SGA_MAX_SIZE

The SGA area includes various buffers and memory pools, most of which can specify their sizes through specific parameters. However, as an expensive resource, a system's physical memory size is limited.

Although for CPU memory addressing, it does not need to be related to the actual physical memory size (this will be introduced in detail later), but excessive use of virtual memory leads to page in/out，

will greatly affect the performance of the system and may even cause the system to crash. Therefore, a parameter is needed to control the maximum size of virtual memory used by SGA. This parameter is SGA_MAX_SIZE. When the instance is started,

Each memory area is only allocated the minimum size required by the instance. During subsequent operations, their size is expanded as needed, and their total size is limited by SGA_MAX_SIZE.

For OLTP system, refer to:

##1G#4G#8G

System memory	##SGA_MAX_SIZE value
1G	##400-500M
##2G
2500M
5G	2.6 PRE_PAGE_SGA When the oracle instance starts, only the smallest size of each memory area will be loaded. While other SGA memory is only allocated as virtual memory, Only when the process touches the corresponding page, it will be replaced in physical memory. But we may hope that once the instance is started, all SGA are allocated to physical memory. At this time, the purpose can be achieved by setting the PRE_PAGE_SGA parameter. The default value of this parameter is FALSE, that is, not all SGA will be placed in physical memory. When set to TRUE, instance startup will place all SGAs into the physical In memory. It allows the instance to start up to its maximum performance state, however, the startup time will also be longer (because in order to make all SGA are placed in physical memory, and the oracle process needs to touch all SGA pages). 2.7 LOCK_SGA In order to ensure that SGA is locked in physical memory without having to page in/out , can be controlled through the parameter LOCK_SGA. The default value of this parameter is FALSE. When specified as TRUE, all SGA can be locked in physical memory. certainly, Some systems do not support memory locking, so this parameter is invalid. 2.8 SGA_TARGET What we want to introduce here is a very important parameter introduced in Oracle10g. Before 10g, each memory area of ​​SGA The sizes of need to be specified through their respective parameters, and they cannot exceed the value of the size specified by the parameters, although their sum may not The maximum limit of SGA has not been reached. In addition, once allocated, the memory in 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, as examples. They have the greatest impact on the performance of the instance. But there is such a contradiction: when memory resources are limited, sometimes the demand for data to be cached is very large, In order to improve the buffer hit, it is necessary to increase the Buffer Cache. However, due to the limited SGA, it can only be "robbed" from other areas - such as reducing the Shared Pool, Increase Buffer Cache; sometimes large chunks of PLSQL code are parsed and stored in memory, resulting in insufficient Shared Pool, Even a 4031 error occurs and the Shared Pool needs to be expanded. At this time, human intervention may be required to regain the memory from the Buffer Cache. With this new feature, this memory contradiction in SGA is easily solved. This feature is called automatic shared memory management （Automatic Shared Memory Management ASMM）. The only parameter that controls this feature is SGA_TARGE. After setting this parameter, you do not need to specify the size for each memory area. SGA_TARGET specifies the maximum memory size that SGA can use, The size of each memory in SGA is controlled by Oracle itself and does not need to be specified manually. Oracle can adjust the size of each area at any time to achieve the system The most reasonable size for optimal system performance, and control their sum to be within the value specified by SGA_TARGET. Once you specify a value for SGA_TARGET (The default is 0, that is, ASMM is not started), the ASMM feature is automatically enabled. 三、oracle 内存调优办法    当项目的生产环境出现性能问题，我们如何通过判断那些参数需要调整呢？    3.1 检查ORACLE实例的Library Cache命中率:     标准:一般是大于99％                   检查方式: select 1-(sum(reloads)/sum(pins)) "Library cache Hit Ratio" from v$librarycache;  处理措施:    如果Library cache Hit Ratio的值低于99%，应调高shared_pool_size的大小。通过sqlplus连接数据库执行如下命令，调整shared_pool_size的大小：     SQL>alter system flush shared_pool; SQL>alter system set shared_pool_size=设定值 scope=spfile; 3.2 检查ORACLE实例的Data Buffer（数据缓冲区）命中率:     标准:一般是大于90%     检查方式:     select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'; 处理措施:     如果HIT RATIO的值低于90%，应调高db_cache_size的大小。通过sqlplus连接数据库执行如下命令，     调整db_cache_size的大小     SQL>alter system set db_cache_size=设定值 scope=spfile 3.3 检查ORACLE实例的Dictionary Cache命中率:      标准:一般是大于95%        检查方式:        select 1 - (sum(getmisses) / sum(gets)) "Data Dictionary Hit Ratio" from v$rowcache; 处理措施:        如果Data Dictionary Hit Ratio的值低于95%，应调高shared_pool_size的大小。通过sqlplus连接数据库执行如下命令，调整shared_pool_size的大小：        SQL>alter system flush shared_pool; SQL>alter system set shared_pool_size=设定值 scope=spfile; 3.4  检查ORACLE实例的Log Buffer命中率:      标准:一般是小于1%        检查方式:       select (req.value * 5000) / entries.value "Ratio" from v$sysstat req, v$sysstat entries where req.name = 'redo log space requests' and entries.name = 'redo entries'; 处理措施:        如果Ratio高于1％，应调高log_buffer的大小。通过sqlplus连接数据库执行如下命令，调整log_buffer的大小：        SQL>alter system set log_buffer=设定值 scope=spfile; 3.5 检查undo_retention:      标准:undo_retention 的值必须大于max(maxquerylen)的值        检查方式:        col undo_retention format a30 select value "undo_retention" from v$parameter where name='undo_retention'; select max(maxquerylen) From v$undostat Where begin_time>sysdate-(1/4); 处理措施:        如果不满足要求，需要调高undo_retention 的值。通过sqlplus 连接数据库执行如下命令，调整undo_retention 的大小：        SQL>alter system set undo_retention= 设定值 scope=spfile; 注：     32bit  和 64bit  的问题     对于 oracle 来说，存在着 32bit 与 64bit 的问题。这个问题影响到的主要是 SGA 的大小。在 32bit 的数据库下，通常 oracle 只能使用不超过 1.7G 的内存，即使我们拥有 12G 的内存，但是我们却只能使用 1.7G，这是一个莫大的遗憾。假如我们安装 64bit 的数据库,我们就可以使用很大的内存，我们几乎不可能达到上限。但是 64bit 的数据库必须安装在 64bit 的操作系统上，可惜目前 windows 上只能安装 32bit 的数据库，我们通过下面的方式可以查看数据库是 32bit 还是 64bit      But under a specific operating system, certain means may be provided so that we can use more than 1.7G of memory, reaching more than 2G or even more. Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of Summarize and organize memory allocation and tuning of oracle learning. For more information, please follow other related articles on the PHP Chinese website!