Hi Friends,
We have memory free very less in one of our Solaris 10 server running
Oracle 11g R2. The memory free will be sometimes 2 GB out of 24 GB. I was
looking at the memory consumption.
ps -efo pmem,rss,pid,pcpu,args | sort -r | head -20
19.3 4730640 27430 0.0 oracleTBDB (LOCAL=NO)
19.3 4727152 5282 0.0 oracleTBDB (LOCAL=NO)
19.3 4724896 5362 0.0 oracleTBDB (LOCAL=NO)
19.3 4723324 27422 0.0 oracleTBDB (LOCAL=NO)
19.3 4722212 5346 1.0 oracleTBDB (LOCAL=NO)
....
Let's take the first PID =27430. This process was reported to hold 4730640
KB, which means 4.7 GB. I know this includes shared memory.
So I gave a pmap for this.
pmap -x 27430 | grep -v 000
ibsjpsrv3 oracle [TBDB]:pmap -x 27430 | grep -v 000
27430: oracleTBDB (LOCAL=NO)
Address Kbytes RSS Anon Locked Mode Mapped
File
---------------- ---------- ---------- ---------- ----------
total Kb 5592364 4901764 92792 -
pmap -x 27430 | grep shmid
ibsjpsrv3 oracle [TBDB]:pmap -x 27430 | grep shmid
0000000060000000 5244928 4710400 - - rwxs- [
dism shmid=0x14 ]
As per metalink document [ SOLARIS: Determining Background Process Size
using pmap [ID 107750.1]], the memory used by a BG process, here it is not
a BG process but a server connection.
shared memory= 4710400
I was thinking how I can derive memory used by this process alone.
So RSS - shared memory = PGA + (code + data ) ?
If Yes then PGA + (code + data ) = 4901764 - 4710400 = 191364 kb = 186
MB.
Let's see the PGA used by this from database.
SQL> select s.sid from v$session s ,v$process p where p.addr=s.paddr and
p.spid='&1';
Enter value for 1: 27430
old 1: select s.sid from v$session s ,v$process p where p.addr=s.paddr
and p.spid='&1'
new 1: select s.sid from v$session s ,v$process p where p.addr=s.paddr
and p.spid='27430'
SID
----------
520
SQL> SET LINESIZE 145
SQL> SET PAGESIZE 9999
SQL>
SQL> COLUMN sid FORMAT 999 HEADING 'SID'
SQL> COLUMN oracle_username FORMAT a12 HEADING 'Oracle
User' JUSTIFY right
SQL> COLUMN os_username FORMAT a9 HEADING 'O/S
User' JUSTIFY right
SQL> COLUMN session_program FORMAT a18 HEADING 'Session
Program' TRUNC
SQL> COLUMN session_machine FORMAT a8 HEADING
'Machine' JUSTIFY right TRUNC
SQL> COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA
Memory'
SQL> COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA
Memory Max'
SQL> COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA
Memory'
SQL> COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA
Memory MAX'
SQL> SQL> SELECT
2 s.sid sid
3 , lpad(s.username,12) oracle_username
4 , lpad(s.osuser,9) os_username
5 , s.program session_program
6 , lpad(s.machine,8) session_machine
7 , (select ss.value from v$sesstat ss, v$statname sn
8 where ss.sid = s.sid and
9 sn.statistic# = ss.statistic# and
10 sn.name = 'session pga memory') session_pga_memory
11 , (select ss.value from v$sesstat ss, v$statname sn
12 where ss.sid = s.sid and
13 sn.statistic# = ss.statistic# and
14 sn.name = 'session pga memory max') session_pga_memory_max
15 , (select ss.value from v$sesstat ss, v$statname sn
16 where ss.sid = s.sid and
17 sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
18 19 , (select ss.value from v$sesstat ss, v$statname sn
20 where ss.sid = s.sid and
21 sn.statistic# = ss.statistic# and
22 sn.name = 'session uga memory max') session_uga_memory_max
23 FROM
24 v$session s
25 WHERE s.sid=&1
26 ORDER BY session_pga_memory DESC
27 /
Enter value for 1: 520
old 25: WHERE s.sid=&1
new 25: WHERE s.sid=520
SID Oracle User O/S User Session Program Machine PGA Memory PGA
Memory Max UGA Memory UGA Memory MAX
---- ------------ --------- ------------------ -------- --------------
-------------- -------------- --------------
520 APP_USER_MB appuser JDBC Thin Client zone2 76,082,400
229,895,392 68,541,416 215,665,464
Here I got PGA as 76 MB. Can i relate this to my previous calculation
which has PGA + (code + data ) = 186 MB ?
I would like to have a second opinion on this to confirm whether this is
correct, or is there any better way to see / tackle high memory
consumption ?
Cheers,
Sreejith
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1390 / Virus Database: 1520/3851 - Release Date: 08/22/11
