Forum Stats

  • 3,827,812 Users
  • 2,260,823 Discussions
  • 7,897,381 Comments

Discussions

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

sweetritz
sweetritz Member Posts: 183
edited Jun 9, 2014 2:03AM in General Database Discussions

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

sweetritz

Best Answer

  • sweetritz
    sweetritz Member Posts: 183
    Answer ✓

    Hi all,

    I gave the following query which resulted the sessions which were inactive since many months and were occupying more memory, so after consulting the Apps team I killed them to release around 21 GB memory out of 64 GB.

    SELECT

    s.sid,s.serial#,s.username

    ,s.status

    ,substr(s.machine,1,10)

    ,s.osuser,s.module

    ,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time

    -- idle time

    -- days separately

    ,substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||

    -- hours

    substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||

    -- minutes

    substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||

    --seconds

    substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2) idle_time

    FROM v$session s, v$process p

    WHERE s.username IS NOT NULL

    -- use outer join to show sniped sessions in

    -- v$session that don't have an OS process

    AND p.addr(+) = s.paddr

    and s.sid=2398

    ORDER BY

    idle_time desc;

«134

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    >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)

    [[email protected] ~]$ 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

    [[email protected] ~]$

    sb92075
  • sweetritz
    sweetritz Member Posts: 183
    edited Jun 3, 2014 9:19AM

    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

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    your system is mostly idle and not paging; so all is OK

    sb92075
  • sweetritz
    sweetritz Member Posts: 183

    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

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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.

  • sweetritz
    sweetritz Member Posts: 183
    edited Jun 3, 2014 9:37AM

    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?

  • Thierry H.
    Thierry H. Member Posts: 854

    what is the output of:

    1- cat /proc/meminfo

    2- free -m

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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.

  • sweetritz
    sweetritz Member Posts: 183

    zeus04-oracle> cat /proc/meminfo

    cat: cannot open /proc/meminfo

    zeus04-oracle> free -m

    ksh: free:  not found

  • sweetritz
    sweetritz Member Posts: 183

    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?

This discussion has been closed.