[oracle-l] pl/sql tables in pga and ora-4030 was pga_aggregate_target and a memory leak

From: Jeroen van Sluisdam <jeroen.van.sluisdam_at_vrijuit.nl>
Date: Fri, 23 Jan 2004 23:52:31 +0100
Message-ID: <EDEE1EDF71CFD41185CA00E018C40D4802A78477@jody.vrijuit.nl>

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;
  assarray2 nAllotment_tabtyp;
  assarray3 nAllotment_tabtyp;
  uitleg varchar2(100);
begin
  uitleg := 'start loop';
  for i in 1..psize loop
    uitleg := 'insert i= ' || i;
    assarray(i) := i;
/*

    uitleg := 'insert i2= ' || i;
    assarray2(i) := i;
*/
  end loop;
/*
  EXCEPTION
    WHEN OTHERS THEN

      dbms_output.enable(20000);
      dbms_output.put_line(' Exception raised ' || uitleg );

*/
end;
end;

.................................

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

U_2>select pool, sum(bytes) from v$sgastat group by pool; POOL SUM(BYTES)
----------- ----------
large pool 218103808
shared pool 570425344
68143904
AME TYPE VALUE
------------------------------------ -----------

---

pga_aggregate_target big integer 0
23:32:33 SQL> show parameter workarea
It seems impossible that such a simple pl/sql can eat up 4Gb of memory. Other
bugs like 3194895 and docid 3156574 are suggesting a 1Gb pga limit (which might
be raised by changing data segment). There is mentioned a patch also according
to 3194895 to lift this, can you find this patch and see if it might be Text continued in next action...

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 

Elapsed: 00:02:32.62
23:42:57 SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 78464
session uga memory max 143872
session pga memory 1071096624
session pga memory max 1071096624
23:44:17 SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 78464
session uga memory max 143872
session pga memory 1071096624
session pga memory max 1071096624
23:45:08 SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ -----------

---

pga_aggregate_target big integer 209715200 23:47:18 SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ -----------

---

workarea_size_policy string AUTO
So it looks workarea_size_policy is definitely limiting max pga available but strange thing is that the 200Mb for pat is meant to be for sort_area and we
are not using sort_area here just filling an array I don't know why you don't get the same results but this is definitely weird

and looks familiar with other bugs filed Regards,
Jeroen Received on Fri Jan 23 2004 - 16:52:31 CST