cancel
Showing results for 
Search instead for 
Did you mean: 

Kernel Conf for Oracle SGA

SOLVED
Go to solution
Steven Chen_1
Super Advisor

Kernel Conf for Oracle SGA

Hi,

We plan to have 4G ram for Oracle 8i on hp9000 64bit 11i platform. Currently we have 2g ram and the shmmax=1073741824. There is only one Oracle instance on the server.

How can we pass the 1.75G SGA limit by tuning the kernel parameters?

Any one please help.

Thanks a lot,

Steven
Steve
14 REPLIES
Patrick Wallek
Honored Contributor
Solution

Re: Kernel Conf for Oracle SGA

If you are running a 64-bit version of Oracle, and since this is 64-bit HP-UX, there is no 1.75GB limit. That is only applicable to 32-bit systems.
Jeff Schussele
Honored Contributor

Re: Kernel Conf for Oracle SGA

Hi Steven,

You simply *can't* pass the 1.75GB 32-bit ceiling with kernel parms.
The only way to do this would be to compile the binary with the SHMEM_MAGIC switch.
Or if the binary was already compiled for EXEC_MAGIC you could chatr it to SHMEM_MAGIC and if the mem calls have mallopt() before *any* malloc() with a M_ENABLE MMAP arg then you could address up to 3.5-3.6 GB.

But realistically - for Oracle the best thing to do is upgrade to the 64-bit flavor as these techniques described above are not fully supported be Oracle, I believe.

Rgds,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Jean-Luc Oudart
Honored Contributor

Re: Kernel Conf for Oracle SGA

Steven,
When you add your memory you also have to increase shmmax parameter (one Gb at the moment) to go up to say 4Gb.
The SGA will be size with the change in your initxxx.ora file.

If you use dynamic buffer cache, don't forget to tune the parameters again once you add the eztra memory.

You only have one instance, hope you have Oracle 8i 64bits (8.1.7.4).
select * from v$version will give you the oracle version.

Regards,
Jean-Luc
fiat lux
Steven Chen_1
Super Advisor

Re: Kernel Conf for Oracle SGA

Thanks a lot, guys.

Jean-Luc: I am using 8174 on 64bit server. Currently I have the followings parameters set to 1073741824:

shmmax
maxdsiz
maxdxiz_64bit
maxssiz_64bit
maxtsiz
maxtsiz_64bit

I'll have your advise to change dct_min_pct or max after 4g in place accordingly.

Please tell me:

1) is setting the above parameters to 4g the way to have sga upto 4g physical ram limit? What if only 3g number?
2) is 4G digit equal to 4294967296?

Thanks a lot.
Steve
Jean-Luc Oudart
Honored Contributor

Re: Kernel Conf for Oracle SGA

Steven,

Realistically, you wonâ t be using 4Gb for your RAM.
You need some RAM to run your system, your applications, for your buffer cache.
The idea is not to use the device swap area (use pseudo swap only).

The shmmax is the physical limit. It does not mean you will use it. The init.ora file is where you will configure the SGA size.

# kmtune | grep shmmax
shmmax 1024000000
This is what I have for 1Gb (I have more than one instance on my server).

Regards,
Jean-L
fiat lux
Steven Chen_1
Super Advisor

Re: Kernel Conf for Oracle SGA

Jean-Luc,

I will do init#.ora for sga utilization.

My point is, for kernel conf to reflect 4g ram, is it the right way to set parameters first to physical ram limit?

Stevn
Steve
Jean-Luc Oudart
Honored Contributor

Re: Kernel Conf for Oracle SGA

Steven,

the kernel parameter is the HWM (high Water Mark) that will limit your SGA size as it is based on one segment.
Therefore you can set this parameter to 4Gb but have a SGA of 2.2Gb if you wish.

I read somewhere that Oracle9i was more clever (???) as it would book more than one shared memory segment is the HWM was hit !
( I still think you should stick to one segment per instance).


Jean-Luc
fiat lux
Steven Chen_1
Super Advisor

Re: Kernel Conf for Oracle SGA

I've talked to Oracle support, who suggested that shmmax must be set to 1G regardless of the amount of RAM on the box. The 64bit OS does not have a limit on SGA size, which can be 1/2 of RAM. There are 6 memory segments allowed on the 64bit OS box.

HP: correct this statement, if it is WRONG.
Steve
Jeff Schussele
Honored Contributor

Re: Kernel Conf for Oracle SGA

Hi Steven,

Well, I'm not HP but I can tell you they ARE flat out wrong.
If you wanted advice on healthcare would you go to your auto mechanic?

For 32-bit Oracle apps, you can certainly set an shmmax of 1.75GB - but realistically you'll only be able to use 1.5-1.6GB & that's it unless you compiled or chatr's the binary. But then IF you had multiple Oracle instances, they could *each* address up to that by using memory windows. But that *only* helps with multiple SIDs.

For 64-bit OS and apps you can set an shmmax of whatever you need, but you don't want a value higher than physical RAM of course.

