No longer having the versions to validate this, what
follows might be waffle, but I thought:
v7.0 => full allocated space
v.7.2 => space allocated more intelligently, never
freed
v7.3 => space allocated more intelligently, reusable
when you delete entries
Cheers
Connor
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote: >
> 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).
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.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).
Received on Tue Jan 20 2004 - 23:59:25 CST
