Re: Need advice on ORA-04030 and pga_aggregate_target parameter

<harvinder76_at_gmail.com> wrote in message news:1179174227.483798.192580_at_u30g2000hsc.googlegroups.com...
> Hi,
>
> Users are getting following errors on 1 of the database machine:
> "Oracle.DataAccess.Client.OracleException ORA-04030: out of process
> memory when trying to allocate 16396 bytes (koh-kghu call ,pmuccst:
> adt/record)"
>
> Configuration: Oracle 10.2.0.1 on RHAT Linux 4, 4GB RAM, SGA-1.2GB,
> PGA -600MB, 10 Shared servers
>
> Following is the info from the dynamic views:
>
> SELECT * FROM V$PGASTAT;
>
> NAME VALUE UNIT
> aggregate PGA target parameter 629145600 bytes
> aggregate PGA auto target 509561856 bytes
> global memory bound 104857600 bytes
> total PGA inuse 63959040 bytes
> total PGA allocated 119723008 bytes
> maximum PGA allocated 3512438784 bytes
> total freeable PGA memory 14090240 bytes
> process count 26
> max processes count 33
> PGA memory freed back to OS 1027295150080 bytes
> total PGA used for auto workareas 1264640 bytes
> maximum PGA used for auto workareas 13465600 bytes
> total PGA used for manual workareas 0 bytes
> maximum PGA used for manual workareas 536576 bytes
> over allocation count 2173
> bytes processed 400043052032 bytes
> extra bytes read/written 0 bytes
> cache hit percentage 100 percent
> recompute count (total) 491550
>
>
> SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM,
> PGA_MAX_MEM
> FROM V$PROCESS;
> PROGRAM PGA_USED_MEM PGA_ALLOC_MEM
> PGA_FREEABLE_MEM PGA_MAX_MEM
>
> oracle_at_qalin1 (S005) 611497 1581681 851968 3386253937
> oracle_at_qalin1 (S006) 616837 1712753 851968 3386122865
> oracle_at_qalin1 (S007) 555533 1516145 851968 3386319473
>

The pga_aggregate_target is a target for the sum of all PGA memory usage - tunable, and non-tunable.

Start up a database, connect 50 sessions to it, and watch the PGA stats as you do so. As each session connects (and creates a process) watch things like:

> aggregate PGA auto target 509561856 bytes
> total PGA inuse 63959040 bytes

The AUTO target will go down, the PGA inuse will go up.

PGA memory is split into tunable (sort, hash, merge bitmap) and untunable (cursor, local data, pl/sql, etc.) You (probably) have a problem with your untunable memory.

> maximum PGA allocated 3512438784 bytes

> maximum PGA used for auto workareas 13465600 bytes
> maximum PGA used for manual workareas 536576 bytes

You don't seem to be doing anything terribly exciting with workareas - 13MB worst case - but you've still managed to allocated at some moment in time 3.5GB, which is probably the moment at which you got the ORA-04030, since you have 4GB of RAM and a 1.2GB SGA. Taking a wild guess at the output:

    (koh-kghu call ,pmuccst: adt/record)"

ADT is usually Abstract Data Type - I would guess that someone has a pl/sql loop that populates an array of objects; or used a select with table cast to do something similar. PL/SQL tables are not in the tunable memory, and Oracle can't stop them from crashing the session and jamming your machine.

The fact that multiple shared_servers show the same extreme probably relates to the way the the 'worst case' session probably migrates around the servers and only tips the session over from time to time.

The behaviour, and associated oddities with the stats from v$process, may be related to the changes in code for PGA handling in the upgrade from 9i to 10g - which probably moved some session heaps outside the UGA heap. In 9i, you would probably have found yourself getting ORA-04031 (SGA full) rather than ORA-04030 (machine exhausted) when a session ran away like this.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html