cancel
Showing results for 
Search instead for 
Did you mean: 

Ora-4030 HELP!

Oleg_60
Occasional Visitor

Ora-4030 HELP!

Hi All!

I have 24Gb RAM on my HP-UX box.

I have many ORA-4030(can not allocate memory) errors in alert.log
At this time i look at glance utility and find out that there is about 2Gb free memory.

Can you say why Oracle can't allocate memory from this 2Gb free piece ???

Thank You!
13 REPLIES
Yogeeraj_1
Honored Contributor

Re: Ora-4030 HELP!

hi,

can you post more information on the following:

o Oracle Version

o Operating system/platform (hp-ux 11.11 64 bits?)

o Determine which process is requesting too much memory:
SQL> col name format a30
SQL> select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc;

o determine what the process is doing by running the following query:
select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = ;

o what is your sort_area_size?

o If you are running Oracle 9i or later, what are the values for WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET

revert

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Nico Schockaert
Trusted Contributor

Re: Ora-4030 HELP!

Hello,
A ORA-4030 is generally not an o/s problem, but rather a limit. The Oracle process will attempt to aquire memory up to some limits. If they request memory from the operating system and the operating system does not provide it, an ORA-4030 error is flagged. The two primary reasons for seeing this are the physical memory has been exhausted or
the o/s limit for process memory has been reached.

I suggest the following: Increase the ulimit for the process memory (data).

Hope this helps,

Nico
Joseph Loo
Honored Contributor

Re: Ora-4030 HELP!

hi,

do u also get ORA-06512?

try to increase the following operating system parameters:

maxdsiz
maxtsiz
maxssiz
maxdsiz64
maxtsiz64
maxssiz64

or u may reply with the current parameters.

regards.
what you do not see does not mean you should not believe
Yogeeraj_1
Honored Contributor

Re: Ora-4030 HELP!

hi,
see also metalink note:233869.1
Subject: Diagnosing and Resolving ORA-4030 errors

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Oleg_60
Occasional Visitor

Re: Ora-4030 HELP!


We use Oracle 9.2.0.6, HP-UX 11.11(64 bit)

It's very big question for me why Glance shows 2Gb free in OS but Oracle can't take this memory ??

Oleg_60
Occasional Visitor

Re: Ora-4030 HELP!


--what is your sort_area_size?

We do not use sort_area_size manual setting

We set WORKAREA_SIZE_POLICY = auto,
PGA_AGGREGATE_TARGET = 2Gb

Oleg_60
Occasional Visitor

Re: Ora-4030 HELP!


Some adds from our box ->

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 262144
stack(kbytes) 8192
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 2048

Have i to set data(kbytes), stack(kbytes), memory(kbytes) to unlimited ?
Can you explain me what means this three mentioned above parameters ?
Simon Wickham_6
Regular Advisor

Re: Ora-4030 HELP!

Hi

You need to increase process memory quota. Please check for Memory Leak and also check the sort area size.

Regards,
Simon
Hein van den Heuvel
Honored Contributor

Re: Ora-4030 HELP!

>> Some adds from our box ->

That looks fine, but this is 99.9% sure NOT to be a Unix limitation, but a volunteer restriction by Oracle following your, perhaps unintentional, instructions in init.ora.


>> I have many ORA-4030(can not allocate memory) errors in alert.log

What is the EXACT error message?
It should indicate a byte size and the type of memory.

>> Can you say why Oracle can't allocate memory from this 2Gb free piece ???

Because the Oracle init params told it exactly how much it could use and no more than that.

>> We set WORKAREA_SIZE_POLICY = auto,
PGA_AGGREGATE_TARGET = 2Gb

There you go. Oracle was told not to use more than 2gb for PGA even if there was more need and more avaiable.

Similar things happen in the SGA.
Do a SHOW SGA, there you'll see a handfull more limitations that oracle was given: buffers, fixed pool, shared pool.
Now ask the Oracle instance how much is being used.

hth,
Hein.
Oleg_60
Occasional Visitor

Re: Ora-4030 HELP!


--What is the EXACT error message?
--It should indicate a byte size and the
--type of memory.

ORA-04030: out of process memory when trying to allocate 4096 bytes (callheap,kdbmal allocation)
ORA-04030: out of process memory when trying to allocate 8389656 bytes (pga heap,redo read buffer)
ORA-04030: out of process memory when trying to allocate 560 bytes (callheap,kcbtmal allocation)

Type of memory is PGA.


Hein van den Heuvel
Honored Contributor

Re: Ora-4030 HELP!



So... can you try an increeased PGA_AGGREGATE_TARGET ?

Apparently you have the memory for more, but still need to tell Oracle that it is ok to use more.

Hein.
Yogeeraj_1
Honored Contributor

Re: Ora-4030 HELP!

hi,

can you please also post information on the following:
a. Dynamic performance view:
select name,to_char(decode( unit,'bytes', value/1024/1024, value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit
from v$pgastat
/


b. The effectiveness of the PGA workareas:
SELECT
case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
else to_char(low_optimal_size/1024/1024,'999999') ||
'mb <= PGA < ' ||
(high_optimal_size+1)/1024/1024|| 'mb'
end pga_size,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram
where total_executions <> 0
order by low_optimal_size
/

C. For each PGA_AGGREGATE_TARGET size what the estimated PGA cache hit would be (all work done in memory gives 100%) and how many bytes it estimates would be read/written to temporary space with that size:
select
trunc(pga_target_for_estimate/1024/1024)
pga_target_for_estimate,
to_char(pga_target_factor * 100,'999.9') ||'%'
pga_target_factor,
trunc(bytes_processed/1024/1024) bytes_processed,
trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
to_char(estd_pga_cache_hit_percentage,'999') || '%'
estd_pga_cache_hit_percentage,
estd_overalloc_count
from v$pga_target_advice
/


that would give a clearer idea on your system.


kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ariel Cary
Frequent Advisor

Re: Ora-4030 HELP!

Hi Oleg,

This sounds much like a problem I had on Itanium platform HP-UX 11.23 with Oracle 9.2.0.6. To confirm your're in this situation check the amount of swap space you have configured in your system. It must be at least 24GB (the size of your RAM). But from my experience I your advice it to be 36GB. It may sound an exaggeration but I learned HP-UX needs to reserve swap memory each time a process is created, adn if you do not have free swap to allocate you will get this ORA-4030 though you have free RAM. Configure more swap space if you're below the suggested value and try it again to see if it reproduces. Also, be adviced Oracle requires considerable more memory per process, see with glance how much physical memory oracle processes are using.

Regars,

Ariel