Re: ORA-04030: out of process memory

From: AK <ak_at_ak.org>
Date: Thu, 12 Aug 2004 04:36:51 GMT
Message-ID: <DtCSc.292$aA.12124@news.optus.net.au>

Hey Howard,

PGA_AGGREGATE_TARGET is set to 24MB on this database. I have been trawling through a lot of documentation about memory tuning and there is so much to learn! Since this is an OLTP application that we are running, I have been reading a lot about the library cache and the methods to work out when to change the shared_pool. Also, I have read about the PGA.

 From what I have read, the value that is set is used to control dynamically the amount of memory alloted to sql statements?

my findings are:

• the V$SQL_WORKAREA_HISTOGRAM view can show the way in which work areas were executed. In my case:

OPTIMAL_PERC	ONEPASS_PERC	MULTIPASS_PERC
99.31	0.64	0.05

So this is pretty good.

• if pga_aggregate_target is set then there is no need to manipulate the *area_size parts of the pga that are used as the work area.
• the v$pgastat view shows relevant statistics

Querying this view shows me one interesting thing:

over allocation count = 585806

Meaning that the PGA_AGGREGATE_TARGET setting has not been honored by oracle since more PGA memory has been allocated since the startup of the instance. The PGA_AGGREGATE_TARGET setting needs to be increased according to the v$pga_target_advice view.

• the v$pga_target_advice and v$pga_target_advice_histogram views are useful for statistics used to tune the setting of pga_aggregate_target. I ran this SQL:

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

        ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
        ESTD_OVERALLOC_COUNT

   FROM v$pga_target_advice

and this yielded:

TARGET_MB	CACHE_HIT_PERC	ESTD_OVERALLOC_COUNT
12	97	13323
18	97	13323
24	100	12859
29	100	12753
34	100	12706
38	100	12675
43	100	12640
48	100	12613
72	100	12387
96	100	12214
144	100	11601
192	100	10956

Now apparently when the ESTD_OVERALLOC_COUNT is non zero, this indicates that the PGA_AGGREGATE_TARGET is too small to even meet the minimum PGA memory needs. Yet the query yields a high cache hit percentage! That confuses me...

So on both counts, I see that my 24MB PGA_AGGREGATE_TARGET needs to be increased. I can double or treble it - the v$pga_target_advice doesnt really indicate to me what to set it to.

I dont have anyone to bounce these findings off - so can I get some opinions on this please?

If I have misinterpreted anything here, please do let me know. Ironically, I am enrolled to do a performance tuning course next month!

thanks,
-AbhinavK

Howard J. Rogers wrote:

> On Wed, 11 Aug 2004 07:32:01 GMT, AK <ak_at_ak.org> wrote:
> 

>> Hi
>>
>> I have an application that runs nicely for many days and then
>> eventually it throws this error:
>>
>> java.sql.SQLException: ORA-04030: out of process memory when
>> trying to allocate 57868 bytes (hash-join subh,kllcqas:kllsltba)
>>
>> I have googled and metalinked this error and have learnt the following:
>>
>> - the oracle process which is servicing the session for this app is
>> trying to allocate memory for the PGA and is unable to
>> - this allocation request is governed by the sort_area_size setting
>> - the problem may be rectified by reducing the sort area size to
>> lower the demand on physical memory
>>
>> Does that sound about right?
>>
>> I am confused because the box running oracle (9.2.0.4) is actually
>> showing that 70% of memory buffers are free and that only 5% of swap
>> is used. What worries me is that swap is being used at all when
>> there is so much memory free. This is on RedHat AS with the latest
>> 2.4 kernel as pulled down via up2date.
>>
>> Also, the alert log shows no errors *at all*. What gives? I would
>> have thought that if an oracle process was unable to malloc then it
>> would be in alert log? How come the application is the place where
>> the error appears?
>>
>> thanks,
>> -A

> 
> 
> Just to clarify... given that you are on 9i, are you absolutely certain  
> you are using sort_area_size as the PGA's sizing parameter? The 
> new(ish)  9i alternative is to use the PGA_AGGREGATE_TARGET parameter to 
> have the  PGA managed automatically and dynamically.
> 
> If you aren't using PGA_AGGREGATE_TARGET, then perhaps you should be.
> 
> Regards
> HJR

Received on Wed Aug 11 2004 - 23:36:51 CDT