1 2 3 Previous Next 35 Replies Latest reply on Jun 9, 2014 6:03 AM by Warrior25

How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 12:46 PM

Hi DBAs,

I've got one Onesight alert mail stating below:

Short Description: Onesight Alert: ZEUS DB Monitor (zeus04)

Description:

Solaris 5.8: vmstat memory(free) - Free Memory for zeus04 2065112 8:06:02 AM IST 2 min

Free Memory below 4GB: Value was 3,965,488 , threshold is 4194304.0 - 12:36:02 AM IST

Upon issuing OS level commands below I got the following understanding:

prstat

This command returned me the top process ID(3578) which is using more CPU. then,

prstat -Lm -p 3578

this command returned me the following output:

PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWPID

3578 oracle 0.7 2.3 1.6 0.0 0.0 90 4.4 1.5 483 235 1K 0 java/838694

3578 oracle 0.3 0.0 0.2 0.0 0.0 95 1.5 0.1 52 8 41 0 java/839336

I thought some Java process would be running so asked the Apps Support team and the said:

we believe this process is related to the Oracle OEM agent Java process

Now how do i drill down the issue and resolve because free memory is going below 2GB almost everyday.

DB environment details:

OS:Solaris 5.8(Unix)

DB version: 10.2.0.5

Memory size:64 GB

Regards,

Ritu

1. Re: How to know which process is causing high memory usage on DB?

sb92075 Jun 3, 2014 1:03 PM (in response to sweetritz)

>Now how do i drill down the issue and resolve because free memory is going below 2GB almost everyday.
above is NOT a problem; but just an observation

on any *NIX system running Oracle DB free RAM will consistently be at or below 5% of total RAM.

a better way to determine if RAM is a resource bottleneck is measured by vmstat.

RAM is a bottleneck when (si + so) > (bi + bo)

[oracle@localhost ~]$ vmstat 6 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b   swpd   free   buff cache   si   so    bi    bo   in   cs us sy id wa st
0 0 169364 98468 102744 470568    1    1     8    30    2 125 3 5 92 1 0
0 0 169364 98328 102756 470556    0    0     0    30 1095 1106 3 16 79 2 0
1 0 169364 102188 102760 470556    0    0     0    17 1001 1139 3 4 92 0 0
0 0 169364 102188 102768 470556    0    0     0    16 1001 1047 2 3 95 0 0
0 0 169364 102188 102772 470556    0    0     0    37 1002 919 2 4 93 1 0
0 0 169364 102188 102776 470556    0    0     0    15 1002 1029 1 2 97 0 0
0 0 169364 102188 102780 470556    0    0     0    30 1004 1123 2 3 94 1 0
0 0 169364 98344 102788 470560    0    0     1    41 1002 1138 6 11 83 0 0
0 0 169364 97848 102804 470560    0    0     0    25 994 1073 1 3 95 1 0
0 0 169364 97848 102820 470560    0    0     0    15 999 939 2 3 95 0 0
[oracle@localhost ~]$
1 person found this helpful
Like Show 0 Likes(0)

Actions
2. Re: How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 1:19 PM (in response to sb92075)

Hi @sb92075
Thanks for the info. here is the output on my server:
$ vmstat 6 10
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m2 m3 in sy cs us sy id
0 0 0 72946072 15218584 580 1864 1809 16 10 0 2 5 0 3 1 11392 26182 5020 6 3 90
0 0 0 42096944 3956280 180 1385 0 12 5 0 0 16 0 15 0 3418 9207 4037 2 2 96
0 0 0 42095224 3955088 191 1406 0 16 12 0 0 102 0 78 0 4790 15375 3959 6 2 92
0 0 0 42083016 3953600 252 1932 1 0 0 0 0 0 0 0 0 4951 48048 4453 6 2 91
0 0 0 42093792 3953576 88 872 1 13 13 0 0 0 0 0 0 5128 22261 4963 8 2 90
0 0 0 42093208 3952936 93 936 23 4 3 0 0 1 0 0 0 4235 14283 3738 5 2 94
0 0 0 42093744 3953416 242 1327 0 16 16 0 0 0 0 0 1 4430 44196 3996 6 2 92
0 0 0 42092776 3952496 223 1562 0 3 1 0 0 9 0 9 0 4770 57002 4373 6 2 92
0 0 0 42093744 3953368 88 852 0 0 0 0 0 0 0 0 0 4199 11456 4378 4 2 94
0 0 0 42081616 3945368 563 4341 0 3 3 0 0 0 0 0 0 3950 44034 3482 6 3 92
Like Show 0 Likes(0)

