Oracle 11g memory setting

最新推荐文章于 2023-10-19 09:37:38 发布

转载最新推荐文章于 2023-10-19 09:37:38 发布 · 211 阅读

文章标签：

本文探讨了在Oracle11g环境下，当大量数据操作、高IO负载和缓存使用导致操作系统内存几乎全被占用的问题。通过分析AWR报告，发现内存_target参数设置不当，导致内存资源分配不合理。文章解释了Oracle11g中引入的memory_target和memory_max_target参数的作用，以及如何调整这些参数来优化内存使用。同时，文章提到了Oracle11g在内存管理上的新特性，如自动内存管理(AMM)和自动共享内存管理(ASMM)，以及它们如何促进实例间的内存资源共享。

DBA Notes: 2011/10/11

Cheng Li

Oracle 11g memory setting

From ehealthy or OEM tools, one issue is raised. Oracle consumes almost 100% of OS memory. The system is an Oracle 11g R1 on Linux.

What I discovered from AWR is indication of huge amount of IO, buffer cash consumption, cache buffer chains, etc. Buffer cache is inside SGA. As for the proof of OS memory usage percentage to close 100%, we can check with Oracle memory setting. Currently, what I find from AWR is as following ( I do not see any other setting such as MEMORY_MAX_TARGET ) :

memory_target 5368709120

n This is about 50G, but OS system is 16G

Conclusion: when the massive data manipulation, large amount IO, buffer cache, Oracle will consume all possible OS memory memory_max_target parameter is not properly set. In Oracle 11g, Oracle introduces the new memory_target and memory_max_target parameters.

In our case, we applied:

# memory max is set to be 5G

memory_max_target 536870912

The AWR reports are saved in /home/oracle/ctmdba/dbalog

[oracle@ctmcmsdb dbalog]$ sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 11 09:18:35 2011

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @awr.sql

Reference:

http://www.dba-oracle.com/oracle11g/oracle_11g_memory_target_parameter.htm

Prior to Oracle 11g, the DBA set the sga_target and sga_max_size parameters, allowing Oracle to reallocate RAM within the SGA. The PGA was independent, as governed by the pga_aggregate_target parameter.

Now in Oracle 11g we see the memory_max_target parameter which governs the total maximum RAM for both the PGA and SGA regions and the new memory_target parameter which governs the existing sizes. This allows RAM to be de-allocated from the SGA and transferred to the PGA.

This is an important Oracle11g new feature because it lays the foundation for inter-instance RAM memory sharing.

We are seeing the second age of mainframe. computing, and server consolidation where it's not uncommon to find a dozen instances on a single large server.

As of Oracle 10g, Automatic Memory Management (AMM) only allows shifting of RAM within the confines of sga_max_size. Now in Oracle 11g, we see this new Automatic Shared Memory Management (ASMM). The memory_target parameter is dynamic (changeable with "alter system" commands), whereby RAM can be de-allocated from an instances SGA/PGA and re-allocated to another instance.

The Oracle 11g documentation notes this on memory_target and memory_max_target:

MEMORY_TARGET specifies the Oracle system-wide usable memory.

The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero.

After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

黑洞吞噬恒星的过程图.jpg