ORA-04030, k570, plenty of memory on the machine

[LS_4]

I'm the DBA and I'm getting 4030 (exact: "callheap, bind var buf") errors on an instance, the machine is a k570, HP-UX 11 64bt, 6 CPU, 3GB RAM.

The SGA I have set at 562MB, and the machine still shows 1.5GB free when the errors occur.

I have attached the kernel parameters, any ideas as to what is causing this issue or what remains to be changed?

NB: The "pending values" column wasn't applied, everything is still as it is in "current"

Thanks in advance

[Yogeeraj_1]

hi,

Please find below a quote from Oracle Metalink on resolving ORA-4030 errors on UNIX systems.


BACKGROUND

==========

The ORA-4030 error is caused when an Oracle process(while doing work on behalf of a client program) requests additional memory from the operating system, but the operating system cannot accommodate the request. This can occur because the operating system does not have enough physical memory or swap available for the process, the OS is configured to limit the amount of memory available for UNIX processes or an actual Oracle BUG has been encountered.

HOW TO RESOLVE THE ORA-4030

===========================

1. Reduce the PGA (Program Global Area) for the client process encountering the error. This is valid if the database is not configured with MTS (Multi Threaded Server) or Shared Servers.


For Oracle 8i and below the major portion of ORA-4030 instances can be solved by reducing the SORT_AREA_SIZE for the sessions. This will lessen the demand that the process places on physical memory. SORT_AREA_SIZE can be modified by the 'alter session' command but persistent changes must be recorded in the init.ora. See the Oracle Reference guide available on your Online Generic Documentation CD-ROM for more information on the 'sort_area_size' parameter.

For Oracle 9i and up, the PGA can be sized using the PGA_AGGREGATE_TARGET parameter set in the init.ora or spfile.ora



2. Increase the amount of memory a UNIX process can request and use from the operating system. This usually refers to stack and/or data size UNIX process resource limits.


This process varies slightly depending on the UNIX platform and the type of UNIX shell you are using. Generally speaking either the 'limit' or 'ulimit' command will allow your System Administrator to increase memory and data size limits. Oracle Support cannot recommend a specific value for these limits. However, doubling existing values or setting them to 'unlimited' is usually sufficient.

3. Increase the amount of swap available on your system. You should have 2-3 times the amount of physical memory available as swap space.

4. Finally, if you still experience the problem after addressing the above issues, it's recommended that you move to the latest patchset release to eliminate any possible Oracle product defects


Hope this helps!

Regards
Yogeeraj

[John Palmer]

ORA-04030 relates to an Oracle client process, not to the SGA itself. It may be stack or data segment related or even lack of swap space.

Relevant kernel parameters are maxssiz (stack) and maxdsiz (data).

I'd advise that you monitor the client Oracle process with 'glance'. Watch out for data and stack segment growth, also swap usage.

An Oracle bug is also a possibility, have you got the latest patchset applied?

Regards,
John

[Bill Hassell]

Most Oracle memory issues are with the 32bit version which is severely restricted on SGA memory. Since you have 1500 megs free, swap space is not an issue, so we'll look at SGA (wich is shared memory in HP-UX).

For 32bit programs, a single quadrant of 1000 megs is available to all 32bit programs (actually, slightly less than 1000 megs) and therein lies the problem: *all* 32bit programs share the same area which also includes shared libraries and memory mapped files, etc. And each shared memory segment requested by a program must be contiguous (no gaps), and while there may be a total of 900 megs free in the shared memory map, it is full of holes (fragmentation) due to the starting and stopping of programs that use shared memory.

The command: ipcs -bmop
will show usage but not fragmentation and if programs are killed with -9 rather than -15, fragmentation due to unused segments is guarenteed. This will also look like a memory leak.

Now the maximum available RAM for most applications using shared memory can be 1750 megs by using chatr or compile-time options to enable shared magic. The details are covered in /usr/share/doc in the two papers on memory management and process management. Most likely a reboot will get back a clean shared memory area and Oracle will no longer have out-of-memory errors. To see how bad memory is fragmented (and see all the other uses for this area), get a copy of shminfo from:

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

(note for IE users: turn off "Folder view for FTP sites" or it IE will likely hang...not a problem for Netscape/Mozilla or Opera)

So there are two solutions: memory windows or Oracle 64bit. For memory windows, you'll need to be up to date on all the patches (there are about 7-8 patches for HP-UX 11.0 prior to June 2002 patches). Once you are up to date, there will be a new white paper in /usr/share/doc for memory windows. This allows Oracle to have it's own private 1750 meg area for SGA without sharing that space with all other shared memory uses. All applications that access SGA (ie, listener apps) must be started inside the Oracle window so they can see the private map.

[LS_4]

Thanks for the contributions

We are running the 64 bit Oracle which has the current patch set:

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

The glance program does not seem to be on this box. For the memory issues and windowing requirement, I do not think this is the issue, as the SGA is only 562MB and the problems arise when 1.75GB is breached.

[LS_4]

Also, what precisely does the maxssiz parameter do?

[John Palmer]

maxssiz is the maximum size of a stack segment.

Your problem is due to a local 'oracle' process hitting some sort of limit.

It may be stack size - exceeding maxssiz.
It may be data size - exceeding maxdsiz.

Either of the above may be due to an Oracle bug.

Regards,
John

[Anonymous]

about kernel parameters see
http://docs.hp.com/hpux/onlinedocs/939/KCParms/KCparams.OverviewAll.html
and
http://docs.hp.com/hpux/onlinedocs/939/KCParms/KCparam.MaxSsiz.html