Where does oracle store pl/sql tables? I have run into problems with developers doing massive bulk collects and I have to bounce the entire server...
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, January 10, 2004 12:54 PM > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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).