| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: pga workarea and ora-04030
Both the PGA and PL/SQL tables are stored in the "data" (a.k.a. "heap")
section of process memory in the Oracle server process.
If using Solaris, running the "pmap" utility against the Oracle server process is useful. I have a posted script named "oramem.sh" posted at "http://www.EvDBT.com/tools.htm" which uses "pmap" to provide a good idea of how much virtual memory an Oracle instance is demanding. For each process, it segregates "text" and "shm" (i.e. shared memory) from "stack" and "data" (i.e. private process memory). A "max()" aggregation is performed on the shared memory and a "sum()" aggregation is performed on the private memory, and the sum is essentially what the Oracle instance is demanding in terms of virtual memory.
Similar utilities exist for Linux and HP-UX...
on 1/10/04 11:04 AM, Ryan at ryan.gaffuri_at_cox.net wrote:
> 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 > >
>>> 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
>>> 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: Tim Gorman INET: tim_at_sagelogix.com 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 - 21:59:25 CST
 |
 |