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).
are the 9.2.0.3 memory leaks critical?
There are a series of metalink notes detailing memory leaks with the PGA in 9203. Has anyone had critical problems? Oracle recommends patching to 9204 to fix this, but it just came out and we prefer to be conservative with our patches. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 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).
9i Automatic Memory Damagement:)
Enjoy: http://www.vldb.org/conf/2002/S29P03.pdf This explains how Oracle9i does the P_A_T, W_S_P 'magic'. Cheers! - Kirti __ 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
--- 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
pga_aggregate_target and a memory leak
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... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 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).
ORA-06505 PL/SQL: variable requires more than 32767 bytes of contiguous memory
Dear DBAs,
Good Morning. Using PL/SQL procedure I am trying to spool out
Japanese Characters with VARCHR2(3600) size and I am getting this error.
Here is my code. For your information it is Japanese Characters
Spool c:\test.log
Declare
cursor c1 is select contact_details from test;
begin
DBMS_OUTPUT.ENABLE(100);
For x in c1 loop
DBMS_OUTPUT.PUT_LINE(''||x.contact_details||'');
End loop;
End;
/
Spool off
Could you please suggest for other ideas I tried with UTL_FILE
also.. Since my OS is English I am getting some Junk Characters
Thank You
Mudhalvan M.M
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mudhalvan, Moovarkku
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: ORA-06505 PL/SQL: variable requires more than 32767 bytes of contiguous memory - Thank You !!!!!
Dear Friends,
(B
(BThank you so much. Yes i was able to solve this issue..
(B
(BFYI ... I used CONVERT function
(B
(B
(BUTL_FILE.PUT_LINE(fh,convert('"$BD9=j%^%M!<%8%c!(J","$BEj;qL>(J","CAPID","$B
Re: RE: large pl/sql table sucking up all memory on a server
i know about the limit clause. I just want to keep someone else from bringing down an instance. I think Ill get a taser and fry the next person who does it. :) From: zhu chao [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 10:34:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: RE: large pl/sql table sucking up all memory on a server I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:34 AM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: RE: large pl/sql table sucking up all memory on a server
That works. I prefer thumb presses, they worked for the Inquisition and they lasted 500 years... dr Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - I think Ill get a taser and fry the next person who does it. :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
large pl/sql table sucking up all memory on a server
One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: large pl/sql table sucking up all memory on a server
Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: large pl/sql table sucking up all memory on a server
Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: large pl/sql table sucking up all memory on a server
we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: large pl/sql table sucking up all memory on a server
3 million records in a forall statement. we are bringing on temps and you know how that goes... Im hoping I can set a parameter somewhere to keep anyone from bringing down a server. such as 'memory for pl/sql table area limit hit' errors out what he is doing. i guess not :( From: Khedr, Waleed [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:29:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: large pl/sql table sucking up all memory on a server
Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject:large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). winmail.dat
Re: RE: large pl/sql table sucking up all memory on a server
it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. From: Bobak, Mark [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 01:24:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject: large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). encoded content removed -- binaries not allowed by ListGuruContent-Type: application/ms-tnef; The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
Re: RE: large pl/sql table sucking up all memory on a server
Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: large pl/sql table sucking up all memory on a server
Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:03 PM Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM Subject: Re: RE: large pl/sql table sucking up all memory on a server we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858
Re: RE: large pl/sql table sucking up all memory on a server
FYI. The USPS delivery just (10 minutes ago) arrived with my copy of Mastering Oracle PL/SQL Practical Solutions, which I ordered from Book Pool, at: http://www.bookpool.com/.x/mzttmcaj4i/sm/1590592174 As you can see, its not available yet on Amazon: http://www.amazon.com/exec/obidos/tg/detail/-/1590592174 /qid=1072734291/sr=1-1/ref=sr_1_1/102-9815245-5757732?v=glances=books If you look on pg249, it discusses bulk collect and pga memory, e.g. I crashed my database session (and shortly thereafter my laptop) because insufficient memory was available to hold the set of 100 employee records. ... This is where a pipelined solution can help. I'm not sure if this will help in this case, but at least I hope this opens the discussion to include a 'new' reference on PL/SQL. Happy Holidays. Regards, Mike Thomas --- Tanel Poder [EMAIL PROTECTED] wrote: Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:03 PM Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM Subject: Re: RE: large pl/sql table sucking up all memory on a server we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY
Re: RE: large pl/sql table sucking up all memory on a server
This is probably old hat for you, but given it's Unix (Sun) and it's a client process, wouldn't you be able to use ulimit to stop memory allocation growing past a certain size? The other thing I'd try is to limit memory through the resource control in Oracle. But that is highly version dependent and I'm not sure which version you running. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:39 AM it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: large pl/sql table sucking up all memory on a server
I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:34 AM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
ora-4030 pga memory allocation running wild
Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
RE: ora-4030 pga memory allocation running wild
This is scary, I'm planning to upgrade 9.2.0.4 from 9.2.0.2. I don't know how removing pga_aggegrate_target will help reducing memory!! Does the program have any memory tables, etc? Did you monitor the PGA size from the Oracle side using v$sesstat? A sql by itself can't consume this memory except there is a major bug some where, which I doubt! Please keep us updated. Thanks Waleed -Original Message-From: Jeroen van Sluisdam [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 10:24 AMTo: Multiple recipients of list ORACLE-LSubject: ora-4030 pga memory allocation running wild Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
Re: ora-4030 pga memory allocation running wild
I'm using auto pga allocation on 9.2.0.3 without any problem. You don't mention which version. You can turn it off with 'alter system set workarea_size_policy=manual; Jared On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote: Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: ora-4030 pga memory allocation running wild
Hi, I'm using oracle 9.2.0.4. I put it off tonight with the statement You mentioned and unfortunately no success. Maybe interesting to know that I started without the event 4030 set And I get the following ora-600 in my alert file: Tue Dec 23 16:46:42 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_15251.trc: ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], This one is reproducible without the event set and a pga_aggregate_target set either 250Mb or 160Mb With the event set I got the following error Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10264.trc: ORA-04030: out of process memory when trying to allocate 2464 bytes (cursor work he,rworalo : rwordops) Tue Dec 23 14:24:40 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10249.trc: ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], [] ORA-04030: out of process memory when trying to allocate 32 bytes (callheap,allocator state) This second tracefile lead me to the sql-statement which explained with a very nice result When I issued the statement to set off auto handling I did not get any such error in my alert file but my batch returned again after an hour With ERROR at line 1: ORA-04030: out of process memory when trying to allocate 56 bytes (callheap,PESBLT space) Could a UX kernel parameter be of any influence here, like max data segment? Could it help to increase this to say 3Gb. Note that we have 4Gb physical memory and 4Gb swap configured. I used to run this in an oracle 7 enviroment on hpux 10.20 and now we moved To 64bit hpux11.11. I can imagine oracle is using more memory here than compared to oracle 7 with the same program such that in the old environment we might stayed below 2Gb and now we are exceeding this. For what it might be worth, this batch is quite big. Sofar this seems to be the only program having memory problems. I have put back workare_size_policy=auto back to be on the safe default side. I hope you can give some more leads because this is quite confusing And causing me headaches because it is causing troubles in my production environment. By the way we tested the migration ofcourse but this batch was not included in the test. Regards, Jeroen -Oorspronkelijk bericht- Van: Jared Still [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 23 december 2003 18:34 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: ora-4030 pga memory allocation running wild I'm using auto pga allocation on 9.2.0.3 without any problem. You don't mention which version. You can turn it off with 'alter system set workarea_size_policy=manual; Jared On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote: Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
Re: Free Memory in v$sgastat
Sinardy, where does oracle say anything like that about free memory? Please, quote me an article or URL. Second, if you are not using MTS, your PGA is a part of your dedicated server address space, not SGA. It does exist, though. Similarly, UGA goes to shared pool instead. Buy yourself The Book (Practical Oracle 8i) and read all about the architecture. I'll get rich from all commissions I receive. of large pool. On 12/16/2003 12:24:25 AM, Sinardy Xing wrote: Hi Mladen, I try to understand 8i, and I am not using MTS, my current understanding is UGA and PGA only exists (I mean in use or in the picture) when you are using MTS. Oracle themselves said 'free memory' are more properly thought of as wasted space I just wondering why this million dollar RDBMS can't make use of this space? everything are in terms of Oracle Block, you can release and reuse the blocks. Well maybe quite difficult to get long continuous blocks, but it still free space, I am so blur now Sinardy -Original Message- Sent: 16 December 2003 12:54 To: Multiple recipients of list ORACLE-L Sinardy, you've attended wrong database tuning course. You need free memory in your shared pool. There is no such thing as sga fragmentation unless there is not enough free memory to satisfy average request. While oracle is not monitoring the size of an average shared pool request. you have things like session_uga_memory and session_pga_memory in v$mystat. In v9 there is also session_stored_procedure_space. If the instance doesn't have enough room to load the next thing to execute (SQL, PL/SQL, Java), it will have to make room. Generally speaking, making room hurts. You want to have enough room to load your stuff into the shared pool and then some. That room is also known as free space. Look into the tuning manual on the OTN and make sure that your buffer cache hit ratio is high enough (sorry folks, I couldn't resist). On 2003.12.15 23:19, Sinardy Xing wrote: Hi all, Why free memory in v$sgastat is a symptom of the fragmentation? Why these free memory are more properly thought of as wasted space? Thanks Sinardy -- 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: Sinardy Xing 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: Free Memory in v$sgastat
Hi, Those are lines from my friend Oracle University student guide (Original) Sinardy -Original Message- Sent: 16 December 2003 22:34 To: Multiple recipients of list ORACLE-L Sinardy, where does oracle say anything like that about free memory? Please, quote me an article or URL. Second, if you are not using MTS, your PGA is a part of your dedicated server address space, not SGA. It does exist, though. Similarly, UGA goes to shared pool instead. Buy yourself The Book (Practical Oracle 8i) and read all about the architecture. I'll get rich from all commissions I receive. of large pool. On 12/16/2003 12:24:25 AM, Sinardy Xing wrote: Hi Mladen, I try to understand 8i, and I am not using MTS, my current understanding is UGA and PGA only exists (I mean in use or in the picture) when you are using MTS. Oracle themselves said 'free memory' are more properly thought of as wasted space I just wondering why this million dollar RDBMS can't make use of this space? everything are in terms of Oracle Block, you can release and reuse the blocks. Well maybe quite difficult to get long continuous blocks, but it still free space, I am so blur now Sinardy -Original Message- Sent: 16 December 2003 12:54 To: Multiple recipients of list ORACLE-L Sinardy, you've attended wrong database tuning course. You need free memory in your shared pool. There is no such thing as sga fragmentation unless there is not enough free memory to satisfy average request. While oracle is not monitoring the size of an average shared pool request. you have things like session_uga_memory and session_pga_memory in v$mystat. In v9 there is also session_stored_procedure_space. If the instance doesn't have enough room to load the next thing to execute (SQL, PL/SQL, Java), it will have to make room. Generally speaking, making room hurts. You want to have enough room to load your stuff into the shared pool and then some. That room is also known as free space. Look into the tuning manual on the OTN and make sure that your buffer cache hit ratio is high enough (sorry folks, I couldn't resist). On 2003.12.15 23:19, Sinardy Xing wrote: Hi all, Why free memory in v$sgastat is a symptom of the fragmentation? Why these free memory are more properly thought of as wasted space? Thanks Sinardy -- 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: Sinardy Xing 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing 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
Free Memory in v$sgastat
Hi all, Why free memory in v$sgastat is a symptom of the fragmentation? Why these "free memory" aremore properly thought of as wasted space? Thanks Sinardy
Re: Free Memory in v$sgastat
Sinardy, you've attended wrong database tuning course. You need free memory in your shared pool. There is no such thing as sga fragmentation unless there is not enough free memory to satisfy average request. While oracle is not monitoring the size of an average shared pool request. you have things like session_uga_memory and session_pga_memory in v$mystat. In v9 there is also session_stored_procedure_space. If the instance doesn't have enough room to load the next thing to execute (SQL, PL/SQL, Java), it will have to make room. Generally speaking, making room hurts. You want to have enough room to load your stuff into the shared pool and then some. That room is also known as free space. Look into the tuning manual on the OTN and make sure that your buffer cache hit ratio is high enough (sorry folks, I couldn't resist). On 2003.12.15 23:19, Sinardy Xing wrote: Hi all, Why free memory in v$sgastat is a symptom of the fragmentation? Why these free memory are more properly thought of as wasted space? Thanks Sinardy -- 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: Free Memory in v$sgastat
Hi Mladen, I try to understand 8i, and I am not using MTS, my current understanding is UGA and PGA only exists (I mean in use or in the picture) when you are using MTS. Oracle themselves said 'free memory' are more properly thought of as wasted space I just wondering why this million dollar RDBMS can't make use of this space? everything are in terms of Oracle Block, you can release and reuse the blocks. Well maybe quite difficult to get long continuous blocks, but it still free space, I am so blur now Sinardy -Original Message- Sent: 16 December 2003 12:54 To: Multiple recipients of list ORACLE-L Sinardy, you've attended wrong database tuning course. You need free memory in your shared pool. There is no such thing as sga fragmentation unless there is not enough free memory to satisfy average request. While oracle is not monitoring the size of an average shared pool request. you have things like session_uga_memory and session_pga_memory in v$mystat. In v9 there is also session_stored_procedure_space. If the instance doesn't have enough room to load the next thing to execute (SQL, PL/SQL, Java), it will have to make room. Generally speaking, making room hurts. You want to have enough room to load your stuff into the shared pool and then some. That room is also known as free space. Look into the tuning manual on the OTN and make sure that your buffer cache hit ratio is high enough (sorry folks, I couldn't resist). On 2003.12.15 23:19, Sinardy Xing wrote: Hi all, Why free memory in v$sgastat is a symptom of the fragmentation? Why these free memory are more properly thought of as wasted space? Thanks Sinardy -- 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: Sinardy Xing 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: How windows manage memory: oracle
Thanks Paul.
I did a check this week with out Win2000 tech support and
was told that it come with 3GB process size while WNT was limited to 2GB
(without special parameters).
What is this pslist command? Is it something from
Unix?
Yechiel AdarMehish
- Original Message -
From:
Paul
Drake
To: Multiple recipients of list ORACLE-L
Sent: Thursday, December 04, 2003 7:34
PM
Subject: Re: How windows manage memory:
oracle
Hi.
The 2 GB process limit kicks in well under 2 * 1024 *1024 * 1024.
its between 1.7 and 1.8 GB.
I'm quite familiar with hitting it in win32, as large memory support was
not enabled in every 8.1.7.x patchset. Large memory support sure works great
in 9.2.0.4.
W2K3 Server (not Advanced) ships with large memory support.
In Windows 2000, one needed to acquire Advanced Server edition for large
memory support.
ways that you know that you hit the process memory limit:
1. unable to startup instance
2. unable to spawn a dedicated server process (in listener.log)
3. unable to allocate n bytes of memory in the shared pool (in
the user's error message)
For tracking memory usage by a process (namely, oracle.exe), I'd
recommend using the sysinternals pslist utility, and log that to an OS file.
There is the performance logs option in the OS, which gives you the benefits
of setting a max file size which will be filled in a circular fashion.
http://www.sysinternals.com
hth.
Pd
Yechiel Adar [EMAIL PROTECTED]
wrote:
I
do not see the problem.SGA is 970M + PGA (20*40) 800 MB + executables
and you got about 2GB whichis the upper limit on NT, unless you used
special startup parameter.Yechiel AdarMehish- Original
Message -To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>Sent: Thursday, December 04, 2003 3:24
PM Hi, friends: Several months ago there is a thread
talking about choosing the propermemory size for windows server running
oracle. And today I logon to one of my small oracle on NT and found
somethingI cannot understand. It is a small application running Oracle
817/win2k. SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But
from taskmanager, Oracle is using 1005M physical Memory and 1013M
virtual memory(youcan view the data from here:
http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif).
SQL show sga Total System Global Area
971040796 bytes Fixed Size 75804 bytes
Variable Size 299798528 bytes Database Buffers 671088640
bytes Redo Buffers 77824 byte SQL select
count(*) from v$session;
COUNT(*) -- 18
SQL select sum(value) from v$sesstat where statistic#=(select
statistic#from v$statname where name='session pga memory
max'); SUM(VALUE)
-- 39526196 And I looked at another
server running SAP/oracle, get similiar data:
http://www.cnoug.org/html/ut/attach/2003/12/04/12518-sap-embed.gif
(780M sga,33 connection and 25M pga). Can
someone explain it?
Regards Zhu
Chao. -- Please see the
official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu
chao INET: [EMAIL PROTECTED] Fat City Network
Services -- 858-538-5051 http://www.fatcity.com San Diego,
California -- Mailing list and web hosting services
-
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: Yechiel AdarINET: [EMAIL PROTECTED]Fat City
Network Services -- 858-538-5051 http://www.fatcity.comSan Diego,
California -- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe
message BODY, include a line containing: UNSUB ORACLE-L(or the name of
mailing list you want to be removed from). You mayalso send the HELP
command for other information (like subscribing).
Do you Yahoo!?Free
Pop-Up Blocker - Get it now
RE: How windows manage memory: oracle
Title: Message winternalssoftware runs a website called sysinternals which has a bunch of useful free utilities for windows (and IIRC Linux now as well). pslist is one of those utilities. www.sysinternals.com -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Yechiel AdarSent: 05 December 2003 07:24To: Multiple recipients of list ORACLE-LSubject: Re: How windows manage memory: oracle Thanks Paul. I did a check this week with out Win2000 tech support and was told that it come with 3GB process size while WNT was limited to 2GB (without special parameters). What is this pslist command? Is it something from Unix? Yechiel AdarMehish - Original Message - From: Paul Drake To: Multiple recipients of list ORACLE-L Sent: Thursday, December 04, 2003 7:34 PM Subject: Re: How windows manage memory: oracle Hi. The 2 GB process limit kicks in well under 2 * 1024 *1024 * 1024. its between 1.7 and 1.8 GB. I'm quite familiar with hitting it in win32, as large memory support was not enabled in every 8.1.7.x patchset. Large memory support sure works great in 9.2.0.4. W2K3 Server (not Advanced) ships with large memory support. In Windows 2000, one needed to acquire Advanced Server edition for large memory support. ways that you know that you hit the process memory limit: 1. unable to startup instance 2. unable to spawn a dedicated server process (in listener.log) 3. unable to allocate n bytes of memory in the shared pool (in the user's error message) For tracking memory usage by a process (namely, oracle.exe), I'd recommend using the sysinternals pslist utility, and log that to an OS file. There is the performance logs option in the OS, which gives you the benefits of setting a max file size which will be filled in a circular fashion. http://www.sysinternals.com hth. Pd Yechiel Adar [EMAIL PROTECTED] wrote: I do not see the problem.SGA is 970M + PGA (20*40) 800 MB + executables and you got about 2GB whichis the upper limit on NT, unless you used special startup parameter.Yechiel AdarMehish- Original Message -To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>Sent: Thursday, December 04, 2003 3:24 PM Hi, friends: Several months ago there is a thread talking about choosing the propermemory size for windows server running oracle. And today I logon to one of my small oracle on NT and found somethingI cannot understand. It is a small application running Oracle 817/win2k. SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from taskmanager, Oracle is using 1005M physical Memory and 1013M virtual memory(youcan view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). SQL show sga Total System Global Area 971040796 bytes Fixed Size 75804 bytes Variable Size 299798528 bytes Database Buffers 671088640 bytes Redo Buffers 77824 byte SQL select count(*) from v$session; COUNT(*) -- 18 SQL select sum(value) from v$sesstat where statistic#=(select statistic#from v$statname where name='session pga memory max'); SUM(VALUE) -- 39526196 And I looked at another server running SAP/oracle, get similiar data: http://www.cnoug.org/html/ut/attach/2003/12/04/12518-sap-embed.gif (780M sga,33 connection and 25M pga). Can someone explain it? Regards Zhu Chao. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: Yechiel AdarINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a
Re: How windows manage memory: oracle
My favorite SF computer is Holly, from the Red Dwarf. Add a hologram like Rimmer and who needs anything else? I believe that Holly was running MS-Windows. On 12/04/2003 04:44:26 PM, Bellow, Bambi wrote: I know I've posted this before, but it's been many years, so here we go again. NT was supposed to be Windows' answer to VMS. WNT, doesn't stand for anything, so how did they come up with the name? V+1=W M+1=N S+1=T Just like I-1=H B-1=A M-1=L Coincidence? Bambi. -Original Message- Sent: Thursday, December 04, 2003 2:49 PM To: Multiple recipients of list ORACLE-L Even though I have never touched VMS myself, I completely agree that it is (was) a great operating system, I've just heard so many good words from respectable sources about it :) About Windows, probably the initial idea was great but since MS is a marketing driven company, they just left off most of the good pieces in order to release new versions sooner... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 10:19 PM That is utterly disgusting memory management. When I come to think of it, there was a guy named David Cutler who was promising that Windows will have the same virtual memory system as VMS, with FREELIM,FREEGOAL, BORROWLIM, GROWLIM and MPW_ parameters. Working sets are also gone as well as the most elaborate privileges system until that time. Authorize was a wonderful tool which still leaves anything that either windows or Unix can offer in the dust. On 12/04/2003 02:54:31 PM, Tanel Poder wrote: SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). Physical memory and virtual memory overlap in windows. If you have allocated 100M of memory, but only 50M of it is mapped to physical memory (rest is in pagefile), you see 100M and 50M accordingly in task manager. Also, there is a situation where you can have more physical memory than virtual memory. Im not sure, but it might be doing something with deallocated memory, which is not reclaimed by OS or smth like that. There is a note about windows nt memory management in metalink, search from there if want additional information. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
How windows manage memory: oracle
Hi, friends: Several months ago there is a thread talking about choosing the proper memory size for windows server running oracle. And today I logon to one of my small oracle on NT and found something I cannot understand. It is a small application running Oracle 817/win2k. SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). SQL show sga Total System Global Area 971040796 bytes Fixed Size75804 bytes Variable Size 299798528 bytes Database Buffers 671088640 bytes Redo Buffers 77824 byte SQL select count(*) from v$session; COUNT(*) -- 18 SQL select sum(value) from v$sesstat where statistic#=(select statistic# from v$statname where name='session pga memory max'); SUM(VALUE) -- 39526196 And I looked at another server running SAP/oracle, get similiar data: http://www.cnoug.org/html/ut/attach/2003/12/04/12518-sap-embed.gif (780M sga,33 connection and 25M pga). Can someone explain it? Regards Zhu Chao. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: How windows manage memory: oracle
Hi, But PGA is only 40M(This is the sum of all process's v$sesstat). So there is more memory utilization then oracle actually should use. From task manager, it is 2018(Physical+Virtual), But from oracle v$(sga + pga) it is only 1020M.This is the problem. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 9:59 PM I do not see the problem. SGA is 970M + PGA (20*40) 800 MB + executables and you got about 2GB which is the upper limit on NT, unless you used special startup parameter. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 3:24 PM Hi, friends: Several months ago there is a thread talking about choosing the proper memory size for windows server running oracle. And today I logon to one of my small oracle on NT and found something I cannot understand. It is a small application running Oracle 817/win2k. SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). SQL show sga Total System Global Area 971040796 bytes Fixed Size75804 bytes Variable Size 299798528 bytes Database Buffers 671088640 bytes Redo Buffers 77824 byte SQL select count(*) from v$session; COUNT(*) -- 18 SQL select sum(value) from v$sesstat where statistic#=(select statistic# from v$statname where name='session pga memory max'); SUM(VALUE) -- 39526196 And I looked at another server running SAP/oracle, get similiar data: http://www.cnoug.org/html/ut/attach/2003/12/04/12518-sap-embed.gif (780M sga,33 connection and 25M pga). Can someone explain it? Regards Zhu Chao. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: How windows manage memory: oracle
Hi. The 2 GB process limit kicks in well under 2 * 1024 *1024 * 1024. its between 1.7 and 1.8 GB. I'm quite familiar with hitting it in win32, as large memory support was not enabled in every 8.1.7.x patchset. Large memory support sure works great in 9.2.0.4. W2K3 Server (not Advanced) ships with large memory support. In Windows 2000, one needed to acquire Advanced Server edition for large memory support. ways that you know that you hit the process memory limit: 1. unable to startup instance 2. unable to spawn a dedicated server process (in listener.log) 3. unable to allocate n bytes of memory in the shared pool (in the user's error message) For tracking memory usage by a process (namely, oracle.exe), I'd recommend using the sysinternals pslist utility, and log that to an OS file. There is the performance logs option in the OS, which gives you the benefits of setting a max file size which will be filled in a circular fashion. http://www.sysinternals.com hth. Pd Yechiel Adar [EMAIL PROTECTED] wrote: I do not see the problem.SGA is 970M + PGA (20*40) 800 MB + executables and you got about 2GB whichis the upper limit on NT, unless you used special startup parameter.Yechiel AdarMehish- Original Message -To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>Sent: Thursday, December 04, 2003 3:24 PM Hi, friends: Several months ago there is a thread talking about choosing the propermemory size for windows server running oracle. And today I logon to one of my small oracle on NT and found somethingI cannot understand. It is a small application running Oracle 817/win2k. SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from taskmanager, Oracle is using 1005M physical Memory and 1013M virtual memory(youcan view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). SQL show sga Total System Global Area 971040796 bytes Fixed Size 75804 bytes Variable Size 299798528 bytes Database Buffers 671088640 bytes Redo Buffers 77824 byte SQL select count(*) from v$session; COUNT(*) -- 18 SQL select sum(value) from v$sesstat where statistic#=(select statistic#from v$statname where name='session pga memory max'); SUM(VALUE) -- 39526196 And I looked at another server running SAP/oracle, get similiar data: http://www.cnoug.org/html/ut/attach/2003/12/04/12518-sap-embed.gif (780M sga,33 connection and 25M pga). Can someone explain it? Regards Zhu Chao. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: Yechiel AdarINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: How windows manage memory: oracle
SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). Physical memory and virtual memory overlap in windows. If you have allocated 100M of memory, but only 50M of it is mapped to physical memory (rest is in pagefile), you see 100M and 50M accordingly in task manager. Also, there is a situation where you can have more physical memory than virtual memory. Im not sure, but it might be doing something with deallocated memory, which is not reclaimed by OS or smth like that. There is a note about windows nt memory management in metalink, search from there if want additional information. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How windows manage memory: oracle
That is utterly disgusting memory management. When I come to think of it, there was a guy named David Cutler who was promising that Windows will have the same virtual memory system as VMS, with FREELIM,FREEGOAL, BORROWLIM, GROWLIM and MPW_ parameters. Working sets are also gone as well as the most elaborate privileges system until that time. Authorize was a wonderful tool which still leaves anything that either windows or Unix can offer in the dust. On 12/04/2003 02:54:31 PM, Tanel Poder wrote: SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). Physical memory and virtual memory overlap in windows. If you have allocated 100M of memory, but only 50M of it is mapped to physical memory (rest is in pagefile), you see 100M and 50M accordingly in task manager. Also, there is a situation where you can have more physical memory than virtual memory. Im not sure, but it might be doing something with deallocated memory, which is not reclaimed by OS or smth like that. There is a note about windows nt memory management in metalink, search from there if want additional information. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: How windows manage memory: oracle
Even though I have never touched VMS myself, I completely agree that it is (was) a great operating system, I've just heard so many good words from respectable sources about it :) About Windows, probably the initial idea was great but since MS is a marketing driven company, they just left off most of the good pieces in order to release new versions sooner... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 10:19 PM That is utterly disgusting memory management. When I come to think of it, there was a guy named David Cutler who was promising that Windows will have the same virtual memory system as VMS, with FREELIM,FREEGOAL, BORROWLIM, GROWLIM and MPW_ parameters. Working sets are also gone as well as the most elaborate privileges system until that time. Authorize was a wonderful tool which still leaves anything that either windows or Unix can offer in the dust. On 12/04/2003 02:54:31 PM, Tanel Poder wrote: SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). Physical memory and virtual memory overlap in windows. If you have allocated 100M of memory, but only 50M of it is mapped to physical memory (rest is in pagefile), you see 100M and 50M accordingly in task manager. Also, there is a situation where you can have more physical memory than virtual memory. Im not sure, but it might be doing something with deallocated memory, which is not reclaimed by OS or smth like that. There is a note about windows nt memory management in metalink, search from there if want additional information. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How windows manage memory: oracle
I know I've posted this before, but it's been many years, so here we go again. NT was supposed to be Windows' answer to VMS. WNT, doesn't stand for anything, so how did they come up with the name? V+1=W M+1=N S+1=T Just like I-1=H B-1=A M-1=L Coincidence? Bambi. -Original Message- Sent: Thursday, December 04, 2003 2:49 PM To: Multiple recipients of list ORACLE-L Even though I have never touched VMS myself, I completely agree that it is (was) a great operating system, I've just heard so many good words from respectable sources about it :) About Windows, probably the initial idea was great but since MS is a marketing driven company, they just left off most of the good pieces in order to release new versions sooner... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 10:19 PM That is utterly disgusting memory management. When I come to think of it, there was a guy named David Cutler who was promising that Windows will have the same virtual memory system as VMS, with FREELIM,FREEGOAL, BORROWLIM, GROWLIM and MPW_ parameters. Working sets are also gone as well as the most elaborate privileges system until that time. Authorize was a wonderful tool which still leaves anything that either windows or Unix can offer in the dust. On 12/04/2003 02:54:31 PM, Tanel Poder wrote: SGA is 970M and PGA(maxsize) is 40M. Connection is 20.But from task manager, Oracle is using 1005M physical Memory and 1013M virtual memory(you can view the data from here: http://www.cnoug.org/html/ut/attach/2003/12/04/12516-oramem2-embed.gif). Physical memory and virtual memory overlap in windows. If you have allocated 100M of memory, but only 50M of it is mapped to physical memory (rest is in pagefile), you see 100M and 50M accordingly in task manager. Also, there is a situation where you can have more physical memory than virtual memory. Im not sure, but it might be doing something with deallocated memory, which is not reclaimed by OS or smth like that. There is a note about windows nt memory management in metalink, search from there if want additional information. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want
Memory consumption on HP-UX
Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Memory consumption on HP-UX
Hi Helmut, Notice the parameter is called pga_aggregate_TARGET and not pga_aggregate_MAX_SIZE. That's because the P_A_T is just that, a target the Oracle does it's best to not exceed. It does this by controlling and rationing the tuneable component of the PGA (ie. those portions of the PGA previously controlled by the *_AREA_SIZE parameters) on a as need/on demand basis based on current system load. However, if the number of sessions/processes is such that the other non-tuneable components of the PGAs were to put pressure on the P_A_T, then Oracle may have no choice but to exceed it. This is not a good thing in that obviously more PGA memory is allocated that you ideally want and also because the workarea operations are not going to be the ideal optimal executions you're after. Increasing the P_A_T would be therefore be recommended, depending of course on your available memory. v$pgastat, v$pga_target_advice and v$process will give you useful info on how much you may have exceeded your P_A_T. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:54 PM Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Richard Foote 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: Memory consumption on HP-UX
Use glance if you have that package installed, look for ps -ef | grep midaemon Allan -Original Message- Sent: Tuesday, November 11, 2003 3:54 AM To: Multiple recipients of list ORACLE-L Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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: Memory consumption on HP-UX
Take care with automatic PGA management. We have TNS12500 HPUX err 12 using it because proceses RESERVING lots of swap. We change to manual PGA (we use sort_area_size, etc.) It was on 9.2.0.1 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Richard Foote Enviado el: martes, 11 de noviembre de 2003 13:35 Para: Multiple recipients of list ORACLE-L Asunto: Re: Memory consumption on HP-UX Hi Helmut, Notice the parameter is called pga_aggregate_TARGET and not pga_aggregate_MAX_SIZE. That's because the P_A_T is just that, a target the Oracle does it's best to not exceed. It does this by controlling and rationing the tuneable component of the PGA (ie. those portions of the PGA previously controlled by the *_AREA_SIZE parameters) on a as need/on demand basis based on current system load. However, if the number of sessions/processes is such that the other non-tuneable components of the PGAs were to put pressure on the P_A_T, then Oracle may have no choice but to exceed it. This is not a good thing in that obviously more PGA memory is allocated that you ideally want and also because the workarea operations are not going to be the ideal optimal executions you're after. Increasing the P_A_T would be therefore be recommended, depending of course on your available memory. v$pgastat, v$pga_target_advice and v$process will give you useful info on how much you may have exceeded your P_A_T. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:54 PM Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Richard Foote 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: Juan Miranda 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: Memory consumption on HP-UX
Hi Juan, We encountered the same problem. Issue was due to OS being set in Eager swapping mode. Support viewed the fact it reserved a massive amount of swap as a feature. However, after switching the OS (HP 5.1 TRU64) to Lazy swap mode, the problem (as one would hope) disappeared and we haven't looked back. We have about 32G of swap disk doing nothing :) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 1:14 AM Take care with automatic PGA management. We have TNS12500 HPUX err 12 using it because proceses RESERVING lots of swap. We change to manual PGA (we use sort_area_size, etc.) It was on 9.2.0.1 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Richard Foote Enviado el: martes, 11 de noviembre de 2003 13:35 Para: Multiple recipients of list ORACLE-L Asunto: Re: Memory consumption on HP-UX Hi Helmut, Notice the parameter is called pga_aggregate_TARGET and not pga_aggregate_MAX_SIZE. That's because the P_A_T is just that, a target the Oracle does it's best to not exceed. It does this by controlling and rationing the tuneable component of the PGA (ie. those portions of the PGA previously controlled by the *_AREA_SIZE parameters) on a as need/on demand basis based on current system load. However, if the number of sessions/processes is such that the other non-tuneable components of the PGAs were to put pressure on the P_A_T, then Oracle may have no choice but to exceed it. This is not a good thing in that obviously more PGA memory is allocated that you ideally want and also because the workarea operations are not going to be the ideal optimal executions you're after. Increasing the P_A_T would be therefore be recommended, depending of course on your available memory. v$pgastat, v$pga_target_advice and v$process will give you useful info on how much you may have exceeded your P_A_T. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:54 PM Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Richard Foote 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: Juan Miranda 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: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego
ORA-27102: out of memory in Tru64
Hi friends , I am getting the following error when trying to take the database to nomount state . I am trying to create a new database . $ svrmgrl Oracle Server Manager Release 3.1.7.0.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SVRMGR connect internal Connected. SVRMGR startup pfile ='/app/oracle/admin/devbcm/pfile/initdevbcm.ora' nomount ORA-27102: out of memory Compaq Tru64 UNIX Error: 12: Not enough space Additional information: 1 Additional information: 98307 My Shm parameters are as given below . $ /sbin/sysconfig -q ipc ipc: msg-max = 8192 msg-mnb = 16384 msg-mni = 64 msg-tql = 40 shm-max = 1073741824 shm-min = 1024 shm-mni = 256 shm-seg = 128 sem-mni = 4096 sem-msl = 1000 sem-opm = 100 sem-ume = 1000 sem-vmx = 32767 sem-aem = 16384 num-of-sems = 1000 max-kernel-ports = 32351 port-hash-max-num = 1617550 port-reserved-max-num = 32351 set-max-num = 4338 ssm-threshold = 0 ssm-enable-core-dump = 1 Please help me to solve this error . Thanks Shibu DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shibu MB 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: ORA-27102: out of memory in Tru64
Hi Shibu (Still going to GOA at new year then ??) I would get your shm-max upped. Regards Lee -Original Message- Sent: 04 November 2003 13:39 To: Multiple recipients of list ORACLE-L Hi friends , I am getting the following error when trying to take the database to nomount state . I am trying to create a new database . $ svrmgrl Oracle Server Manager Release 3.1.7.0.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SVRMGR connect internal Connected. SVRMGR startup pfile ='/app/oracle/admin/devbcm/pfile/initdevbcm.ora' nomount ORA-27102: out of memory Compaq Tru64 UNIX Error: 12: Not enough space Additional information: 1 Additional information: 98307 My Shm parameters are as given below . $ /sbin/sysconfig -q ipc ipc: msg-max = 8192 msg-mnb = 16384 msg-mni = 64 msg-tql = 40 shm-max = 1073741824 shm-min = 1024 shm-mni = 256 shm-seg = 128 sem-mni = 4096 sem-msl = 1000 sem-opm = 100 sem-ume = 1000 sem-vmx = 32767 sem-aem = 16384 num-of-sems = 1000 max-kernel-ports = 32351 port-hash-max-num = 1617550 port-reserved-max-num = 32351 set-max-num = 4338 ssm-threshold = 0 ssm-enable-core-dump = 1 Please help me to solve this error . Thanks Shibu DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shibu MB 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). ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robertson Lee - lerobe 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: memory usage by dbw very high
what is meant by OP,tanel.. Original Poster. Tanel.
Re: memory usage by dbw very high
hi tanel and mladen not every time a process is started does it swap but sometimes swapping does happen.(this is from the top o/p which shows a increase in the memory used in swap.).how do we check whether a single process swaps or not? and the dbw process is using more % of memory than a couple of days back.(o/p pf ps aux). is this how linux kernel works or is there something else i can check. thanks sai what is meant by OP,tanel.. Tanel Poder [EMAIL PROTECTED] wrote: Thanks Mladen, that was a good tip about linux kernel enhancement, howeverOP still uses 2.4.9 as stated in original post.I just wanted to know whether OP actually sees excessive paging or justmemory being "full", the latter one, as you know, isn't really a problem.Tanel.- Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>Sent: Saturday, November 01, 2003 5:39 PM The whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserFS or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing! pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate thebuffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see "free" memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very highrich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08)is used by non shared memory size. i went thru all the processes and found dbwr u! sing the max %mem. what could be the reason? sai "Jesse, Rich" <[EMAIL PROTECTED]>wrote: If I'm not mistaken, this figure includes the size of the sharedmemory segment from the SGA. Take the output of the "oracle" line of"ipcs -a" (hopefully you'll only have one!) and subtract it from the processsize to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA-Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-Lhi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swappingwhen any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idle CPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idle CPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle CPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idle Mem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448K buff Swap: 2048152K av, 1652K used, 2046500K free 1852468K cached sga size: Total System Global Area 1084823632 bytes Fixed! Size 452688 bytes Variable Size 335544320 bytes Database Buffers 738197504 bytes Redo Buffers 10629120 bytes pga aggregate size:700M and ps o/p of dbw process USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND oracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06 ora_dbw0_revenue please advise. what is really going on. thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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 a! n 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, i
Re: memory usage by dbw very high
mladen i have gtop...and i am trying to get thru the preferences which u have mentioned. 1. can you please help me to find the kernel mode for the processes. 2. on this linux box i see that demon kswapd and bdflush in the process list.should they always be running or do they get intiated only when paging or swapping happens. thanks sai Mladen Gogala [EMAIL PROTECTED] wrote: What we have here is a confusion with terminology. Process cannot "be swapping". When there is a serious shortage of memory, the swap demon(yes, your Unix box is haunted) known by the horrible name of [kswapd] writes the whole address space space belonging to the process onto swap. At that point, process is swapped.Unless, we are talking about the kswapd process, the process cannot "be swapping". Kernelswap demon seldomly uses oracle database.Process can be paging. When there is serious shortage of memory (but less serious then inthe first case), pages are stolen from the process and written onto the swap. It's called"page replacement". When processes need pages that have been thrown out of memory by the page replacement demon (in case of Linux, it's called bdflush but on some unix implementations,there is a process called "updated" or ! "paged " which performs this function.), it pages them in.If the page is in memory (buffer cache would a good place to look), we're talking about the soft page fault. If the page has to be read from the disk, we're talking about hard pagefault. Processes that page get charged for a lot of CPU time, all of it in the kernel mode.If you have gtop (my kindest advice is to get it), you can set preferences and see kernel modefor the processes. Ones consuming large amounts of the kernel mode are ones that are paging.On 11/03/2003 01:24:27 PM, Sai Selvaganesan wrote: hi tanel and mladen not every time a process is started does it swap but sometimes swapping does happen.(this is from the top o/p which shows a increase in the memory used in swap.).how do we check whether a single process swaps or not? and the dbw process is using more % of memory than a couple of days back.(o/p pf ps aux). is this how linux kernel works or is th! ere something else i can check. thanks sai what is meant by OP,tanel.. Tanel Poder <[EMAIL PROTECTED]>wrote: Thanks Mladen, that was a good tip about linux kernel enhancement, however OP still uses 2.4.9 as stated in original post. I just wanted to know whether OP actually sees excessive paging or just memory being "full", the latter one, as you know, isn't really a problem. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" Sent: Saturday, November 01, 2003 5:39 PMThe whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserF! S or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate the buffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see "free" memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high ! rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08) is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai "Jesse, Rich" wrote: If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the "oracle" line of "ipcs -a" (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Su! ssex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system,
Re: memory usage by dbw very high
Preferences-Process Fields-STime, UTime. On 11/03/2003 02:44:34 PM, Sai Selvaganesan wrote: mladen i have gtop...and i am trying to get thru the preferences which u have mentioned. 1. can you please help me to find the kernel mode for the processes. 2. on this linux box i see that demon kswapd and bdflush in the process list.should they always be running or do they get intiated only when paging or swapping happens. thanks sai Mladen Gogala [EMAIL PROTECTED] wrote: What we have here is a confusion with terminology. Process cannot be swapping. When there is a serious shortage of memory, the swap demon (yes, your Unix box is haunted) known by the horrible name of [kswapd] writes the whole address space space belonging to the process onto swap. At that point, process is swapped. Unless, we are talking about the kswapd process, the process cannot be swapping. Kernel swap demon seldomly uses oracle database. Process can be paging. When there is serious shortage of memory (but less serious then in the first case), pages are stolen from the process and written onto the swap. It's called page replacement. When processes need pages that have been thrown out of memory by the page replacement demon (in case of Linux, it's called bdflush but on some unix implementations, there is a process called updated or paged which performs this function.), it pages them in. If the page is in memory (buffer cache would a good place to look), we're talking about the soft page fault. If the page has to be read from the disk, we're talking about hard page fault. Processes that page get charged for a lot of CPU time, all of it in the kernel mode. If you have gtop (my kindest advice is to get it), you can set preferences and see kernel mode for the processes. Ones consuming large amounts of the kernel mode are ones that are paging. On 11/03/2003 01:24:27 PM, Sai Selvaganesan wrote: hi tanel and mladen not every time a process is started does it swap but sometimes swapping does happen.(this is from the top o/p which shows a increase in the memory used in swap.).how do we check whether a single process swaps or not? and the dbw process is using more % of memory than a couple of days back.(o/p pf ps aux). is this how linux kernel works or is there something else i can check. thanks sai what is meant by OP,tanel.. Tanel Poder wrote: Thanks Mladen, that was a good tip about linux kernel enhancement, however OP still uses 2.4.9 as stated in original post. I just wanted to know whether OP actually sees excessive paging or just memory being full, the latter one, as you know, isn't really a problem. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 5:39 PM The whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserFS or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate the buffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see free memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08) is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai Jesse, Rich wrote: If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the oracle line of ipcs -a (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp
Re: memory usage by dbw very high
What we have here is a confusion with terminology. Process cannot be swapping. When there is a serious shortage of memory, the swap demon (yes, your Unix box is haunted) known by the horrible name of [kswapd] writes the whole address space space belonging to the process onto swap. At that point, process is swapped. Unless, we are talking about the kswapd process, the process cannot be swapping. Kernel swap demon seldomly uses oracle database. Process can be paging. When there is serious shortage of memory (but less serious then in the first case), pages are stolen from the process and written onto the swap. It's called page replacement. When processes need pages that have been thrown out of memory by the page replacement demon (in case of Linux, it's called bdflush but on some unix implementations, there is a process called updated or paged which performs this function.), it pages them in. If the page is in memory (buffer cache would a good place to look), we're talking about the soft page fault. If the page has to be read from the disk, we're talking about hard page fault. Processes that page get charged for a lot of CPU time, all of it in the kernel mode. If you have gtop (my kindest advice is to get it), you can set preferences and see kernel mode for the processes. Ones consuming large amounts of the kernel mode are ones that are paging. On 11/03/2003 01:24:27 PM, Sai Selvaganesan wrote: hi tanel and mladen not every time a process is started does it swap but sometimes swapping does happen.(this is from the top o/p which shows a increase in the memory used in swap.).how do we check whether a single process swaps or not? and the dbw process is using more % of memory than a couple of days back.(o/p pf ps aux). is this how linux kernel works or is there something else i can check. thanks sai what is meant by OP,tanel.. Tanel Poder [EMAIL PROTECTED] wrote: Thanks Mladen, that was a good tip about linux kernel enhancement, however OP still uses 2.4.9 as stated in original post. I just wanted to know whether OP actually sees excessive paging or just memory being full, the latter one, as you know, isn't really a problem. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 5:39 PM The whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserFS or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate the buffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see free memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08) is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai Jesse, Rich wrote: If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the oracle line of ipcs -a (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idle CPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idle CPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle CPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idle Mem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448K buff Swap: 2048152K av, 1652K used, 2046500K free 1852468K cached sga size: Total System Global Area
Re: memory usage by dbw very high
Thanks Mladen, that was a good tip about linux kernel enhancement, however OP still uses 2.4.9 as stated in original post. I just wanted to know whether OP actually sees excessive paging or just memory being full, the latter one, as you know, isn't really a problem. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 01, 2003 5:39 PM The whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserFS or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate the buffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see free memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08) is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai Jesse, Rich [EMAIL PROTECTED] wrote: If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the oracle line of ipcs -a (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idle CPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idle CPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle CPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idle Mem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448K buff Swap: 2048152K av, 1652K used, 2046500K free 1852468K cached sga size: Total System Global Area 1084823632 bytes Fixed Size 452688 bytes Variable Size 335544320 bytes Database Buffers 738197504 bytes Redo Buffers 10629120 bytes pga aggregate size:700M and ps o/p of dbw process USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND oracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06 ora_dbw0_revenue please advise. what is really going on. thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: memory usage by dbw very high
Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see "free" memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08)is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai"Jesse, Rich" [EMAIL PROTECTED] wrote: If I'm not mistaken, this figure includes the size of the shared memorysegment from the SGA. Take the output of the "oracle" line of "ipcs -a"(hopefully you'll only have one!) and subtract it from the process size toget a better idea of the non-shared memory size of the process.RichRich Jesse System/Database Administrator[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA-Original Message-Sent: Friday, October 31, 2003 3:49 PMTo: Multiple recipients of list ORACLE-Lhii have a system that has no active users at this point of time. the memoryused by the dbw process is very high leading to a lot of swapping when anyprocess starts.here are the spcesversion:9.2.0.4os:Linux 2.4.9-e.24smpo/p from top:1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stoppedCPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idleCPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idleCPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idleCPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idleMem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448KbuffSwap: 2048152K av, 1652K used, 2046500K free 1852468Kcachedsga size:Total System Global Area 1084823632 bytesFixed Size 452688 bytesVariable Size 335544320 bytesDatabase Buffers 738197504 bytesRedo Buffers 10629120 bytespga aggregate size:700Mand ps o/p of dbw processUSER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMANDoracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06ora_dbw0_revenueplease advise. what is really going on.thankssai-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jesse, RichINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: memory usage by dbw very high
The whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserFS or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate the buffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see free memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08) is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai Jesse, Rich [EMAIL PROTECTED] wrote: If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the oracle line of ipcs -a (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idle CPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idle CPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle CPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idle Mem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448K buff Swap: 2048152K av, 1652K used, 2046500K free 1852468K cached sga size: Total System Global Area 1084823632 bytes Fixed Size 452688 bytes Variable Size 335544320 bytes Database Buffers 738197504 bytes Redo Buffers 10629120 bytes pga aggregate size:700M and ps o/p of dbw process USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND oracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06 ora_dbw0_revenue please advise. what is really going on. thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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).
memory usage by dbw very high
hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35132 processes: 131 sleeping, 1 running, 0 zombie, 0 stoppedCPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idleCPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idleCPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idleCPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idleMem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448K buffSwap: 2048152K av, 1652K used, 2046500K free 1852468K cached sga size: Total System Global Area 1084823632 bytesFixed Size 452688 bytesVariable Size 335544320 bytesDatabase Buffers 738197504 bytesRedo Buffers 10629120 bytes pga aggregate size:700M and ps o/p of dbw process USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND oracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06 ora_dbw0_revenue please advise. what is really going on. thanks sai
RE: memory usage by dbw very high
If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the oracle line of ipcs -a (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 processes: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idle CPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idle CPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle CPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idle Mem: 3089964K av, 3083380K used,6584K free, 846848K shrd, 193448K buff Swap: 2048152K av,1652K used, 2046500K free 1852468K cached sga size: Total System Global Area 1084823632 bytes Fixed Size 452688 bytes Variable Size 335544320 bytes Database Buffers 738197504 bytes Redo Buffers 10629120 bytes pga aggregate size:700M and ps o/p of dbw process USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND oracle4062 0.0 16.4 1131260 508168 ?S10:16 0:06 ora_dbw0_revenue please advise. what is really going on. thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: memory usage by dbw very high
rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08)is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai"Jesse, Rich" [EMAIL PROTECTED] wrote: If I'm not mistaken, this figure includes the size of the shared memorysegment from the SGA. Take the output of the "oracle" line of "ipcs -a"(hopefully you'll only have one!) and subtract it from the process size toget a better idea of the non-shared memory size of the process.RichRich Jesse System/Database Administrator[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA-Original Message-Sent: Friday, October 31, 2003 3:49 PMTo: Multiple recipients of list ORACLE-Lhii have a system that has no active users at this point of time. the memoryused by the dbw process is very high leading to a lot of swapping when anyprocess starts.here are the spcesversion:9.2.0.4os:Linux 2.4.9-e.24smpo/p from top:1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stoppedCPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idleCPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idleCPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idleCPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idleMem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448KbuffSwap: 2048152K av, 1652K used, 2046500K free 1852468Kcachedsga size:Total System Global Area 1084823632 bytesFixed Size 452688 bytesVariable Size 335544320 bytesDatabase Buffers 738197504 bytesRedo Buffers 10629120 bytespga aggregate size:700Mand ps o/p of dbw processUSER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMANDoracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06ora_dbw0_revenueplease advise. what is really going on.thankssai-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jesse, RichINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Automatic Undo Management Memory management in 9i
Fellow Listers, Could you please share your experience with Automatic Undo Management and Automatic Memory Management. Would you recommend it? One of the Sr. DBAs here suggested not to implement automatic memory management in 9.2.0.3 but wants to implement it in 9.2.0.4. His suggestion that things would have been fixed in newer version of oracle does'nt seem right to me. I have RTFM ed and seems simple for AUM ...as with memory management, I am a little hesitant and would like to consider your experiences. Thanks in advance. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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: x$ constructs and memory
Thanks Raj, I knew about dbms_system.ksdwrt to write to trace files or the alert log or both but not these two. I have see from google that kcfrms allows the resetting of IO counters in v$session_event and v$filestat. And KSDFLS is part of the suite of functions to write to the alert log or trace file and flushes any pending output. http://www.oracleadvice.com/Tips/db ms_system_v2.htm gives a good description of the functions in DBMS_SYSTEM. Thanks Raj, kind regards Pete In article [EMAIL PROTECTED], Jamadagni, Rajendra [EMAIL PROTECTED] writes dbms_system.KCFRMS|KSDFLS (not sure about this one). Raj -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: x$ constructs and memory
Thanks very much Gopal, I have just replied to Raj's post on the same subject. kind regards Pete In article [EMAIL PROTECTED], K Gopalakrishnan [EMAIL PROTECTED] writes Pete: Sorry for the delay. I was traveling back to Bangalore from San Francisco when you sent the message. There is a procedure in the DBMS_SYSTEM package called KCFRMS which resets certain timing information from the X$KCFIO (which is exposed as V$FILESTAT). And also there is an event which can be used to flush the buffer cache and that will reset the part of the X$BH stats (very similar to ALTER SYSTEM FLUSH BUFFER CACHE in 10g and above!!). Regards, Gopal -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: x$ constructs and memory
Pete: Sorry for the delay. I was traveling back to Bangalore from San Francisco when you sent the message. There is a procedure in the DBMS_SYSTEM package called KCFRMS which resets certain timing information from the X$KCFIO (which is exposed as V$FILESTAT). And also there is an event which can be used to flush the buffer cache and that will reset the part of the X$BH stats (very similar to ALTER SYSTEM FLUSH BUFFER CACHE in 10g and above!!). Regards, Gopal - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 04, 2003 3:24 AM Hi Gopal, I have followed this thread with interest and i was waiting for you to elaborate on the following statement, specifically what undocumented procedures ? kind regards Pete code and you can not create/update/delete them. However there are some undocumented procudures , thru which you can reset certain tables. Regards, Gopal -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: K Gopalakrishnan 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: x$ constructs and memory
Hi Gopal, I have followed this thread with interest and i was waiting for you to elaborate on the following statement, specifically what undocumented procedures ? kind regards Pete code and you can not create/update/delete them. However there are some undocumented procudures , thru which you can reset certain tables. Regards, Gopal -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: x$ constructs and memory
Title: RE: x$ constructs and memory dbms_system.KCFRMS|KSDFLS (not sure about this one). Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Pete Finnigan [mailto:[EMAIL PROTECTED]] Sent: Friday, October 03, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Subject: Re: x$ constructs and memory Hi Gopal, I have followed this thread with interest and i was waiting for you to elaborate on the following statement, specifically what undocumented procedures ? kind regards Pete This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: x$ constructs and memory
No, X$ tables exist even before a database is created - they are mostly instance related structures, not database or data dictionary ones. Do a startup nomount and select from x$ksuse or even dual for example and you see. You just can't select from these x$ tables which want to read physical database structures when database doesn't exist or isn't mounted/open. The translation of SGA memory structures to a returnable row set is pure C code, I think. Or if you can point me to these certain catalog scripts, I'd be glad to read them :O) But yes, about the fixed area I wasn't entirely correct at first. The Oracle term fixed_sga is really fixed, that it's size shouln't change if you don't relink of patch your executables. x$version contents are probably in fixed_sga. The other stuff, like enqueues goes to variable SGA (shared pool), but still many memory structures are not dynamic - they're allocated during startup and will remain the same during the lifetime of an instance. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 9:24 PM With all due respect, I don't believe that it is a fixed area. You can create X$ tables by running certain catalog scripts. I believe that the description of X$ tables is located logically close to the description of the data dictionary, which would mean shared pool, not the fixed one. Now, can we get back to bears? -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tanel Poder Sent: Monday, September 29, 2003 1:45 PM To: Multiple recipients of list ORACLE-L Subject: Re: x$ constructs and memory What I have not checked so far is how an ALTER SYSTEM increasing a parameter affects the SGA. In practice it's a realloc() (functionally speaking). It would seem reasonable to me to have a shared memory segment to hold all parameters which can by dynamically changed. I wouldn't touch it if parameters are decreased, but I would have to realloc it in case of a massive increase. Hmm, I guess that I would allow some spare memory initially, performance penalty would otherwise be severe. Which all makes the 10g dynamic rearrangement quite sensible ... Hi! I think the behaviour depends on which parameter you are changing. If you're changing shared_pool_size to higher size, then just additional extents of memory are allocated and heap header is updated. If you set sort_area_size higher, nothing particular happens, except some maximum is increased in UGA I believe and during next sort you can go up to that limit. Some parameters like enqueue_resources can't be changed in the fly, because they are fixed, they stay in fixed area of SGA, fixed area isn't managed as heap as I understand, it does not have any free or LRU lists, because it's physical structure remains unchanged during the lifetime of an instance. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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
RE: x$ constructs and memory
Hi Steve and welcome back, Thanks for that detailed answer BUT... A practical question from the original post remains: What happens when these x$constructs begin to consume large amounts of memory? From your explanation I'm assuming that, beyond monitoring the SGA and PGA, memory consumption of individual X$ in-memory data structures is generally not something we need to worry about. How can we determine how much memory they actually consume? Are there any related tunable parameters of which we should be aware? Thanks, Steve Orr -Original Message- Sent: Monday, September 29, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Daniel Fink Sent: Tuesday, 30 September 2003 1:10 AM To: Multiple recipients of list ORACLE-L I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble mind. As I began to ponder this (I asked the resident marmot, but he must be a SQL*Server expert...), I came up with several questions. Where in memory (sga or other) do the x$ constructs reside? Some of them are 'populated' by reading file-based structures (control file, datafile headers, undo segments). Does this information reside in memory or is it loaded each time the x$ construct is accessed? What happens when these x$constructs begin to consume large amounts of memory? Is there an upper bound? Daniel Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Adams 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: Orr, Steve 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
RE: x$ constructs and memory
I don't generally get too involved in the x$ stuff, just because it normally helps me very little in my DBA work. Nonetheless, I have been following this one somewhat, and if my understanding is correct, x$ tables are not actually responsible for consuming memory, they are merely a mechanism for displaying various structures internal to the kernel, many of which happen to be transient. Jared Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 07:49 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: x$ constructs and memory Hi Steve and welcome back, Thanks for that detailed answer BUT... A practical question from the original post remains: What happens when these x$constructs begin to consume large amounts of memory? From your explanation I'm assuming that, beyond monitoring the SGA and PGA, memory consumption of individual X$ in-memory data structures is generally not something we need to worry about. How can we determine how much memory they actually consume? Are there any related tunable parameters of which we should be aware? Thanks, Steve Orr -Original Message- Sent: Monday, September 29, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Daniel Fink Sent: Tuesday, 30 September 2003 1:10 AM To: Multiple recipients of list ORACLE-L I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble mind. As I began to ponder this (I asked the resident marmot, but he must be a SQL*Server expert...), I came up with several questions. Where in memory (sga or other) do the x$ constructs reside? Some of them are 'populated' by reading file-based structures (control file, datafile headers, undo segments). Does this information reside in memory or is it loaded each time the x$ construct is accessed? What happens when these x$constructs begin to consume large amounts of memory? Is there an upper bound? Daniel Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Adams 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
Re: x$ constructs and memory
Mladen: I am not sure where I am failing to understand you ;). First of all X$ objects are NOT tables, so there is no question of blocks or memory or dictionary cache. They are some C structures and their point in time (I am not finding a better word) values are exposed as table formats. That is what my understanding. I don't see any relation between them and dictionary cache.. AM I missing something? Regards, Gopal - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 2:24 AM Description of the X$ does reside in the dictionary cache, but those tables are entry points into the code. So, besides their description, they don't consume memory, i.e. their blocks aren't cached. On Tue, 2003-09-30 at 15:29, [EMAIL PROTECTED] wrote: I don't generally get too involved in the x$ stuff, just because it normally helps me very little in my DBA work. Nonetheless, I have been following this one somewhat, and if my understanding is correct, x$ tables are not actually responsible for consuming memory, they are merely a mechanism for displaying various structures internal to the kernel, many of which happen to be transient. Jared Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 07:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: x$ constructs and memory Hi Steve and welcome back, Thanks for that detailed answer BUT... A practical question from the original post remains: What happens when these x$constructs begin to consume large amounts of memory? From your explanation I'm assuming that, beyond monitoring the SGA and PGA, memory consumption of individual X$ in-memory data structures is generally not something we need to worry about. How can we determine how much memory they actually consume? Are there any related tunable parameters of which we should be aware? Thanks, Steve Orr -Original Message- Sent: Monday, September 29, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Daniel Fink Sent: Tuesday, 30 September 2003 1:10 AM To: Multiple recipients of list ORACLE-L I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble
RE: x$ constructs and memory
Hi Steve, The X$ interfaces do not use memory persistently, and the memory usage of the X$ tables is fixed and necessary to an instance. Thus memory growth is not possible. Memory growth is possible for the segmented arrays, which some of the X$ interfaces expose. However, it is very unusual, because the defaults are rather generous. If you query V$RESOURCE_LIMIT, you will normally see that the MAX_UTILIZATION falls way short of the INITIAL_ALLOCATION. Even if there is significant growth, it is unlikely to chew up more than a few M of shared pool memory, because the structures involved are each very small. (You do however need to worry about similar growth in the instance lock database in a RAC environment). To answer another question raised later in this thread ... the metadata for X$ objects is present in the library cache during a query and may be cached afterwards, but there is no corresponding metadata in the dictionary cache. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Steve Sent: Wednesday, 1 October 2003 12:49 AM To: Multiple recipients of list ORACLE-L Hi Steve and welcome back, Thanks for that detailed answer BUT... A practical question from the original post remains: What happens when these x$constructs begin to consume large amounts of memory? From your explanation I'm assuming that, beyond monitoring the SGA and PGA, memory consumption of individual X$ in-memory data structures is generally not something we need to worry about. How can we determine how much memory they actually consume? Are there any related tunable parameters of which we should be aware? Thanks, Steve Orr -Original Message- Sent: Monday, September 29, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Daniel Fink Sent: Tuesday, 30 September 2003 1:10 AM To: Multiple recipients of list ORACLE-L I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble mind. As I began to ponder this (I asked the resident marmot, but he must be a SQL*Server expert...), I came up with several questions. Where in memory (sga or other) do the x$ constructs reside? Some of them are 'populated' by reading file-based structures (control file, datafile headers, undo segments). Does this information reside in memory or is it loaded each time the x$ construct is accessed? What happens when these x$constructs begin to consume large
Re: x$ constructs and memory
Hi! Yep, I also think that x$ tables have nothing to do with row cache, instead their behaviour is hardcoded to Oracle executable. I did a simple test just in case (but I'm not sure whether it was sufficient), by parsing a select from x$kturd 10 times didn't see any big increases in v$rowcache stats. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:14 AM Mladen: I am not sure where I am failing to understand you ;). First of all X$ objects are NOT tables, so there is no question of blocks or memory or dictionary cache. They are some C structures and their point in time (I am not finding a better word) values are exposed as table formats. That is what my understanding. I don't see any relation between them and dictionary cache.. AM I missing something? Regards, Gopal - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 2:24 AM Description of the X$ does reside in the dictionary cache, but those tables are entry points into the code. So, besides their description, they don't consume memory, i.e. their blocks aren't cached. On Tue, 2003-09-30 at 15:29, [EMAIL PROTECTED] wrote: I don't generally get too involved in the x$ stuff, just because it normally helps me very little in my DBA work. Nonetheless, I have been following this one somewhat, and if my understanding is correct, x$ tables are not actually responsible for consuming memory, they are merely a mechanism for displaying various structures internal to the kernel, many of which happen to be transient. Jared Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 07:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: x$ constructs and memory Hi Steve and welcome back, Thanks for that detailed answer BUT... A practical question from the original post remains: What happens when these x$constructs begin to consume large amounts of memory? From your explanation I'm assuming that, beyond monitoring the SGA and PGA, memory consumption of individual X$ in-memory data structures is generally not something we need to worry about. How can we determine how much memory they actually consume? Are there any related tunable parameters of which we should be aware? Thanks, Steve Orr -Original Message- Sent: Monday, September 29, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now
x$ constructs and memory
I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble mind. As I began to ponder this (I asked the resident marmot, but he must be a SQL*Server expert...), I came up with several questions. Where in memory (sga or other) do the x$ constructs reside? Some of them are 'populated' by reading file-based structures (control file, datafile headers, undo segments). Does this information reside in memory or is it loaded each time the x$ construct is accessed? What happens when these x$constructs begin to consume large amounts of memory? Is there an upper bound? Daniel Fink begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: x$ constructs and memory
Dan - I think you are in grave danger of forgetting the point of sitting on the top of mountains Either that or your Colorado mountains have nothing on our variety from the NW Highlands of Scotland... (grin!) peter edinburgh -Original Message- From: Daniel Fink [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 4:10 PM To: Multiple recipients of list ORACLE-L Subject: x$ constructs and memory I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble mind. As I began to ponder this (I asked the resident marmot, but he must be a SQL*Server expert...), I came up with several questions. Where in memory (sga or other) do the x$ constructs reside? Some of them are 'populated' by reading file-based structures (control file, datafile headers, undo segments). Does this information reside in memory or is it loaded each time the x$ construct is accessed? What happens when these x$constructs begin to consume large amounts of memory? Is there an upper bound? Daniel Fink * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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).
