Memory used by a single oracle server process in Solaris

From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>
Date: Tue, 23 Aug 2011 14:51:34 +0530
Message-ID: <OF420DE65A.54F37DC5-ON652578F5.003245FD-652578F5.003369EE_at_ibsplc.com>

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."

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 23 2011 - 04:21:34 CDT