Oracle 9.2.0.6 Memory leak
IPavlov
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.
joel garry
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