SHMMAX and Oracle SGA

[Tim Killinger]

Hello,

We're new to UX as we're porting an oracle based app to rp5450s. As the sysadmn, I'm trying to understand how to allocate memory for Oracle SGA; I want to make it as large as possible for data caching/io performance. Here are a couple of questions - please comment if it sounds like I'm missing a point. (We have 8gig ram, 12gig database, 3 processor rp5450 and va7100.)

- Is there a way to "dedicate" any of shared memory for SGA?

- Is there a way to show how much shared memory is actually being used by Oracle SGA?

- Suggested ballpark kernel paramater settings for above config?

- Any other comments that will help make me smarter about this general issue?

[eran maor]

Hi

the easy command to find out about shared memory is shminfo but you need to dowenload this file .

please take a look at this answer :
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x1382107d277ad611abdb0090277a778c,00.html

bill hassel has much more exper. on this issue .

also about kernel params , i will take a lot at dbc_max_pct that it will be 5 becuase you have 8 giga memory and i will not recomment that you will need more the 400 m.b for buffer chase.

also look for maxsiz and maxdsiz and maxssiz ( the param for 64 bit !! )

increase the maxdsiz as size of your memory and also increase the maxssiz .

i will also recommend that you will look at oracle site for guide lines .

[T G Manikandan · Accepted solution]

To find the amount of SGA that the Oracle uses I found out a script which is attached.

make sure that your buffer cache parameter values are not very high
dbc_max_pct bring it down to 5% for the amount of memory you have.

[Anthony deRito]

Keep an eye on the bigger picture... the first thing you need to determine is how much memory your system has. In other words, before you tune your SGA, begin by making sure your OS is able to keep up before it begins to show signs of paging out to swap.... some paging out is normal but you don't wan't to allocate so much memory to your SGA that the virtual memory subsystem begins to page out parts of the SGA from physical memory out to swap. There's more going on with the system than just Oracle processing so it is possible that other processes on the host can cause paging or swapping of the SGA.

Use Glance as the system is implemented to tune for optimal performance. It may take several passes to optimize performance. By the way, this great HP doc explains Glance specs that may help you (if you use Glance).

http://docs.hp.com/hpux/pdf/B3691-90061.pdf

Good luck.

[Dave Chamberlin]

If you are using 32bit Oracle then the SGA cannot exceed 1.75GB for a single instance. The optimizing of your resources largely depends on the nature of the database. Is it primarily a tranaction database (OLTP) or a data warehouse, etc? If you have a lot of large pl/sql packages you would want to make the shared pool large enough to pin those packages. If you are processing a lot of data, you would want as many block buffers as you can get in the remaining space (take your database block size times the number of block buffers to figure how much RAM this will be). The shared pool size and the number of block buffers are set in the init.ora parameter file and can be changed easily - though you have to restart the instance to take effect. For my database (mostly OLTP), I have set the system buffer cache to be about 300MB using dbc_max_pct. Setting this kernel parameter to the default 50% value is a waste of RAM and will not improve performance. See discussions in this forum on dbc_max_pct. Tuning is an iterative process. Do some more research, start at your best guess and then see where your bottleneck is.

[Jim Butler]

Here is an oracle kernel I use - works well -
Note that dbcmaxpct Times MB RAM should not exceed 128 Mb - so the note above about that should be heeded -

But you can use this as a spare parts kernel.

good luck

dbc_max_pct 35
default_disk_ir 1
max_thread_proc 256
maxdsiz 0x200000000
maxfiles 512
maxssiz 040000000
maxswapchunks 815
maxtsiz 0X0D000000
maxuprc (MAXUSERS*5)
maxusers 64
ncallout 2064
nflocks 1000
nkthread 2048
npty 96
nstrpty 60
semmni 600
semmns 1000
semmnu 256
semume 96
shmmax 0X200000000
shmmni 850
shmseg 400

[Steven E. Protter]

Oracle has a utility called Installprep.sh

You need to have added the oracle user, the dba group and have the oracle user be a member of the group.

The utility will check the kernel and give you minimums among other issues.

If you are already running oracle, go to metalink and download the rda utility. That will collect data which you can look at, or upload to metalink.oracle.com

BTW, vastly increase the minimums for shared memory areas, message queues and such, especially if you use other databases such as adabas, which uses the same resources.

Hope this helps.

Steve

[Steven E. Protter]

almost as important as the kernel are patches.