What Oracle is NOT telling you is that prior to the 9 version, IF you defined an shmmax value *smaller* than the SGA size they would error out & not start. NOW what Oracle will do is spawn multiple shared mem segments in values just smaller than the shmmax value & link them all together & this will cause tremendous performance penalties. Oh yes - it will run - but nowhere near optimally & of course they'll blame the OS. BUT if you defined a shmmax value large enough it WILL run optimally.

So there is NO logical reason to believe those statements. Only you & your DBAs know what the optimum shmmax value should be & you should follow *that* advice not some frontline Oracle tech who has no stake in your application's performance & probably could not care less.

My 2 cents,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Steven Chen_1
Super Advisor

Re: Kernel Conf for Oracle SGA

Jeff,

Thank you very much for your comment that makes a lot of sense.

For good judgement, I'm going to get HP support to answer it too.
Steve
Bill Hassell
Honored Contributor

Re: Kernel Conf for Oracle SGA

As mentioned, a 64bit program has no practical limits on memory usage. There are two main types of memory used in 64bit programs, local (limited by maxdsiz_64) and shared memory (limited by shmmax per segment requested). So a 64bit program can ask for dozens of gigabytes of RAM depending on the runaway program limits set with maxdsiz_64 and shmmax.

Oracle indeed partitions large SGA into 1Gb segments, so you can setup 3Gb for SGA and you'll see Oracle ask for 3 shared memory segments (probably a way to handle large SGA's on multiple flavors of opsystems). And as mentioned, maxdsiz_64 is just a fence...you can set it to 20Gb if you like. It simply prevents programs asking for more than that amount.

Now, within the 4Gb limit, you can certainly configure Oracle to use 4Gb (or even 8Gb) of RAM for SGA. HP-UX is a virtual memory system and it will gladly accomplish the task as long as there is enough swap space. Of course, there can be a 100:1 performance degradation once SGA exceeds available memory. Hopefully the shock value of that hit will be understood. For a single instance of Oracle in a 4Gb system I would allow 1Gb for the opsystem, buffer cache, memory mapped files and programs, thus allowing abouyt 3Gb for SGA. So the kernel params that need to be changed are:

dbc_max_pct = no more than 400 megs (10% of 4Gb RAM)
dbc_max_pct = 2
maxdsiz=1750 megs
maxdsiz_64=3000 megs
shmmax=2000megs

Note that shmmax=2Gb simply allows a program to request up to that limit. Oracle will request segments in !gb chunks as needed.

NOTE: forget all of this if you are running 32bit Oracle code (64bit HP-UX runs 32bit as well as 64bit programs). There are severe limits for 32bit programs beyond just the 960meg or 1750meg limits which will require careful planning and patches. All 32bit programs must use shared memory from a 32bit pool that all other programs use. That pool cannot be expanded so if you need more, you'll have to (patches!) use memory windows.


Bill Hassell, sysadmin
Jim Butler
Valued Contributor

Re: Kernel Conf for Oracle SGA

Bill,

In my experience - I have had issues with dbc_max_pct greater than 300 Mb with Oracle (specific to Oracle), and have read misc tech notes which recommend that Oracle set the dbc_max_pct to 128 mb

4G x.03 = 120 Mb

So dbc Max should be set to 3 -

Other than that - looks like some real good info here.

Happy Holidays
Man The Bilge Pumps!
Bill Hassell
Honored Contributor

Re: Kernel Conf for Oracle SGA

The buffer cache is probably the most controversial parameter in HP-UX. It's job is pretty simple: buffer reads and writes for the filesystem to reduce disk I/O. The dynamic feature (enabled when bufpages=nbuf=0) is supposed to adjust as needed but until 11i, these adjustments were sluggish. And as with any large memory component, the combination of SGA, buffer cache, multiple applications, 32bit limits, and so on, Oracle's cryptic error messages make the real problem difficuly to determine.

When DBA's are given a large SGA to play with, one of the first things they will do is to increase the Oracle equivalent of a buffer cache. This one is more effective in that Oracle only adds items that make sense to buffer. HP-UX can't tell the difference between records that will be sequentially accessed (good for read-ahead) and those that are one-time visits. So a small buffer cache (128megs) does make sense with a large SGA, but you'll need Online JFS to add the options to the Oracle data disks (only) to bypass the buffer cache and use Oracle's buffering exclusively.


Bill Hassell, sysadmin

Re: Kernel Conf for Oracle SGA

With regards to all of the ideas discussed above, the performance improvement or degradation due to a "correct" change or a bad one may not even be noticeable unless the change is addressing a particular bottleneck. Our experience has been that "neat" or "nice-to-do" changes may appear to improve performance numbers, but unless there was a bottleneck, the application user noticed NOTHING! And ultimately, the true measure of performance improvement is what the application user perceives.

However, it is always good to follow rules of thumb like those discussed by others previously (e.g., don't have SGA exceed free memory, reduce DBC size if Oracle has adequate buffers, etc.), just consider some of the more involved changes (e.g., Oracle bypassing HP-UX buffer cache) more seriously before implementing only if you've identified a specific bottleneck that the change might address.

And of course the NUMBER 1 RULE of performance tuning. Make only one change at a time...

=;-) Alex