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" <ORACLE-L_at_fatcity.com>
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" <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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ryan
INET: ryan.gaffuri_at_cox.net
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 Sat Jan 10 2004 - 12:04:24 CST
