Need advice on ORA-04030 and pga_aggregate

From: <harvinder76_at_gmail.com>
Date: 14 May 2007 13:23:47 -0700
Message-ID: <1179174227.483798.192580@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

max(pga_used_mem) max_pga_used_mem
, max(pga_alloc_mem) max_pga_alloc_mem
, max(pga_max_mem) max_pga_max_mem

from v$process
/
19164289 40582953 3386319473

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

PSEUDO          0          0          0          0
oracle_at_qalin1 (PMON)   213725  366221  0          366221
oracle_at_qalin1 (PSP0)    212937  366221  0          366221
oracle_at_qalin1 (MMAN)   220937  366221  0          366221
oracle_at_qalin1 (DBW0)   19164289          40582953          131072
44318505
oracle_at_qalin1 (LGWR)   10902229          23106685          196608
23565437
oracle_at_qalin1 (CKPT)    308505  1657617            1114112
2771729
oracle_at_qalin1 (SMON)   1319161            3118733
1507328            3577485
oracle_at_qalin1 (RECO)   433237  1087117            65536   1087117
oracle_at_qalin1 (CJQ0)     616505  1676941            786432  2070157
oracle_at_qalin1 (MMON)  1220449            3249753
1638400            3446361
oracle_at_qalin1 (MMNL)   218349  431757  0          431757
oracle_at_qalin1 (D000)     651201  664177  0          1218189
oracle_at_qalin1 (S000)     5319113            6496881            393216
739582577
oracle_at_qalin1 (S001)     5378853            7217777
1048576            3380945521
oracle_at_qalin1 (S002)     8128853            10035825          131072
3381273201
oracle_at_qalin1 (S003)     4899257            6496881
1048576            3381600881
oracle_at_qalin1 (S004)     615721  1909361            1179648
3386188401
oracle_at_qalin1 (S005)     611497  1581681            851968  3386253937
oracle_at_qalin1 (S006)     616837  1712753            851968  3386122865
oracle_at_qalin1 (S007)     555533  1516145            851968  3386319473
oracle_at_qalin1 (S008)     610401  1778289            1048576
3386253937
oracle_at_qalin1 (S009)     609297  1974897            1114112
3386122865
oracle_at_qalin1 (J000)      254085  1087117            0
1087117
oracle_at_qalin1 (QMNC)   222077  366221  0          366221
oracle_at_qalin1 (q000)      613877  1414797            262144  1414797
oracle_at_qalin1 (q001)      307561  562829  0          562829

PGA_MAX_MEM for shared servers is ~3GB sometimes, not sure what is causing that big consumption but the current allocated memory looks reasonable

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,

       (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS

FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

LOW_KB          HIGH_KB          OPTIMAL_EXECUTIONS
ONEPASS_EXECUTIONS            MULTIPASSES_EXECUTIONS
2          4          101146391        0          0
4         128       43179   0          0
128       256       50124   0          0
256       512       43867   0          0
512       1024     170969  0          0
1024     2048     10837   0          0
2048     4096     148       0          0
4096     8192     55         0          0
8192     16384   16         0          0
16384   32768   4          0          0

No processing caused anything but optimal executions, there are no one pass or multi pass executions.

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;

TARGET_MB     CACHE_HIT_PERC        ESTD_OVERALLOC_COUNT
75         98         4
150       98         4
300       98         4
450       98         4
600       100       4
720       100       4
840       100       4
960       100       4
1080     100       4
1200     100       3
1800     100       0
2400     100       0
3600     100       0
4800     100       0

PGA hit is 100% at 600MB but overalloc count is not 0 until 1800MB.

SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT
WHERE name like 'workarea exec%');

PROFILE          CNT      PERCENTAGE
workarea executions - optimal     101481578        100
workarea executions - onepass   0          0
workarea executions - multipass 0          0

What can be the possible reason for this error and how we determine what may have caused the maximum memory usage for process to spike to 3GB and since we don't have multiple pass executions what can be the other reasons?

Thanks
--Harvinder Received on Mon May 14 2007 - 15:23:47 CDT

Need advice on ORA-04030 and pga_aggregate_target parameter