Re: RE: pga_aggregate_target and a memory leak
where did you hear that oracle 10g was written almost entirely outside the US? what critical problems have you had with 9i? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 23, 2004 10:19 PM On 01/23/2004 07:54:25 PM, Arnold, Sandra wrote: We still have an 8.1.5 database as well as two 8.1.7.4 and one 9.2.04 databases. We are planning on upgrading our 8i databases this year. The rate we are going it probably will be two years before we get to 10g. Sandra That would be a very courageous thing to do. I'm not sure that 10g will be stable enough for a big production database in 2 years. Experience with 9i tells us that nothing before 9.2.0.4 was not fit for a real production use. If I remember correctly, 9i is out for more then 2 years now. Have in mind that 10g is the first version that was written almost entirely outside of the US. I wouldn't rush into upgrading to 10g, if I were you. And the rumor is that 10g is so unstable that even with the standards lowered so much, Oracle doesn't want to release like that. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: RE: pga_aggregate_target and a memory leak
Personal communication. On 01/24/2004 06:44:24 AM, Ryan wrote: where did you hear that oracle 10g was written almost entirely outside the US? what critical problems have you had with 9i? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 23, 2004 10:19 PM On 01/23/2004 07:54:25 PM, Arnold, Sandra wrote: We still have an 8.1.5 database as well as two 8.1.7.4 and one 9.2.04 databases. We are planning on upgrading our 8i databases this year. The rate we are going it probably will be two years before we get to 10g. Sandra That would be a very courageous thing to do. I'm not sure that 10g will be stable enough for a big production database in 2 years. Experience with 9i tells us that nothing before 9.2.0.4 was not fit for a real production use. If I remember correctly, 9i is out for more then 2 years now. Have in mind that 10g is the first version that was written almost entirely outside of the US. I wouldn't rush into upgrading to 10g, if I were you. And the rumor is that 10g is so unstable that even with the standards lowered so much, Oracle doesn't want to release like that. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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_aggregate_target and a memory leak
what are the specs of that box? what does it cost? Ive never worked on something that big. how big is the database your working on? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:24 PM So, my intention to set P_A_T to 140G on a new datawarehouse is ill-advised? I'm not kidding, by the way. The Sun E15K belonging to the project I'm currently working on (purportedly) has 160G of RAM. It is still in the box, so I'm not believing anything until I type prtconf... I wasn't planning to use more than 10G or so for SGA, and that much only because I can... wee-hah!... Any thoughts? on 1/21/04 3:14 PM, Jonathan Lewis at [EMAIL PROTECTED] wrote: A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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). -- 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_aggregate_target and a memory leak
I read the paper about the adaptive memory and how it gets wasted, but with 10G SGA you can afford to be a bit wasteful. I would set workarea_size_policy to manual and then set sort_area_size to 32M and hash area size to 128M. With the memory sizes you mentioned, there shouldn't be any problems. Anythingadaptive, on the other hand, is an overhead. That overhead is implemented in the oracle server processes (ora_s000...) and any bug has a great potential to waste more then a little CPU. Also, you don't want segment space management to be set on AUTO in your tablespaces because DW type databases are not update intensive and you don't want to be reading any more blocks then necessary because of the free space in the block that is left to accommodate updates that will never come. Also, if you can get your data files on a file system that supports direct I/O, it would be nice. VxFS is the first thing that comes to mind...If you manage to make it happen, set filesystemio_options parameter to setall, so that oracle will use both asynchronous and direct I/O. You should also minimize the number of DML_LOCKS that you wish to allow and consider using table locking ( row_locking=intent ), to shorten the path through the oracle code. On 01/22/2004 11:24:41 PM, Tim Gorman wrote: So, my intention to set P_A_T to 140G on a new datawarehouse is ill-advised? I'm not kidding, by the way. The Sun E15K belonging to the project I'm currently working on (purportedly) has 160G of RAM. It is still in the box, so I'm not believing anything until I type prtconf... I wasn't planning to use more than 10G or so for SGA, and that much only because I can... wee-hah!... Any thoughts? on 1/21/04 3:14 PM, Jonathan Lewis at [EMAIL PROTECTED] wrote: A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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_aggregate_target and a memory leak
Kirti, So is April 12th the latest date you heard for when 10g might be released?? Because it was the end of 2003, but I didn't know it had slipped all the way into April... -Original Message- Kirtikumar Deshpande Sent: Wednesday, January 21, 2004 7:24 PM To: Multiple recipients of list ORACLE-L Thanks, Ryan. Yes, it is on OWI, for those who are new to OWI. Covers OWI from 8i to 10g. Co-authored with Richmond Shee and K.Gopalakrishnan. It will not be out till 10g goes production. Unfortunately, April 12th is not firm. 10g changes Regards, - Kirti --- Ryan [EMAIL PROTECTED] wrote: Im assuming its his wait interface book. Ill get it as soon as it comes out. Hopefully it will be as good as his other tuning book. Is the April 12th date firm? Now the bigger question: Will it be out before the 10G database? http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1074724628/ sr=1 -2/ref=sr_1_2/104-1361632-8254324?v=glances=books - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 5:14 PM A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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
RE: Re: pga_aggregate_target and a memory leak
Hi, The bug I saw on the course was 3194895, but I am not able to see this one Myself with my account, maybe some internal use only, but take a look at Docs 3156574 or 2790318 this looks similar The teacher also mentioned a patch to lift the 1GB pga limit to 5Gb But I am not able to find this also. I will email him To ask for details. Anybody else experience with this or this patch? Regards, Jeroen -Oorspronkelijk bericht- Van: Arnold, Sandra [mailto:[EMAIL PROTECTED] Verzonden: vrijdag 23 januari 2004 3:19 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Re: pga_aggregate_target and a memory leak I am interested in the bug number. Currently am having memory problems that may be related to the pga. Sandra -Original Message- Sent: Thursday, January 22, 2004 5:09 PM To: Multiple recipients of list ORACLE-L Yes I have and still have a problem with pga memory leak When using pl/sql tables. I'm on 9i performance and tuning course at oracle Now and discussed this with the teacher. He went looking and found a bug Stating that on 9i (9.2.0.2 and further) there seems to be a limit on total pga per process of 1Gb. Setting pat=0 and work_area_size manual gave me a workaround for my production problem but with a test of just a simple Got a decent explanation today that pat=0 gives me more memory for pl/sql Tables because there are always in pga and pat is about sort areas so setting pat=0 gives more memory and less possibility of not having enough. Pl/sql procedure assigning values to an array of number keeps reproducing A pl/sql storage error also with pat=0 and wasp=manual. I left the bug number in my notes, can get that tomorrow if somebody is interested. Jeroen -Oorspronkelijk bericht- Van: Ryan [mailto:[EMAIL PROTECTED] Verzonden: donderdag 22 januari 2004 11:05 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: Re: pga_aggregate_target and a memory leak Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see
Re: RE: pga_aggregate_target and a memory leak
i heard tom kyte speak in december. He said first quarter 2004 for solaris. most people seem to still be on 8i. We have both 8i and 9i instance here. It will probably be a year before many employers are using it anywy. From: Grabowy, Chris [EMAIL PROTECTED] Date: 2004/01/23 Fri PM 03:24:45 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: pga_aggregate_target and a memory leak Kirti, So is April 12th the latest date you heard for when 10g might be released?? Because it was the end of 2003, but I didn't know it had slipped all the way into April... -Original Message- Kirtikumar Deshpande Sent: Wednesday, January 21, 2004 7:24 PM To: Multiple recipients of list ORACLE-L Thanks, Ryan. Yes, it is on OWI, for those who are new to OWI. Covers OWI from 8i to 10g. Co-authored with Richmond Shee and K.Gopalakrishnan. It will not be out till 10g goes production. Unfortunately, April 12th is not firm. 10g changes Regards, - Kirti --- Ryan [EMAIL PROTECTED] wrote: Im assuming its his wait interface book. Ill get it as soon as it comes out. Hopefully it will be as good as his other tuning book. Is the April 12th date firm? Now the bigger question: Will it be out before the 10G database? http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1074724628/ sr=1 -2/ref=sr_1_2/104-1361632-8254324?v=glances=books - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 5:14 PM A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande
RE: RE: pga_aggregate_target and a memory leak
We still have an 8.1.5 database as well as two 8.1.7.4 and one 9.2.04 databases. We are planning on upgrading our 8i databases this year. The rate we are going it probably will be two years before we get to 10g. Sandra -Original Message- Sent: Friday, January 23, 2004 5:39 PM To: Multiple recipients of list ORACLE-L i heard tom kyte speak in december. He said first quarter 2004 for solaris. most people seem to still be on 8i. We have both 8i and 9i instance here. It will probably be a year before many employers are using it anywy. From: Grabowy, Chris [EMAIL PROTECTED] Date: 2004/01/23 Fri PM 03:24:45 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: pga_aggregate_target and a memory leak Kirti, So is April 12th the latest date you heard for when 10g might be released?? Because it was the end of 2003, but I didn't know it had slipped all the way into April... -Original Message- Kirtikumar Deshpande Sent: Wednesday, January 21, 2004 7:24 PM To: Multiple recipients of list ORACLE-L Thanks, Ryan. Yes, it is on OWI, for those who are new to OWI. Covers OWI from 8i to 10g. Co-authored with Richmond Shee and K.Gopalakrishnan. It will not be out till 10g goes production. Unfortunately, April 12th is not firm. 10g changes Regards, - Kirti --- Ryan [EMAIL PROTECTED] wrote: Im assuming its his wait interface book. Ill get it as soon as it comes out. Hopefully it will be as good as his other tuning book. Is the April 12th date firm? Now the bigger question: Will it be out before the 10G database? http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1074724628/ sr=1 -2/ref=sr_1_2/104-1361632-8254324?v=glances=books - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 5:14 PM A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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
Re: RE: pga_aggregate_target and a memory leak
On 01/23/2004 07:54:25 PM, Arnold, Sandra wrote: We still have an 8.1.5 database as well as two 8.1.7.4 and one 9.2.04 databases. We are planning on upgrading our 8i databases this year. The rate we are going it probably will be two years before we get to 10g. Sandra That would be a very courageous thing to do. I'm not sure that 10g will be stable enough for a big production database in 2 years. Experience with 9i tells us that nothing before 9.2.0.4 was not fit for a real production use. If I remember correctly, 9i is out for more then 2 years now. Have in mind that 10g is the first version that was written almost entirely outside of the US. I wouldn't rush into upgrading to 10g, if I were you. And the rumor is that 10g is so unstable that even with the standards lowered so much, Oracle doesn't want to release like that. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: pga_aggregate_target and a memory leak
Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful
Re: Re: pga_aggregate_target and a memory leak
Paul, Most of my work is on HP-UX and AIX. I have yet to see any ORA-600 and memory leaks related to P_A_T. All databases that I work with are on 9.2.0.4, except just one running on 9.2.0.3. No memory leak there either. - Kirti --- Paul Drake [EMAIL PROTECTED] wrote: --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: pga_aggregate_target and a memory leak
I have had a problem on my 9i database for three weeks. I am getting a ORA-7445 error which is pointing to some memory problems. It is occurring during the CTX_DOC.FILTER process. We are running this process from a custom PL/SQL package that is being initiated from an Oracle Job. However, we still have the problem when we run it from a crontab job. I currently have a 21 page TAR concerning this problem. Sandra Arnold Principal DBA NCI Information Systems 175 Oak Ridge Turnpike Oak Ridge, TN 37830 -Original Message- Sent: Thursday, January 22, 2004 5:05 AM To: Multiple recipients of list ORACLE-L Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane
RE: Re: pga_aggregate_target and a memory leak
Sandra - Are you on 9.2.0.4? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 22, 2004 10:44 AM To: Multiple recipients of list ORACLE-L I have had a problem on my 9i database for three weeks. I am getting a ORA-7445 error which is pointing to some memory problems. It is occurring during the CTX_DOC.FILTER process. We are running this process from a custom PL/SQL package that is being initiated from an Oracle Job. However, we still have the problem when we run it from a crontab job. I currently have a 21 page TAR concerning this problem. Sandra Arnold Principal DBA NCI Information Systems 175 Oak Ridge Turnpike Oak Ridge, TN 37830 -Original Message- Sent: Thursday, January 22, 2004 5:05 AM To: Multiple recipients of list ORACLE-L Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked
RE: Re: pga_aggregate_target and a memory leak
Yes. On Solaris 5.8. -Original Message- Sent: Thursday, January 22, 2004 3:10 PM To: Multiple recipients of list ORACLE-L Sandra - Are you on 9.2.0.4? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 22, 2004 10:44 AM To: Multiple recipients of list ORACLE-L I have had a problem on my 9i database for three weeks. I am getting a ORA-7445 error which is pointing to some memory problems. It is occurring during the CTX_DOC.FILTER process. We are running this process from a custom PL/SQL package that is being initiated from an Oracle Job. However, we still have the problem when we run it from a crontab job. I currently have a 21 page TAR concerning this problem. Sandra Arnold Principal DBA NCI Information Systems 175 Oak Ridge Turnpike Oak Ridge, TN 37830 -Original Message- Sent: Thursday, January 22, 2004 5:05 AM To: Multiple recipients of list ORACLE-L Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit
RE: Re: pga_aggregate_target and a memory leak
Yes I have and still have a problem with pga memory leak When using pl/sql tables. I'm on 9i performance and tuning course at oracle Now and discussed this with the teacher. He went looking and found a bug Stating that on 9i (9.2.0.2 and further) there seems to be a limit on total pga per process of 1Gb. Setting pat=0 and work_area_size manual gave me a workaround for my production problem but with a test of just a simple Got a decent explanation today that pat=0 gives me more memory for pl/sql Tables because there are always in pga and pat is about sort areas so setting pat=0 gives more memory and less possibility of not having enough. Pl/sql procedure assigning values to an array of number keeps reproducing A pl/sql storage error also with pat=0 and wasp=manual. I left the bug number in my notes, can get that tomorrow if somebody is interested. Jeroen -Oorspronkelijk bericht- Van: Ryan [mailto:[EMAIL PROTECTED] Verzonden: donderdag 22 januari 2004 11:05 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: Re: pga_aggregate_target and a memory leak Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB
[oracle-l] Re: pga_aggregate_target and a memory leak
Sandra - Are you on 9.2.0.4? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 22, 2004 10:44 AM To: Multiple recipients of list ORACLE-L I have had a problem on my 9i database for three weeks. I am getting a ORA-7445 error which is pointing to some memory problems. It is occurring during the CTX_DOC.FILTER process. We are running this process from a custom PL/SQL package that is being initiated from an Oracle Job. However, we still have the problem when we run it from a crontab job. I currently have a 21 page TAR concerning this problem. Sandra Arnold Principal DBA NCI Information Systems 175 Oak Ridge Turnpike Oak Ridge, TN 37830 -Original Message- Sent: Thursday, January 22, 2004 5:05 AM To: Multiple recipients of list ORACLE-L Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked
RE: Re: pga_aggregate_target and a memory leak
I am interested in the bug number. Currently am having memory problems that may be related to the pga. Sandra -Original Message- Sent: Thursday, January 22, 2004 5:09 PM To: Multiple recipients of list ORACLE-L Yes I have and still have a problem with pga memory leak When using pl/sql tables. I'm on 9i performance and tuning course at oracle Now and discussed this with the teacher. He went looking and found a bug Stating that on 9i (9.2.0.2 and further) there seems to be a limit on total pga per process of 1Gb. Setting pat=0 and work_area_size manual gave me a workaround for my production problem but with a test of just a simple Got a decent explanation today that pat=0 gives me more memory for pl/sql Tables because there are always in pga and pat is about sort areas so setting pat=0 gives more memory and less possibility of not having enough. Pl/sql procedure assigning values to an array of number keeps reproducing A pl/sql storage error also with pat=0 and wasp=manual. I left the bug number in my notes, can get that tomorrow if somebody is interested. Jeroen -Oorspronkelijk bericht- Van: Ryan [mailto:[EMAIL PROTECTED] Verzonden: donderdag 22 januari 2004 11:05 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: Re: pga_aggregate_target and a memory leak Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L
Re: pga_aggregate_target and a memory leak
So, my intention to set P_A_T to 140G on a new datawarehouse is ill-advised? I'm not kidding, by the way. The Sun E15K belonging to the project I'm currently working on (purportedly) has 160G of RAM. It is still in the box, so I'm not believing anything until I type prtconf... I wasn't planning to use more than 10G or so for SGA, and that much only because I can... wee-hah!... Any thoughts? on 1/21/04 3:14 PM, Jonathan Lewis at [EMAIL PROTECTED] wrote: A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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: [oracle-l] Re: pga_aggregate_target and a memory leak
It is 9.2.0.4 running on Sun Solaris Version 8. What is strange I can filter and sync the same documents on my test database without getting the errors. The test database is the same version but the patches on the OS is more up-to-date. Sandra -Original Message- Sent: Thursday, January 22, 2004 8:20 PM To: Multiple recipients of list ORACLE-L Sandra - Are you on 9.2.0.4? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 22, 2004 10:44 AM To: Multiple recipients of list ORACLE-L I have had a problem on my 9i database for three weeks. I am getting a ORA-7445 error which is pointing to some memory problems. It is occurring during the CTX_DOC.FILTER process. We are running this process from a custom PL/SQL package that is being initiated from an Oracle Job. However, we still have the problem when we run it from a crontab job. I currently have a 21 page TAR concerning this problem. Sandra Arnold Principal DBA NCI Information Systems 175 Oak Ridge Turnpike Oak Ridge, TN 37830 -Original Message- Sent: Thursday, January 22, 2004 5:05 AM To: Multiple recipients of list ORACLE-L Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general? has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:04 PM --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re
Re: pga_aggregate_target and a memory leak
Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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_aggregate_target and a memory leak
Kirti, you're back! Must have finished the book. :) Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Did you try increasing P_A_T to a larger number? Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? I've been curious as to what the effects would be of having P_A_T too low. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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_aggregate_target and a memory leak
Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: pga_aggregate_target and a memory leak
kirti-- would you recommend avoiding pga_aggregate_target for now? From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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
Re: Re: pga_aggregate_target and a memory leak
I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti --- [EMAIL PROTECTED] wrote: kirti-- would you recommend avoiding pga_aggregate_target for now? From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: pga_aggregate_target and a memory leak
A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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_aggregate_target and a memory leak
Im assuming its his wait interface book. Ill get it as soon as it comes out. Hopefully it will be as good as his other tuning book. Is the April 12th date firm? Now the bigger question: Will it be out before the 10G database? http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1074724628/sr=1 -2/ref=sr_1_2/104-1361632-8254324?v=glances=books - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 5:14 PM A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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_aggregate_target and a memory leak
Thanks, Ryan. Yes, it is on OWI, for those who are new to OWI. Covers OWI from 8i to 10g. Co-authored with Richmond Shee and K.Gopalakrishnan. It will not be out till 10g goes production. Unfortunately, April 12th is not firm. 10g changes Regards, - Kirti --- Ryan [EMAIL PROTECTED] wrote: Im assuming its his wait interface book. Ill get it as soon as it comes out. Hopefully it will be as good as his other tuning book. Is the April 12th date firm? Now the bigger question: Will it be out before the 10G database? http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1074724628/sr=1 -2/ref=sr_1_2/104-1361632-8254324?v=glances=books - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 5:14 PM A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? 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: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- 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). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: pga_aggregate_target and a memory leak
--- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: I think it depends on your applications. In DSS type environments we are still stuggling to figure out if P_A_T is helping or not. Initial tests are not in P_A_T's favor. But in another Application, that is 80% OLTP, P_A_T was the only choice to avoid swapping. This 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance level. It has over 600 persistent users. No MTS in use. - Kirti Kirti, I saw in a 9.2.0.4 database just this evening, much to my surprise, an ORA-00600 in the alert log with - you guessed it - [723], [10332], [10332], [memory leak]. The database was setup in a less than optimal fashion as far as memory allocations go. The initial pga_aggregate_target was only 64M (server had 3 GB of memory and only one instance up) so I'm calling this one a non-sensical configuration error for the moment, as there is no need to size a PGA so small. If you're running with that small a memory footprint, don't use pga_aggregate_target. After resetting the parameter to 256M and cycling the instance, no ORA-00600's were recorded at instance shutdown. That was not really a good test though, will have to see tomorrow evening after the day's load has hit it. Paul this was on w2k server sp3, 9.2.0.4 std ed From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2004/01/21 Wed PM 02:44:31 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: pga_aggregate_target and a memory leak Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. Did you try increasing P_A_T to a larger number? Yes... Oracle is supposed to grab the memory it needs, if available, regardless of the P_A_T setting. Also, did your system go in to excessive paging or swapping? Yes, it did with a large P_A_T. I've been curious as to what the effects would be of having P_A_T too low. I saw more disk sorts.. As time permits, I will play with event 10032, 10033 trace for sorts to see what's going on.. Oracle is supposed to grab whatever memory it needs. I'm assuming at this point that doing so involves a different code path as it needs to alloc the memory. Don't know what the cost of that is, haven't tried to test it. It seems likely that the OS was out of memory, regardless of the P_A_T value. No. The system has 4 GB of physical memory. Over 2GB was free. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2004 06:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pga_aggregate_target and a memory leak Setting P_A_T to a 1GB limit with over 2GB of *available memory* on AIX 4.3.3 and 9.2.0.4 caused ORA-4030, till we turned off hash joins. OS level resources (ulimit -a) were all set to 'unlimited'. In a very limited testing, setting P_A_T to less than S_A_S (and S_A_R_S) worked, however, the disk sorts increased. Finally, Developers chose no hash joins, 1GB P_A_T and 'AUTO' workarea_size_policy... seems to run okay... - Kirti --- Stephane Faroult [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't
Re: pga_aggregate_target and a memory leak
[EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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_aggregate_target and a memory leak
I have been asking questions on this list recently about a Possible similar problem recently with pl/sql tables. This was on hpux 11.11 with oracle 9.2.0.4 I still haven't found the answer completely but pat=0 and was_pol = manual Is a workaround that seems to be ok. I have a lack of time For further testing but will try do do so and report some more. Regards, Jeroen -Oorspronkelijk bericht- Van: Stephane Faroult [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 20 januari 2004 20:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: pga_aggregate_target and a memory leak [EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Jeroen van Sluisdam 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).
