How to size SGA depending on the available Physical Memory / RAM

[Karthikeyan_5]

Hi All,

I want to know how to size the SGA and as well as depended/related HP-UX OS Kernel Parameters depending on the available RAM size......i hope there should be some General Rule or some sort of Thumb Rule.......

Thanks in advance......

Bye,
Karthik

[Michael Tully]

http://docs.oracle.com
http://docs.oracle.com/DOCS_DOT_PDF_Storage/a86566.pdf

Here is what we use on our only oracle server which is being used for data warehouse. These were the recommendations from oracle themselves. You may need to make changes to your buffer cache settings depending on the amount of RAM you have. (dbc_min_pct & dbc_max_pct)

* Configurable Parameters

STRMSGSZ 65535
dump lvol
dbc_max_pct 10
dbc_min_pct 2
ksi_alloc_max (NPROC*8)
max_thread_proc 256
maxdsiz 1073741824
maxdsiz_64bit 2147483648
maxssiz 134217728
maxssiz_64bit 1073741824
maxswapchunks 16384
maxuprc ((NPROC*9)/10)
msgmap (MSGTQL+2)
msgmni NPROC
msgseg 32767
msgtql NPROC
ncallout (NPROC+16)
ncsize ((8*NPROC+2048)+VX_NCSIZE)
nfile (15*NPROC+2048)
nflocks 4096
ninode (8*NPROC+2048)
nkthread (((NPROC*7)/4)+16)
nproc 4096
nstrpty 1024
nstrtel 1024
npty 1024
semmap (SEMMNI+2)
semmni 4096
semmns (SEMMNI*2)
semmnu (NPROC-4)
semvmx 32768
shmmax 4294967295
shmmni 512
shmseg 32
st_ats_enabled 0
vps_ceiling 64

Also:
http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1058149042333+28353475&searchCrit=allwords

What is your current RAM/swap size ? This will determine the SGA/shared memory sizes.

[twang]

Take a look at the attached paper.

[Lee Tae-kyung]

1. The size of Shmmax is RAM size.
If the value of shmmax is smaller than sga, oracle engine uses a swap device.

2. In general, SGA is a half of RAM size.

3. The memory for a oracle session need 1.5-2Mbyte memory.

4. The data buffer cache in SGA is assigned four-fifths of SGA.

Good day~~

[Stefan Farrelly]

There is also a major consideration for the 32bit pool of shared memory if you are running a 32bit database on a 64bit OS. If so then you only have 1Gb in Q3 for the SGA without using the 750MB in the global Q4 pool - ie. 1GB is the limit for your SGA.

So are you running 32bit of 64bit Oracle ?

[Karthikeyan_5]

Hi Michael Tully,

I am not using any database......

I want in general rule for sizing SGA & related OS Kernel Parameters......assume RAM = x MB.....

Now i want all the calculations depending on this......for example: swap = twice of
RAM = 2 * x MB....etc....

bye,
karthik

[Karthikeyan_5]

Hi Lee Tae-kyung,

It was simple and more informative......thanks...

U've talked only about SHMMAX Kernel Parameter is that only important one related to ORACLE, but in almost all books they have told that the following OS Kernel Parameters are important:

SHMMAX
SHMMNI
SHMSEG
SHMMNS
SEMMNI
SEMMSL
SEMMNS
SEMMNU

And Regarding the estimation of SGA size depending upon available RAM, i.e. fine that overall SGA size is 50% of RAM.....but SGA is the collection components like Shared Pool, Large Pool, Redo Buffer, DB Block Buffer...etc

So, can u pls give me simple and general sizing concept on various SGA components...

bye,
karthik

[Karthikeyan_5]

Hi Michael Tully,

None of your exact Doc links are opening to me.....

So can u pls attach them for me.....

Regards,
Karthik