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:
[root@DBWINTHOR-NEW ~]# 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?
[root@DBWINTHOR-NEW ~]# 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)