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 <[EMAIL PROTECTED]> 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" > <[EMAIL PROTECTED]> > 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:[EMAIL PROTECTED] > > 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" > <[EMAIL PROTECTED]> > > 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:[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. > > > > > > > > > > -- > > -- > 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). ===== Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "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: [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).
