Sizing SGA for Oracle 9i-64bit on HP-UX 11i

[PAUL CHEN_2]

I would like to know
1) how large SGA I can set when I have 4 Gig RAM, i.e., 1/3 RAM or ... Currently, my SGA is 1.4 Gig, I am planning to increase it.
2) Does SGA relate to SHMMAX? How shall I set SHMMMAX?
3) Oracle uses maxdsize_64bit or mxssize_64bit?

Thanks

Paul

[eran maor]

Hi Paul

sizing the SGA dep. on the size of the free memory you have left about the os is loaded .

1. you need to understand that if you have 4 g.b memory it doesnt tell that you have 4 g.b memory free , one mistake that a lot of pepole do is not to change the size of the buffer cashe size ( the kernel param dbc_max_pct ) that tell the amont of memory is alloaction for buffer cashe ( in % ) .
this param need to be no more the 10% .
how to define the size of the SGA i dont know what to tell becuase i m not a oracle dba .

2. the SGA is connected to the maxdsize , the shmmax but also SHMMAX and SHMMMAX need to be define with your oracle installion guidelines .

3 .oracle use the maxdsize_64bit to define his max data seg. size for the SGA but also you need to define the maxssize like you guide of oracle install tell you .

i m used to it from installing oracle for openview and not from exp. of oracle dba
so it will be better also to hear from a oracle dba about your que.

[Bill Hassell]

SHMMAX is the size of a single shared memory segment. Oracle can use many gigabytes for SGA but will request them in 1Gb segments, so SHMMAX can stay at just over 1Gb while you let the DBAs increase SGA to 3 or 4 Gb. Oh, SGA is just like other process memory...it will be paged out as necessary so increasing SGA so large that it starts paging itself and/or processes will negate any gains on performance by having a large SGA.

The maxdsiz and maxdsiz_64 parameters are not related to SGA. They refer to the size of RAM local to each program. Oracle doesn't usually need a lot of local memory so setting maxdsiz to 900 megs and maxdsiz_64 to 2000 megs should be fine. 64bit programs are bound by the xxx_64 parameters.

Set your buffer cache maximum to about 400-600 megs (dbc_max_pct=10) and then monitor overall memory usage with Glance's m (memory) page. If page-outs start occuring at more than 1-digit rates, you may need to add another 4Gb for maximum performance.

[Hein van den Heuvel · Accepted solution]

Bill wrote: "Oracle can use many gigabytes for SGA but will request them in 1Gb segments"

While this may have been true for certain Oracle version on certain HP-Ux versions this is generally not the case. 64-bit Oracle will request up to shmmax. That is why it is there!
Personally I do not worry too much about shmmax. You are going to be hard pressed to be able to measure a difference between 1*4 GB or 4*1GB. Still, why not just set it large? And certainly, set it in the hundreds of MB, not tens. Just too messy to see all those sections with ipcs -m.

Paul,
Be sure to run statspack (level 7?). Takes snapshots around a representative business windows, and request a report. It will have a shared pool advisor where Oracle guestimates how much IO reduction might be achieved by increasing buffer pool. Similar for shared pool.

fwiw, I frequently set the SGA to 3/4 of the physical memory. This is for Servers just doing oracle and not having too many connections (slave processes).

hth,
Hein.

[PAUL CHEN_2]

All, Does Oracle SGA use maxdsize_64 or maxssize_64?

Thanks

[Joseph Loo]

Hi,

shmmax is but one of the many kernel paramters you have to change so as not to cause out-of-memory error and performance problem.

If you have access to Metalink, please read Metalink Note: 169706.1?

On the Kernel Parameter settings on the HP-UX 11.11 (64bit), to run 9.2.0 :

KSI_ALLOC_MAX (NPROC*8)
MAX_THREAD_PROC 256
MAXDSIZ 1073741824 bytes
MAXDSIZ_64BIT 2147483648 bytes
MAXSSIZ 134217728 bytes
MAXSSIZ_64BIT 1073741824
MAXSWAPCHUNKS 16384
MAXUPRC ((NPROC*9)/10)
MSGMAP (MSGTQL+2)
MSGMNI NPROC
MSGSEG 32767
MSGTQL NPROC
NCALLOUT (NKTHREAD+16)
NCSIZE ((8*NPROC+2048)+VX_NCSIZE)
NFILE (15*NPROC+2048)
NFLOCKS 4096
NINODE (8*NPROC+2048)
NKTHREAD (((NPROC*7)/4)+16)
NPROC 4096
SEMMAP (SEMMNI+2)
SEMMNI 4096
SEMMNS (SEMMNI*2)
SEMMNU (NPROC - 4)
SEMVMX 32768
SHMMAX AvailMem
SHMMIN 1
SHMMNI 512
SHMSEG 32
VPS_CEILING 64

which you should have follow. see above that parameters on maxssiz_64bit and maxdsiz_64bit

regards.

[Bill Hassell]

The kernel parameters maxssiz_64, maxdsiz_64 and maxtsiz_64 are not 'used' by the program, they are fences or limits imposed by the kernel to prevent a runaway program from misuing memory. If the limit is too low, the program (Oracle or any other process) will fail to run. These limits do not set the size of a program, rather they act as barriers.

maxdsiz is local memory and Oracle does not ask for a lot of extra RAM--most of it's work is done in SGA or shared memory. maxssiz is the maximum stack size and except for unusual programs, does not need to be changed. maxtsiz is the size of the executable instruction set and is also quite small compared to the default limit.