Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

CIS Unix (Keith Daley)
Occasional Advisor

Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

Wonder if anyone can shed some light on this issue.

We're running 32Bit Oracle 8.1.7.4 on 11i. We recently upgraded from 11.00 and were sailing fairly close to the wind on the SGA size - i.e. it was around 1.69GB. Since the upgrade to 11i, we have been forced to shrink the size of the SGA to around 1.65G otherwise Oracle fails to start with an "ORA-27102: out of memory" error.

The shmmax kernel parameter is set to 1GB - but I'm not too worried about the fact that the entire SGA won't fit into 1 segment. But this has not changed from 11.00 anyway.

I'm guessing this has something to do with the fact that the maximum SGA for 32Bit Oracle 8 is 1.75GB and the fact that the remaining "headroom" around 100MB is taken up by shared libraries and memory mapped objects.

I guess with the 11i upgrade a number of shared libraries have changed which now consume more memory.

Before anyone says anything - upgrade to 64bit 8174 is NOT - I repeat NOT - an option at the moment.

My questions are:

a) Are these libraries being loaded into the shared memory space (1.75GB) used by the Oracle SGA? i.e. Is my understanding there?!!

b) If so, is there anyway I can tell what shared libraries are being loaded into the shared memory space? (So that I can relate them to the libraries under 11.00)

c) Using ipcs -mb to report on the shared memory segment sizes - when these are added up for Oracle should they match the SGA size exactly? (Because ours don't - they're about 500k out!)

d) Has anyone any experience of using the "chatr" command to modify the SHMMEM_MAGIC compiler directive on the "oracle" binaries to overcome this 1.75GB limit?

And in general....help!!

And if you're interested we're running this on a V-Class with 8GB RAM (2.8GB of which is unused and available!!!)

Many thanks.
6 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

Hi Keith

Oracle 32 bits :

Your SGA is limited to 1.75Gb

You can either migrate to Oracle 8174 64bits or reduce the other pool shared_pool, java_pool) if possible.
In the longer term you probably better with option#1 (I know it's not an option).

I don't think there is anythingelse you can do for a single instance of Oracle.

memory window
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=71986

Regards,
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

Hi Keith (again !)

FInd attached a script to check the size for the shared_pool.


Regards
Jean-Luc
fiat lux
Steven E. Protter
Exalted Contributor

Re: Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

There may be an OS limit involved here preventing you from exceeding 1.75 GB SGA


I do not believe the Oracle SGA fully loads in "shared" memory" Could be wrong there, but ipcs -sp might help you account for some of the missing memory. Gotta love those Semaphores.


Does this database really need that big an SGA?

Note on item d. If you find some way to get around this limit and have a problem, Oracle may give you a line I've heard in less drastic circumstances: That configuration is not forwarded.

I'd suggest some tuning of the database, perhaps run statspack data and see about trimming down this monster.

Perhaps multiple smaller instances for example.

SEP

Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Hein van den Heuvel
Honored Contributor

Re: Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

If you are living that 'close' then you'll need to evaluate and re-balance the various needs. How much can you shrink the shared pool before parsing becomes too much more expensive. Does adding more block buffers indeed reduce IOs significantly, or is that memory better spend on shared pool.
Set cursor-space-for-time to false.
Reduce everything else in sight down to actual use over time (v$resource_limit (sp?))
processes,
sessions,
dml_locks,
files.
Can the log_buffer be trimmed down? 1MB is often enough
Can the java pool be eliminated (might not be needed)

Good luck!
Hein.
CIS Unix (Keith Daley)
Occasional Advisor

Re: Oracle 8.1.7.4 32bit on HP-UX 11i - Problems with SGA approaching 1.7GB

Guys,

Thanks for your responses. You are correct that there may be one or two tweaks that we could apply to Oracle to relieve some of the pressure on the SGA limit - however, none of these are really going to give me the "big win" in terms of freeing up a big enough chunk of shared memory to make that much of a difference. I'm more interested in understanding WHY we are constrained to this magical 1.75GB figure and if there's anything that can be done to get around it. In addition, understanding why the upgrade to 11i has forced us to shrink the size of the SGA would go a long way of lifting the mystery on the problem as a whole. I suspect it's down to the shared libraries - but was hoping someone might come back with a definitive - "yes we had that problem too" and "this is how we overcame it " | grep -v "64 Bit Upgrade" !!!!
Jean-Luc Oudart
Honored Contributor