Re: Oracle 9.2.0.6 Memory leak

IPavlov wrote:
> We've had couple of incidents on large production system where memory
> utilization on Unix box running Oracle database server grew to 99% and
> performance was severely effected.
> Client applications received some ORA-4030 errors.
> Most of the memory was consumed by Oracle client and background
> processes. Memory utilization after the machine was last rebooted was
> 57%.
> After some research we found information about Oracle bug #4168725 -
> PGA memory leak, which seems to be relevant in our case since the
> version of Oracle Server we are running doesn't contain the patch for
> this bug.
> The only problem is that on our system the jump in memory utilization
> seems to occur sporadically after the system was running for weeks
> or even months with no downtime of DB server and no significant growth
> in memory utilization while the bug description seems to imply that
> memory is constantly leaked until there is no memory left for client
> processes and then ORA-4030 starts to appear.
> The management therefore is not convinced that the memory problem is
> caused by bug #4168725 and is reluctant to upgrade to the version with
> the patch.
> Oracle support also claims that the problem is caused by client
> application, although it is difficult to even come up with some
> scenario
> where client application would cause Oracle server to consume memory
> in such way.
> Did anybody have experience with this Oracle bug or had similar problem
> with memory consumption by Oracle processes? - If so, could you please
> share your experience.
>
> IP.

Since this error is "Operating system process private memory has been exhausted" it may make a difference exactly which "unix" you are on. All unix have settings that limit how much memory a process can have. I take it you've gone through metalink Note:233869.1. I think your management is properly not convinced it is bug 416875. You see, in order for something to be called a bug it has to be demonstrable and replicable. This tends to bias bugs towards more easily replicable situations, like a constant memory leak. Since your problem is sporadic, it may or may not be so easy to replicate (if it is a bug). Also, it is possible that the various point releases of Oracle have different sensitivity to particular situations - obviously, Oracle has been changing and fixing how memory is used in every point release. So going back to the Note: I mentioned... sort_area_size - maybe you have some sorting going on that goes over some limit and sets things off? Maybe some PL/SQL you have is a hog? Maybe you can hint some problem SQL to have the CBO use something less memory intensive? And of course, maybe you've missed something on the unix side. In particular, swap recommendations can be way off on some unix, very version and platform dependent (for example Note:199746.1 has the old swap space = 2-3x physical memory chestnut, on recent hp-ux you can have a much smaller primary swap with file based secondary swap so you don't crash the system when some process goes nutso, and you can then be more liberal about process size limits, and just watch for swapping, and figure out what's causing it when it happens).

>From Note:153367.1: "When setting this parameter, you should examine
the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET. " I think that quote is _way_ off. Google pga_aggregate_target site:www.jlcomp.demon.co.uk for more understanding of that parameter. I've seen situations where simply hacking it in half solved problems voodoo-style. Are you using it? What is it set at? What are the physical characteristics of your platform? Do you have multiple instances?

jg

--
@home.com is bogus.
Poppies!  Toto, I'm so sleepy...
http://www.signonsandiego.com/uniontrib/20060727/news_1b27neurocri.html