Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] pl/sql tables in pga and ora-4030 was pga_aggregate_target and a memory leak
I contined testing with pl/sql testprogram and found some interesting prove about this 1 gb limit for pga with pat set.
All tests are done on hpux11.11 9.2.0.4
Testprogram
create or replace procedure testarray( psize number ) as
begin
declare
TYPE nAllotment_tabtyp IS TABLE OF number
INDEX BY BINARY_INTEGER;assarray nAllotment_tabtyp;
uitleg := 'insert i2= ' || i;
assarray2(i) := i;
*/
end loop;
/*
EXCEPTION
WHEN OTHERS THEN
dbms_output.enable(20000); dbms_output.put_line(' Exception raised ' || uitleg );*/
.................................
Quotes from my last update to the tar:
When setting all manual
I see the pga going over 2Gb and the showing negative numbers by looking
at v$sessstat, os-level I only have top and like I mentioneed earlier you
see
that going up 2 Gb also and further to 4Gb not above this limit as
expected!!
Notice I am now testing with a hpux setting datasegment 4Gb (ulimit 4194303)
Test 1: workarea_size_policy=manual pat=0
After a few minutes running
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 81312
session uga memory max 112960
session pga memory 2132275152
session pga memory max 2132275152
Still monitoring this, the amount seem to stuck after 15 minutes or so at
this
4Gb (value of top)
end value:
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 81312
session uga memory max 112960
session pga memory -154903592
session pga memory max -154903592
After more then 30 minutes finally
it crashes agaIN
VU_2>exec testarray( 100000000 );
begin testarray( 100000000 ); end;
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error ORA-06512: at "VRIJ_UIT.TESTARRAY", line 14 ORA-06512: at line 1
23-JAN-04 22:40:10 Text continued from previous action...
appropriate?
Output from top
Memory: 3733508K (3051156K) real, 5720660K (4872688K) virtual, 70976K free
Page# 1/14
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU
COMMAND
0 ? 23640 oracle 128 20 4116M 2436M sleep 10:00 2.54 2.53
oracleVU_2
New info : Test 2: workarea_size_policy=auto pat=200M same shared_pool of
500m
I am utterly convinced this is all done in pga outside shared pool so
enlarging this only gets me the same problem sooner
Ahh and now after already 2.5 minutes I get a similar problem
at the 1Gb limit
U_2>exec testarray( 100000000 );
begin testarray( 100000000 ); end;
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error ORA-06512: at "VRIJ_UIT.TESTARRAY", line 14 ORA-06512: at line 1
and looks familiar with other bugs filed
Regards,
Jeroen
Received on Fri Jan 23 2004 - 16:52:31 CST
![]() |
![]() |