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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jeroen van Sluisdam
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
