Re: shmmax sizing recommendations

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.

Don.

On Wed, Nov 18, 2009 at 9:46 AM, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> wrote:
> 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
> encountered.
>
>    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.
> http://orainternals.wordpress.com/2008/10/31/performance-issue-high-kernel-mode-cpu-usage/
>
> Cheers
>
> 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 11.1.0.7 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
>> discretion.
>>
>> 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
>> 4294967295.
>> === 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
>> http://seilerwerks.wordpress.com
>> ultimate: http://www.mufc.us
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>
>

-- 
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l