Re: RE: large pl/sql table sucking up all memory on a server
i know about the limit clause. I just want to keep someone else from bringing down an instance. I think Ill get a taser and fry the next person who does it. :) From: zhu chao [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 10:34:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: RE: large pl/sql table sucking up all memory on a server I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:34 AM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: [EMAIL PROTECTED] 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
Re: RE: large pl/sql table sucking up all memory on a server
That works. I prefer thumb presses, they worked for the Inquisition and they lasted 500 years... dr Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - I think Ill get a taser and fry the next person who does it. :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: large pl/sql table sucking up all memory on a server
Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: large pl/sql table sucking up all memory on a server
Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Khedr, Waleed 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: RE: large pl/sql table sucking up all memory on a server
we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: [EMAIL PROTECTED] 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: RE: large pl/sql table sucking up all memory on a server
3 million records in a forall statement. we are bringing on temps and you know how that goes... Im hoping I can set a parameter somewhere to keep anyone from bringing down a server. such as 'memory for pl/sql table area limit hit' errors out what he is doing. i guess not :( From: Khedr, Waleed [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:29:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Khedr, Waleed 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: [EMAIL PROTECTED] 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: large pl/sql table sucking up all memory on a server
Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject:large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: RE: large pl/sql table sucking up all memory on a server
it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. From: Bobak, Mark [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 01:24:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject: large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). encoded content removed -- binaries not allowed by ListGuruContent-Type: application/ms-tnef; The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
Re: RE: large pl/sql table sucking up all memory on a server
Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: [EMAIL PROTECTED] 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: 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).
Re: RE: large pl/sql table sucking up all memory on a server
Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:03 PM Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM Subject: Re: RE: large pl/sql table sucking up all memory on a server we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: [EMAIL PROTECTED] 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858
Re: RE: large pl/sql table sucking up all memory on a server
FYI. The USPS delivery just (10 minutes ago) arrived with my copy of Mastering Oracle PL/SQL Practical Solutions, which I ordered from Book Pool, at: http://www.bookpool.com/.x/mzttmcaj4i/sm/1590592174 As you can see, its not available yet on Amazon: http://www.amazon.com/exec/obidos/tg/detail/-/1590592174 /qid=1072734291/sr=1-1/ref=sr_1_1/102-9815245-5757732?v=glances=books If you look on pg249, it discusses bulk collect and pga memory, e.g. I crashed my database session (and shortly thereafter my laptop) because insufficient memory was available to hold the set of 100 employee records. ... This is where a pipelined solution can help. I'm not sure if this will help in this case, but at least I hope this opens the discussion to include a 'new' reference on PL/SQL. Happy Holidays. Regards, Mike Thomas --- Tanel Poder [EMAIL PROTECTED] wrote: Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:03 PM Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM Subject: Re: RE: large pl/sql table sucking up all memory on a server we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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
Re: RE: large pl/sql table sucking up all memory on a server
This is probably old hat for you, but given it's Unix (Sun) and it's a client process, wouldn't you be able to use ulimit to stop memory allocation growing past a certain size? The other thing I'd try is to limit memory through the resource control in Oracle. But that is highly version dependent and I'm not sure which version you running. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:39 AM it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: RE: large pl/sql table sucking up all memory on a server
I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:34 AM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: [EMAIL PROTECTED] 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: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
