[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: [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:
