SEO

I am using Oracle 9.2.0.5 on HP UNIX 11i. It is a production system.
Following error occurs:

ORA-21503: program terminated by fatal error
ORA-04030: out of process memory when trying to allocate 32768 bytes
(Alloc statemen,prefetch buff a
lloc)

This error is not shown in alert.log file instead reported by a third
party application.
I set event in init.ora:

SQL> show parameter event;

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
event string 21503 trace name errorstack le
vel 3, 04030 trace name errors
tack level 3

Hoping that some information will be written in Oracle trace files but
none is written.

I have 20GB of free memory on that server (so OS memory exhaustion on
database server should no be an issue). IPGA_AGGREGATE_TARGET was set
to 2GB.

I have read many articles on metalink as well as on internet, however,
cannot find the actual cause.


Any ideas on how to troubleshoot this information will be helpful.

On 9 Mar 2006 12:16:46 -0800, dbaplusplus@hotmail.com wrote:

>
>I have read many articles on metalink as well as on internet, however,
>cannot find the actual cause.

shared pool fragmentation caused by not using bind variables.
Consequently, increasing the shared pool will not help.

--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker schrieb:
> On 9 Mar 2006 12:16:46 -0800, dbaplusplus@hotmail.com wrote:
>
>> I have read many articles on metalink as well as on internet, however,
>> cannot find the actual cause.
>
> shared pool fragmentation caused by not using bind variables.
> Consequently, increasing the shared pool will not help.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Shared pool fragmentation usually cause ORA-4031, OP is getting
ORA-4030, which indicates lack for process memory. Having 20Gb free
memory on the server and hitting this error sounds a little strange however.

Best regards

Maxim

Yes, shared pool fragement gives ORA-4031. Metalink Oracle Notes
clearly say ORA-4030 is PGA issue.
I am wondering whether there is some bug in automatic PGA memeory
management using PGA_AGGREGATE_TARGET, so somehow Oracle is placing
some limitation on how much PGA a process can have and this limit is
recahed (instead of my process running out of memory on sever - 20GB is
free).

I have been talking to Oracle support, If they cannot figure this out,
I may try manual way of PGA memory management.

Try cutting pga_aggregate_target in half. Have you checked the 9206
patch set to see if the pga issues it fixes apply to your environment?
I've been convinced (without proof) it doesn't work as advertised, just
blindly cutting it seems to fix problems (for my 9206 system, 400M
works good). But see
http://www.jlcomp.demon.co.uk/faq/pat_presentation.html and
http://www.jlcomp.demon.co.uk/untested.html for real information.

Do you have multiple instances? Shared servers? How much physical
memory do you have? What are your init.ora parameters that affect the
SGA, anyways? sort area size?

Some of the recommended unix kernel parameter settings have changed
over time, have you checked them lately? If you are not running out of
memory, then you may be running out of a memory resource that is
dependent on the kernel parameters. I assume you've seen Note:233869.1
and Note:199746.1, but go over them again anyways. What's your
unlockable_mem, too? (This can get screwy if you let the system manage
it, especially if you've got a large SGA, or bogus shared memory
segments. The system can't lock memory for a process, even though
there appears to be plenty of memory available, so gives back a message
to Oracle, which then 4031's).

jg
--
@home.com is bogus.
RAC really doesn't like being bored:
https://metalink.oracle.com/metalink...,1,1,helvetica

I disabled. automatic pga memory management by setting
workarea_size_policy=MANUAL and setting SORT_AREA_SIZE=200M.
I still get out of process memory but somehow my third party aplication
is not showing any Oracle error code. It shows
a very long sql statement which it was executing and sql statement is
chopped,

I am wondering whether it is an application issue where an unusally
long sql statement is dynamically being generated,

There must be some limit to length of sql statement. What is this
limit, I will like to know?

Thanks

dbaplusplus@hotmail.com wrote:
> I disabled. automatic pga memory management by setting
> workarea_size_policy=MANUAL and setting SORT_AREA_SIZE=200M.
> I still get out of process memory but somehow my third party aplication
> is not showing any Oracle error code. It shows
> a very long sql statement which it was executing and sql statement is
> chopped,
>
> I am wondering whether it is an application issue where an unusally
> long sql statement is dynamically being generated,
>
> There must be some limit to length of sql statement. What is this
> limit, I will like to know?
>
> Thanks

DBA--, you may have a limitation on your virtual address space. I know
that
such thing exists on HP-UX and that default is not very big. When you
take
into account that SGA is also mapped into the address space, you may
wish to contact your system administrator who will need to relink the
kernel.
I don't remember which parameter it was, but your SA has a tool called
"SAM"
which lists all of those parameters. SAM has been around for so long
and has
changed so much that it should be called Son of SAM.

My System Admin already looked into all the kernal parameters - there
is 16GB free memory on that box and 48 GB free swap space.

It is defintely some Oracle "PGA" kind of limitaion (e.g., length of
sql statement or some memory for sql statamenet parsing etc ..)

My System Admin already looked into all the kernal parameters - there
is 16GB free memory on that box and 48 GB free swap space.

It is defintely some Oracle "PGA" kind of limitaion (e.g., length of
sql statement or some memory for sql statamenet parsing etc ..)
Since it is third part applicaion, I do not have access to source code.

I was talking about process address space limit, not free memory. How
large can a process get?