Re: pga workarea and ora-04030
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 1, 2, 3, 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 1 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_2exec testarray(1); begin testarray(1); 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
Re: pga workarea and ora-04030
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 1, 2, 3, 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 1 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_2exec testarray(1); begin testarray(1); 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
RE: pga workarea and ora-04030
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_2exec testarray(1); begin testarray(1); 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_2exec testarray( 1 ); Exception raised insert i= 17613935 Running with a second table involved: after 17 minutes 29 seconds 22:40:20 VU_2exec testarray( 1 ); 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 sleep7: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
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_2exec testarray( 1 ); Exception raised insert i= 17613935 Running with a second table involved: after 17 minutes 29 seconds 22:40:20 VU_2exec testarray( 1 ); 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 sleep7: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_tabtypIS 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(2); 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
Re: pga workarea and ora-04030
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_2exec testarray( 1 ); Exception raised insert i= 17613935 Running with a second table involved: after 17 minutes 29 seconds 22:40:20 VU_2exec testarray( 1 ); 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 sleep7: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_tabtypIS 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(2); 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
Re: pga workarea and ora-04030
In the UGA, I should think (which also means the SGA if you are running MTS). It can't be in the PGA (ignoring the fact that the UGA is in the PGA for non-MTS) or you couldn't have global pl/sql tables that persist across database calls. 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: Saturday, January 10, 2004 6:04 PM 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 -- 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).
Re: pga workarea and ora-04030
I have seen people bulk collect into pl/sql tables so much data that you cannot even connect to the server. So I'm assuming that ones the UGA fills up, Oracle will allocate whatever unused memory is left on the server for pl/sql tables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 4:04 PM In the UGA, I should think (which also means the SGA if you are running MTS). It can't be in the PGA (ignoring the fact that the UGA is in the PGA for non-MTS) or you couldn't have global pl/sql tables that persist across database calls. 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: Saturday, January 10, 2004 6:04 PM 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 -- 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).
RE: pga workarea and ora-04030
Ryan, As Jonathan mentioned, global PL/SQL tables are allocated from the UGA. In MTS, they will be allocated from the SGA's large pool (or, in the event one has not been defined, which is a big no-no for MTS, then from the shared pool.) Note in this case, if the large_pool is insufficient to satisfy the demands, the user's session will encounter ORA-4031. In dedicated server, the UGA is allocated in the PGA. The user's session will allocate more private memory (by growing the PGA heap) from system memory. If/when the PL/SQL table grows so large as the process can no longer allocate memory from the O/S, then the user's session will encounter ORA-4030. Note the slightly different allocation mechanism, and the different Oracle errors encountered (4031 vs. 4030). Based on the fact that you're encountering ORA-4030, I'd guess that your user is connect via dedicated server. Hope that helps, -Mark PS Note that when this error occurs, if you simply kill the background process of the session that has allocated all that RAM, it should immediately be freed and the problem should clear up. It should not be necessary to bounce the entire instance, just the problem user. -Original Message- From: Ryan [mailto:[EMAIL PROTECTED] Sent: Sat 1/10/2004 4:34 PM To: Multiple recipients of list ORACLE-L Cc: Subject:Re: pga workarea and ora-04030 I have seen people bulk collect into pl/sql tables so much data that you cannot even connect to the server. So I'm assuming that ones the UGA fills up, Oracle will allocate whatever unused memory is left on the server for pl/sql tables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 4:04 PM In the UGA, I should think (which also means the SGA if you are running MTS). It can't be in the PGA (ignoring the fact that the UGA is in the PGA for non-MTS) or you couldn't have global pl/sql tables that persist across database calls. 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: Saturday, January 10, 2004 6:04 PM 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 -- 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). winmail.dat
Re: pga workarea and ora-04030
:= '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(2); 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: Tim Gorman 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).
RE: pga workarea and ora-04030
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_2exec testarray( 1 ); Exception raised insert i= 17613935 Running with a second table involved: after 17 minutes 29 seconds 22:40:20 VU_2exec testarray( 1 ); 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 sleep7: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_tabtypIS 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(2); 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. 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 One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November 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: Tuesday, January 06, 2004 2:54 PM Hi, I have posted a problem before which I can only solve with a workaround but because I'm not getting satisdactory answers from Oracle I'm trying alternatives. - problem is a batch pl/sql package which ends with ora-4030 - batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4 recently - setting pga_aggegrate_target=0 and workarea_size_policy=manual solves the error - This is the only batch which results in errors Due to recent posts I have tried smm_max_size set to 100Mb and I still get this error. All of the following is done with _smm_max_size set and first setting pga_aggegrate_target=50M and workarea_size_policy=auto Monitoring v$sql_workarea_active leads me a max. use of 532Kb. The figures below didn't change during the batch I only saw temporarily another workarea for the same sid OPTYPEACTTIME
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. 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 One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November 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: Tuesday, January 06, 2004 2:54 PM Hi, I have posted a problem before which I can only solve with a workaround but because I'm not getting satisdactory answers from Oracle I'm trying alternatives. - problem is a batch pl/sql package which ends with ora-4030 - batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4 recently - setting pga_aggegrate_target=0 and workarea_size_policy=manual solves the error - This is the only batch which results in errors Due to recent posts I have tried smm_max_size set to 100Mb and I still get this error. All of the following is done with _smm_max_size set and first setting pga_aggegrate_target=50M and workarea_size_policy=auto Monitoring v$sql_workarea_active leads me a max. use of 532Kb. The figures below didn't change during the batch I only saw temporarily another workarea for the same sid OPTYPEACTTIMEWA_SIZE EXP_SIZEACT MAXMEM PASS TEMPSEG TBLSP -- -- -- -- -- -- -- -- --- GROUP BY ( 1378396893 532480 532480 532480 532480 0 14:30:44 SQL / NAME VALUE UNIT - - aggregate PGA target parameter 104857600 bytes aggregate PGA auto target 6553600 bytes global memory bound 104857600 bytes total PGA inuse 1105825792 bytes total PGA allocated 1129529344 bytes maximum PGA allocated 1135382528 bytes total freeable PGA memory 458752 bytes PGA memory freed back to OS 1303117824 bytes total PGA used for auto workareas 737280 bytes maximum PGA used for auto workareas 1163264 bytes total PGA used for manual workareas 0 bytes NAME VALUE UNIT - - maximum PGA used for manual workareas 16384 bytes over allocation count 979 bytes processed 3141169152 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent a) Why do I see manual workarea used despite workarea_size_policy=auto ? It is a test environment with just me and a developer on it b) Total pga used reports as 737Kb and total pga allocated finishes on 1.1Gb How can I relate this to the workarea? c) What's the exact connection with the _smm_max_size? When I increase the pga_aggregate_target to 2Gb and the smm_max_size also the program fails around the following numbers from pgastat QL / NAME VALUE UNIT - - aggregate PGA target parameter 2147483648 bytes aggregate PGA auto target 1895003136 bytes global memory bound 2097152000 bytes total PGA inuse 41918464 bytes total PGA allocated 1137232896 bytes maximum PGA allocated 1137249280 bytes total freeable PGA memory 1074987008 bytes PGA memory freed back to OS 131072 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 0 bytes total PGA used for manual workareas 0 bytes NAME VALUE UNIT - - maximum PGA used for manual workareas 2347008 bytes over allocation count 0 bytes processed 1603424256 bytes extra bytes read/written 6708224 bytes cache hit percentage 99.58 percent d) What is remarkable that auto workareas are now on 0, the manual stuff might be by another testuser, the total pga_allocated is just a little bit higher. I am completely confused by now and I hope you can shed some light on this. Regards, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED]
Re: pga workarea and ora-04030
When I increase the pga_aggregate_target to 2Gb and the smm_max_size also the program fails around the following numbers from pgastat I'm not advocating fiddling with hidden parameters here, but there is one more parameter which limits PGA usage, it's _pga_max_size and defaults to 200M. Anyway, go with Jonathan's recommendation and try to allocate a large PL/SQL table to test whether it fails. Btw, is your batch job running in serial mode or with parallel slaves? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).