Memory used by a single oracle server process in Solaris

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

Other related posts: