Hi,
I followed you advice and made small testprogram see below:
I only get the ora-06500 which I have had before in the original program as
A followup error so to me it seems to be reproducible.
In manuals I only find that the index
Of a pl/sql table cannot be more than 2**31, which is something like
2.000.000.000
I found on metalink some posts which suggested this might be functioning
better enlarging shared pool and also max user data (ulimit of oracle)
We increased maxdseg in the ux-kernel parameters to 4Gb to increase this
limit (was 2Gb). There is 4Gb available of physical memory in the box.
Using a shared pool 0f 500Mb, pga_aggregate_target 100Mb
setting 100.000.000 elements -> 22 minutes and it fails
Exception raised insert i= 68102540
Using 1 table, shared pool 2Gb
setting 1.000.000 elements -> 14 seconds
setting 10.000.000 elements -> 282 seconds
setting 100.000.000 elements -> 12 min 24 seconds fails
21:54:37 VU_2>exec testarray( 100000000 );
Exception raised insert i= 17613935
Running with a second table involved: after 17 minutes 29 seconds
22:40:20 VU_2>exec testarray( 100000000 );
Exception raised insert i= 8806960
So it is reduced by 50%. But why is the result with a smaller sga
Giving me more elements set?
Watching the oracle serverprocess with top utility
I see the memory resident part
Most of the time around 2600M but more interesting the process is
Most of the time sleeping, what the heck is it doing all the time before
Going into an error?
1 ? 4728 oracle 128 20 4116M 2626M sleep 7:49 1.20 1.20
oracleVU_2
I cannot find any other restriction then 2**31 limit on the index.
I don't know how to calculate how much memory this is taking because
watching sqlworkarea of pgastat doesn't show any useful info in this case.
But it looks to I'm hitting a limit somehow.
Can somebody explain which limit this is and how is it composed or
influenced (temp, sga ?) ?
Is this reproducible on other systems / versions ?( Metalink post reports
This also on early 8.1.x versions , I couldn't find this on 9.x versions)
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;
-----Oorspronkelijk bericht-----
Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
Verzonden: dinsdag 6 januari 2004 16:49
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: pga workarea and ora-04030
The workarea_policy stuff does not apply
to things like pl/sql tables, only to tuneable
memory. Given that you don't have the
problem when you disable p_a_t and w_p,
it may be that there is some buggy event
occurring where the workarea_policy code
is being infringed by an abuse of pga memory.
You could try setting up test cases where
you use a pl/sql loop to build a pl/sql table.
Make it a procedure with an input parameter
that is the table size, and see how big the table
has to before the procedure crashes. Fiddle
with the p_a_t, and w_p (they can be set
separately) to see if the crash point moves.
This may give you (or Oracle Corp) some clues.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 06, 2004 2:54 PM
>
> Hi,
>
> I have posted a problem before which I can only solve with a workaround
but
> because I'm not getting
> satisdactory answers from Oracle I'm trying alternatives.
> - problem is a batch pl/sql package which ends with ora-4030
> - batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4
> recently
> - setting pga_aggegrate_target=0 and workarea_size_policy=manual
> solves the error
> - This is the only batch which results in errors
>
> Due to recent posts I have tried smm_max_size set to 100Mb and I still get
> this error. All of the following
> is done with _smm_max_size set and first setting pga_aggegrate_target=50M
> and workarea_size_policy=auto
>
>
> Monitoring v$sql_workarea_active leads me a max. use of 532Kb. The
figures
> below didn't change during the batch
> I only saw temporarily another workarea for the same sid
> OPTYPE ACTTIME WA_SIZE EXP_SIZE ACT MAXMEM
PASS
> TEMPSEG TBLSP
> ---------- ---------- ---------- ---------- ---------- ---------- --------
--
> ---------- -------------------------------
> GROUP BY ( 1378396893 532480 532480 532480 532480
0
>
> 14:30:44 SQL> /
>
> NAME
VALUE
> UNIT
> ---------------------------------------------------------------- ---------
-
> ------------
> aggregate PGA target parameter
104857600
> bytes
> aggregate PGA auto target
6553600
> bytes
> global memory bound
104857600
> bytes
> total PGA inuse
1105825792
> bytes
> total PGA allocated
1129529344
> bytes
> maximum PGA allocated
1135382528
> bytes
> total freeable PGA memory
458752
> bytes
> PGA memory freed back to OS
1303117824
> bytes
> total PGA used for auto workareas
737280
> bytes
> maximum PGA used for auto workareas
1163264
> bytes
> total PGA used for manual workareas
0
> bytes
>
> NAME
VALUE
> UNIT
> ---------------------------------------------------------------- ---------
-
> ------------
> maximum PGA used for manual workareas
16384
> bytes
> over allocation count
979
> bytes processed
3141169152
> bytes
> extra bytes read/written
0
> bytes
> cache hit percentage
100
> percent
>
> a) Why do I see manual workarea used despite workarea_size_policy=auto
?
> It is a test environment with just me and a developer on it
> b) Total pga used reports as 737Kb and total pga allocated finishes on
> 1.1Gb How can I relate this to the workarea?
> c) What's the exact connection with the _smm_max_size?
>
> When I increase the pga_aggregate_target to 2Gb and the smm_max_size also
> the program fails around
> the following numbers from pgastat
> QL> /
>
> NAME
VALUE
> UNIT
> ---------------------------------------------------------------- ---------
-
> ------------
> aggregate PGA target parameter
2147483648
> bytes
> aggregate PGA auto target
1895003136
> bytes
> global memory bound
2097152000
> bytes
> total PGA inuse
41918464
> bytes
> total PGA allocated
1137232896
> bytes
> maximum PGA allocated
1137249280
> bytes
> total freeable PGA memory
1074987008
> bytes
> PGA memory freed back to OS
131072
> bytes
> total PGA used for auto workareas
0
> bytes
> maximum PGA used for auto workareas
0
> bytes
> total PGA used for manual workareas
0
> bytes
>
> NAME
VALUE
> UNIT
> ---------------------------------------------------------------- ---------
-
> ------------
> maximum PGA used for manual workareas
2347008
> bytes
> over allocation count
0
> bytes processed
1603424256
> bytes
> extra bytes read/written
6708224
> bytes
> cache hit percentage
99.58
> percent
>
> d) What is remarkable that auto workareas are now on 0, the manual
stuff
> might be by another testuser, the total pga_allocated is just a little bit
> higher.
>
> I am completely confused by now and I hope you can shed some light on
this.
>
> Regards,
>
> Jeroen
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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).
--
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).
