Re: shmmax sizing recommendations
Date: Wed, 18 Nov 2009 10:01:14 -0600
hey Riyaj. Hope all is well!
Looks like there's ~ 70gb of swap allocated.
$ free -m
total used free shared buffers cached Mem: 63448 56884 6563 0 146 6817 -/+ buffers/cache: 49920 13528 Swap: 71817 0 71816
Not much used now. The 04030 only happened on a rogue query on Sunday night, we don't expect that query to be re-run.
OSS did suggest setting this event:
ALTER SYSTEM SET EVENTS '4030 trace name heapdump level 536870917;name errorstack level 3';
The note has both 32-bit and 64-bit sections. Titled: Maximum SHMMAX values for Linux x86 and x86-64 (Doc ID 567506.1)
Thanks for the link, I'll give it a read shortly.
On Wed, Nov 18, 2009 at 9:46 AM, Riyaj Shamsudeen
> Howdy Don
> First, ORA-4030 is at PGA level. So, I am not sure why SHMMAX is playing
> a role in a 64 bit environment. Also, How much swap space is allocated? You
> can encounter ORA-4030 errors if the swap space is insufficient too.
> Further, You might want to understand which type of PGA heap is growing and
> ORA-4030 trace files usually can give you a good understanding of the growth
> Another option is to trigger PGA heapdump at a process level if ORA-4030
> I just reviewed that note and it is for Linux x86 platform only. I would
> go with OSS recommendation. There is nothing wrong in having few more shared
> memory segments for an instance as long as SHMMAX is meaningful (like
> 4GB-1). Only downside is that each connect and disconnect will execute more
> attach and detach calls. This can be a problem in few applications with
> excessive concurrent connects and disconnects. But, that is an exception.
> I had encountered a client issue related to this setting, but this
> problem is rare though.
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
> OakTable member http://www.oaktable.com
> Co-author: "Expert Oracle practices: Oracle Database Administration from the
> Oak Table" http://www.apress.com/book/view/9781430226680
> On Wed, Nov 18, 2009 at 8:56 AM, Don Seiler <don_at_seiler.us> wrote:
>> While troubleshooting an ORA-04030 issue, Oracle Support also took
>> notice that we had shmmax a bit out of whack. Whether or not this
>> would contribute to the 04030, I'll not get into right now. This is
>> Oracle 18.104.22.168 64-bit on OEL 5.3. The server has 64gb of physical
>> RAM. It hosts 3 RDBMS instances and 1 ASM instance, with a sum total
>> SGA sizes of ~42 gb and HugePages configured for 44gb. The largest
>> instance has an SGA of just under 30gb.
>> OSS first suggested setting shmmax to "RAM times 0.5 but not greater
>> then 4GB-1" and then pointed me to Doc Id 567506.1:
>> === BEGIN QUOTE ===
>> Oracle Global Customer Support officially recommends a " maximum" for
>> SHMMAX of just less than 4Gb, or 4294967295.
>> The maximum size of a shared memory segment is limited by the size of
>> the available user address space. On 64-bit systems, this is a
>> theoretical 2^64bytes. So the "theoretical limit" for SHMMAX is the
>> amount of physical RAM that you have. However, to actually attempt to
>> use such a value could potentially lead to a situation where no system
>> memory is available for anything else. Therefore a more realistic
>> "physical limit" for SHMMAX would probably be "physical RAM - 2Gb".
>> In an Oracle RDBMS application, this "physical limit" still leaves
>> inadequate system memory for other necessary functions. Therefore, the
>> common "Oracle maximum" for SHMMAX that you will often see is "1/2 of
>> physical RAM". Many Oracle customers chose a higher fraction, at their
>> One last data point for 64-bit Linux systems: A SHMMAX value of 4Gb or
>> greater will lead to coredump limitations. If a customer is willing to
>> forfeit complete and successful core file generation under any and all
>> circumstances, then a SHMMAX value of 4Gb or greater is fine. It is
>> for this reason that Oracle Global Customer Support officially
>> recommends a " maximum" for SHMMAX of just less than 4Gb, or
>> === END QUOTE ===
>> I'm wondering what the masters out in the field think. Would you
>> advise setting SHMMAX to fit the largest SGA? Is there any known
>> performance hit by having more, smaller segments for an instance
>> rather than 1 large one?
>> Don Seiler
>> ultimate: http://www.mufc.us
-- Don Seiler http://seilerwerks.wordpress.com ultimate: http://www.mufc.us -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 18 2009 - 10:01:14 CST