I am having difficulty tracing the source of a "heavy swapping" problem with a single database. Any ideas on where to go next are greatly appreciated. I have copied/pasted the results of several settings/configs/events/trace files based on what I have looked at so far.
Oracle 11.2.0.3 database running on Solaris OS. This database supports a commercial product (called Fortify) that scans source code for security vulnerabilities. 99.8% of the time there are no users on this database and no transactions are taking place ... it should be idle but it reports constant swapping and it is very slow to startup, shutdown, apply patches, etc. It performs terribly compared to the other OLTP and heavy batch databases we run. This database starts up (very slowly ... takes approx 4 minutes to start), and after starting all its processes (roughly 9 minutes after startup) it begins reporting these heavy swapping events in the alert log.
Params:
memory_target=6G (even tried 10G to see if that made any difference)
Also tried manually setting pga_aggregate_target=5G, sga_target=5G,
memoryimm_mode_without_autosga=FALSE (disabling AMM)
_______________________________________
Snippet from alert log:
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [9.02%] pct of memory swapped out [0.00%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.
_______________________________________
_______________________________________
Snippet from dbrm trace file:
Heavy swapping observed in last 5 mins: [pct of total memory][bytes]
total swpin [ 9.42%][6326816K], total swpout [ 0.00%][0K]
vm stats captured every 30 secs for last 5 mins:
swpin: swpout:
[ 1.41%][ 952240K] [ 0.00%][ 0K]
[ 0.39%][ 266032K] [ 0.00%][ 0K]
[ 1.46%][ 981656K] [ 0.00%][ 0K]
[ 0.79%][ 536192K] [ 0.00%][ 0K]
[ 0.78%][ 524584K] [ 0.00%][ 0K]
[ 0.68%][ 457696K] [ 0.00%][ 0K]
[ 0.76%][ 510792K] [ 0.00%][ 0K]
[ 0.55%][ 369312K] [ 0.00%][ 0K]
[ 0.89%][ 602664K] [ 0.00%][ 0K]
[ 1.67%][ 1125648K] [ 0.00%][ 0K]
Heavy swapping observed in last 5 mins: [pct of total memory][bytes]
_______________________________________
_______________________________________
Snippet from mmon trace file (not sure what this means but it accompanies the dbrm trace file):
*** 2013-05-01 06:53:02.378
minact-scn master-status: grec-scn:0x04fa.4741d51b gmin-scn:0x04fa.4741d51b gcalc-scn:0x04fa.4741d51b
*** 2013-05-01 06:58:03.353
minact-scn master-status: grec-scn:0x04fa.4741d633 gmin-scn:0x04fa.4741d633 gcalc-scn:0x04fa.4741d633
minact-scn master-status: grec-scn:0x04fa.4741d780 gmin-scn:0x04fa.4741d780 gcalc-scn:0x04fa.4741d780
*** 2013-05-01 07:08:08.480
minact-scn master-status: grec-scn:0x04fa.4741d831 gmin-scn:0x04fa.4741d831 gcalc-scn:0x04fa.4741d831
*** 2013-05-01 07:13:12.356
minact-scn master-status: grec-scn:0x04fa.4741dace gmin-scn:0x04fa.4741dace gcalc-scn:0x04fa.4741dace
_______________________________________
_______________________________________
Results of: SHOW PARAMETER SERVERS;
NAME TYPE VALUE
max_shared_servers integer
parallel_max_servers integer 50
parallel_min_servers integer 8
parallel_servers_target integer 50
shared_servers integer 1
_______________________________________
_______________________________________
Results of: vmstat
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 -- in sy cs us sy id
0 0 0 5309392 15835712 514 2048 14959 0 0 0 0 5 5 -21 0 4606 10083 5281 1 2 98
_______________________________________
_______________________________________
Results of: top (doesn't change a whole lot)
load averages: 1.12, 1.19, 1.20 07:08:04
96 processes: 95 sleeping, 1 on cpu
CPU states: 97.5% idle, 0.7% user, 1.8% kernel, 0.0% iowait, 0.0% swap
Memory: 64G real, 15G free, 26G swap in use, 3277M swap free
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
4734 root 67 59 0 61M 56M sleep 23.5H 0.04% cvm
4979 root 18 59 0 46M 21M sleep 21.2H 0.03% cma
16365 root 1 38 0 2472K 1824K sleep 0:00 0.02% sadc
9501 oracle 1 59 0 0K 0K sleep 0:14 0.01% oracle
23838 oracle 3 59 0 0K 0K sleep 101:25 0.01% tnslsnr
9297 oracle 1 59 0 0K 0K sleep 0:13 0.01% oracle
9361 oracle 1 59 0 0K 0K sleep 0:12 0.01% oracle
27236 oracle 1 59 0 0K 0K sleep 0:08 0.01% oracle
9476 oracle 11 59 0 0K 0K sleep 0:07 0.01% oracle
10384 oracle 1 59 0 0K 0K sleep 0:11 0.01% oracle
9495 oracle 1 59 0 0K 0K sleep 0:10 0.01% oracle
9447 oracle 11 59 0 0K 0K sleep 0:01 0.01% oracle
17987 fortify 76 59 0 1714M 812M sleep 149:57 0.00% java
24567 oracle 7 59 0 173M 158M sleep 148:45 0.00% emagent
9214 oracle 1 59 0 0K 0K sleep 0:06 0.00% oracle
_______________________________________
_______________________________________
Finally, the results of /usr/sbin/swap -l:
swapfile dev swaplo blocks free
/dev/swap 4294967295,4294967295 16 33554416 33554416
_______________________________________
Edited by: 825118 on May 1, 2013 9:51 AM