Re: ORA-04030: out of process memory

• Post for FREE via your newsreader at post.usenet.com ****

Hi

> 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.

PGA_AGGREGATE_TARGET limits the amount of memory used instance-wide for all PGAs. i.e. if you have 50 processes, teoretically only about 500KB of PGA will be given to each process. Therefore 24MB is too low!

> - 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.

Correct, if you didn't set WORKAREA_SIZE_POLICY to MANUAL.

> - 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.

PGA_AGGREGATE_TARGET is not an absolute limit, it's just a target. Therefore if its value is too low, and as I wrote before it is in your case..., Oracle will simply use much memory.

> 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...

Your high cache hit percentage has been generated with a larger PGA, not with the amount of memory specified with PGA_AGGREGATE_TARGET. So there is no contradiciton.

> 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.

Don't trust too much to the advisors...

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=