Get the December patch set, install it.

Go to metalink.oracle.com if you have access and get the current patch list for HP-UX

Install them all.

If you are new to HP-UX you might not know you need to put java in seperately for oracle.

go to http://www.hp.com/go/java

There is another patch list on top of everything else that MUST be installed prior to java.

Do it in the wrong order and suffer.

When you are done with patches, then do the kernel. One of the java patches stepped on a nice kernel and has our database very unhappy now.

Summary: learn from my mistakes.

If you lack metalink access, email me at work Thursday and I'll hook you up.

Steve

[Bill Hassell]

The first question is: Oracle 64bit or 32bit? Very big issues will exist when Oracle runs in 32bit mode. While it is true that the maximum amount of shared memory (for 32bit programs) is 1.75Gb, Oracle must fight with all other processes for even small portions (few hundred megs) of shared memory. There is only one map for the 32bit shared memory and must be shared with memory mapped files, shared libraries and shared memory segments from other programs. Thus, 32bit Oracle will be severely limited in the amount shared memory that it can reliably request. One possible workaround is to use memory windows where each Oracle instance has it's own dedicated shared memory. You'll probably need some pacthes to activate memory windows if the memory window white paper is not found in /usr/share/doc.

For 64bit Oracle, there are no practical limitations (terabytes) for shared memory.

[Tim Killinger]

We're using 64 bit UX.

We're moving the application from 64bit OpenVMS, which has a feature whereby you can "reserve" memory, isolating it from the operating system and the potential for paging it.

What I'm wondering if there is such a similar feature in UX so I can generously dedicate a portion of my 8 gig to Oracle to cache as much data as possible.

Thanks to everyone's response so far! I'll close this thread and assign points soon!

[Heinz Gaertner]

Hi,

for the memory issue. As far as i know it is possible to lock the SGA. Normally only root can lock memory because normal users do not have the mlock privilige. You can set this for any user with the setprivgrp command (must be set with every reboot, so a startup script should do this). The other thing is to set the oracle database to allow locking of the SGA. The db-parameter is lock_sga. Sorry to say that this is only theoretical knowlegde, never tested this on a live system. Normally we do a memory sizing, so that you never will need to lock memory (because there is enough hardware).
For more information about shared memory you probably want to use the program shminfo. This little thing should be available at www.software.hp.com (sorry, don't have the exact link at the moment).

Hope this is helpful

Heinz

[Kimathi Njeru]

You have a whole lot of answers here that will point you in the right direction.

The correct Oracle parameter that you will want to use to lock your SGA in memory is 'pre_page_sga'.

This is a boolean that when set to TRUE, will load all SGA pages into memory. It might increase instance startup but you will see a slight improvement in performance with the amount of RAM that you have.

It is platform specific though!
Kim

[Tim Killinger]

Thanks for all the replys everyone..... I apreciate it... and I'm learning!

[Bill Hassell]

It's very important to differentiate between 64bit HP-UX (the operating system) and 64bit Oracle (an application program). There are far too many critical database apps running 32bit Oracle code on 64bit HP-UX which creates the memory limitations.

If your DBA is really running 64bit Oracle, then SGA can be several Gb in size and take advantage of in-memory sort areas, fast row insertions and bypass the HP-UX buffer cache with Oracle's built-in cache. Note that in order to bypass HP-UX's buffer cache, there are two steps:

- Make sure that all Oracle data and index lvols are separate mountpoints, and that rollback, redo and archive logs as well as Oracle executables and config files are not mixed into these data area mountpoints.

- Mount the data lvols are mounted with the options: rw,nosuid,delaylog,mincache=direct,convosync=direct,nodatainlog

NOTE: The options mincache and convosync are part of Advanced JFS, an optional product, but should be part of any Oracle installation. Once you have increased the Oracle SGA for a larger cache (perhaps 500 to 800 megs) and remounted the data/index lvols with the above options, the HP-UX buffer cache can be reduced to about 200 megs (for 8Gb RAM, that would be about 3 percent. Set (kernel parameters) dbc min to about 1 or 2 percent and dbc max to 3 or 4 percent.

Here is the link for the contributed program: shminfo:

ftp://contrib:9unsupp8@hprc.external.hp.com/sysadmin/programs/shminfo/

Note that IE has a big problem with ftp sites (this is not http://) so you'll need to go into Tools->Internet Options->Advanced and turn OFF the folder view for FTP sites.