RE: Free memory in server and session PGA memory
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "sreejith.sreekantan@xxxxxxxxxx" <sreejith.sreekantan@xxxxxxxxxx>, Oracle - L <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 14 Nov 2011 16:07:53 -0500
Sreejith,
Just trying to answer your basic questions 1-4 :
1) If the process/connection isn't dropped then memory is not released to the
OS. This is expected behavior of the Oracle RDBMS. Since you use WL with pooled
connections then chances are they are perpetual in nature and as such no memory
will be released until WL is restarted.
2) Whether it's efficient or not depends on your particular situation. If you
have lots of WL processes that request a DB handle, do a little SQL, then
release the handle then it's very efficient because the alternative is to
create a new connection every time and that would kill all scalability of your
application both on the app server and the db server.
3) The sessions are inactive in oracle because most of the time no WL process
has requested and use the connections from the pool. This is expected and
normal behavior. If any given WL server has 30 connections to the database but
under normal load only 3 processes at a time use a handle then 27 will be
marked as inactive in oracle. Under heavy load you may see 25 or all 30
connections in use and you have to size the pool for this load because
otherwise you have waiting on the app server under this load.
4) Is there a question in there? :) The answer is the same as #1 because the
question is the same.
In general I hate monitors that check for server memory and server CPU. It is
good to collect the stats so you can graph and trend it but as alerts they are
mostly useless. If your CPU alert goes off and you go check what's going on
you'll find a bunch of db processes doing their thing. Most of the time all you
can do is shrug and say "yeah the app is putting a heavy load on the db. Not
too much I can do about it.". Unless of course you find a run away process but
in my experience most of the time you can't fix it.
Same thing for the memory monitor which just causes you to do a lot of work and
ask questions like below. You can't fix it. It is what it is.
Thanks,
Finn
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sreejith S Nair
Sent: Sunday, November 13, 2011 9:12 PM
To: Oracle - L
Subject: Free memory in server and session PGA memory
Hi Friends,
Environment : Solaris 10 , oracle 11.2.0.2
We have our production databases monitored by the BMC portal application. We
often get alerts and warnings on free memory on the server. Say my server has
24 GB physical memory, we will get an alert when it comes down 2.4GB which is
10%.
We troubleshoot this starting with vmstat command and we can see the 'free'
part in vmstat do report a value less than 3 GB. This server is part of a two
node rac which hosts two databases. If I do a ps command sort on pmem I can see
that the processes consuming most memory ( which has pmem field , which is a
ratio as 4 - 5 ) are oracle server processes ( oracle<ORACLE_SID>(LOCAL=NO)).
Now IF I check what these processes are doing I can see that these are coming
from jdbc thin client ( yes, we use weblogic). Most of these sessions coming
top in ps command are now INACTIVE. So my understanding is that these are app
server connections which were once took for doing something and later released
to connection pool. The question here are
1. Why memory taken by these processes ( of course from PGA ) are not released
to OS after use.
2. I understand this is default weblogic behavior of keeping connections in
pool and use them when they need. But considering memory associated with these
are still not released to OS , is this an efficient way ?
3. If I check the wait event of these INACTIVE sessions they have ' SQL Net
wait '. Again , is this a good behavior from weblogic ?
4. I have also run Tanel's procmm and noticed the Anon memory almost equals to
'PGA used by this session' from v$sesstat which again confirms that 'unused
memory' ( once used) is still associated with the process.
I would like to get help from experts who might have already thought about this
and will have answer to my questions, which in a way may not e relevant.
PS : I already have an SR open for which I did not get a proper response.
Best Regards,
Sreejith
-- Sent from my iPhone
DISCLAIMER: "The information in this e-mail and any attachment is intended
only for the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly contact
the sender and destroy all copies of the original communication. IBS makes no
warranty, express or implied, nor guarantees the accuracy, adequacy or
completeness of the information contained in this email or any attachment and
is not liable for any errors, defects, omissions, viruses or for resultant loss
or damage, if any, direct or indirect." --
//www.freelists.org/webpage/oracle-l
>>> This e-mail and any attachments are confidential, may contain legal,
>>> professional or other privileged information, and are intended solely for
>>> the addressee. If you are not the intended recipient, do not use the
>>> information in this e-mail in any way, delete this e-mail and notify the
>>> sender. CEG-IP1
--
//www.freelists.org/webpage/oracle-l
Other related posts:
