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

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

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.