Forum Stats

  • 3,838,745 Users
  • 2,262,397 Discussions
  • 7,900,746 Comments

Discussions

Why Oracle DB Server is Heavly Swapping?

Hey guys, how are you doing? Hope it's ok! ;)

Here I'm facing an annoying problem with a legacy Server (Standalone Database running 11.2 on RHEL 6). From 10 days ago, the monitoring is freaking out with frequent alarms of high Swap consumption, and checking that, I've found nothing strange:

-- Memory config (ASMM):

SQL> show parameter sga


NAME    TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga    boolean FALSE

pre_page_sga    boolean FALSE

sga_max_size    big integer 20G

sga_target    big integer 15G

SQL> show parameter pga

NAME    TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target    big integer 4G


SQL> show parameter memory

NAME    TYPE VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address    integer 0

memory_max_target    big integer 0

memory_target    big integer 0

shared_memory_address    integer 0


Status of PGA/UGA Consumption:


SQL> SELECT SCHEMANAME, STATE, TRUNC(sum(value/1024/1024),2) "MB" FROM (

select s.sid, n.name, s.value, USERNAME, ss.COMMAND, ss.STATUS, ss.STATE, ss.SCHEMANAME, ss.SQL_ID

from v$statname n,v$sesstat s, v$session ss

where n.STATISTIC# = s.STATISTIC#

and s.SID = 2 3 4 5 ss.SID

and name like 'session%memory'

order by 3 desc)

group by SCHEMANAME, STATE

order by 3 desc 6 7 8 9

10 ;


SCHEMANAME STATE MB

------------------------------ ------------------- ----------

FRIGOCENTER WAITING 1841.05

SYS WAITING 160.62

IONV_SYNC WAITING 156.8

SYS WAITED SHORT TIME 38.17

IONV_SYNC WAITED SHORT TIME 28.38

FUSIONT WAITING 1.94


6 rows selected.


Consumption of SGA:

SQL> SELECT ROUND (used.bytes / 1024 / 1024, 2) used_mb,

    ROUND (free.bytes / 1024 / 1024, 2) free_mb,

    ROUND (tot.bytes / 1024 / 1024, 2) total_mb

 FROM (SELECT SUM (bytes) bytes

     FROM v$sgastat

     WHERE name != 'free memory') used 2  3  4  5  6 ,

    (SELECT SUM (bytes) bytes

     FROM v$sgastat

     WHERE name = 'free memory') free,

    (SELECT SUM (bytes) bytes FROM v$sgastat) tot 7  8  9  10  

 11 ;


  USED_MB  FREE_MB TOTAL_MB

---------- ---------- ----------

 11780.25  1504.31 13284.56


But, see this free output:

[[email protected] ~]# free -m

       total    used    free   shared  buffers   cached

Mem:     32057   31478    578   12393     6   19917

-/+ buffers/cache:   11553   20503

Swap:    16383    8528    7855


Why is so much memory being consumed? I've defined SGA+PGA to ~60% of the total memory of this server, and Ok, there is the (in)famous FS linux cache, but, Swapping? Seriously Mr Torvalds?

Bellow is the top 10 processes consuming memory. At first look, I'm focused at understand why mman and dbwn are consuming so much memory, but after a little bit of reading I realize that the memory consumption reported is Shared Memory, so apparently all this process uses the same shared memory segments. Ok, but now I've no idea where to go. What this can be? A Linux issue maybe?


[[email protected] ~]# ps -eo pmem,pcpu,vsize,pid,cmd | sort -k 1 -nr | head -10

26.2 0.0 21205416 2988 ora_mman_WINT

22.7 0.0 21218460 2994 ora_dbw2_WINT

22.4 0.0 21217436 2992 ora_dbw1_WINT

22.4 0.0 21216412 2990 ora_dbw0_WINT

19.1 0.0 21218424 12025 oracleWINT (LOCAL=NO)

12.8 14.0 21246328 26081 oracleWINT (LOCAL=NO)

12.3 0.0 21208204 34703 oracleWINT (LOCAL=NO)

11.9 45.8 21254584 23898 oracleWINT (LOCAL=NO)

10.8 4.4 21246328 26083 oracleWINT (LOCAL=NO)

10.8 3.2 21211488 1428 oracleWINT (LOCAL=NO)

Tagged:

Answers