Actions
3. Re: How to know which process is causing high memory usage on DB?

sb92075 Jun 3, 2014 1:24 PM (in response to sweetritz)

your system is mostly idle and not paging; so all is OK
1 person found this helpful
Like Show 0 Likes(0)

Actions
4. Re: How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 1:27 PM (in response to sb92075)

@sb92075 thanks for replying. Then how do I resolve the issue? If its idle then memory consumption should be very less but its 3GB free out of 64GB currently. Any help in digging what is causing the high usage
Like Show 0 Likes(0)

Actions
5. Re: How to know which process is causing high memory usage on DB?

sb92075 Jun 3, 2014 1:34 PM (in response to sweetritz)

sweetritz wrote:

@sb92075 thanks for replying. Then how do I resolve the issue? If its idle then memory consumption should be very less but its 3GB free out of 64GB currently. Any help in digging what is causing the high usage

The only "issue" is your unrealistic expectations.
3/64 = 0.046875 which is very close to my ballpark guesstimate of 5% that is normal & to be expected.
Change the threshold to 2GB then then "alarm" will be thrown less frequently!
There is NO real problem; only an imaginary problem exists between your ears.
Like Show 0 Likes(0)

Actions
6. Re: How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 1:37 PM (in response to sb92075)

@sb92075 cool then. one small update currently the free memory is 1278 MB.
What do you have to say about this? still should I be resting and scratching my ears and not digging it to know the cause?
Like Show 0 Likes(0)

Actions
7. Re: How to know which process is causing high memory usage on DB?

Thierry H. Jun 3, 2014 1:43 PM (in response to sweetritz)

what is the output of:
1- cat /proc/meminfo
2- free -m
Like Show 0 Likes(0)

Actions
8. Re: How to know which process is causing high memory usage on DB?

sb92075 Jun 3, 2014 1:44 PM (in response to sweetritz)

sweetritz wrote:

@sb92075 cool then. one small update currently the free memory is 1278 MB.

What do you have to say about this? still should I be resting and scratching my ears and not digging it to know the cause?

Not that many years ago, no system had even 1GB total RAM.
Free RAM is wasted RAM.
Any RAM that Oracle does not require, then the OS uses it as OS file system cache.
OS is designed to MAXIMIZE the use of RAM rather than allow it to sit idle & be WASTED!
"free RAM" is a meaningless indicator of performance; since it will typically remain slightly above ZERO%
As long as the system (OS) is not actively SWAPPING, then RAM is not a resource bottleneck.
Like Show 0 Likes(0)

Actions
9. Re: How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 1:45 PM (in response to Thierry H.)

zeus04-oracle> cat /proc/meminfo
cat: cannot open /proc/meminfo
zeus04-oracle> free -m
ksh: free: not found
Like Show 0 Likes(0)

Actions
10. Re: How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 1:49 PM (in response to sb92075)

if there are some new sessions that requires to connect to DB and returning an error as "cannot connect : no free memory available". Then what I am supposed to do?
Like Show 0 Likes(0)

Actions
11. Re: How to know which process is causing high memory usage on DB?

Thierry H. Jun 3, 2014 1:52 PM (in response to sweetritz)

please check the following links:
http://blog.scoutapp.com/articles/2010/01/11/free-memory-on-linux-free-m-vs-proc-meminfo
Determining free memory on Linux

This will explain you that the real available RAM is the free + buffer + cached.

As SB explained, the RAM is just allocated ... but can and will be freed by the kernel when a process requires it.

HTH,
Thierry
Like Show 0 Likes(0)

Actions
12. Re: How to know which process is causing high memory usage on DB?

sweetritz Jun 3, 2014 1:56 PM (in response to Thierry H.)

In my case it is Unix server. (Solaris)
Like Show 0 Likes(0)

Actions
13. Re: How to know which process is causing high memory usage on DB?

Thierry H. Jun 3, 2014 2:06 PM (in response to sweetritz)

This is goes about *NIX systems: Unix, Linux.
If you read the links provided and (re)read all what SB told you, make the correlations, then you should be able to reach the conclusion
Like Show 0 Likes(0)

Actions
14. Re: How to know which process is causing high memory usage on DB?

jgarry Jun 3, 2014 4:14 PM (in response to Thierry H.)

Oh come on, if someone is asking about Solaris don't bother with linux.
Using Process Memory Matrix script for understanding Oracle process memory usage - tech.E2SN
1 person found this helpful
Like Show 0 Likes(0)

Actions

1 2 3 Previous Next

Go to original post