Hi All,
Environment details:
OS Version: SunOS 5.10 Generic_125100-10 sun4u sparc SUNW,SPARC-Enterprise
No of CPU: 8
RAM Size: 32 GB
SWAP Size: 63 GB
Always when i use OS tools like prstat, top i use to find oracle processes are consuming memory 100% as shown below.
prstat -a
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
6806 oracle 8352M 8306M sleep 59 0 0:00:34 6.0% oracle/11
5328 oracle 1698M 1653M sleep 21 0 0:00:45 5.4% oracle/1
28313 oracle 1699M 1653M sleep 39 0 0:01:54 5.1% oracle/1
28316 oracle 8351M 8305M sleep 31 0 0:02:18 4.9% oracle/1
5330 oracle 8350M 8304M cpu8 2 0 0:00:44 4.7% oracle/1
6754 oracle 1699M 1653M sleep 59 0 0:00:16 2.6% oracle/1
20206 oracle 8353M 8307M sleep 59 0 0:00:36 2.1% oracle/11
7621 oracle 1699M 1653M sleep 60 0 0:00:07 1.4% oracle/11
9063 oracle 8352M 8306M cpu2 0 0 0:00:02 1.4% oracle/11
15181 root 12M 7352K sleep 60 0 0:01:03 1.1% sendmail/1
19659 oracle 8350M 8296M sleep 60 0 1:59:52 0.8% oracle/1
15217 root 300M 298M sleep 59 0 0:00:23 0.4% mail.local/1
8948 oracle 1700M 1604M sleep 59 0 14:56:32 0.4% oracle/258
8959 oracle 1700M 1596M sleep 59 0 14:54:54 0.4% oracle/258
11053 oracle 8351M 8300M sleep 59 0 0:00:28 0.4% oracle/1
15733 oracle 8351M 8304M sleep 59 0 0:00:41 0.3% oracle/1
24171 oracle 8351M 8300M sleep 59 0 0:02:22 0.3% oracle/1
3882 root 58M 7488K sleep 59 0 0:57:23 0.2% coda/2
8963 oracle 8360M 8264M sleep 100 - 6:21:45 0.2% oracle/1
1954 root 282M 49M sleep 59 0 2:05:58 0.2% fmd/18
20480 root 11M 4696K sleep 59 0 0:43:16 0.2% bpbkar/11
8971 oracle 8360M 8263M sleep 100 - 5:49:42 0.2% oracle/1
20856 ops 508M 126M sleep 56 1 11:40:13 0.1% java/19
6437 oracle 45M 27M sleep 59 -20 3:41:23 0.1% ocssd.bin/19
6759 oracle 6302M 4267M sleep 59 0 0:01:14 0.1% oracle/1
8966 oracle 1706M 1601M sleep 59 0 3:13:48 0.1% oracle/15
5699 oracle 6343M 4293M sleep 59 0 0:07:07 0.1% oracle/11
8958 oracle 8360M 8260M sleep 49 0 1:44:25 0.1% oracle/1
1914 root 6280K 1152K sleep 59 0 0:46:55 0.1% syslogd/13
7754 oracle 8349M 8299M sleep 59 0 0:00:00 0.1% oracle/1
NPROC USERNAME SIZE RSS MEMORY TIME CPU
659 oracle 4716G 4418G 100% 96:24:22 39%
109 root 1904M 741M 0.0% 19:59:37 2.7%
3 ops 516M 127M 0.0% 11:40:13 0.1%
1 smmsp 7624K 808K 0.0% 0:00:02 0.0%
6 daemon 25M 3992K 0.0% 0:00:07 0.0%
But using top ordered by size column
load averages: 3.65, 3.73, 2.99; up 14+23:28:59 01:21:54
764 processes: 762 sleeping, 2 on cpu
CPU states: 62.9% idle, 29.4% user, 7.7% kernel, 0.0% iowait, 0.0% swap
Memory: 32G phys mem, 8235M free mem, 63G total swap, 63G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
9710 oracle 11 59 0 8379M 8298M sleep 7:14 0.00% oracle
8936 oracle 13 59 0 8374M 8239M sleep 25:30 0.02% oracle
9712 oracle 14 59 0 8364M 8276M sleep 6:58 0.00% oracle
9042 oracle 15 59 0 8363M 8258M sleep 83:30 0.05% oracle
8951 oracle 11 59 0 8360M 8259M sleep 100:26 0.07% oracle
8958 oracle 1 59 0 8360M 8260M sleep 104:26 0.07% oracle
8971 oracle 1 100 -20 8360M 8263M sleep 349:43 0.13% oracle
8963 oracle 1 100 -20 8360M 8264M sleep 381:46 0.14% oracle
10194 oracle 11 59 0 8359M 8301M sleep 54:50 0.35% oracle
9002 oracle 258 59 0 8358M 8256M sleep 13:50 0.01% oracle
9031 oracle 258 59 0 8356M 8252M sleep 13:23 0.00% oracle
9036 oracle 258 59 0 8356M 8251M sleep 13:26 0.00% oracle
9022 oracle 258 59 0 8356M 8251M sleep 13:33 0.00% oracle
9052 oracle 11 59 0 8356M 8287M sleep 15:35 0.00% oracle
As you can see top says out of 32 GB RAM only 8 GB of RAM is free.
I believe all OS tools includes sharable memory and private memory, so how can i get accurate memory consumption by oracle processes. Thought to give a try using script provided by Jonathan Lewis.
As we can see from top that process 9710 is consuming 8379M, so i will find pga and sgs size for accurate memory consumption by this process.
$ ps -ef|grep 9710
oracle 9710 1 0 Dec 06 ? 7:15 ora_arc0_ABCdb1
oracle 22503 22272 0 01:26:23 pts/3 0:00 grep 9710
SQL> sho sga
Total System Global Area 8589934592 bytes
Fixed Size 2043856 bytes
Variable Size 922751024 bytes
Database Buffers 7650410496 bytes
Redo Buffers 14729216 bytes
SQL> sho parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1628M
SQL> select spid, sid, s.serial#, p.program from v$session s, v$process p where paddr=addr and spid=9710;
SPID SID SERIAL# PROGRAM
------------ ---------- ---------- ------------------------------------------------
9710 529 3 oracle@ABC01p (ARC0)
SQL> @"mem.sql" 529
old 9: and ss.sid = &m_sid
new 9: and ss.sid = 529
NAME VALUE
---------------------------------------- ----------------
session uga memory 158,568
session uga memory max 158,568
session pga memory 23,318,760
session pga memory max 23,318,760
old 16: where sid = &m_sid
new 16: where sid = 529
CATEGORY ALLOCATED USED MAX_ALLOCATED
---------- ---------------- ---------------- ----------------
PL/SQL 2,088 224 2,088
Other 46,204,337 46,204,337
old 12: where sid = &m_sid
new 12: where sid = 529
PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------------- ---------------- ---------------- ----------------
22,496,153 46,206,425 0 46,206,425
So process 9710 is consuming 8 GB of SGA and PGA_MAX_MEM of 46,206,425 bytes = 8000 MB + 44 MB = 8044MB ??????
Then how come TOP tool is showing 8379M from where does this 8379 - 8044 = 335 MB came from ????
Will be thankful if anyone can provide me the proper way of monitoring memory consumption by oracle processes, as sometimes DBA get alerts about high memory consumption on the server.
-Yasser