Re: SGA bigger than 50G
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: andrew.kerber@xxxxxxxxx
- Date: Wed, 5 May 2010 16:43:55 -0400
I think I can contribute to several comments that have been made.
- Swap = 3/4 of RAM is a rule of thumb. The swap sizing is different for
linux and solaris, as well as it depends on whether you are using DISM or
ISM in Solaris.
- 70% of you RAM for PGA/OS is also a rule of thumb for datawarehouses. I
find it very unlikely to need 45 GB of RAM (not total memory!) for hash
joins/sorts. By default queries are limited to significantly less memory in
10g, regardless of your PGA settings. Not to mention each area itself (hash
or sort) is limited to < 4GB.
For linux, ensure that:
- you use hugepages
- you use lock_sga=true
- you configure limits.conf
- you configure shmall and shmmax (as D'Hooge Freek points out). Those are
limits not settings, just make them very big.
Depending on the type of warehouse you have, your 50GB SGA may be a bit
overkill, but without knowing what kind of queries you are going to run it
it may be appropriate. For all I know, your datawarehouse maybe doing index
nested loops all over the place and not need any significant amounts of PGA.
--
Christo Kutrovsky
Senior Consultant
Pythian.com
I blog at http://www.pythian.com/blogs/
On Wed, May 5, 2010 at 10:23 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx>wrote:
> Um, not quite true. I have seen this sort of error even when there is
> plenty of RAM available if the swap isn't sufficient. It depends on the OS
> settings and a few things I haven't been entirely able to track down. Its
> usually easier just to set the swap per the Oracle installation
> documentation rather than try and decide if it is going to be a problem.
>
> On Wed, May 5, 2010 at 8:20 AM, D'Hooge Freek <Freek.DHooge@xxxxxxxxx>wrote:
>
>> I don’t think that the swap has anything to do with the maximum size of
>> your sga.
>>
>> Swap will only be used when the ram in the server is not sufficient for
>> all the processes (oracle and non oracle) running on the server.
>>
>> Also when using hugepages your sga can’t be swapped out.
>>
>>
>>
>> On linux the maximum amount of shared memory (and thus your sga size) is
>> controlled by the shmall, shmmax and shmmni kernel parameters.
>>
>> Shmall is the total amount of shared memory (expressed in pages) for the
>> entire system.
>>
>> Shmmax is the maximum size of a single memory segment and shmmni is the
>> maximum number of shared memory segments (again system wide).
>>
>> An oracle sga can consist out of multiple shared memory segments and the
>> maximum size of the sga is limited to the minium of shmmax * shmmni and
>> shmall * page_size.
>>
>> Note that 11gR2, when using the memory_target parameter) works
>> differently.
>>
>>
>>
>>
>>
>>
>>
>> Regards,
>>
>>
>>
>> Freek D'Hooge
>>
>> Uptime
>>
>> Oracle Database Administrator
>>
>> email: freek.dhooge@xxxxxxxxx
>>
>> tel +32(0)3 451 23 82
>>
>> http://www.uptime.be
>> disclaimer: www.uptime.be/disclaimer<http://www.uptime.be/disclaimer.html>
>>
>>
>> <http://www.uptime.be/disclaimer.html>
>> ------------------------------
>>
>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Andrew Kerber
>> *Sent:* woensdag 5 mei 2010 15:00
>>
>> *To:* smishra_97@xxxxxxxxx
>> *Cc:* dbvision@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>>
>> *Subject:* Re: SGA bigger than 50G
>>
>>
>>
>> Could be a swap problem, the installation instructions say swap should
>> equal 3/4 RAM.
>>
>> On Wed, May 5, 2010 at 6:55 AM, Sanjay Mishra <smishra_97@xxxxxxxxx>
>> wrote:
>>
>> Nuno
>>
>>
>>
>> Here is the Swap Info. One thing is true that SHMALL is small how the swap
>> space can affect it. Appreciate your comments
>>
>>
>>
>> cat /proc/swaps
>> Filename Type Size Used
>> Priority
>> /dev/mapper/system_vg-swap_lv partition 25165816
>> 0 -1
>>
>> Sanjay
>>
>>
>> ------------------------------
>>
>> *From:* Nuno Souto <dbvision@xxxxxxxxxxxx>
>> *Cc:* "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
>> *Sent:* Wed, May 5, 2010 6:23:37 AM
>>
>>
>> *Subject:* Re: SGA bigger than 50G
>>
>>
>> Sanjay Mishra wrote,on my timestamp of 5/05/2010 12:31 PM:
>> > Yes it is failing with error like
>> > ORA-27102: out of memory
>> > Linux-x86_64 Error: 28: No space left on device
>> >
>> > :/proc/sys/kernel$cat shmmax
>> > 48318382080
>> > I tried with
>> > sga_target=38624M
>> > sga_max_size=38624M
>>
>> Is your pagefile/swap partition < 38GB by any chance?
>>
>> -- Cheers
>> Nuno Souto
>> in cold Sydney, Australia
>> dbvision@xxxxxxxxxxxx
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
--
Christo Kutrovsky
Senior Consultant
Pythian.com
I blog at http://www.pythian.com/blogs/
Other related posts:
