Operating System - HP-UX
1748195 Members
4508 Online
108759 Solutions
New Discussion юеВ

Re: Kernel Conf for Oracle SGA

 
SOLVED
Go to solution
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
Alex Ostapenko
Advisor

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