Re: pga workarea and ora-04030

If you want to work out how much difference there is in different code paths, then you have to do some very patient testing.

Run your test program for lots of different array sizes, say 10000, 20000, 30000, and so on up to 100M. On each run, disconnect and reconnect your session, and check v$sesstat for pga and uga memory usage before and after each run, as well as the memory reported from the O/S (I think ps -al and look at the RSS figure for your shadow process is the HP-UX option - but someone may have a better idea).

You then need to run a second set of tests where the size of an array element is significantly different from the first test - e.g. test1 uses a varchar2(32) test2 uses varchar2(1000) (and the third test uses varchar2(8000) ....). Then you may be able to figure out the significant differences in handling

It is quite likely that there is a different code path for allocating and freeing memory as you change versions of Oracle, or change parameters within a version; and it is quite possible that a piece of code for handling arrays changed from version to version - and any change could have introduced an unreasonable error.

In passing, I thought the 'array is a fully pre-allocated' was a version 6 thing that got fixed in version 7. I would be amazed if arrays had gone backwards a step - it's easy enough to check: change your test to populate just element 1 and element 100000000 and see if your session still crashes.

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

Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

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" <ORACLE-L_at_fatcity.com> Sent: Monday, January 12, 2004 11:44 AM

> Jonathan,
>
> Thanks for your answer this clarifies a bit more
> But it still bothers me that this program can swallow
> 4Gb of physical memory and 4 Gb of swap and it is still not
> enough. You explain that the memory of pl/sql tables is not in
> the sga so that's clear now.
>
> What still bothers me is that my original program works fine
> with pga_target = 0 and wa-size-policy=manual
> When I try this with this test-program it fails (see below)
> VU_2>exec testarray(100000000);
> begin testarray(100000000); end;
>
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-04030: out of process memory when trying to allocate 8144 bytes
(cursor
> work he,qesaQBInit:buffer)
> ORA-06508: PL/SQL: could not find program unit being called
> ORA-06512: at "SYS.DBMS_OUTPUT", line 127
> ORA-06512: at "VRIJ_UIT.TESTARRAY", line 23
> ORA-06500: PL/SQL: storage error
> ORA-06512: at line 1
>
> Somehow these setting influence the way the pl/sql program works.
> This testprogram is clearly not enough to explain this behaviour. Because
we
> Use quite some pl/sql I would like to know more because it could happen
> Maybe with other programs.
>
> Oracle 7 the same code runs fine also. I read a post that the difference
for
> pl/sql tables is that they are now implemented as fully allocated arrays
in
> memory whether they were implemented in oracle 7 and chained linked lists.
>
> Obviously this takes more memory but why do these 2 settings play such a
> role? Is the memory involved differently when using these settings?
> Can I monitor specific memory usage with these setting and how should this
> be done on HPUX?
>
> Regards,
>
> Jeroen
> -----Oorspronkelijk bericht-----
> Van: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Verzonden: Saturday, January 10, 2004 6:54 PM
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: Re: pga workarea and ora-04030
>
>
> I think what you've demonstrated is
> that pl/sql tables are not limited by
> pga-aggregate target, and that a pl/sql
> table can grow until it has taken up all
> the available memory on your machine.
>
> I'd guess that each element in your table
> takes about the same space - with a little
> error round the edges - so you can have
> 17.6M rows before you are out of memory -
> either as two tables of 8.8M or one table
> of 17.6M.
>
> The sleep time is probably because you start
> going to SWAP and your session spends time
> dumping real memory to disc.
>
> When the SGA is 1.5G smaller, that frees up
> an extra 1.5G of memory for you to use as
> PGA - so you get lots more entries in the
> table before you run out of memory.
>
>
> 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
>
>
> Next public appearance2:
> March 2004 Hotsos Symposium - Keynote
> March 2004 Charlotte NC - OUG Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
>
>
> 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" <ORACLE-L_at_fatcity.com>
> Sent: Friday, January 09, 2004 10:34 PM
>
>
> > 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:jonathan_at_jlcomp.demon.co.uk]
> > 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.
> >
> >
>
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).